利用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
-------------------------------------------
TEST
TEST3
6 MB
此时recyclebin被清空，表空间中只剩下test3表。
由此可见：
当我们在删除一个表的时候，被删除的表以及相关的对象(比如索引、约束、嵌套表等等)并没有被移除，并且依然占用着空间，直到我们从recyclebin中将他们清楚，或者由于表空间的限制，数据库将他们清除，这样他们才会被清除掉。
现在来演示恢复
我们再创建一个相同的表test4
[html]
SQL> create table test4 as select * from dba_objects;
Table created.
删除掉
[html]
SQL> drop table test3;
Table dropped.
SQL> drop table test4;
Table dropped.
这时候在recyclebin中可以查到删除的表
[html]
SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$5Jl2esAhoA7gQAB/AQB4Dg==$0 TEST3
BIN$5Jl2esAioA7gQAB/AQB4Dg==$0 TEST4
闪回并查看结果
[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
发现被删除的test4成功的恢复了。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。