Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

Recycle Bin Tips

Oracle Tips by Burleson

Oracle  Flashback Drop

Prior to Oracle , a DROP command permanently removed objects from the database.  In Oracle , a DROP command places the object in the recycle bin.  The extents allocated to the segment are not reallocated until you purge the object.  You can restore the object from the recycle bin at any time.

This feature eliminates the need to perform a point-in-time recovery operation.  Therefore, it has minimum impact to other database users.

Recycle Bin

A recycle bin contains all the dropped database objects until,

  • You permanently drop them with the PURGE command.
     

  • Recover the dropped objects with the UNDROP command.
     

  • There is no room in the tablespace for new rows or updates to existing rows.
     

  • The tablespace needs to be extended.

You can view the dropped objects in the recycle bin from two dictionary views:

  • user_recyclebin – lists all dropped user objects
     

  • dba_recyclebin – lists all dropped system-wide objects

  • Example 1: Dropping an Object

    In the example below, the name of the object is changed when it is dropped and moved to the recycle bin.  The recycle bin also keeps the original name of the object.  This feature allows you to create a new object of the same name and then drop it again.

    SQL> create table test (col_a varchar(4));

    Table created.

    SQL> select object_name, original_name, type
         from   user_recyclebin;

    no rows selected

    SQL> drop table test;

    Table dropped.

    SQL> select object_name, original_name, type
         from   user_recyclebin;

    OBJECT_NAME           ORIGINAL_NAME      TYPE
    --------------------- ----------------   ------------------
    RB$$42513$TABLE$0     TEST               TABLE

     

    SQL> create table test (col_b varchar(4));

    Table created.

    SQL> select object_name, original_name, type
         from   user_recyclebin;

    OBJECT_NAME           ORIGINAL_NAME      TYPE
    --------------------- ----------------   ------------------RB$$42513$TABLE$0     TEST               TABLE

     

    SQL> drop table test;

    Table dropped.

    SQL> select object_name, original_name, type from user_recyclebin;

    Example 1: Dropping an Object

    OBJECT_NAME              ORIGINAL_NAME      TYPE
    ------------------------ ----------------   ------------------
    RB$$42513$TABLE$0        TEST               TABLE
    RB$$42514$TABLE$0        TEST               TABLE 

    Example 2: Restoring a Dropped Object

    This example will restore a dropped table test.

    SQL> flashback table RB$$42514$TABLE$0 to before drop;

    Flashback complete.       

    Example 3: Dropping a Table Permanently

    This statement puts the table in the recycle bin:

    SQL> drop table purge test;

    This statement removes the table permanently:

    SQL> purge table RB$$42514$TABLE$0;
    Table purged.       
         

    Example 4: Dropping a Tablespace

    You can only issue this command when the tablespace users is empty. Objects in the recycle bin of tablespace users will be purged:

    SQL> drop tablespace users;

    When you issue this command, objects in the tablespace users are dropped.  They are not placed in the recycle bin.  Any objects in the recycle bin belonging to the tablespace users are purged.

    SQL> drop tablespace users including contents;

    Example 5: Purging the Recycle Bin

    This statement purges the user recycle bin:

    SQL> purge recyclebin;

    Recyclebin purged.

    This statement removes all objects from the recycle bin:

    SQL> purge dba_recyclebin;

    DBA Recyclebin purged.

    Oracle  Example 5: Purging the Recycle Bin

    This statement purges all objects from tablespace users in the recycle bin:

    SQL> purge tablespace users;

    Tablespace purged.

     

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks