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 /
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
To cleardown the recyclebin for the entire database, you need to be connected as SYSDBA :
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.