How to restore the drop table in Oracle10g

In Oracle10g, If you accidentally drop a table, Can you restore it? The answer is yes. In Oracle10g, when you run the drop table tablename command, the table will not be deleted, instead, the table is stored in the recycle bin (the recycle bin concept is similar to the recycle bin in Windows). Therefore, we can use the flashback command to restore the drop table, next we will make a small research on this issue. First we will create a table create table test (ID int, name varchar (20) and then insert several pieces of data insert into test (ID, name) values (1, 'A'); insert into test (ID, name) values (2, 'B'); Commit; well, for better observation, run the following command to clear the recycle bin: purge recyclebin; SQL> select original_name, dropscn from recyclebin; if no row is selected, the recycle bin has been cleared and the preceding Drop the created test table, and check the SQL> drop table test; Table deleted in the recycle bin. Www.2cto.com SQL> select original_name, dropscn from recyclebin; original_name dropscn ------------------------------------ test 172.1691sql> select * from test; select * from test * 1st Line Error: ORA-00942: the table or view does not have an SQL statement.> at this time, the drop table is found in the recycle bin, and the table cannot be found using the SELECT statement, next we will use flashback to restore SQL> flashback table test to before drop; flash back is complete. SQL> select * from test; ID name ---------- -------------------- after 1 A2 B is restored, the table structure and data are retrieved so well. Let's take another question, this is the question in the OCP examination. The above experiment basically carries out the following operations. What will happen? Drop table test; -- drop table test table creates the test table again with the same name, but the field is different. Create Table Test (A int, B varchar (20), c varchar (30 )) drop table test -- drop the test table again and execute flashback to restore the flashback table test to before drop. In this case, you can think about which table will be restored? Is it the first time you created it? Or the second creation? Let's talk about SQL> drop table test. The table has been deleted. Www.2cto.com SQL> Create Table Test (2 A int, 3 B varchar (20), 4 C varchar (30) 5); the table has been created. SQL> drop table test; the table has been deleted. SQL> select original_name, dropscn from recyclebin; original_name dropscn ---------------------------------- ---------- test 1743515 test 1743435sql> flashback table test to before drop; flash back complete. SQL> select * from test; SQL> DESC test; is the name blank? Type ----------------------------------------- -------- ---------------------------- a number (38) B varchar2 (20) C varchar2 (30) through the above experiment, we can see that although the name is the same, however, flashback only restores the last table created. Note the preceding statement used to query the recycle bin. From the recycle bin, we can see that there are two test tables, but the dropscn is different, use the flashback command to restore only the large dropscn table. Previously, the flashback command is junk. Note that if the purge option is added to the drop table, the table cannot be recovered, see the following tutorial SQL> drop table test purge; www.2cto.com table deleted. SQL> select original_name, dropscn from recyclebin; original_name dropscn -------------------------------- ---------- test 1743435sql> flashback table test to before drop; The Flash is complete. SQL> select * from test; ID name ---------- -------------------- 1 A2 bdrop uses the purge option, so the drop is not placed in the recycle bin. Why is the recovery successful when the flashback table is executed? We can see from the experiment above that the test table we created for the first time is restored, and the test table created for the second time cannot be restored by the author.