Use recyclebin's retention policy to restore the deleted table

Use the recyclebin retention policy to restore the deleted table oracle 10 Gb has a recyclebin retention policy. Now let's see how this policy works. Create a 15 M test table space [html] SQL> create tablespace tsp_test datafile '/u01/app/oracle/oradata/orcl/tsp_test01.dbf' size 15 M; Tablespace created. create a user in the tablespace. The default tablespace is the tablespace [html] SQL> create User test identified by "test" default tablespace tsp_test; user created. grant the user the DBA permission [html] SQL> Grant dba to test; grant succeeded. log on as a new user and create a table [html] [oracle @ RedHat ~] $ Sqlplus test/test SQL * Plus: Release 10.2.0.1.0-Production on Fri Aug 23 15:25:09 2013 Copyright (c) 1982,200 5, Oracle. all rights reserved. connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production With the Partitioning, OLAP and Data Mining options SQL> create table test1 as select * from dba_objects; Table created. in this case, view the tablespace [html] SQL> select owner, segment_name, r Ound (bytes/1024/1024, 2) | 'mb'm from dba_segments where tablespace_name = 'tsp _ test '; OWNER ------------------------------ SEGMENT_NAME using M tables TEST TEST1 6 MB and then create a table [html] SQL> create Table test2 as select * from dba_objects; table created. in this case, check the usage of the tablespace [html] select B. file_name, B. tablespace_name, B. bytes/1024/1024 SIZEM, (B. bytes-sum (nvl (. bytes, 0)/1024/1024 USERDM, substr (B. bytes-sum (nvl (. bytes, 0)/(B. bytes) * 100, 1, 5) PERCENTUSED from dba_free_space a, dba_data_files B where. file_id = B. file_id and B. tablespace_name = 'tsp _ test' group by B. tablespace_name, B. file_name, B. bytes 9 order by B. tablespace_name; FILE_NAME ------------------------- Export TABLESPACE_NAME sizem userdm perce ------------------------------ ------------ -----/u01/app/oracle/oradata/orcl/tsp_test01.dbf TSP_TEST 15 12.0625 80.41 trial 12.0625 M, if the usage rate is 80.41%, delete test1 in sequence, and then delete test2 [html] SQL> drop table test1; Table dropped. SQL> drop table test2; Table dropped. at this time, check the table space and recyclebin usage. [html] SQL> selec T owner, segment_name, round (bytes/1024/1024, 2) | 'mb'm from dba_segments where tablespace_name = 'tsp _ test '; OWNER login SEGMENT_NAME login M release test bin $ 5Jl2esAgoA7gQAB/AQB4Dg ==$ 0 6 mb test bin $ 5Jl2esAfoA7gQAB/AQB4Dg ==$ 0 6 mb owner quota ---------------------------- -- SEGMENT_NAME begin M --------------------------------------------- recyclebin [html] SQL> select object_name, ORIGINAL_NAME from user_recyclebin; OBJECT_NAME ORIGINAL_NAME ------------------------------ -------------------------------- BIN $ 5Jl2esAfoA7gQAB/AQB4Dg = $0 TEST1 BIN $ 5Jl2esAgoA7gQAB/AQB4Dg = $0 TEST2 can be seen. Although the table is deleted The space has not been released, and two deleted tables exist in the recyclebin. At this time, the tablespace size is 3 MB, create another table [html] SQL> create Table test3 as select * from dba_objects; table created. view the table space and recyclebin usage [html] SQL> select owner, segment_name, round (bytes/1024/1024, 2) | 'mb'm from dba_segments where tablespace_name = 'tsp _ test'; OWNER ---------------------------- SEGMENT_NAME limit --------------------------------------------------------------------- ----------- M partition test bin $ partition/AQB4Dg = $0 6 mb test TEST3 6 mb owner partition SEGMENT_NAME limit M partition SQL> select object_name, ORIGINAL_NAME from user_recyclebin; OBJECT_NAME ORIGINAL_NAME Partition ---------------------- -------- Recycle BIN $ 5Jl2esAgoA7gQAB/AQB4Dg ==$ 0 TEST2. At this time, the recyclebin is cleared. Check recyclebin and tablespace [html] SQL> purge recyclebin; Recyclebin purged. SQL> select object_name, ORIGINAL_NAME from user_recyclebin; no rows selected SQL> select owner, segment_name, round (bytes/1024/1024, 2) | 'mb'm from dba_segments where tablespace_name = 'tsp _ test'; OWNER ------------------------------ SEGM ENT_NAME tablespace M ------------------------------------------- TEST TEST3 6 MB. At this time, recyclebin is cleared, and only table test3 is left in the tablespace. It can be seen that when we delete a table, the deleted table and related objects (such as indexes, constraints, and nested tables) are not removed and still occupy space, it is not until we clear them from recyclebin, or the database clears them due to table space restrictions so that they will be cleared. Now, we will demonstrate how to create an identical table test4 [html] SQL> create Table test4 as select * from dba_objects; table created. delete [html] SQL> drop table test3; Table dropped. SQL> drop table test4; Table dropped. in this case, you can find the deleted table [html] SQL> select OBJECT_NAME, ORIGINAL_NAME from user_recyclebin; OBJECT_NAME ORIGINAL_NAME ------------------------------ -------------------------------- BIN $ 5Jl2esAhoA7gQAB/AQB4Dg = $0 TEST3 BIN $ 5Jl2esAioA7gQAB/AQB4Dg ==$ 0 TEST4 flash back and view the result [html] SQL> flashback table test4 to before drop; Flashback complete. SQL> select OBJECT_NAME, ORIGINAL_NAME from user_recyclebin; OBJECT_NAME ORIGINAL_NAME ------------------------------ -------------------------------- BIN $ 5Jl2esAhoA7gQAB/AQB4Dg ==$ 0 TEST3 SQL> select count (*) from test4; COUNT (*) ---------- 50558 the deleted test4 is successfully restored.