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:

PURGE RECYCLEBIN

You could log in as SYSDBA and run:

PURGE DBA_RECYCLEBIN

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.

Jeffrey Kemp 9 August 2010

SQL / tips-&-tricks /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.