- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2011-04-19 22:28:08
drop后的表被放在回收站(user_recyclebin)里，而不是直接删除掉。这样，回收站里的表信息就可以被恢复，或彻底清除。
1.通过查询回收站user_recyclebin获取被删除的表信息，然后使用语句
flashback
table
将回收站里的表恢复为原名称或指定新名称，表中数据不会丢失。
若要彻底删除表，则使用语句：drop table
2.清除回收站里的信息
清除指定表：purge table
清除当前用户的回收站：purge recyclebin;
清除所有用户的回收站：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】将表重命名
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: 表或视图不存在
--彻底删除表
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
------------------------------ --------------------------------
--------- ------------------------- ------------------------------
------------------- ------------------- -------
-------------------------------- ---------- --------- -------
----------- ------------ -----
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
------------------------------ --------------------------------
--------- ------------------------- ------------------------------
------------------- ------------------- -------
-------------------------------- ---------- --------- -------
----------- ------------ -----
bin$g5jfma/oshc6 wswkjiv2w==$0 test1
drop table tp_test1 2007-08-23:07:57:28 2007-08-23:07:58:51 1411156 yes
yes 53086 53086 53086 896
--清除回收站里的表信息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
ref:
sql> desc tt
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
id number(38)
sql> select * from tt;
id
----------
100
100
3
4
sql> drop table tt;
表已删除。
sql> create table tt(id int , name varchar2(10));
表已创建。
sql> drop table tt;
表已删除。
sql> create table tt(id int , name varchar2(10) , time date);
表已创建。
sql> drop table tt;
表已删除。
sql> select original_name , droptime from dba_recyclebin where original_name='tt
' order by droptime;
original_name droptime
-------------------------------- -------------------
tt 2007-09-21:20:17:59
tt 2007-09-21:20:18:31
tt 2007-09-21:20:18:47
sql> purge table tt;
表已清除。
sql> select original_name , droptime from dba_recyclebin where original_name='tt
' order by droptime;
original_name droptime
-------------------------------- -------------------
tt 2007-09-21:20:18:31
tt 2007-09-21:20:18:47
sql>
结论：recyclebin中存在同名对象时，执行purge table table_name时，最先被删除的对象先被从recyclebin中释放！
ref: