Systems Engineering and RDBMS

Recovering a dropped table using Flashback feature

Posted by decipherinfosys on July 23, 2007

Prior to Oracle 10g, it was not possible to recover the table back once it is dropped. Oracle introduced new feature ‘Oracle Flashback Table’, one of the many useful features. Using this new feature we can recover a dropped table to its earlier state with a single statement.

First let us create an empty table and populate it with some data. If you already have table with the same name, change all the occurrences of the table name with some other name.

CREATE TABLE TEST
(
TEST_ID NUMBER(9) NOT NULL,
TEST_DATE DATE,
TEST_INDIC NUMBER(1) DEFAULT 0 NOT NULL,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
— TABLESPACE Clause
/

CREATE SEQUENCE TEST_SEQ
START WITH 1
CACHE 100
/

CREATE INDEX TEST_IND_1 ON TEST(TEST_DATE)
— TABLESPACE CLAUSE
/

CREATE OR REPLACE TRIGGER TIB_TEST
BEFORE INSERT ON TEST FOR EACH ROW
BEGIN
IF :NEW.TEST_ID IS NULL THEN
SELECT TEST_SEQ.NEXTVAL INTO :NEW.TEST_ID
FROM DUAL;
END IF;
END;
/

INSERT INTO Test(test_date) VALUES(sysdate);
INSERT INTO Test(test_date) VALUES(sysdate);

In order to recover table back, we need to make sure that recyclebin parameter is on. You can read more on recycle bin in our previous post here. Run following SQL to see the current value of parameter.

SQL>show parameter recyclebin

NAME TYPE VALUE
———————————— ———– ——————————
recyclebin string OFF

Recyclebin parameter value is set to off, so we need to turn it on first. We can do it either at session level or at system level. It is a dynamic parameter so there is no need to stop and start the database after changing its value. We will set it at a system level. Alternatively we can put it in the init<sid>.ora file as well.

SQL> ALTER SYSTEM SET recyclebin = ON;

Flashing back to before drop

Before drop syntax allows to restore the table in a state just before the table was dropped. We already created table previously. Let us check it first and then drop it.

SQL>select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
TEST TABLE

Now let us drop the table and then again run the same query.

SQL>drop table test;
Table dropped.
SQL>select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
BIN$77qGCfjISXWU+Zmy+f5GFQ==$0 TABLE

Even though we have dropped the table, table is not actually dropped. Instead it is renamed with system generated name and is kept in recyclebin. You can view details either from user_recyclebin view or recyclebin synonym. It also renames any indexes and triggers of the table. Below query shows the original name and new name of all the objects which were dropped.

SQL> select original_name, type, object_name from recyclebin;

ORIGINAL_N TYPE OBJECT_NAME
———- ————————- ——————————
TEST TABLE BIN$77qGCfjISXWU+Zmy+f5GFQ==$0
PK_TEST INDEX BIN$eYaVVVT7Q2eGx2qr1f+Nhg==$0
TEST_IND_1 INDEX BIN$ALBfM1WhRPKBrsAkaHmQNA==$0
TIB_TEST TRIGGER BIN$8UeSfnDBQtuinSD2vUFF7g==$0

Now let us try to recover the table. Issue following command to revive the table.

SQL> flashback table test to before drop;
Flashback complete.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
TEST TABLE

Table is now back entirely including trigger and indexes along with data. Flashback command renames the table to its original name but does not rename the indexes, triggers or constraints. We need to manually rename them. Get the object name and its original name from the query on the previous page.

SQL> alter trigger “BIN$8UeSfnDBQtuinSD2vUFF7g==$0” rename to TIB_TEST;
Trigger altered.

SQL> alter index “BIN$eYaVVVT7Q2eGx2qr1f+Nhg==$0” rename to PK_TEST;
Index altered.

SQL> alter index “BIN$ALBfM1WhRPKBrsAkaHmQNA==$0” rename to TEST_IND_1;
Index altered.

Constraint names are not retrievable from user_recyclebin view so we have to refer to original scripts or any other documentation to get its original name but following is the syntax to rename the constraints.

SQL> alter table test rename constraint “BIN$2WvwhoEMTRiDYrEumwLPCg==$0” to PK_TEST;

Flashing back to SCN (System change number)

Other use of flashback table is to replace the data in the table to a different point of time using timestamp and/or SCN clause. Here we will show you how we can achieve it using SCN.

Let us first get the current SCN value. Make sure that you have execute privilege on dbms_flashback package. Run following statement to get current SCN.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
3965449

At this point, data in the table is as shown below.

SQL> select test_id, test_indic from test;

TEST_ID TEST_INDIC
———- ———-
1 0
2 0

Now we will update value of test_indic column to 1.

SQL> update test
2 set test_indic = 1
3 /

2 rows updated.

SQL> commit;

Commit complete.

First we will compare the old value and new value of test_indic column by joining against table version at particular SCN. Run following SQL. Make sure to change the SCN value with correct value or you can parameterize the query to get the input of SCN number.

SQL> select a.test_id, a.test_indic new_indic, b.test_indic old_indic
2 from test a inner join test as of scn 3965449 b on b.test_id = a.test_id;

TEST_ID NEW_INDIC OLD_INDIC
———- ———- ———-
1 1 0
2 1 0

In above query, we are self joining the test table with one of its past version to compare the updated column values. Now let us see how we can restore the table to that SCN value. Issue following command to reinstate the copy of the table at SCN value.

SQL> flashback table test to scn 3965449;
flashback table test to scn 3965449
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

Oops! We ran into an error. Whenever we are using flashback clause with SCN or TIMESTAMP, we need to enable the row movement for a table.

SQL> alter table test enable row movement;
Table altered.

Now issue the flashback statement again and review the data in the table.

SQL> flashback table test to scn 3965449;
Flashback complete.

SQL> select test_id, test_indic from test;
TEST_ID TEST_INDIC
———- ———-
1 0
2 0

We have successfully reverted back the changes and restored the table to a specific point in time. In similar fashion flashback clause can be used with timestamp and restore point options as well. For limitations and detailed description of flashback and row movement clause, please refer to Oracle 10g SQL reference.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: