Oracle10g recycle bin and permanently delete table: Drop table xx purge

The drop table is placed in the recycle bin (user_recyclebin) instead of deleted directly. In this way, the table information in the recycle bin can be restored or completely cleared.
1. query the recycle bin user_recyclebin to obtain the information of the table to be deleted, and then use the statement
Flashback table <user_recyclebin.object_name or user_recyclebin.original_name> to before drop [Rename to <new_table_name>];

 

Restore the table in the recycle bin to the original name or specify a new name. data in the table is not lost.
To completely delete a table, use the following statement: Drop table <table_name> purge;

2. clear information in the recycle bin.
Clear the specified table: purge table <table_name>;
Clear the recycle bin of the current user: purge recyclebin;
Clear the recycle bin of all users: purge dba_recyclebin;

========================================================== ========================================================
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as test
SQL> select * From test1;
A B C
--------------
11 5
11 10
11 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
20 11
11 rows selected
SQL> Create Table Test2 as select * From test1; s
Table created
SQL> select * From Test2;
A B C
--------------
11 5
11 10
11 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
20 11
11 rows selected
SQL> drop table Test2;
Table dropped
SQL> select object_name, original_name, operation, type from user_recyclebin;
Object_name original_name Operation Type
------------------------------------------------------------------------------------------------
Bin $ g5jfma/oshc6 + wswkjiv2w ==$ 0 test1 drop table
Bin $ vqwemdg4r9mk9fyjndyzvg ==$ 0 Test2 drop table
SQL> flashback table Test2 to before drop Rename to test3; -- [to test3] rename the table
Done
SQL> select * From test3;
A B C
--------------
11 5
11 10
11 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
20 11
11 rows selected
SQL> select * From Test2;
Select * From Test2
ORA-00942: Table or view does not exist
-- Permanently delete a table
SQL> drop table test3 purge;
Table dropped
SQL> select * From user_recyclebin where original_name = 'test3 ';
Object_name original_name operation type ts_name createtime droptime dropscn partition_name can_undrop can_purge related base_object purge_object Space
Certificate certificate ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
SQL> select * From user_recyclebin;
Object_name original_name operation type ts_name createtime droptime dropscn partition_name can_undrop can_purge related base_object purge_object Space
Certificate certificate ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
Bin $ g5jfma/oshc6 + wswkjiv2w ==$ 0 test1 drop table tp_test1 2007-08-23: 07: 57: 28 2007-08-23: 07: 58: 51 1411156 Yes 53086 53086 53086
-- Clear the table information in the recycle bin test1
SQL> purge table test1;
Done
SQL> select * From user_recyclebin;
Object_name original_name operation type ts_name createtime droptime dropscn partition_name can_undrop can_purge related base_object purge_object Space