Oracle Illustrated

Feb 21 2010   8:02AM GMT

Migrating from 9i to 11g – Purge & Recyclebin

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

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;

—————————— ——————————– ———

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.

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:

Share this item with your network: