Going Green with the Oracle Recyclebin

I’ve been seeing rather a lot of Chris Hemsworth lately…in more ways than one.
My most recent trip to the local Cinema saw him reprising the role of Thor, or “Phwoarrr !” as Deb insists on calling him.
No spoilers, but let’s just say that the scene with the topless blonde was not all I’d hoped for.
Not that I feel the need to compete but, like Chris, I can also do my bit to save the planet, courtesy of a bit of recycling.

Once upon a time, when you issued a DROP TABLE command, the table, together with it’s associated indexes and triggers, was wiped from the face of your database, as if it had never existed.
Of course, if you subsequently decided that you shouldn’t have dropped the table, your options were limited to re-creating it (and the data, indexes etc) by hand, or going through the fun and frolics of a point-in-time recovery.
Since 10g however, things have been a bit different.

What is the Recyclebin

Essentially, the recyclebin is a way of Oracle keeping track of tables that have been dropped but will only be deleted when the database needs the space.
The space taken up by these objects is considered as free space by the database and reported as such. Having said that, they do still show up in _SEGMENTS views.
In the event that the tablespace is full, Oracle will automatically start to delete objects that are in that tablespace’s recyclebin.
Tables can be recovered from the recyclebin and restored to their original state – more-or-less – by means of the Flashback Drop functionality.

Flashback Drop is standard out-of-the-box functionality on all Oracle Editions.

Before we start…

It’s probably worth checking that your recyclebin is ready for use.
By default, the recyclebin is enabled. This is set by means of the init.ora parameter recyclebin.
This parameter can be modified at session level so, to check that it is enabled …

SELECT display_value
FROM v$parameter2
WHERE name='recyclebin'
/

If the value is not ON, then you can set it like this :

ALTER SESSION SET recyclebin=on
/

One more thing to check. Remember that the recyclebin only works for objects created in non-system, locally managed tablespaces.
So, you need to either specify such a tablespace when creating a table, or ensure that your default tablespace fits the bill.
To check this :

SELECT ts.tablespace_name, ts.extent_management
FROM user_tablespaces ts, user_users usr
WHERE ts.tablespace_name = usr.default_tablespace
/

Hopefully, you’ll get something like :

TABLESPACE_NAME  EXTENT_MANAGEMENT
---------------- -----------------
USERS            LOCAL

If the tablespace name you get back from this query is SYSTEM or (less likely) SYSAUX then you’ll need to specify another tablespace when creating your tables. Incidentally, you’ll probably also want to let your DBA know.
The same applies if the extent management for your default tablespace is set to DICTIONARY.

The joys of Recycling

Right, now that’s out of the way, we need to find an example.
In the spirit of this post, I’ve decided to reuse something that’s already there, rather than making a brand-new example…

CREATE TABLE my_depts AS
    SELECT * FROM hr.departments
/
--
-- Now add an index...
--
ALTER TABLE my_depts ADD CONSTRAINT dept_pk PRIMARY KEY( department_id)
/
--
-- solely for the purposes of this example, the trigger
--
CREATE OR REPLACE TRIGGER dept_bi_trg
    BEFORE INSERT ON my_depts
    FOR EACH ROW
BEGIN
    :new.department_name := 'ECO-'||:new.department_name;
END;
/

…and now drop the lot….

DROP TABLE my_depts
/

Dumpster Diving

Now let’s see if we can find our table in the recyclebin …

SELECT object_name
FROM recyclebin
WHERE original_name = 'MY_DEPTS'
AND type = 'TABLE'
/

Run this and we should get something like :

OBJECT_NAME
------------------------------
BIN$6pbQ4NqXxL/gQAB/AQEKYg==$0

We can still query this table, using the object_name assigned to it in the recyclebin.
NOTE – you need to enlose the table name in double-quotes :

SELECT department_id, department_name
FROM "BIN$6pbQ4NqXxL/gQAB/AQEKYg==$0"
/

It’s not just the table that’s been recycled, the trigger and index are also there …

SELECT object_name, original_name, type
FROM recyclebin
WHERE base_object = 
    (
        SELECT purge_object
        FROM recyclebin
        WHERE original_name = 'MY_DEPTS'
        AND type = 'TABLE'
    )
ORDER BY purge_object
/

This query returns :

OBJECT_NAME                     ORIGINAL_NAME  TYPE
------------------------------  -------------  -------
BIN$6pbQ4NqXxL/gQAB/AQEKYg==$0  MY_DEPTS       TABLE
BIN$6pbQ4NqWxL/gQAB/AQEKYg==$0  DEPT_BI_TRG    TRIGGER
BIN$6pbQ4NqVxL/gQAB/AQEKYg==$0  DEPTTPK        INDEX

Fun with Flashback Drop

If you decide that you’ve been a bit hasty and would like to bring the table back, you can simply issue the
following command :

FLASHBACK TABLE my_depts TO BEFORE DROP
/

Once you get the flashback complete message, my_depts has been magically restored…

SQL> SELECT COUNT(*)
  2  FROM my_depts
  3  /

  COUNT(*)
----------
	27

SQL> 

…as is the trigger…

INSERT INTO my_depts( department_id, department_name)
VALUES(300, 'WARRIORS')
/

1 row created

SELECT department_name
FROM my_depts
WHERE department_id = 300
/

DEPARTMENT_NAME
------------------------------
ECO-WARRIORS

….and the index…

INSERT INTO my_depts( department_id, department_name)
VALUES(300, 'WASTE AND RECYCLING')
/

INSERT INTO my_depts( department_id, department_name)
*
ERROR at line 1:
ORA-00001: unique constraint (MIKE.BIN$6pbQ4NqUxL/gQAB/AQEKYg==$0) violated

Wacky object names

Hang on a minute – that unique constraint name looks a bit odd…

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'MY_DEPTS'
/

CONSTRAINT_NAME 	       C
------------------------------ -
BIN$6pbQ4NqTxL/gQAB/AQEKYg==$0 C
BIN$6pbQ4NqUxL/gQAB/AQEKYg==$0 P

So, although the constraints have been restored, they’ve retained their recyclebin names.
This also applies to the associated index…

SELECT index_name
FROM user_indexes
WHERE table_name = 'MY_DEPTS'
/

INDEX_NAME
------------------------------
BIN$6pbQ4NqVxL/gQAB/AQEKYg==$0

…and to the trigger…

SELECT trigger_name
FROM user_triggers
WHERE table_name = 'MY_DEPTS'
/

TRIGGER_NAME
------------------------------
BIN$6pbQ4NqWxL/gQAB/AQEKYg==$0

Unless you follow a rather unusual naming convention, this is probably not ideal.
It’s probably a good idea therefore, to make a note of the original_name values in the recyclebin of all of the objects associated with a table you’re going to recover.
You can then go and rename them once they are restored…

ALTER INDEX "BIN$6pbQ4NqVxL/gQAB/AQEKYg==$0" RENAME TO dept_pk;
ALTER TRIGGER "BIN$6pbQ4NqWxL/gQAB/AQEKYg==$0" RENAME TO dept_bi_trg;

The constraints are slightly different in that they will not have been held as distinct objects in the recyclebin.
This means you’ll have to use whatever naming convention you have in place to reset them to a “sensible” name.

We know that one of these constraints is the Primary Key (constraint_type = ‘P’ in user_constraints) so this should be simple enough :

ALTER TABLE my_depts RENAME CONSTRAINT "BIN$6pbQ4NqUxL/gQAB/AQEKYg==$0" TO dept_pk
/

For the check constraint (constraint_type = ‘C’) we’ll need a bit more info :

SELECT search_condition
FROM user_constraints
WHERE constraint_name = "BIN$6pbQ4NqTxL/gQAB/AQEKYg==$0"
/

SEARCH_CONDITION
----------------------------------------

"DEPARTMENT_NAME" IS NOT NULL

Now we know what the constraint is doing, we could rename it to something a bit more relevant :

ALTER TABLE my_depts RENAME CONSTRAINT "BIN$6pbQ4NqTxL/gQAB/AQEKYg==$0" TO dname_nn

Cleaning out the recyclebin

If you decide that you really don’t want to restore a given table, you can remove it ( and it’s dependent objects) from the recyclebin.
This will work with either the original table name or the current recyclebin name.
It’s worth bearing in mind that the same object can be in the recyclebin multiple times (e.g. the same table dropped, created and then dropped again). In these circumstances, specifying the original name will cause only the oldest copy of the object to be purged.
Therefore, it’s probably safer to use the recyclebin name.

I’ve now dropped the table again, which generates a slightly different recyclebin name :

DROP TABLE my_depts
/

SELECT object_name, original_name, type
FROM recyclebin
WHERE base_object = 
    (
        SELECT purge_object
        FROM recyclebin
        WHERE original_name = 'MY_DEPTS'
        AND type = 'TABLE'
    )
ORDER BY purge_object
/

OBJECT_NAME                    ORIGINAL_NAME  TYPE                    
------------------------------ -------------- ---------
BIN$6tSA+jKDwI7gQAB/AQEM8A==$0 MY_DEPTS       TABLE                     
BIN$6tSA+jKBwI7gQAB/AQEM8A==$0 DEPT_PK        INDEX                     
BIN$6tSA+jKCwI7gQAB/AQEM8A==$0 DEPT_BI_TRG    TRIGGER  

Now, the safest way to remove this table, and it’s dependent objects, from the recyclebin is to do the following :

PURGE TABLE "BIN$6tSA+jKDwI7gQAB/AQEM8A==$0"
/

table purged

For the current user, we can cleardown the recyclebin altogether with

PURGE RECYCLEBIN

To cleardown the recyclebin for the entire database, you need to be connected as SYSDBA :

PURGE dba_reyclebin

Avoiding the recyclebin completely

If you want to avoid sending a particular table to the recyclebin altogether, you can simply append PURGE to your DROP TABLE statement like this :

DROP TABLE my_depts PURGE
/

Alternatively, to simply disable the recyclebin for the current session, you can issue :

ALTER SESSION SET recyclebin = off
/

There you have it, all good wholesome planet-saving stuff without the need to (un)dress like a Norse God of Thunder.

About these ads

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