Friday, August 15, 2008

Reclaim space by purging the DBA Recycle Bin

SQL> select count(*), sum(space) from dba_recyclebin;

COUNT(*) SUM(SPACE)
---------- ----------
187 85504

purge dba_recyclebin;


# Flashback a dropped table.

select type, object_name, original_name from recyclebin;


TYPE OBJECT_NAME
------------------------- ------------------------------
ORIGINAL_NAME
--------------------------------
TABLE BIN$X/qPdSN0kATgQNwKilpFfQ==$0
X

TABLE BIN$YRYsJpVJzxngQNwKilpz2Q==$0
ADDRESS

INDEX BIN$YRYsJpVIzxngQNwKilpz2Q==$0
IDX_ADDR_DELETED

INDEX BIN$YRYsJpVHzxngQNwKilpz2Q==$0
IDX_ADDR_CITYSTATE

INDEX BIN$YRYsJpVGzxngQNwKilpz2Q==$0
IDX_ADDR_ADDRESS

INDEX BIN$YRYsJpVFzxngQNwKilpz2Q==$0
IDX_ADDR_ADDRESSID


flashback table address to before drop;