Archive for

Flashback DROP

Flashback DROP 

• The Flashback drop command applies only to tables, but all associated objects will also be recovered,except for foreign key constraints.
• In earlier releases of the Oracle Database, when a table was dropped all references to it were removed from the data dictionary.
• If it were possible to see the source code for the old DROP TaBLE command, you would see that it was actually a series of DELETE commands against the various tables in the SYS schema that define a table and its space usage, followed by commit. 
• There was no actual clearing of data from disk, but the space used by a dropped table was flagged as being unused and thus available for reuse.
• Even though the blocks of the table were still there, there was no possible way of getting to them because the data dictionary would have no record of which blocks were part of the dropped table.the only way  to recover a dropped table was to do a point in time recovery, restoring a version of the database from before the drop when the data dictionary still knew about the table.
• In release 10g of the Oracle database , the implementation of the DROP TABLE command has been completely changed.
• tables are no longer dropped at all, they are renamed.
• You can see that a table, EMP occupies one extent of 64KB, which starts in the 25the of file 4.After the rename to NEW_NAME, the storage is exactly the same; therefore the table is the same,
  Querying the view DBA_OBJECTS would show that the table’s object number had not changed either.
• The release 10g implementation of the DROP TABLE command has been mapped internally onto a RENAME command, which affects the table all its associated indexes, triggers and constaints with the exception
  of foreign key constraints, which are dropped.
• If they were maintained, even with a different name, then DML on the non dropped parent table would be constrained by the contents of a dropped table, which would be absurd.
• The dropped objects can be queried by looking at the recycle bin to obtain their names.
• There is a recycle bin for each other, visible in the USER_RECYCLEBIN data dictionary view or DBA_RECYCLEBIN.
• The space occupied by the recycle bin objects will be reused automatically when a tablespace comes under space pressure(after whic
  time the objects cannot be recovered), or you can manually force Oracle to really drop the objects with the PURGE command.

SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMP’;
 

   FILE_ID   BLOCK_ID      BYTES
———- ———- ———-
         4                  25      65536
 

SQL> alter table EMP rename to EMPLOYEE;
alter table EMP rename to EMPLOYEE
*
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> conn scott/tiger
Connected.
SQL> alter table EMP rename to EMPLOYEE;
 

Table altered.

SQL> conn sys/sys@riyadh as sysdba
Connected.
SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMP’;
 

no rows selected
 

SQL> select file_id,block_id,bytes from dba_extents where segment_name=’EMPLOYEE’;
 

   FILE_ID   BLOCK_ID      BYTES
———- ———- ———-
         4               25             65536
 

Flashback DROP Example :
 

Step 1
 

Create user
 

SQL> show user;
USER is “SYS”
SQL> create user dropper identified by dropper;
 

User created.
 

SQL> grant connect,resource to dropper;
 

Grant succeeded.
 

Step 2
Create a table, with an index and a constraint and insert a row.
 

SQL> connect dropper/dropper@riyadh;
Connected.
SQL> create table test(name varchar2(10));  

Table created.  

SQL> create index test_idx on test(name);  

Index created.  

SQL> alter table test add(constraint name_u unique(name));  

Table altered. 
SQL> insert into test values(‘Azar’);
 

1 row created.
 

SQL> commit;
 

Commit complete.
 

SQL> select * from test;
 

NAME
———-
Azar
 

Step 3
 
Confirm the contents of your schema
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

TEST_IDX
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
NAME_U                         U TEST
 

Step 4 

Drop the table
 

SQL> drop table test;
 

Table dropped.
 

Step 5
 
Re-run the queries from step 3.Note that the objects and the constaints do still exist but that they now
have system generated names, all prefixed with BIN$.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
BIN$Zl4wALngQtKfJL+9mgzJgg==$0
TABLE
 

BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
BIN$BBKVasdjRsqyYqne8rM8VQ==$0 U BIN$Zl4wALngQtKfJL+9mgzJgg==$0
 

Step 6 

Query your recycle bin to see the mapping of the original name to the recyclebin names.
 

SQL> select object_name,original_name,type from user_recyclebin;
 

OBJECT_NAME                    ORIGINAL_NAME
—————————— ——————————–
TYPE
————————-
BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0 TEST_IDX
INDEX
 

BIN$Zl4wALngQtKfJL+9mgzJgg==$0 TEST
TABLE
 

Step 7
 

Query the recycle bin but that you cannot do DML against it.
 

SQL> select * from “BIN$Zl4wALngQtKfJL+9mgzJgg==$0”;
 

NAME
———-
Azar
 

SQL> insert into “BIN$Zl4wALngQtKfJL+9mgzJgg==$0” values (‘Mohd’);
insert into “BIN$Zl4wALngQtKfJL+9mgzJgg==$0” values (‘Mohd’)
            *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
 

Step 8
 

Recover the table with Flashback Drop 
SQL> flashback table test to before drop;
 

Flashback complete.
 

Step 9
 

Rerun the step 3.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
BIN$BBKVasdjRsqyYqne8rM8VQ==$0 U TEST
 

Step 10
Rename the index and constraint to the original names.

SQL> alter index “BIN$WvYAWvXkQ/qWhSjzqs9UKQ==$0” rename to name_idx;
 

Index altered.
 

SQL> alter table test rename constraint “BIN$BBKVasdjRsqyYqne8rM8VQ==$0” to name_u;
 

Table altered.
 

SQL> select object_name,object_type from user_objects;
 

OBJECT_NAME
——————————————————————————–
 

OBJECT_TYPE
——————-
TEST
TABLE
 

NAME_IDX
INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 

CONSTRAINT_NAME                C TABLE_NAME
—————————— – ——————————
NAME_U                         U TEST
 

SQL> select * from test;
 

NAME
———-
Azar