Flashback table with foreign key constraint.
Flashback table with foreign key constraint.
ALTER TABLE EMP DROP CONSTRAINT FK_DEPTNO ;
ALTER TABLE EMP ADD ( CONSTRAINT FK_DEPTNO FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ON DELETE SET NULL ENABLE);
09:44:40 SQL> set linesize 1000
09:44:44 SQL> select * from emp;
[@more@]Flashback table with foreign key constraint.
ALTER TABLE EMP DROP CONSTRAINT FK_DEPTNO ;
ALTER TABLE EMP ADD ( CONSTRAINT FK_DEPTNO FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ON DELETE SET NULL ENABLE);
09:44:40 SQL> set linesize 1000
09:44:44 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
09:44:45 SQL> select * from dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
09:44:55 SQL> delete dept where deptno=10 ;
1 row deleted.
09:47:42 SQL> select * from dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
09:47:44 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300
14 rows selected.
09:47:46 SQL> commit ;
Commit complete.
09:47:49 SQL> alter table dept enable row movement ;
Table altered.
09:48:20 SQL> alter table emp enable row movement ;
Table altered.
09:48:25 SQL> select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300
14 rows selected.
09:48:41 SQL> select * from dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
09:48:45 SQL> flashback table dept to timestamp to_timestamp('2007-08-19 9:47:46','yyyy-mm-dd hh24:mi:ss') ;
Flashback complete.
09:51:35 SQL> select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300
14 rows selected.
09:51:48 SQL> select * from dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
09:51:54 SQL> flashback table emp to timestamp to_timestamp('2007-08-19 9:47:46','yyyy-mm-dd hh24:mi:ss') ;
Flashback complete.
09:53:11 SQL> select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
09:53:14 SQL> flashback table dept to timestamp to_timestamp('2007-08-19 9:50:00','yyyy-mm-dd hh24:mi:ss') ;
flashback table dept to timestamp to_timestamp('2007-08-19 9:50:00','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
09:55:36 SQL> flashback table emp to timestamp to_timestamp('2007-08-19 9:50:00','yyyy-mm-dd hh24:mi:ss') ;
Flashback complete.
09:56:34 SQL> select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300
14 rows selected.
09:56:38 SQL> select * from dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
09:56:41 SQL> flashback table dept to timestamp to_timestamp('2007-08-19 9:50:00','yyyy-mm-dd hh24:mi:ss') ;
Flashback complete.
10:09:50 SQL> select * from dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10:09:53 SQL> flashback table emp to timestamp to_timestamp('2007-08-19 9:47:46','yyyy-mm-dd hh24:mi:ss') ;
flashback table emp to timestamp to_timestamp('2007-08-19 9:47:46','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/350519/viewspace-964108/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Flashback】Flashback Table功能實踐
- flashback技術之---flashback table
- SQL FOREIGN KEY 約束SQL
- oracle flashback特性(2.1)--Flashback Table之RECYCLEBINOracle
- With KEY & With Table KEY 的使用
- 應用oracle flashback--Flashback Table之RECYCLEBINOracle
- mysql 使用foreign key（外來鍵）MySql
- Flashback database與flashback table使用條件區別Database
- 【徵文】應用oracle flashback(2.1)--Flashback Table之RECYCLEBINOracle
- 關於primary key和foreign key的問題處理
- foreign key的一些總結
- flashback drop/query/table/database/archiveDatabaseHive
- 影響flashback table的操作！
- 全面學習oracle flashback特性(2.1)--Flashback Table之RECYCLEBINOracle
- 詳解外來鍵約束（foreign key）
- oracle flashback特性(2.2)--Flashback Table之從UNDO中恢復Oracle
- 【徵文】應用oracle flashback(2.3)--Flashback Table之注意事項Oracle
- Mysql 外來鍵(FOREIGN KEY)使用注意事項MySql
- 全面學習oracle flashback特性(2.3)--Flashback Table之注意事項Oracle
- 【徵文】應用oracle flashback(2.2)--Flashback Table之從UNDO中恢復Oracle
- 外來鍵刪除(T-SQL Drop Foreign Key)SQL
- Script: To list Foreign Key Constraints (Doc ID 1039297.6)AI
- 全面學習oracle flashback特性(2.2)--Flashback Table之從UNDO中恢復Oracle
- sys使用者不支援flashback table特性！
- Script to Check for Foreign Key Locking Issues [ID 1019527.6]
- [轉] mysql 外來鍵（Foreign Key）的詳解和例項MySql
- imp 匯入遇到 FK (Foreign Key) 導致錯誤處理
- sql_mode...foreign_key_checks...unique_checks...sql_notes不能為nullSQLNull
- 【Foreign Key】Oracle外來鍵約束三種刪除行為Oracle
- Oracle 1Z0 053 Q666(Flashback Table)Oracle
- Oracle10g New Feature -- 3.Flashback TableOracle
- 聊聊Oracle外來鍵約束（Foreign Key）的幾個操作選項Oracle
- Oracle OCP 1Z0-053 Q686(Flashback Table)Oracle
- Oracle10g 回收站及徹底刪除table : drop table xx purge 以及flashbackOracle
- 建立外來鍵時報 Cannot add foreign key constraint 解決方法AI
- 如何使外來鍵(Foreign Key)或其他constraint失效的語句AI
- Oracle OCP 1Z0 053 Q373(Flashback Table)Oracle
- Oracle OCP 1Z0-053 Q385(Flashback Table Recovery)Oracle