Purge all Recyclebins without SYSDBA
Want to purge all the recyclebins for all users in your database?
Can’t (or don’t want to) log in as each user individually to run this, one by one:
You could log in as SYSDBA and run:
But, what if you don’t have SYSDBA access? (Yes, this has happened to me, with a client-supplied VM – they told me the SYSTEM password but not the SYS password)
You can use the PURGE TABLESPACE x USER y option – run this to generate the commands to run, then run the output as a script:
SELECT DISTINCT REPLACE(REPLACE('PURGE TABLESPACE "#TS#" USER "#OWNER#";' ,'#TS#',ts_name) ,'#OWNER#',owner) FROM dba_recyclebin WHERE ts_name IS NOT NULL;
PURGE TABLESPACE "USER_DATA" USER "SCOTT"; PURGE TABLESPACE "USER_DATA" USER "LARRY";
WARNING: this command will result in irreversible loss of data for all users on your database.
Tested on Oracle 10gR2.
9 August 2010 - 4:04 pm
but you still need DROP ANY TABLE/INDEX/etc privilege to run this statement, so strictly speaking you can’t clean out recycle bin without proper privileges (it’s not clear from your post).
9 August 2010 - 6:31 pm
Thanks Timur, good point.
SYSTEM has the DBA role by default so it automatically has DROP ANY TABLE privilege.