Oracle Illustrated

Feb 21 2010   8:02AM GMT

Migrating from 9i to 11g – Purge & Recyclebin



Posted by: Lakshmi Venkatesh
Tags:

From Oracle 10g if we try to drop a table when the recycle mode is ON then it just gets renamed – and does not gets deleted. So, the space occupied doesn’t get purged.

DROP TABLE test_readonly;

SHOW recyclebin

– Note when a table is dropped it is just renamed. It is not droped when the RECYCLE mode is ON. So the space occupied is there as it is.

SQL> select object_name, original_name, operation from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION
—————————— ——————————– ———
BIN$uCJ5kSosTM+s7chj6cJeGQ==$0 TEST_READONLY DROP
BIN$wl9CUjW7RsapzkrsoHDm+w==$0 TEST_READONLY DROP

Note: TEST_READONLY was dropped, recreated then dropped again.

To drop a table without storing it in recyclebin -

SQL> DROP TABLE test_Readonly purge;

Table dropped.

Elapsed: 00:00:00.40
SQL> select * from user_recyclebin;

no rows selected

Elapsed: 00:00:00.00

To purge the table that was dropped

PURGE TABLE β€œBIN$wl9CUjW7RsapzkrsoHDm+w==$0”

Table purged.

Elapsed: 00:00:02.92

To purge the recyclebin

PURGE recyclebin;

Recyclebin purged.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: