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.