Because of the error, drop table causes the key table to be lost. The recovery method is as follows
DROP TABLE InoracleIn this article, the table is just renamed and the relevant data is modified in the data dictionary, but the physical space occupied by the table is not released, and the original table space still occupies.
Where did this table go at this time? The drop table renames the table to start with BIN$ and is placed in the recycling bin, namely recyclebin.
To restore the table that was dropped by mistake, you just need to take it out from the recycling bin. Oracle9i has begun to introduce the concept of flashback table.
flashback table is used to restore objects that users deleted by mistake. It can both restore DML operations and restore DDL operations (like drop table). The use of flashback table depends on recyclebin [Recycle Bin].
test:
SQL> conn scott/123456;
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
Note: recyclebin is turned on by default
SQL> drop table test;
Table dropped.
Comment: The test table is dropped by mistake;
Solution:
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$BnzUiiqUBgngUwsKChQQcQ==$0 TABLE 2014-10-28:22:34:30
Note: recyclebin is the consent term for user_recyclebin, so when querying the corresponding table, you must find the information of the dropped table under the corresponding user;
Note: Make sure the table that was dropped by mistake is in the Recycle Bin
SQL> flashback table test to before drop;
Flashback complete.
Note: Use the above command to restore the table that was dropped by mistake
Note: The table that has been deleted accidentally can be restored, but the indexes and constraints on the table will not be restored. Therefore, after restoring the table, remember to rebuild the above indexes and constraints.
Sometimes, accidentally deleted tables cannot be restored from the recycle bin, which involves the management of recyclebin and the utilization principles of oracle space.
Recycle bin management:
1: Skip the recycle bin drop table directly and free up space, so you won’t find the corresponding table in the recyclebin, and you can’t recover it with flashback table.
DROP TABLE PURGE;
2: Clear the entire recycling station
purge recyclebin；
You can also clear the specified object, such as PURGE TABLE;
3: show recyclebin; view the content of the recycling bin
4：If you delete the table with the same name multiple times, how do you record it, manage it, and restore it in the recycling bin?
When deleting the table, in addition to renaming it to the name starting with BIN$, it will also bedba_recyclebinCREATETIME, DROPTIME, DROPSCN and other fields are used to assist in the unique positioning of a certain table and restore it
SQL> flashback table "BIN$BoBD7teTC0jgUwsKChQpVQ==$0" to before drop;
Flashback complete.
Rules for utilization of oracle space: