The Recycle Bin

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

The Recycle Bin

Rating:

Introduction

The recycle bin is a feature in Oracle 10g that assists the database user and the DBA. Database users now do not have to pay attention to the operations that were unwillingly performed, as Oracle 10g provides a facility to recover the dropped database objects at any later desired stages.

Apart from media failure, the major cause of application outage can be accounted on part of human errors. Human errors are inevitable and cannot be avoided. If a user mistakenly drops any database object, he can recover the same at any later stages from the recycle bin. Dropping the incorrect database object has always been a major cause of concern for database users and DBA's. The recycle bin provides a way out to resolve this issue.

The Flashback Drop (recycle bin) operation is one among the seven Flashback recovery techniques.

The remaining six are listed below:

• Flashback query
• Flashback version query
• Flashback transaction query
• Flashback table
• Flashback database
• Flashback query function

One of the major tasks of DBA includes the optimum utilization of the space within a database. This can be accomplished by keeping track of the space used and the free space of a database.

The recycle bin plays a pivotal role during backup and recovery operations on a database.

Definition of the Recycle Bin

The recycle bin is a logical/virtual storage space that is used to store dropped database objects. Oracle manages the recycle bin automatically as far as the storage of dropped objects and their permanent removal are concerned.

Overview of the Recycle Bin

In a database environment, a recycle bin is assigned for each and every database user.

If a database user needs to view the dropped objects, then he can simply issue the SHOW RECYCLEBIN command.

When the SHOW RECYCLEBIN command is issued, the result is displayed in various columns, such as the original table name, their recycle bin names, and the time when they were dropped.

Note: When a database user feels the need to have detailed information, then the USER_RECYCLEBIN view or the DBA_RECYCLEBIN global view can be queried.
An example to show how the above-mentioned views are used is as follows:

SELECT owner, original_name, type, droptime, can_undrop, space from dba_recyclebin;

A database object should be purged in order to remove it permanently from the recycle bin. For this purpose, the PURGE command is used. As required, the PURGE command can be used in various ways. Some of them are described below:

• DROP TABLE <table_name> PURGE: It simply drops the schema, but the space occupied is not released. The table is not moved to the recycle bin.
• PURGE TABLE <table_name>: It simply removes the table from the recycle bin, and if there exists several database objects having the same name, the oldest one is purged.
• PURGE index<index_name>: It removes an index from the recycle bin.
• PURGE tablespace <tablespace_name>: It removes all the dropped database objects from the tablespace.
• PURGE tablespace <tablespace_name> user <user_name>: It removes all the dropped objects of a user from the tablespace.
• PURGE user_recyclebin: It removes all the dropped objects i.e., all the database objects present in the recycle bin.
• PURGE dba_recycle bin;: It simply removes all the database objects present in the recycle bin, provided the user performing these operations has the DBA privileges.

Some important points to know about the recycle bin

• When any database object is recovered from the recycle bin, then all its dependent objects are also recovered.
• To view, restore, and purge database objects owned by some other users, one must login and connect as SYSDBA.
• A database user can view those database objects in the recycle bin that are owned by him by using the SELECT * FROM RECYCLEBIN; command.
• The system generates a unique name for every database object whenever it is moved to the recycle bin.
This reduces a name conflict that may arise in case a user drops a table and re-creates a table with the same name and drops it again or two users own a database object with the same name and drop them.
• The naming convention used by the recycle bin is BIN/BIN, where unique_id/globalUID is the global unique identifier for the database object of 26 characters. Here, unique_id/globalUID makes the name of the database object unique, and version is the version number assigned by Oracle.

How to enable and disable the Recycle Bin

Note: The recycle bin is by default enabled, but it can be disabled by using a few hidden parameters.

The recycle bin initialization parameter helps in enabling and disabling the recycle bin.
The recycle bin can be disabled and enabled as follows:

• ALTER SESSION SET recycle bin = OFF;
• ALTER SYSTEM SET recycle bin = OFF;
and
• ALTER SESSION SET recycle bin = ON;
• ALTER SYSTEM SET recycle bin = ON;

The database objects present in the recycle bin are in an ambiguous state, which means that Oracle automatically overwrites the space occupied by these database objects by any active database object that requires the space.

When a database object is dropped, Oracle does not remove the space associated with that database object. Rather, it renames the object and moves it to the recycle bin. The dropped database objects continue to occupy the space unless they are purged.

The database objects that are present in the recycle bin for a longer period are deleted automatically by Oracle. The recycle bin follows the FIFO order and the FIFO order implies that the recently dropped database objects are not valid for a Flashback operation. The Flashback operation implies a group of features, and allows users to view the pre-image of database objects, or to return database objects to a previous state.

How to regain the space from the recycle bin

The following example describes how one can regain the space occupied by the database objects in the recycle bin:

Consider the case in which a tablespace named abc of 2MB is entirely occupied by a table named xyz. In such a case, when the DBA_FREE_SPACE parameter is queried against, it shows that no space is available in the abc tablespace. Now, when a database user has dropped the table, the DBA_FREE_SPACE parameter will show that the entire tablespace is empty.

Note: This is not the real picture. Querying against DBA_SEGMENTS will reflect the real picture of the space, free as well as occupied, in the recycle bin.

The syntactic version of the above situation is as follows:

SELECT sum(bytes) FROM dba_free_space WHERE tablespace_name = 'abc';

Result:

SUM(BYTES)
-------------------

SELECT segment_name, bytes FROM dba_segments WHERE tablespace_name = 'abc';

Result:

SEGMENT_NAME                            BYTES
--------------------------               -------------
XYZ                                               1966080

DROP table xyz;

Result:

Table dropped.

Now, to gain the real picture of the used and free space, follow the syntax given below:

SELECT sum(bytes) FROM dba_free_space WHERE tablespace_name = 'abc';

Result:

SUM(BYTES)
-------------------
1966080

SELECT segment_name, bytes FROM dba_segments WHERE tablespace_name = 'abc';

Result:

SEGMENT_NAME                                                BYTES
-------------------------                                       -----------
BIN jvmUwa= =$0         1966080

Note:

• The names of the database objects in the recycle bin are always 30 characters long.
• The recycle bin does not possess a fixed amount of pre-allocated space.
• The recycle bin does not preserve the referential constraints, if any, on a table. A user needs to re-create the referential constraints after the table has been recovered from the recycle bin.

This article has described the recycle bin and thrown some light on the various operations performed in it.

Rating: