当前位置:操作系统 > Unix/Linux >>

利用recyclebin的保留策略恢复被删除的表

利用recyclebin的保留策略恢复被删除的表
 
oracle 10g有了一个recyclebin的保留策略,我们现在看看这个策略是怎么工作的。
创建一个15M的测试表空间
 
[html] 
SQL> create tablespace tsp_test datafile '/u01/app/oracle/oradata/orcl/tsp_test01.dbf' size 15M;  
  
Tablespace created.  
在这个表空间中创建一个用户,默认表空间为本表空间
[html] 
SQL> create user test identified by "test" default tablespace tsp_test;  
  
User created.  
给用户赋予DBA权限
[html] 
SQL> grant dba to test;  
  
Grant succeeded.  
以新建的用户登录,创建一个表
[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, 2005, 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.  
这时候查看表空间的情况
[html] 
SQL> select owner,segment_name,round(bytes/1024/1024,2)||' MB' m from dba_segments where tablespace_name='TSP_TEST';  
  
OWNER  
------------------------------  
SEGMENT_NAME  
--------------------------------------------------------------------------------  
M  
-------------------------------------------  
TEST  
TEST1  
6 MB  
再创建一个表
[html] 
SQL> create table test2 as select * from dba_objects;  
  
Table created.  
这时候查看此表空间的使用情况
[html] 
select b.file_name ,  
       b.tablespace_name ,  
       b.bytes / 1024 / 1024 SIZEM,  
       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 USERDM,  
       substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) PERCENTUSED  
  from dba_free_space a, dba_data_files b  
 where a.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  
--------------------------------------------------------------------------------  
TABLESPACE_NAME             SIZEM     USERDM PERCE  
------------------------------ ---------- ---------- -----  
/u01/app/oracle/oradata/orcl/tsp_test01.dbf  
TSP_TEST                   15    12.0625 80.41  
可以看到试用了12.0625M,使用率80.41%
那么现在按照顺序先删除表test1,再删除test2
 
[html] 
SQL> drop table test1;  
  
Table dropped.  
  
SQL> drop table test2;  
  
Table dropped.  
这时候再查看表空间的情况和recyclebin的使用情况
[html] 
SQL> select owner,segment_name,round(bytes/1024/1024,2)||' MB' m from dba_segments where tablespace_name='TSP_TEST';  
  
OWNER  
------------------------------  
SEGMENT_NAME  
--------------------------------------------------------------------------------  
M  
-------------------------------------------  
TEST  
BIN$5Jl2esAgoA7gQAB/AQB4Dg==$0  
6 MB  
  
TEST  
BIN$5Jl2esAfoA7gQAB/AQB4Dg==$0  
6 MB  
  
OWNER  
------------------------------  
SEGMENT_NAME  
--------------------------------------------------------------------------------  
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  
可以看出来,表虽然删除了,但是表空间还没有释放,同时recyclebin中已经存在了删除的两个表,那么这时候表空间还有3M的大小,我们再建一个同样的表
[html] 
SQL> create table test3 as select * from dba_objects;  
  
Table created.  
再查看表空间和recyclebin的使用情况
[html] 
SQL> select owner,segment_name,round(bytes/1024/1024,2)||' MB' m from dba_segments where tablespace_name='TSP_TEST';  
  
OWNER  
------------------------------  
SEGMENT_NAME  
--------------------------------------------------------------------------------  
M  
-------------------------------------------  
TEST  
BIN$5Jl2esAgoA7gQAB/AQB4Dg==$0  
6 MB  
  
TEST  
TEST3  
6 MB  
  
OWNER  
------------------------------  
SEGMENT_NAME  
--------------------------------------------------------------------------------  
M  
-------------------------------------------  
  
  
SQL> select object_name,ORIGINAL_NAME from user_recyclebin;  
  
OBJECT_NAME            ORIGINAL_NAME  
------------------------------ --------------------------------  
BIN$5Jl2esAgoA7gQAB/AQB4Dg==$0 TEST2  
这时候把recyclebin给清空了,在查看recyclebin和表空间
 
[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  
------------------------------  
SEGMENT_NAME  
--------------------------------------------------------------------------------  
M  
------------------
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,