Oracle Recycle bin

Problem statement:

This morning I dropped close to 100GB of unnecessary data from one of my non production systems and was hoping SUM(BYTES) from dba_segments will show 100GB lesser than previous check.

But to my surprise, SUM(BYTES) was showing the same size even after dropping tables worth of 100GB.

Reason:

The reason is RECYCLE BIN, which is ON by default.

SQL> show parameter recyclebin

NAME TYPE VALUE
———————————— ———– ————————–
recyclebin string on

 

When I dropped those tables, DBMS doesn’t immediately remove the space allocated to these tables. Instead, instance renamed these tables and kept in recycle bin. This looks like a nice feature, because if we drop a table by mistake then we can reclaim the table back from recycle bin. This feature is called Flashback Drop, and FLASHBACK TABLE statement is used to restore the table.

To purge entire recycle bin (i.e., recycle bin for all users), you need to run below statement:

SQL> purge dba_recyclebin;

Note 1: How older data you can recover is depends upon db_flashback_retention_target parameter. This parameter is in minutes. By default 24 hours.

SQL> show parameter retention

NAME TYPE VALUE
———————————— ———– ——————-
db_flashback_retention_target integer 1440

Note 2: How to drop a table with out keeping in recycle bin

Example: DROP TABLE <SCHEMA_NAME>.<TABLE_NAME> purge;

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: