--Initialize data
drop table test Purge;
CREATE TABLE Test as SELECT * from Dba_objects;
Delete from test where object_id is null;
ALTER TABLE TEST ADD constraint pk_test_object_id primary key (OBJECT_ID);
CREATE INDEX ind_t_object_name on test (object_name);
--Perform a delete operation
drop table test;
Select R.object_name,r.original_name,r.operation,r.type from RecycleBin R;
object_name original_name Operation TYPE
------------------------------- ------------------ -------- --------
Bin$ffq1sqrovjjguaoklg9c7a==$0 TEST DROP TABLE
Bin$ffq1sqrnvjjguaoklg9c7a==$0 pk_test_object_id DROP INDEX
Bin$ffq1sqrmvjjguaoklg9c7a==$0 ind_t_object_name DROP INDEX
--statements that generate a flashback table
Select ' Flashback table ' | | R.original_name | | ' to before drop; ' CC
From RecycleBin R
where type = ' TABLE ';
Cc
--------------------------------------
Flashback table TEST to before drop;
--The index will be flashed back, but the name will be changed
Select ' Alter index ' | | R.object_name | | ' Rename to ' | |
R.original_name | | ' "; ' CCC
From RecycleBin R
where type = ' INDEX ';
Ccc
---------------------------------------------------------------------------------
Alter index "BIN$FFQ1SQRMVJJGUAOKLG9C7A==$0" Rename to "Ind_t_object_name";
Alter index "BIN$FFQ1SQRNVJJGUAOKLG9C7A==$0" Rename to "pk_test_object_id";
Final execution:
Flashback table TEST to before drop;
Alter index "BIN$FFQ1SQRIVJJGUAOKLG9C7A==$0" Rename to "Ind_t_object_name";
Alter index "BIN$FFQ1SQRJVJJGUAOKLG9C7A==$0" Rename to "pk_test_object_id";
Oracle 11g DROP TABLE rear flash back