oracle—回收站

最新推荐文章于 2025-04-09 17:23:49 发布

ducduc

最新推荐文章于 2025-04-09 17:23:49 发布

阅读量942

点赞数

分类专栏： oracle 文章标签： oracle sql table user object database

oracle 专栏收录该内容

23 篇文章

订阅专栏

（1）目前oracle（ Release 10.2.0.1.0）回收站不支持系统表空间
[1]实验
SQL> SELECT tablespace_name FROM USER_TABLES WHERE table_name='T';
TABLESPACE_NAME
------------------------------
SYSTEM

SQL> select count(*) from t;
  COUNT(*)
----------
   10000
SQL> drop table t;
Table dropped.
SQL> select count(*) from t;
select count(*) from t
                  *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> rollback;
Rollback complete.
SQL> select count(*) from t;
select count(*) from t
                  *
ERROR at line 1:
ORA-00942: table or view does not exist （可见oracle的drop是自动提交的）

SQL> SELECT original_name, object_name, TYPE, droptime FROM user_recyclebin;
no rows selected

可见我们删除的t表没有放入回收站！
（2）闪回表特性，可以毫不费力地恢复被意外删除的表
[1]实验
一创建用户和授权
SQL> create user test identified by ss123456;
User created.
SQL> grant connect,create table ,resource  to test;
Grant succeeded.

C:/Documents and Settings/Administrator>sqlplus [email=test/ss123456@ot]test/ss123456@ot[/email]
SQL*Plus: Release 9.0.1.0.1 - Production on Thu May 14 15:00:26 2009
(c) Copyright 2001 Oracle Corporation.  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>
二建demo表
SQL> create table demo (id int);
Table created.
SQL> insert into demo values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT tablespace_name FROM USER_TABLES WHERE table_name='DEMO';
TABLESPACE_NAME
------------------------------
USERS

可见用户的表建在了users表空间中；
三删除demo
SQL> drop table demo;
Table dropped.
SQL> commit;
Commit complete.
SQL> select * from demo;
select * from demo
            *
ERROR at line 1:
ORA-00942: table or view does not exist
四利用闪回表特性，可以毫不费力地恢复被意外删除的表
1 查看回收站
SQL> SELECT original_name, object_name, TYPE, droptime FROM user_recyclebin;
ORIGINAL_NAME    OBJECT_NAME    TYPE                                  DROPTIME
------------- ---------------------------       ------------                            -------------------
DEMO             BIN$cI/hTpOYT4GOLWfF+7O78Q==$0 TABLE       2009-05-14:15:09:40
2 恢复
SQL> FLASHBACK TABLE demo TO BEFORE DROP;
闪回完成。
SQL> select * from demo;
      ID
----------
      1