Flashback Table and Flashback Drop


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:

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database

When I am adding datafile to my 19C database facing the below error. SQL> alter tablespace DATA  add datafile '/u01/data/data15.dbf...