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.

Forms Library: PKG_DEBUG
Directory File List in PL/SQL (Oracle 10g)

Comments

  1. Timur Akhmadeev
    9 August 2010 - 4:04 pm

    Jeff,

    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).

    • Thanks Timur, good point.

      SYSTEM has the DBA role by default so it automatically has DROP ANY TABLE privilege.

Leave a Reply

Your email address will not be published / Required fields are marked *