Oracle10g 回收站及彻底删除table : drop table xx purge
来源:互联网 发布:php导入excel文件 编辑:程序博客网 时间:2024/05/17 07:53
drop后的表被放在回收站(user_recyclebin)里,而不是直接删除掉。这样,回收站里的表信息就可以被恢复,或彻底清除。
1.通过查询回收站user_recyclebin获取被删除的表信息,然后使用语句
flashback table <user_recyclebin.object_name or user_recyclebin.original_name> to before drop [rename to <new_table_name>];
将回收站里的表恢复为原名称或指定新名称,表中数据不会丢失。
若要彻底删除表,则使用语句:drop table <table_name> purge;
2.清除回收站里的信息
清除指定表:purge table <table_name>;
清除当前用户的回收站: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
来自:http://blog.csdn.net/wildwave/article/details/5180273
- Oracle10g 回收站及彻底删除table : drop table xx purge
- Oracle10g 回收站及彻底删除table : drop table xx purge
- Oracle10g 回收站及彻底删除table : drop table xx purge
- Oracle10g 回收站及彻底删除table
- Oracle10g 回收站及彻底删除table
- Oracle Drop表并未直接删除 drop table xx purge
- Oracle Drop表并未直接删除 drop table xx purge
- drop table purge
- drop table tablename purge
- 回收站打开,drop table…
- oracle 数据库中 drop table 与purge
- Oracle tips:drop table和purge
- Oracle 回收站功能,彻底删除表(purge recyclebin)
- Oracle 回收站功能,彻底删除表(purge recyclebin)
- CREATE TABLE、ALTER TABLE、DROP TABLE等,及表内列的添加、删除以及列的修改
- shark执行drop table if exists table XX会报错?
- move table and purge table
- 闪回drop的表(Flashback Table 回收站闪回)
- king 选 太子
- UVA 112 & POJ 1145 Tree Summing(数据结构,二叉树,栈)
- 天地尘埃
- Leetcode 位运算 Single Number
- 毕业生手册(关于档案\户口 大全)
- Oracle10g 回收站及彻底删除table : drop table xx purge
- oracle 分区表
- http基础之MIME格式
- mediawiki 安装部署 LAMPP+mediawiki
- 有关DBLINK操作的语句执行机制及优化方式
- 标准C++中的string类的用法总结
- SDOI省选培训day1小结
- 30天自制操作系统——前三天
- 编写自己的C++IDE 准备工作