In previous versions of Oracle, once you had dropped a table it was gone forever. The only way to restore it would be to run a script or restore from a database export.
In Oracle 10g there is a new feature called the recycle bin. When tables are dropped, they are stored there for purposes of quick recovery.
Recovering dropped tables (example)
1. Run SQL Plus, then connect to Obiwan and type the following commands to create a table and populate it:
CREATE TABLE OBIPETS(ID NUMBER, NAME VARCHAR2(20)); INSERT INTO OBIPETS VALUES (1, 'Cat'); INSERT INTO OBIPETS VALUES (2, 'Dog'); COMMIT;
2. Next, drop the table:
DROP TABLE OBIPETS;
3. After that, have a look at your tables:
SELECT * FROM TAB;
4. You should see something like the following:
TNAME TABTYPE CLUSTERID ----------------------------------------------- ----------- --------- BIN$mz4vSB3VSmSwp//uWZQgpw==$0 TABLE
5. This is the table that you have dropped. You can restore it by typing the following:
FLASHBACK TABLE OBIPETS TO BEFORE DROP;
6. Type the following code to check if it has been recovered:
SELECT * FROM TAB;
7. You should see something like the following:
TNAME TABTYPE CLUSTERID -------------- ---------- ---------- OBIPETS TABLE
8. You can now use the table as before.
Note that constraints may not always be preserved in recovered tables and that tables you have dropped a long time ago (this may be as little as a few hours if the database is in heavy use) may not be recovered.
Managing the recycle bin
1. After you have created and dropped many tables, your recycle bin may begin to get quite large and unwieldy. However, you can empty it using the PURGE RECYCLEBIN command. e.g.:
CREATE TABLE A(B NUMBER); CREATE TABLE B(B NUMBER); CREATE TABLE C(B NUMBER); DROP TABLE A; DROP TABLE B; DROP TABLE C; DROP TABLE OBIPETS; SELECT * FROM TAB;
2. You should see something like the following:
TNAME TABTYPE CLUSTERID -------------------------------------------- ---------- ------------ BIN$nZkvl14oSUuwPT/EjuC+pQ==$0 TABLE BIN$8ubkjGm6TcaCNYBMfTsAaw==$0 TABLE BIN$A7lrPo8GS8OOKjMKur+Zrw==$0 TABLE BIN$tkRKJrMsTfKa0Dn+iH2Zsw==$0 TABLE
3. Next, type in the following:
PURGE RECYCLEBIN; SELECT * FROM TAB:
4. You will see that there are now no dropped tables. Make sure that there is nothing that you may wish to restore in future before you do this though.
You can also bypass the recycle bin by using the purge option with the drop command.
CREATE TABLE A(B NUMBER); DROP TABLE A PURGE; SELECT * FROM TAB:
5. You should then see that there is no recycle bin object associated with table A.