Skip to content

Purge all Recyclebins without SYSDBA

August 9, 2010

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.

About these ads

From → SQL

2 Comments
  1. 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).

  2. Thanks Timur, good point.

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

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 216 other followers

%d bloggers like this: