LLI Wiki — My Digital Knowledge Base

Oracle: inspect and purge the recycle bin

Check how many objects and how much space the recycle bin holds, purge it, and disable it for new sessions.

When a table is dropped without PURGE, Oracle keeps it in the recycle bin, where it still uses space. The commands below show what the recycle bin holds, purge it, and disable it.

Check the recycle bin

show parameter recyclebin;
select count(*) from dba_recyclebin;
select min(CREATETIME) from dba_recyclebin;

The last query gives the date of the oldest dropped object.

Objects of the current user:

show recyclebin
select * from user_recyclebin;

All users (from a DBA account):

select * from dba_recyclebin;

The current-user view shows the same columns for the current user:

select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;

Purge

PURGE TABLE test;          -- one object of the current user
PURGE RECYCLEBIN;          -- all objects of the current user
PURGE DBA_RECYCLEBIN;      -- all dropped objects of all users (DBA account)

To drop a table without sending it to the recycle bin:

DROP TABLE test PURGE;

Follow a long purge

Compare the space still used by the recycle bin a few minutes apart:

select sum(SPACE) from DBA_RECYCLEBIN;

To follow the purging session, check its progress in V$SESSION_LONGOPS:

select SOFAR, TOTALWORK, UNITS, TIME_REMAINING
from V$SESSION_LONGOPS
where SID = <sid> and SERIAL# = <serial#>;

Disable the recycle bin

ALTER SYSTEM SET recyclebin = OFF DEFERRED;

A plain alter system set recyclebin=off fails with ORA-02096, because the parameter cannot be changed dynamically. DEFERRED applies the value to new sessions. Start a new session and check it:

show parameter recyclebin

The alternative ALTER SYSTEM SET recyclebin = OFF SCOPE=SPFILE takes effect at the next instance restart.

Notes

Mastodon LLIMastodon SocialFosstodon