闪回删除

1.首先，以scott用户连接到数据库
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott

2.清空回收站
SQL> purge recyclebin;

Done

3.查询emp、dept表中的约束
SQL> select owner,constraint_name,constraint_type,table_name,r_constraint_name from user_constraints where table_name in('EMP','DEPT');

OWNER                          CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     R_CONSTRAINT_NAME
------------------------------ ------------------------------ --------------- ------------------------------ ------------------------------
SCOTT                          SYS_C0018227                   C               DEPT                          
SCOTT                          PK_DEPT                        P               DEPT                          
SCOTT                          SYS_C0018229                   C               EMP                           
SCOTT                          PK_EMP                         P               EMP                           
SCOTT                          FK_DEPTNO                      R               EMP                            PK_DEPT

4.查看terry用户的权限，此处看到DML操作相关的权限只有drop any table
SQL> select * from dba_sys_privs where grantee='TERRY';

GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
TERRY                          UNLIMITED TABLESPACE                     NO
TERRY                          DROP ANY TABLE                           NO

SQL>  select owner,table_name from dba_tab_privs where wner='TERRY';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
TERRY                          INSERT_EMP

此步骤操作查出的结果与实验无关。只是证明terry没有其他的权限。

5.登录到terry用户，删除emp、dept表：
SQL> conn terry/terry@testdb
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as terry

SQL> drop table scott.emp;

Table dropped

SQL> drop table scott.dept;

Table dropped

6.在terry用户下查看回收站，删除的对象并不保存在此
SQL> select object_name,original_name,operation,type from recyclebin;

7.scott用户登录，查看回收站
SQL> conn scott/scott@testdb
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott

SQL> select object_name,original_name,operation,type from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$3F+NLdg+z1DgQAB/AQAwiw==$0 IDX_EMP2                         DROP      INDEX
BIN$3F+NLdg/z1DgQAB/AQAwiw==$0 PK_EMP                           DROP      INDEX
BIN$3F+NLdhEz1DgQAB/AQAwiw==$0 DEPT                             DROP      TABLE
BIN$3F+NLdhAz1DgQAB/AQAwiw==$0 EMP                              DROP      TABLE
BIN$3F+NLdhDz1DgQAB/AQAwiw==$0 PK_DEPT                          DROP      INDEX

8.查看约束：
SQL> select owner,constraint_name,constraint_type,table_name,r_constraint_name from user_constraints where table_name in('EMP','DEPT');

OWNER                          CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     R_CONSTRAINT_NAME
------------------------------ ------------------------------ --------------- ------------------------------ ------------------------------

根据回收站中的对象名查询：
SQL> select owner,constraint_name,constraint_type,table_name,r_constraint_name from user_constraints where table_name in('BIN$3F+NLdhEz1DgQAB/AQAwiw==$0','BIN$3F+NLdhAz1DgQAB/AQAwiw==$0');

OWNER                          CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     R_CONSTRAINT_NAME
------------------------------ ------------------------------ --------------- ------------------------------ ------------------------------
SCOTT                          BIN$3F+NLdg8z1DgQAB/AQAwiw==$0 C               BIN$3F+NLdhAz1DgQAB/AQAwiw==$0
SCOTT                          BIN$3F+NLdg9z1DgQAB/AQAwiw==$0 P               BIN$3F+NLdhAz1DgQAB/AQAwiw==$0
SCOTT                          BIN$3F+NLdhBz1DgQAB/AQAwiw==$0 C               BIN$3F+NLdhEz1DgQAB/AQAwiw==$0
SCOTT                          BIN$3F+NLdhCz1DgQAB/AQAwiw==$0 P               BIN$3F+NLdhEz1DgQAB/AQAwiw==$0

根据constraint_type可以看到，这时emp、dept表之间的外键约束已经没了。（类型为R的约束）

因此这时我如果只恢复emp，也是可以的了。

9，恢复emp表

SQL> flashback table emp to before drop;

Done

这时，约束的名称还是回收站里的名称，而不是以前的名称了（以前是PK……）。对于参照性约束在表恢复后不能自动回复，只能手工重建。

SQL> select owner,constraint_name,constraint_type,table_name,r_constraint_name from user_constraints where table_name in('EMP','DEPT');

OWNER                          CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     R_CONSTRAINT_NAME
------------------------------ ------------------------------ --------------- ------------------------------ ------------------------------
SCOTT                          BIN$3F+NLdg8z1DgQAB/AQAwiw==$0 C               EMP                           
SCOTT                          BIN$3F+NLdg9z1DgQAB/AQAwiw==$0 P               EMP                           

10.恢复dept
SQL> flashback table dept to before drop;

Done

SQL> select owner,constraint_name,constraint_type,table_name,r_constraint_name from user_constraints where table_name in('EMP','DEPT');

OWNER                          CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     R_CONSTRAINT_NAME
------------------------------ ------------------------------ --------------- ------------------------------ ------------------------------
SCOTT                          BIN$3F+NLdhBz1DgQAB/AQAwiw==$0 C               DEPT                          
SCOTT                          BIN$3F+NLdhCz1DgQAB/AQAwiw==$0 P               DEPT                          
SCOTT                          BIN$3F+NLdg8z1DgQAB/AQAwiw==$0 C               EMP                           
SCOTT                          BIN$3F+NLdg9z1DgQAB/AQAwiw==$0 P               EMP                           

对于这些约束，可以将其重命名。但操作未成功：
SQL> select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='EMP';

OWNER                          CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME
------------------------------ ------------------------------ --------------- ------------------------------
SCOTT                          BIN$3F/gcI0to//gQAB/AQAwlg==$1 C               EMP
SCOTT                          BIN$3F/gcI0uo//gQAB/AQAwlg==$1 P               EMP

SQL> ALTER INDEX "BIN$3F/gcI0uo//gQAB/AQAwlg==$1" RENAME TO PK_EMP;

ALTER INDEX "BIN$3F/gcI0uo//gQAB/AQAwlg==$1" RENAME TO PK_EMP

ORA-01418: ？？？？？？？？

[oracle@localhost ~]$ oerr ora 1418
01418, 00000, "specified index does not exist"
// *Cause:
// *Action:

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-764056/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-764056/