• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Understanding Flashback Table Feature in Oracle Database

by admin

Flashback Query concept was introduced in Oracle9i. But it can’t flash back DDL operations such as dropping a table. Flashback Table feature in Oracle 10g makes the revival of a dropped table as easy as the execution of few statements.

Let’s see how Flashback Table Works. Let’s first create a test table named “TESTFLASH” and insert some data into it.

SQL> Create table TESTFLASH (id number);
SQL> Insert into TESTFLASH values (1);
SQL> Insert into TESTFLASH values (2);
SQL> Insert into TESTFLASH values (3);
SQL> Insert into TESTFLASH values (4);
SQL> Insert into TESTFLASH values (5);
SQL> Commit;
SQL> select * from TESTFLASH;

        ID
----------
         1
         2
         3
         4
         5
         
5 rows selected.

1. Let’s see the table in the present schema.

SQL> select * from tab;

TNAME                    TABTYPE  CLUSTERID
------------------------ ------- ----------
TESTFLASH                TABLE

2. Now, we accidentally drop the table:

SQL> drop table TESTFLASH;

Table dropped.

3. Let’s check the status of the table now.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

The table TESTFLASH is gone but note the presence of the new table BIN$04LhcpndanfgMAAAAAANPw==$0. The dropped table TESTFLASH, instead of completely disappearing, was renamed to a system-defined name. It stays in the same tablespace, with the same structure as that of the original table. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$04LhcpndanfgMAAAAAANPw==$0, preserving the complete object structure of the dropped table.

The table and its associated objects are placed in a logical container known as the “RECYCLE BIN,” which is similar to the one in your PC. However, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. The RECYCLE BIN is merely a logical structure that catalogs the dropped objects. Use the following command from the SQL*Plus prompt to see its content (you’ll need SQL*Plus 10.1 to do this):

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ ------------------
TESTFLASH        BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE        2004-02-16:21:13:31

This shows the original name of the table, TESTFLASH, as well as the new name in the recycle bin (BIN$04LhcpndanfgMAAAAAANPw==$0). Use the FLASHBACK TABLE command to reinstate the table.

SQL> FLASHBACK TABLE TESTFLASH TO BEFORE DROP;

FLASHBACK COMPLETE.

Now query the TESTFLASH table:

SQL> select * from TESTFLASH;

        ID
----------
         1
         2
         3
         4
         5
         
5 rows selected.

4. Let’s see the table in the present schema.

SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TESTFLASH                      TABLE

5. The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.

SQL> Show Recyclebin
SQL>

Filed Under: oracle

Some more articles you might also be interested in …

  1. Types of Patches in Oracle Apps 11i/R12
  2. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
  3. Beginners Guide to Automatic Storage Management (ASM)
  4. Empty Directories in the Flash Recovery Area (FRA) are not deleted
  5. How to Connect to an Oracle Pluggable Database (PDB)
  6. Oracle database – How to create pfile or spfile using the current parameters
  7. Understanding Oracle Database Automatic SGA Memory Tuning
  8. Oracle 12.2 : RMAN Cross-Platform Transport of PDB into Destination CDB
  9. Oracle 12c: Migrate non-CDB and Convert to a PDB using 12c RMAN Active Database Duplication
  10. Understanding Transparent Data Encryption and Keystores in RAC

You May Also Like

Primary Sidebar

Recent Posts

  • “aws s3 mv” Command Examples
  • “aws s3 mb” Command Examples
  • “aws s3 ls” Command Examples
  • “aws s3 cp” Command Examples

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright