1.首先,以scott用户连接到数据库
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
2.清空回收站
SQL> purge recyclebin;
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');
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
------------------------------ ------------------------------ --------------- ------------------------------ ------------------------------
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';
SQL> select * from dba_sys_privs where grantee='TERRY';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
TERRY UNLIMITED TABLESPACE NO
TERRY DROP ANY TABLE NO
------------------------------ ---------------------------------------- ------------
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 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> 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
------------------------------ -------------------------------- --------- -------------------------
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');
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
------------------------------ ------------------------------ --------------- ------------------------------ ------------------------------
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
------------------------------ ------------------------------ --------------- ------------------------------ ------------------------------
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
------------------------------ ------------------------------ --------------- ------------------------------ ------------------------------
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
------------------------------ ------------------------------ --------------- ------------------------------
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:
01418, 00000, "specified index does not exist"
// *Cause:
// *Action:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-764056/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-764056/