Have your recyclebin being full/big but don’t want to make a complete “PURGE RECYCLE BIN;”?
Want remove only older than x days (like older than 30 days)?
DELETE from DBA_RECYCLEBIN where droptime<sysdate-30;
PURGE DBA_RECYCLEBIN where droptime<sysdate-30;
A good way to perform that is to execute output from:
# 30 Days
select 'purge table '||owner||'."'||OBJECT_NAME||'";' from dba_recyclebin where type='TABLE' and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-30;
# 30 minutes
select 'purge table '||owner||'."'||OBJECT_NAME||'";' from dba_recyclebin where type='TABLE' and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-(30/(24*60));
There is some reference here too:
How To Drop / Delete / Purge Recyclebin or DBA_RECYCLBIN For Objects Older Than x Days/minutes or selective purge of objects from recycle bin (Doc ID 1596147.1)
And here you can see an awesome procedure in case you want to automate that in a job or similar:
Hope it help you!
Struggling with that, right?
As you know, in Oracle Database 9i the view V$SESSION doesn’t have SQL_ID column…
So how to map SQLs in my database? And, for example, how to get the SQLs causing a lock?
In the end of the day, the SQL_ID is only a representation of the hash_value of an SQL. You can even make the translation from SQL_ID to Hash Value as you can check on this post by Tanel Poder.
Ok, but I have to map which sql is causing the lock in my 9i database, how can I do that?
Here it goes:
If session status is ACTIVE:
SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.SQL_ADDRESS = s1.ADDRESS
If session status is INACTIVE:
SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.prev_sql_addr = s1.ADDRESS
You’re welcome! 😉
See you next week!
Have you ever killed a session in database and the session doesn’t disappear? Or yet, you killed the SPID of the session in OS and the session still running in database, but now you cannot see the SPID?
Very weird, but it happens. First of all, let us point some interesting items: