The FLASHBACK TABLE statement enables users to get a table to a
previous point in time. It provides a fast, online solution to
get back a table that has been accidentally modified or deleted
by a user or application. It eliminates the DBA to perform more
complicated point in time recovery operations.
We should remember the thing is you must use automatic undo
management to use the Flashback Table feature. It is based
on undo information stored in an undo tablespace.
Now let's go for experiment.
Flashback Drop
1)SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
2)Drop the Table,
SQL> drop table test;
Table dropped.
3)Now see the Status,
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$SnQhAfQtUQzgRAAZ0Xbz6g==$0 TABLE
In fact when we issue drop table command the object resides
on recycle bin. We can see the recycle bin objects from
dba_recyclebin or simply,
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------------------
TEST BIN$SnQhAfQtUQzgRAAZ0Xbz6g==$0 TABLE 2008-04-09:12:35:17
4)Get the Table Back:
Invoke, flahsback command. Like,
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
However if you have another table you can use rename to clause
with flashback,
flashback table test to before drop rename to test2;
SQL> drop table test;
Table dropped.
SQL> flashback table test to before drop rename to test2;
Flashback complete.
SQL> desc test2;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
However you can drop table permanently without staying it
in recyclebin using,
drop table test2 purge;
To drop table from recyclebin, use,
purge table_name;
Flashback Table
You can also get back you present table to a previous state.
Like You deleted some row and you have committed. Now you
can back your data. Here is an example for your better
understanding,
SQL> select * from t;
A
----------
1
100
10
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
A
----------
1
100
10
2
SQL> flashback table t to timestamp systimestamp-interval '1'
minute;
Flashback complete.
SQL> select * from t;
A
----------
1
100
10
Important Things:-
1) objects will go to recyclebin or it will not go is based
on recyclebin parameter settings.
If I set alter system set recyclebin=off then object will not
go in recycle bin.
2)Dropped SYS and SYSTEM schema objects are don't go in
recyclebin.
3)The un-drop feature brings the table back to its original
name, but not the associated objects like indexes and triggers,
which are left with the recycled names.Views and procedures
defined on the table are not recompiled and remain in the
invalid state. These old names must be retrieved manually
and then applied to the flashed-back table.
Like,
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'TEST')
AND ORIGINAL_NAME != 'TEST';
OBJECT_NAME ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpdfanfgMAAAAAANPw==$0 TEST_01 INDEX
BIN$04LhcpdfanfgMAAAAAANPw==$0 TR_TEST TRIGGER
After the table is flashed-back, the indexes and triggers on
the table TEST will be named as shown in the OBJECT_NAME column.
From the above query, you can use the original name to rename
the objects as follows:
ALTER INDEX "RECYCLE BIN NAME" RENAME TO TEST_01;
ALTER TRIGGER "RECYCLE BIN NAME" RENAME TO TR_TEST;
But exception is the bitmap indexes. When they are dropped,
they are not placed in the recycle bin and so they are not
retrievable. The constraint names are also not retrievable
from the view. They have to be renamed from other sources.
4) To do flashback enable row movement must be enabled.
alter table test enable row movement;
No comments:
Post a Comment