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.