Oracle – for when it was like that when you got there

Main menu

Post navigation

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 :

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…

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…

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 :

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 :