{"id":555,"date":"2015-11-04T08:43:11","date_gmt":"2015-11-04T08:43:11","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=555"},"modified":"2015-11-04T08:43:11","modified_gmt":"2015-11-04T08:43:11","slug":"oracle-flashback-%ec%a0%95%eb%a6%ac","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=555","title":{"rendered":"Oracle Flashback \uc815\ub9ac"},"content":{"rendered":"

\n <\/p>\n

    \n

  1. \n

    Flashback
    \n<\/h1>\n<\/li>\n
  2. \n

    Flashback\uc774\ub780<\/span>?
    \n<\/h2>\n

    \uc0ac\uc6a9\uc790 \uc2e4\uc218\uc5d0 \uc758\ud55c \uc190\uc0c1\ub41c \ub370\uc774\ud130\ub97c Database\uc758 \ud06c\uae30\uc640 \uc0c1\uad00\uc5c6\uc774 \ubcf5\uad6c\ub97c \ud560\uc218 \uc788\ub294\uae30\ub2a5\uc774\ub2e4. \uc774 Flashback \uae30\ub2a5\uc740 \uc77c\ubc18\uc801\uc778 \ubcf5\uad6c\uc5d0\uc11c \uc6b0\ub824\ub418\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc758\ud06c\uae30\ub97c \uac71\uc815\ud558\uc9c0 \uc54a\uc544\ub3c4 \ub41c\ub2e4.
    \n<\/span><\/p>\n

    \ubcf4\ud1b5\uc758 \uc0ac\uc6a9\uc790 \uc2e4\uc218\ub294 \ucee4\ub2e4\ub780\uc2dc\uc2a4\ud15c \uc7a5\uc560\uac00\uc218\ubc18\ub418\uba70, \uc774\ub97c \ubcf5\uad6c\ud558\uae30 \uc704\ud574\uc11c\ub294\ub9ce\uc740 \uc790\uc6d0\uacfc \uc2dc\uac04\uc774 \ud544\uc694\ud558\ub2e4. \ud558\uc9c0\ub9cc 9i\uc5d0\uc11c \uc9c0\uc6d0\ub418\ub294 flashback query\uc640 10g\uc5d0\uc11c \uc9c0\uc6d0\ud558\ub294 \ub2e4\uc591\ud55c flashback\uc744\ud1b5\ud558\uc5ec \uc190\uc27d\uac8c \uc0ac\uc6a9\uc790\uc2e4\uc218\ub97c \uc190\uc27d\uac8c \ubcf5\uad6c\ud55c\ub2e4.
    \n<\/span><\/p>\n

    Oracle 9i \ubd80\ud130\ub294 AUM \ud658\uacbd\ud558\uc5d0\uc11c Flashback \uae30\ub2a5\uc744 \uc774\uc6a9\ud558\uc5ec \uc798\ubabb\ub41c DML operation \uc73c\ub85c \uc778\ud55c \ubcf5\uad6c\ub97c \uc27d\uac8c \ud560 \uc218 \uc788\ub2e4. \ubb3c\ub860 \uc774\uc804\uae4c\uc9c0 \ud588\ub358 \ubc29\ubc95\uc778 Point in Time Recovery \ub610\ud55c \uc720\ud6a8\ud558\ub2e4.
    \n<\/span><\/p>\n

    \n <\/p>\n

    \u25b3 9i : Flashback query
    \n<\/span><\/p>\n

    \u25b3 10g : Flashback Database
    \n<\/span><\/p>\n

    Flashback Drop
    \n<\/span><\/p>\n

    Flashback Version Query
    \n<\/span><\/p>\n

    Flashback Transaction Query
    \n<\/span><\/p>\n

    Flashback Table
    \n<\/span><\/p>\n

    Oracle Flashback Feature\ub294 10g Standard Edition\uc5d0\uc11c\ub294 \uc9c0\uc6d0\ud558\uc9c0 \uc54a\ub294\ub2e4.
    \n\t\t\t\t<\/span><\/p>\n

    Note <\/strong>: \uc5ec\uae30\uc11c \ud55c \uac00\uc9c0 \uc9da\uace0 \ub118\uc5b4\uac08 \uc810\uc740 Flashback table, Flashback Database, Flashback Drop, Flashback Version Query, Flashback Transaction Query\ub294 \uc544\ub798\uc758 \ud45c\uc640 \uac19\uc774 \uac01\uae30 \ub2e4\ub978 \uc601\uc5ed\uc744 \uc0ac\uc6a9\ud55c\ub2e4\ub294 \uc810\uc774\ub2e4.
    \n<\/span><\/p>\n

    \n <\/p>\n

    Flashback Technologies
    \n<\/strong><\/span><\/p>\n

    \n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n
    \n

    Flashback Operation<\/strong><\/span> <\/p>\n<\/td>\n

    		\n

    Implementation<\/strong><\/span> <\/p>\n<\/td>\n<\/tr>\n

    \n

    Flashback Database<\/span> <\/p>\n<\/td>\n

    		\n

    Flashback logs + Redo logs<\/span> <\/p>\n<\/td>\n<\/tr>\n

    \n

    Flashback Drop<\/span> <\/p>\n<\/td>\n

    		\n

    Recycle bin<\/span> <\/p>\n<\/td>\n<\/tr>\n

    \n

    Flashback Version Query<\/span> <\/p>\n<\/td>\n

    		\n

    Undo<\/span> <\/p>\n<\/td>\n<\/tr>\n

    \n

    Flashback Transaction Query<\/span> <\/p>\n<\/td>\n

    		\n

    Undo<\/span> <\/p>\n<\/td>\n<\/tr>\n

    \n

    Flashback Table<\/span> <\/p>\n<\/td>\n

    		\n

    Undo<\/span> <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    \n <\/p>\n<\/li>\n

  3. \n

    Flashback(9i)
    \n<\/h2>\n<\/li>\n
  4. \n

    Flashback Overview
    \n<\/h3>\n
    \n\n\n<\/colgroup>\n\n\n
    \n

    – Oracle 9i New features
    \n<\/span><\/p>\n

    – Flashback\uc740 \uc0ac\uc6a9\uc790\uac00 Database\uc758 \uacfc\uac70 \uc2dc\uc810\uc758 Consistent view\ub97c \ubcfc \uc218 \uc788\uac8c \ud574\uc900\ub2e4.
    \n<\/span><\/p>\n

    – \uc0ac\uc6a9\uc790\ub4e4\uc740 System time or SCN \ub97c \uae30\ucd08\ub85c Read-only view\ub97c \uc0dd\uc131\ud560 \uc218 \uc788\ub2e4.
    \n<\/span><\/p>\n

    – \uadf8 \uc2dc\uc810\uc758 Transaction committed \ubd80\ubd84\ub9cc \ubcfc \uc218 \uc788\ub2e4.
    \n<\/span><\/p>\n

    – Self-service repair\ub97c \uac00\ub2a5\ud558\uac8c \ud574\uc900\ub2e4.
    \n<\/span><\/p>\n

    – DDL\uc740 \uc9c0\uc6d0\ud558\uc9c0 \uc54a\ub294\ub2e4.
    \n<\/span><\/p>\n

    – Flashback\uc740 AUM (Automate Undo Management) \uc0ac\uc6a9\uc2dc\ub9cc \uac00\ub2a5\ud558\ub2e4.
    \n<\/span><\/p>\n

    – Undo \uc815\ubcf4\ub294 System level\uc758 Undo retention \uae30\uac04 \ub3d9\uc548\ub9cc \uc720\uc9c0\ud55c\ub2e4.
    \n<\/span><\/p>\n

    – Flashback\uc740 Session level\uc5d0\uc11c Enabled \ud560 \uc218 \uc788\ub2e4.
    \n<\/span><\/p>\n

    – Flashback \uae30\ub2a5\uc744 disable \ud558\uae30 \uc804\uc5d0 open\ub41c PL\/SQL cursor\ub97c \uc774\uc6a9\ud558\uba74 disable \uc2dc\ud0a8 \ud6c4\uc5d0\ub294
    \n<\/span><\/p>\n

    DML\ub97c \ud1b5\ud574\uc11c self-service repair\ub97c \ud560 \uc218 \uc788\ub2e4.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    \u25b2 <\/span>Undo Retention \uc9c0\uc815
    \n<\/span><\/span><\/p>\n

    SQL> connect \/as sysdba
    \n<\/span><\/p>\n

    SQL> alter system set undo_retention = <seconds> ;
    \n<\/span><\/p>\n

    \n <\/p>\n

    \uc774 parameter\uc740 dynamic\ud558\uac8c \ubcc0\uacbd\uc774 \uac00\ub2a5\ud558\uba70 initSID.ora\uc5d0 \uc9c0\uc815\ud560 \uc218 \uc788\ub2e4.
    \n<\/span><\/p>\n

    undo_retention\uc740 \uac01 Site\ubcc4\ub85c \uc5c5\ubb34 \uc131\uaca9 \ubc0f Undo Size\uc5d0 \ub530\ub77c\uc11c \uc801\uc808\ud558\uac8c \uc0b0\uc815\ud574\uc11c \uba85\uc2dc\ud574 \uc900\ub2e4. \ub610\ud55c undo_management=auto \uc778\uc9c0 \ud655\uc778\ud55c\ub2e4.
    \n<\/span><\/p>\n

    \n <\/p>\n

    \u25b2 <\/span>\uad8c\ud55c \ubd80\uc5ec
    \n<\/span><\/span><\/p>\n

    SQL> grant execute on dbms_flashback to scott;
    \n<\/span><\/p>\n

    \n <\/p>\n<\/li>\n

  5. \n

    \uc608\uc81c 1 (AS OF SCN)
    \n<\/h3>\n

    \u25b2 SCOTT session<\/span>\uc5d0\uc11c<\/span> SYSTEMSTAMP<\/span>\ub97c<\/span>
    \n\t\t\t\t\t<\/span>\uc774\uc6a9\ud558\uc5ec<\/span>
    \n\t\t\t\t\t<\/span>\ud604\uc7ac<\/span>
    \n\t\t\t\t\t<\/span>\uc2dc\uac04\uc744<\/span>
    \n\t\t\t\t\t<\/span>\uc870\ud68c\ud558\uc2dc\uc624<\/span>.
    \n<\/span><\/span><\/p>\n

    SQL> conn soctt\/tiger
    \n<\/span><\/span><\/p>\n

    Connected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select systimestamp from dual;
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    SYSTIMESTAMP
    \n<\/span><\/p>\n

    —————————————————————————
    \n<\/span><\/p>\n

    07-AUG-05 07.40.22.644800 AM -07:00
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    SQL> conn \/as sysdba
    \n<\/span><\/span><\/p>\n

    Connected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> grant execute on dbms_flashback to scott;
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    Grant succeeded.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> conn scott\/tiger
    \n<\/span><\/span><\/p>\n

    Connected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select dbms_flashback.get_system_change_number() from dual;
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
    \n<\/span><\/p>\n

    —————————————–
    \n<\/span><\/p>\n

    851258
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    \u25b2 SCOTT <\/span>\uc18c\uc720\uc758<\/span> Table<\/span>\uc5d0\uc11c<\/span>
    \n\t\t\t\t\t<\/span>\ubd80\uc11c\ubc88\ud638\uac00<\/span> 20<\/span>\uc778<\/span>
    \n\t\t\t\t\t<\/span>\ubd80\uc11c\uc6d0<\/span>, <\/span>\ubd80\uc11c\uc815\ubcf4\ub97c<\/span>
    \n\t\t\t\t\t<\/span>\ubaa8\ub450<\/span>
    \n\t\t\t\t\t<\/span>\uc0ad\uc81c<\/span>, Commit <\/span>\ud558\uc2dc\uc624<\/span>.
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    SQL> delete from emp where deptno=20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    5 rows deleted.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    \u25b2 <\/span>\uc0ad\uc81c\ub41c<\/span> Data<\/span>\uac00<\/span>
    \n\t\t\t\t\t<\/span>\uc798<\/span>
    \n\t\t\t\t\t<\/span>\ubabb<\/span>
    \n\t\t\t\t\t<\/span>\uc0ad\uc81c\ub41c<\/span>
    \n\t\t\t\t\t<\/span>\uac83\uc744<\/span>
    \n\t\t\t\t\t<\/span>\uc54c\uac8c<\/span>
    \n\t\t\t\t\t<\/span>\ub418\uc5c8\ub2e4<\/span>. <\/span>\uc0ad\uc81c\ub41c<\/span> Data<\/span>\ub97c<\/span>
    \n\t\t\t\t\t<\/span>\ub2e4\uc2dc<\/span>
    \n\t\t\t\t\t<\/span>\ub418\uc0b4\ub9ac\uace0\uc790<\/span>
    \n\t\t\t\t\t<\/span>\ud55c\ub2e4<\/span>.
    \n<\/span><\/span><\/p>\n

    \u203b<\/span> 5<\/span>\ubd84<\/span>
    \n\t\t\t\t\t\t<\/span>\ud6c4\uc5d0<\/span>
    \n\t\t\t\t\t\t<\/span><\/strong><\/span><\/p>\n

    \n <\/p>\n

    SQL> !date<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    Sun Aug 7 07:40:59 PDT 2005
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from emp where deptno=20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    no rows selected
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from emp as of scn 851258
    \n\t\t\t\t\t\t\t\t<\/span><\/strong>where deptno=20;<\/span>
    \n\t\t\t\t\t<\/span><\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ——— ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 17-DEC-80 800 20
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 02-APR-81 2975 20
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 12-JAN-83 1100 20
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 03-DEC-81 3000 20
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> insert into emp
    \n<\/span><\/span><\/p>\n

    2 select * from emp<\/span>
    \n\t\t\t\t\tas of scn 851258<\/strong><\/span>
    \n\t\t\t\t<\/span><\/p>\n

    3 where deptno=20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    5 rows created.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from emp
    \n<\/span><\/span><\/p>\n

    2 where deptno=20;
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ——— ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 17-DEC-80 800 20
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 02-APR-81 2975 20
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 12-JAN-83 1100 20
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 03-DEC-81 3000 20
    \n<\/span><\/p>\n

    \n <\/p>\n<\/li>\n

  6. \n

    \uc608\uc81c 2 (AS OF TIMESTAMP)
    \n<\/h3>\n

    \n <\/p>\n

    \u25b2 HR_TEST01 session<\/span>\uc5d0\uc11c<\/span> SYSTEMSTAMP<\/span>\ub97c<\/span>
    \n\t\t\t\t\t<\/span>\uc774\uc6a9\ud558\uc5ec<\/span>
    \n\t\t\t\t\t<\/span>\ud578\uc7ac<\/span>
    \n\t\t\t\t\t<\/span>\uc2dc\uac04\uc744<\/span>
    \n\t\t\t\t\t<\/span>\uc870\ud68c\ud558\uc2dc\uc624<\/span>.
    \n<\/span><\/span><\/p>\n

    SQL> show user<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    USER is “HR_TEST1”
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select systimestamp from dual;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    SYSTIMESTAMP
    \n<\/span><\/p>\n

    —————————————————————————
    \n<\/span><\/p>\n

    02-AUG-05 03.09.52.609909 AM -05:00
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    SQL> delete emp_test1 where department_id=20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    2 rows deleted.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select employee_id from emp_test1<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    2 where department_id=20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    no rows selected
    \n<\/span><\/p>\n

    \n <\/p>\n

    \u25b2 <\/span>\uc0ad\uc81c\ub41c<\/span> Data<\/span>\uac00<\/span>
    \n\t\t\t\t\t<\/span>\uc798<\/span>
    \n\t\t\t\t\t<\/span>\ubabb<\/span>
    \n\t\t\t\t\t<\/span>\uc0ad\uc81c\ub41c<\/span>
    \n\t\t\t\t\t<\/span>\uac83\uc744<\/span>
    \n\t\t\t\t\t<\/span>\uc54c\uac8c<\/span>
    \n\t\t\t\t\t<\/span>\ub418\uc5c8\ub2e4<\/span>. <\/span>\uc0ad\uc81c\ub41c<\/span> Data<\/span>\ub97c<\/span>
    \n\t\t\t\t\t<\/span>\ub2e4\uc2dc<\/span>
    \n\t\t\t\t\t<\/span>\ub418\uc0b4\ub9ac\uace0\uc790<\/span>
    \n\t\t\t\t\t<\/span>\ud55c\ub2e4<\/span>.
    \n<\/span><\/span><\/p>\n

    \u203b<\/span> 5<\/span>\ubd84<\/span>
    \n\t\t\t\t\t\t<\/span>\ud6c4\uc5d0<\/span>
    \n\t\t\t\t\t\t<\/span><\/strong><\/span><\/p>\n

    SQL> select employee_id<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    2 from emp_test1 as of timestamp(to_timestamp(’02-AUG-05:03:11:00′, ‘DD-MON-YY:HH24:MI:SS’))<\/strong><\/span>
    \n\t\t\t\t\t<\/span><\/span><\/p>\n

    3* where department_id=20<\/span>;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPLOYEE_ID
    \n<\/span><\/p>\n

    ———–
    \n<\/span><\/p>\n

    201
    \n<\/strong><\/span><\/p>\n

    202
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    SQL> select employee_id<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    2 from emp_test1<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    3 where department_id=20;
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    no rows selected
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> insert into emp_test1<\/span>
    \n\t\t\t\t\t<\/strong><\/span><\/p>\n

    2 select * from emp_test1 as of timestamp(to_timestamp(’02-AUG-05:03:11:00′, ‘DD-MON-YY:HH24:MI:SS’))<\/strong><\/span>
    \n\t\t\t\t\t<\/span><\/span><\/p>\n

    3 where department_id=20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    2 rows created.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select employee_id<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    2 from emp_test1<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    3* where department_id=20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    EMPLOYEE_ID
    \n<\/span><\/p>\n

    ———–
    \n<\/span><\/p>\n

    201
    \n<\/span><\/p>\n

    202
    \n<\/span><\/p>\n

    \n <\/p>\n<\/li>\n

  7. \n

    \uc608\uc81c 3 (Package, SCN \/ timestamp)
    \n<\/h3>\n

    \u25b2 HR_TEST01 session<\/span>\uc5d0\uc11c<\/span> SYSTEMSTAMP<\/span>\ub97c<\/span>
    \n\t\t\t\t\t<\/span>\uc774\uc6a9\ud558\uc5ec<\/span>
    \n\t\t\t\t\t<\/span>\ud578\uc7ac<\/span>
    \n\t\t\t\t\t<\/span>\uc2dc\uac04\uc744<\/span>
    \n\t\t\t\t\t<\/span>\uc870\ud68c\ud558\uc2dc\uc624<\/span>.
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    SQL> show user<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    USER is “HR_TEST1”
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select systimestamp from dual;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    SYSTIMESTAMP
    \n<\/span><\/p>\n

    —————————————————————————
    \n<\/span><\/p>\n

    02-AUG-05 03.31.21.752682 AM -05:00
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    SQL> conn \/as sysdba<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    Connected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select dbms_flashback.get_system_change_number() from dual;
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
    \n<\/span><\/p>\n

    —————————————–
    \n<\/span><\/p>\n

    107270
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    SQL> conn hr_test1\/oracle<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    Connected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> delete from emp_test1<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    2 where department_id=20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    2 rows deleted.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> delete from dept_test1<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    2 where department_id=20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    1 row deleted.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    \u25b2 <\/span>\uc0ad\uc81c\ub41c<\/span> Data<\/span>\uac00<\/span>
    \n\t\t\t\t\t<\/span>\uc798<\/span>
    \n\t\t\t\t\t<\/span>\ubabb<\/span>
    \n\t\t\t\t\t<\/span>\uc0ad\uc81c\ub41c<\/span>
    \n\t\t\t\t\t<\/span>\uac83\uc744<\/span>
    \n\t\t\t\t\t<\/span>\uc54c\uac8c<\/span>
    \n\t\t\t\t\t<\/span>\ub418\uc5c8\ub2e4<\/span>. <\/span>\uc0ad\uc81c\ub41c<\/span> Data<\/span>\ub97c<\/span>
    \n\t\t\t\t\t<\/span>\ub2e4\uc2dc<\/span>
    \n\t\t\t\t\t<\/span>\ub418\uc0b4\ub9ac\uace0\uc790<\/span>
    \n\t\t\t\t\t<\/span>\ud55c\ub2e4<\/span>.
    \n<\/span><\/span><\/p>\n

    \u203b<\/span> 5<\/span>\ubd84<\/span>
    \n\t\t\t\t\t\t<\/span>\ud6c4\uc5d0<\/span>
    \n\t\t\t\t\t\t<\/span><\/strong><\/span><\/p>\n

    \n <\/p>\n

    SQL> conn \/as sysdba<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    SQL> grant execute on dbms_flashback to hr_test1;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    Grant succeeded.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> conn hr_test01\/oracle<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    SQL> begin
    \n<\/span><\/span><\/p>\n

    dbms_flashback.enable_at_system_change_number(107270);<\/strong>
    \n\t\t\t\t\t\t<\/span><\/span><\/span><\/p>\n

    end;
    \n<\/span><\/p>\n

    \/
    \n<\/span><\/p>\n

    \n <\/p>\n

    PL\/SQL procedure successfully completed.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select employee_id from emp_test1
    \n<\/span><\/span><\/p>\n

    where department_id=20;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPLOYEE_ID
    \n<\/span><\/p>\n

    ———–
    \n<\/span><\/p>\n

    201
    \n<\/span><\/p>\n

    202
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from dept_test1
    \n<\/span><\/span><\/p>\n

    where department_id=20;
    \n<\/span><\/p>\n

    \n <\/p>\n

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    \n<\/span><\/p>\n

    ————- —————————— ———- ———–
    \n<\/span><\/p>\n

    20 Marketing 201 1800
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> begin
    \n<\/span><\/span><\/p>\n

    dbms_flashback.disable;<\/strong>
    \n\t\t\t\t\t\t<\/span><\/span><\/span><\/p>\n

    end;
    \n<\/span><\/p>\n

    \/
    \n<\/span><\/p>\n

    \n <\/p>\n

    PL\/SQL procedure successfully completed.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> declare
    \n<\/span><\/span><\/p>\n

    cursor emp_curs is
    \n<\/span><\/p>\n

    select * from emp_test1 where department_id = 20;
    \n<\/span><\/p>\n

    cursor dept_curs is
    \n<\/span><\/p>\n

    select * from dept_test1 where department_id = 20;
    \n<\/span><\/p>\n

    \n <\/p>\n

    emp_rec emp_curs%ROWTYPE;
    \n<\/span><\/p>\n

    \n <\/p>\n

    dept_rec dept_curs%ROWTYPE;
    \n<\/span><\/p>\n

    \n <\/p>\n

    begin
    \n<\/span><\/p>\n

    dbms_flashback.enable_at_system_change_number(107270);
    \n\t\t\t\t\t\t\t<\/span><\/strong><\/span><\/span><\/p>\n

    — dbms_flashback.enable_at_time(to_timestamp(‘ 02-AUG-05:03.31:21′,’DD-MON-YY:HH24:MI:SS’));
    \n<\/strong><\/span><\/span><\/p>\n

    \n <\/p>\n

    open emp_curs;
    \n<\/span><\/p>\n

    open dept_curs;
    \n<\/span><\/p>\n

    dbms_flashback.disable;
    \n<\/span><\/p>\n

    \n <\/p>\n

    loop
    \n<\/span><\/p>\n

    fetch dept_curs into dept_rec;
    \n<\/span><\/p>\n

    exit when dept_curs%NOTFOUND;
    \n<\/span><\/p>\n

    insert into dept_test1 values(dept_rec.DEPARTMENT_ID,
    \n<\/span><\/p>\n

    dept_rec.DEPARTMENT_NAME, dept_rec.MANAGER_ID,
    \n<\/span><\/p>\n

    dept_rec.LOCATION_ID);
    \n<\/span><\/p>\n

    end loop;
    \n<\/span><\/p>\n

    \n <\/p>\n

    loop
    \n<\/span><\/p>\n

    fetch emp_curs into emp_rec;
    \n<\/span><\/p>\n

    exit when emp_curs%NOTFOUND;
    \n<\/span><\/p>\n

    insert into emp_test1 values(emp_rec.employee_id, emp_rec.first_name,emp_rec.last_name, emp_rec.email,
    \n<\/span><\/p>\n

    emp_rec.phone_number,emp_rec.hire_date, emp_rec.job_id, emp_rec.salary,
    \n<\/span><\/p>\n

    emp_rec.commission_pct, emp_rec.manager_id, emp_rec.department_id);
    \n<\/span><\/p>\n

    end loop;
    \n<\/span><\/p>\n

    \n <\/p>\n

    end;
    \n<\/span><\/p>\n

    \/
    \n<\/span><\/p>\n

    \n <\/p>\n

    PL\/SQL procedure successfully completed.
    \n<\/span><\/p>\n

    \n <\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    \n <\/p>\n

    \u203b \ub610\ub294, \uc704 \ub0b4\uc6a9\uc744 Procedure\ub97c \uc0dd\uc131\ud574\uc11c \ubcf5\uad6c \ud560 \uc218\ub3c4 \uc788\ub2e4.(flash.sql)
    \n<\/span><\/p>\n

    \n <\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    create or replace procedure exam_flash
    \n<\/span><\/p>\n

    as
    \n<\/span><\/p>\n

        cursor tmp_curs is
    \n<\/span><\/p>\n

            select * from emp where deptno = 20;
    \n<\/span><\/p>\n

        emp_rec tmp_curs%ROWTYPE;
    \n<\/span><\/p>\n

    \n <\/p>\n

    begin
    \n<\/span><\/p>\n

        dbms_flashback.enable_at_time(’03\/08\/21 10:47:32 ‘);
    \n<\/strong><\/span><\/p>\n

        open tmp_curs;
    \n<\/span><\/p>\n

        dbms_flashback.disable;
    \n<\/span><\/p>\n

        
    \n<\/span><\/p>\n

        loop
    \n<\/span><\/p>\n

            fetch tmp_curs into emp_rec;
    \n<\/span><\/p>\n

            exit when tmp_curs%NOTFOUND;
    \n<\/span><\/p>\n

            insert into emp values (
    \n<\/span><\/p>\n

                emp_rec.empno, emp_rec.ename, emp_rec.job,
    \n<\/span><\/p>\n

                emp_rec.mgr,emp_rec.hiredate, emp_rec.sal,
    \n<\/span><\/p>\n

                emp_rec.comm, emp_rec.deptno
    \n<\/span><\/p>\n

            );
    \n<\/span><\/p>\n

        end loop;
    \n<\/span><\/p>\n

    end;
    \n<\/span><\/p>\n

    \/<\/span> <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    Flashback\uc744 \uc774\uc6a9\ud574 \uacfc\uac70 \ub370\uc774\ud130 \ubcf5\uad6c
    \n<\/span><\/p>\n

    SQL> @flash \/* exam_flash procedure \uc0dd\uc131 *\/
    \n<\/span><\/p>\n

    SQL> exec exam_flash
    \n<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    SQL> select employee_id from emp_test1 where department_id = 20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    EMPLOYEE_ID
    \n<\/span><\/p>\n

    ———–
    \n<\/span><\/p>\n

    201
    \n<\/span><\/p>\n

    202
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from dept_test1 where department_id=20;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    \n<\/span><\/p>\n

    ————- —————————— ———- ———–
    \n<\/span><\/p>\n

    20 Marketing 201 1800
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;<\/span>
    \n\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    \n <\/p>\n<\/li>\n

  8. \n

    Flashback(10g)
    \n<\/h2>\n<\/li>\n
  9. \n

    Flashback Database
    \n<\/h3>\n

    Flashback Database <\/strong>\uac1c\uc694
    \n<\/span><\/p>\n

    Oracle Database 10g \uc774\uc804\uae4c\uc9c0\ub294 transactional point-in-time recovery\ub97c \uc704\ud574\uc11c\ub294 backup\uc6a9 file\uacfc redo log file\uc744 \uc774\uc6a9\ud558\uc5ec \uc6d0\ud558\ub294 \uc2dc\uac04\uae4c\uc9c0\uc758 \ubcf5\uad6c\ub97c \ud558\uc600\uc5c8\ub2e4. \uadf8\ub7ec\ub098 \uc774 \ubc29\ubc95\uc740 backup\uc6a9 file\uc774 \uc624\ub798\ub41c \uac83\uc774\uba70, archive log\uac00 \ub9ce\uc774 \uc313\uc5ec \uc788\uc744 \ub54c\ub294 \ub9ce\uc740 \uc2dc\uac04\uc774 \uc18c\uc694\ub41c\ub2e4. Oracle Database 10g\ubd80\ud130\ub294 flashback database\ub97c \uc774\uc6a9\ud558\uc5ec \uc880 \ub354 \ube60\ub978 recovery\uac00 \uac00\ub2a5\ud558\uac8c \ub418\uc5c8\ub2e4.
    \n<\/span><\/span><\/span><\/p>\n

    Flashback database\uc758 \uc0ac\uc6a9 \uc6a9\ub3c4\ub294 logical data corruption\uc774\ub098 user error\uc2dc \uc720\uc6a9\ud558\ub2e4.
    \n<\/span><\/p>\n

    (Physical data corruption\uc740 H\/W \ubb38\uc81c\uc774\uae30 \ub54c\ubb38\uc5d0 Flashback database\ub85c recovery\ub294 \ubd88\uac00\ub2a5\ud558\ub2e4.) Flashback Database\uc758 \uc7a5\uc810\uc740 \uae30\uc874\uc758 traditional point-in-time recovery\uc5d0 \ube44\ud574 \ub9e4\uc6b0 \ube60\ub978 recovery\uac00 \uac00\ub2a5\ud558\ub2e4\ub294 \uac83\uc774\ub2e4. \uc774\ub7ec\ud55c \ube60\ub978 \uc131\ub2a5\uc744 \ub0bc \uc218 \uc788\ub294 \uc774\uc720\ub294 flashback database\ub294 database\uc758 \ud06c\uae30\uc5d0 \ube44\ub840\ud574\uc11c recovery\uc2dc\uac04\uc774 \ub298\uc5b4\ub098\ub294 \uac83\uc774 \uc544\ub2c8\ub77c, \ubcc0\uacbd\ub41c data\uc758 \uc591\uc5d0 \ube44\ub840\ud574\uc11c recovery\uc2dc\uac04\uc774 \uac78\ub9b0\ub2e4\ub294 \uc810\uc774\ub2e4.
    \n<\/span><\/p>\n

    \n <\/p>\n


    \n\t\t\t\t<\/span><\/p>\n

    \uc704\uc758 \uadf8\ub9bc<\/span>, <\/span>\uc55e\uc758 \uc124\uba85\uacfc \uac19\uc774 <\/span>Flashback Database<\/span>\ub294 \ub9e4\uc6b0 \ube60\ub978 \uc2dc\uac04\uc758 <\/span>recovery<\/span>\ub97c \uac00\ub2a5\ud558\uac8c \ud55c\ub2e4<\/span>.
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    Flashback Database\ub97c \uc218\ud589\ud558\uae30 \uc704\ud55c 3\uac00\uc9c0 \uad6c\uc131\uc694\uc18c
    \n<\/strong><\/span><\/p>\n

    1. Archive Mode
    \n<\/span><\/p>\n

    Flashback Database \uae30\ub2a5\uc744 \uc801\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 Archive Mode\ub85c \uc124\uc815\ud558\uc5ec\uc57c \ud55c\ub2e4.
    \n<\/span><\/p>\n

    \n <\/p>\n

    2. Flashback Log File
    \n<\/span><\/p>\n

    Flashback Log File\uc740 \uc624\ub77c\ud074 Database\ub97c \uad6c\uc131\ud558\ub294 Block(\ubcc0\uacbd\ub418\uae30 \uc774\uc804\uc758 \uc774\ubbf8\uc9c0 Block)\uc744 \uc800\uc7a5\ud558\ub294 \ub85c\uadf8 \ud30c\uc77c\ub85c\uc11c 10g\uc5d0\uc11c \uc0c8\ub86d\uac8c \uc18c\uac1c\ub418\uace0 \uc788\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ubcf5\uad6c\uc601\uc5ed(database recovery area)\uc5d0 \uc0dd\uc131\ub418\uc5b4\uc9c4\ub2e4.
    \n<\/span><\/p>\n

    \uae30\uc874\uc758 redo log\uc640\uc758 \ucc28\uc774\uc810
    – redo log\uc758 \uacbd\uc6b0\uc5d0\ub294 archive\ud560 \uc218 \uc788\ub294 \uae30\ub2a5\uc774 \ud568\uaed8 \uc81c\uacf5\ub418\uc5c8\uc9c0\ub9cc, Flashback Log\ub294 archive \uae30\ub2a5\uc774 \ub530\ub85c \uc81c\uacf5\ub420 \ud544\uc694\uac00 \uc5c6\ub2e4.(db_recovery_file_dest, db_recovery_file_dest_size)
    \n<\/span><\/p>\n

    – Flashback Log\uc758 \uacbd\uc6b0\uc5d0\ub294 \ubb3c\ub9ac\uc801\uc778 database \ubcf5\uad6c\uc5d0\ub294 \uc0ac\uc6a9\ub420\uc218 \uc5c6\ub2e4\ub294 \uc810\uc774\ub2e4.
    \n<\/span><\/p>\n

    \n <\/p>\n

    3. RVWR Background Process
    \n<\/span><\/p>\n

    Flashback Database \uae30\ub2a5\uc774 \ud65c\uc131\ud654 \ub418\uc5b4\uc9c0\uba74, rvwr\uc774\ub77c\ub294 background process\uac00 \uc2dc\uc791\ub41c\ub2e4.
    \n<\/span><\/p>\n

    \uc5ed\ud560 : Flashback Database Data\ub97c Flashback Log\uc5d0 \uae30\ub85d
    \n<\/span><\/p>\n

    \n <\/p>\n

    \n <\/p>\n

    \u2022 Flashback Database \ud14c\uc2a4\ud2b8
    \n<\/strong><\/span><\/p>\n

    Database \uc5d0 Flashback \uae30\ub2a5\uc774 ON \ub418\uc5b4 \uc788\ub294\uc9c0 \ud655\uc778\ud55c\ub2e4.
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> select FLASHBACK_ON from v$database;
    \n<\/span><\/p>\n

    \n <\/p>\n

    FLASHBACK_ON
    \n<\/span><\/p>\n

    ——————
    \n<\/span><\/p>\n

    NO
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> shutdown immediate;
    \n<\/span><\/p>\n

    Database closed.
    \n<\/span><\/p>\n

    Database dismounted.
    \n<\/span><\/p>\n

    ORACLE instance shut down.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> startup mount
    \n<\/span><\/p>\n

    ORACLE instance started.
    \n<\/span><\/p>\n

    \n <\/p>\n

    Total System Global Area 394264576 bytes
    \n<\/span><\/p>\n

    Fixed Size 2006632 bytes
    \n<\/span><\/p>\n

    Variable Size 218104216 bytes
    \n<\/span><\/p>\n

    Database Buffers 167772160 bytes
    \n<\/span><\/p>\n

    Redo Buffers 6381568 bytes
    \n<\/span><\/p>\n

    Database mounted.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select FLASHBACK_ON from v$database;
    \n<\/span><\/p>\n

    \n <\/p>\n

    FLASHBACK_ON
    \n<\/span><\/p>\n

    ——————
    \n<\/span><\/p>\n

    YES
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> alter database open;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Database altered.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    Test Case \uc0dd\uc131
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> conn scott\/tiger
    \n<\/span><\/p>\n

    Connected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> drop table flash;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Table dropped.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> conn scott\/tiger
    \n<\/span><\/p>\n

    Connected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> create table flash as select * from user_objects;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Table created.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select count(*) from flash;
    \n<\/span><\/p>\n

    \n <\/p>\n

    COUNT(*)
    \n<\/span><\/p>\n

    ———-
    \n<\/span><\/p>\n

    65
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select current_scn from v$database;
    \n<\/span><\/p>\n

    \n <\/p>\n

    CURRENT_SCN
    \n<\/span><\/p>\n

    ———–
    \n<\/span><\/p>\n

    25833961
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> truncate table flash;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Table truncated.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select current_scn from v$database;
    \n<\/span><\/p>\n

    \n <\/p>\n

    CURRENT_SCN
    \n<\/span><\/p>\n

    ———–
    \n<\/span><\/p>\n

    25833994
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select count(*) from flash;
    \n<\/span><\/p>\n

    \n <\/p>\n

    COUNT(*)
    \n<\/span><\/p>\n

    ———-
    \n<\/span><\/p>\n

    0<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    Flashback Database <\/strong>\ub97c \uc900\ube44\ud558\uae30 \uc704\ud574 Instance <\/strong>\ub97c \uc885\ub8cc\uc2dc\ud0a8\ub2e4.<\/strong>
    \n\t\t\t\t<\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> conn \/as sysdba
    \n<\/span><\/p>\n

    Connected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> shutdown immediate
    \n<\/span><\/p>\n

    Database closed.
    \n<\/span><\/p>\n

    Database dismounted.
    \n<\/span><\/p>\n

    ORACLE instance shut down.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    Flashback Database <\/strong>\ub97c \uc900\ube44\ud558\uae30 \uc704\ud574 Insatnce <\/strong>\ub97c Mount <\/strong>\uc2dc\ud0a8\ub2e4.
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> startup mount
    \n<\/span><\/p>\n

    ORACLE instance started.
    \n<\/span><\/p>\n

    \n <\/p>\n

    Total System Global Area 188743680 bytes
    \n<\/span><\/p>\n

    Fixed Size 778036 bytes
    \n<\/span><\/p>\n

    Variable Size 162537676 bytes
    \n<\/span><\/p>\n

    Database Buffers 25165824 bytes
    \n<\/span><\/p>\n

    Redo Buffers 262144 bytes
    \n<\/span><\/p>\n

    Database mounted.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    \uc6d0\ud558\ub294 \uc2dc\uc810\uc73c\ub85c \ub418\ub3cc\uc544 \uac00\uae30 \uc704\ud574 \uc870\uae08\uc804\uc5d0 \uae30\ub85d\ud588\ub358 SCN \uc73c\ub85c Flash Back \ud55c\ub2e4.
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n\n
    \n

    SQL> flashback database to scn 25833961;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Flashback complete.<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    alter_<SID>.log
    \n<\/span><\/p>\n

    \n <\/p>\n

    Incomplete Recovery applied until change 25833975
    \n<\/span><\/p>\n

    Flashback Media Recovery Complete
    \n<\/span><\/p>\n

    Completed: flashback database to scn 25833961<\/span> <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    Database \ub97c read only\ub85c open \ud558\uc5ec Data\ub97c \ud655\uc778 \ud6c4\uc5d0, Resetlogs \ub85c Open \ud558\uc5ec truncate \uc804\uc758 \ub370\uc774\ud130\ub97c \ubcf5\uad6c\ud55c\ub2e4.
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> alter database open;
    \n<\/span><\/p>\n

    alter database open
    \n<\/span><\/p>\n

    *
    \n<\/span><\/p>\n

    ERROR at line 1:
    \n<\/span><\/p>\n

    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> alter database open read only;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Database altered.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select count(*) from scott.flash;
    \n<\/span><\/p>\n

    \n <\/p>\n

    COUNT(*)
    \n<\/span><\/p>\n

    ———-
    \n<\/span><\/p>\n

    65
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> shutdown immediate;
    \n<\/span><\/p>\n

    Database closed.
    \n<\/span><\/p>\n

    Database dismounted.
    \n<\/span><\/p>\n

    ORACLE instance shut down.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> startup mount;
    \n<\/span><\/p>\n

    ORACLE instance started.
    \n<\/span><\/p>\n

    \n <\/p>\n

    Total System Global Area 394264576 bytes
    \n<\/span><\/p>\n

    Fixed Size 2006632 bytes
    \n<\/span><\/p>\n

    Variable Size 218104216 bytes
    \n<\/span><\/p>\n

    Database Buffers 167772160 bytes
    \n<\/span><\/p>\n

    Redo Buffers 6381568 bytes
    \n<\/span><\/p>\n

    Database mounted.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> alter database open resetlogs;
    \n<\/span><\/p>\n

    Database altered.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select count(*) from scott.flash;
    \n<\/span><\/p>\n

    \n <\/p>\n

    COUNT(*)
    \n<\/span><\/p>\n

    ———-
    \n<\/span><\/p>\n

    65<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    \n <\/p>\n<\/li>\n

  10. \n

    Flashback Drop
    \n<\/h3>\n

    \u25b3 10g\uc5d0\uc11c DROP TABLE\uc744 \ud558\uac8c \ub418\uba74 \uae30\ubcf8\uc801\uc73c\ub85c \uc2e4\uc81c \uadf8\uac83\uc744 DROP \ud558\ub294 \uac83\ubcf4\ub2e4 RECYCLE BIN\uc5d0 \uc774\ub3d9 \uc2dc\ud0a4\uac70\ub098 \uc774\ub984\uc744 \ubc14\uafb8\uac8c \ub429\ub2c8\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 Drop \ub41c Table\uc744 \ubcf5\uad6c\ud55c\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 Drop table\uc774 \uc644\uc804 drop \ub418\uc9c0 \uc54a\uace0, window\uc758 \ud734\uc9c0\ud1b5\uacfc \uac19\uc740 recyclebin\uc5d0 \ubcf4\uad00\ub41c\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 \uc774 drop\ub41c table\uc740 \uc644\uc804 \uc0ad\uc81c\ub97c \uc704\ud574\uc11c\ub294 purge \uc791\uc5c5\uc774 \ud544\uc694\ud558\uba70, space\uac00 \ubd80\uc871\ud55c \uacbd\uc6b0\uc5d0\ub294 \uc790\ub3d9 reuse\ub41c\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 Drop\ub418\uc5b4 recyclebin\uc5d0 \uc788\ub294 bin$xxxxxx table\uc5d0 \ub300\ud55c \uc9c1\uc811\uc870\ud68c\ub3c4 \uac00\ub2a5\ud568.
    \n<\/span><\/p>\n

    \u25b3 \uad00\ub828 view
    \n<\/span><\/p>\n

    – dba_recyclebin, user_recyclebin
    \n<\/span><\/p>\n

    \u25b3 \uad00\ub828 parameter
    \n<\/span><\/p>\n

    _recyclebin = FALSE : recyclebin \uae30\ub2a5\uc744 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\ub294 \uacbd\uc6b0 False\ub85c \uc9c0\uc815
    \n<\/span><\/p>\n

    \u25b3 \uc81c\uc57d\uc0ac\ud56d : table\uc774 system tablespace\uc5d0 \uc788\ub294 object\ub294 \ubcf5\uad6c \ubd88\uac00.
    \n<\/span><\/p>\n

    locally managed tablespace\uc5d0 \uc704\uce58\ud574 \uc788\ub294 table\ub9cc \ubcf5\uad6c \uac00\ub2a5.
    \n<\/span><\/p>\n

    Table\uc774 \ubcf5\uad6c\ub418\uba74 \uadf8 table\uc758 index, trigger \ub4f1\uc758 \uc5f0\uad00\ub41c object\ub3c4 \ud568\uaed8 \ubcf5\uad6c\ub41c\ub2e4.
    \n<\/span><\/p>\n

    (bitmap join index\uc81c\uc678)
    \n<\/span><\/p>\n

    Partioned index-organized table\uc740 recycle bin\uc5d0 \uc758\ud574 \ubcf4\ud638 \ubc1b\uc9c0 \ubabb\ud55c\ub2e4.
    recycle bin\uc740 \ucc38\uc870 \ubb34\uacb0\uc131\uc744 \ubcf4\uc7a5\ud558\uc9c0 \uc54a\ub294\ub2e4.
    \n<\/span><\/p>\n

    \n <\/p>\n

    \u2022 \uc608\uc81c 1
    \n<\/strong><\/span><\/p>\n

    1) Table\uc744 drop \ud558\uae30 (\uc7a5\uc560 \ub9cc\ub4e4\uae30)
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> select * from tab;
    \n<\/span><\/p>\n

    \n <\/p>\n

    TNAME TABTYPE CLUSTERID
    \n<\/span><\/p>\n

    —————————— ——- ———-
    \n<\/span><\/p>\n

    EMP TABLE
    \n<\/span><\/p>\n

    DEPT TABLE
    \n<\/span><\/p>\n

    BONUS TABLE
    \n<\/span><\/p>\n

    SALGRADE TABLE
    \n<\/span><\/p>\n

    DUMMY TABLE
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> drop table emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Table dropped.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from tab;
    \n<\/span><\/p>\n

    \n <\/p>\n

    TNAME TABTYPE CLUSTERID
    \n<\/span><\/p>\n

    —————————— ——- ———-
    \n<\/span><\/p>\n

    BIN$Lr5d8GB+sLbgQz36Y8Owtg==$0<\/strong> TABLE
    \n<\/span><\/p>\n

    DEPT TABLE
    \n<\/span><\/p>\n

    BONUS TABLE
    \n<\/span><\/p>\n

    SALGRADE TABLE
    \n<\/span><\/p>\n

    DUMMY TABLE
    \n<\/span><\/p>\n

    \n <\/p>\n

    Recycle Bin \ubcf4\uae30
    \n<\/strong><\/span><\/p>\n

    SQL> select object_name, original_name, type, DROPTIME, can_undrop from user_recyclebin
    \n<\/span><\/p>\n

    2 order by droptime;
    \n<\/span><\/p>\n

    \n <\/p>\n

    OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME CAN
    \n<\/span><\/p>\n

    —————————— ———————- ————————- ——————- —
    \n<\/span><\/p>\n

    BIN$LumGwpeDSHHgRAAQgzW92A==$0 EMP TABLE 2007-04-25:15:01:08 YES<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    2) Drop\ub41c Table \ubcf5\uad6c\ud558\uae30 1
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> flashback table emp to before drop;<\/strong>
    \n\t\t\t\t\t\t\t\t\t<\/span><\/p>\n

    \n <\/p>\n

    Flashback complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select object_name, original_name, type from user_recyclebin;
    \n<\/span><\/p>\n

    \n <\/p>\n

    no rows selected
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select table_name from user_tables;
    \n<\/span><\/p>\n

    \n <\/p>\n

    TABLE_NAME
    \n<\/span><\/p>\n

    ——————————
    \n<\/span><\/p>\n

    DUMMY
    \n<\/span><\/p>\n

    SALGRADE
    \n<\/span><\/p>\n

    BONUS
    \n<\/span><\/p>\n

    DEPT
    \n<\/span><\/p>\n

    EMP<\/strong><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    3) Drop\ub41c table \ubcf5\uad6c\ud558\uae30 2 (\ub3d9\uc77c \uc774\ub984\uc758 table\uc774 \uc774\ubbf8 \uc788\ub294 \uacbd\uc6b0, \ub2e4\ub978 \uc774\ub984\uc73c\ub85c \ubcf5\uad6c\ud558\uae30)
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> flashback table scott.emp to before drop rename to dropped_emp;<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    Drop\ub41c table \uc644\uc804 \uc0ad\uc81c\ud558\uae30
    \n<\/strong><\/span><\/p>\n

    SQL> drop table scott.emp purge; — drop \uc2dc \ubc14\ub85c purge\ud558\ub294 \uacbd\uc6b0
    \n<\/span><\/p>\n

    SQL> purge recyclebin;
    \n<\/span><\/p>\n

    or
    \n<\/span><\/p>\n

    SQL> purge dba_recyclebin;
    \n<\/span><\/p>\n

    or
    \n<\/span><\/p>\n

    SQL> purge table scott.emp
    \n<\/span><\/p>\n

    \n <\/p>\n

    \uc544\ub798\ub294 \uba87\uac00\uc9c0 PURGE \uc635\uc158\uc758 \uc608 \uc785\ub2c8\ub2e4.
    \n<\/strong><\/span><\/p>\n

    \n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n
    \n

    PURGE TABLE tablename;<\/span><\/p>\n<\/td>\n

    		\n

    Specific table<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    PURGE INDEX indexname;<\/span><\/p>\n<\/td>\n

    		\n

    Specific index<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    PURGE TABLESPACE ts_name;<\/span><\/p>\n<\/td>\n

    		\n

    All tables in a specific tablespace<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    PURGE TABLESPACE ts_name USER username;<\/span><\/p>\n<\/td>\n

    		\n

    All tables in a specific tablespace for a specific user<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    PURGE RECYCLEBIN;<\/span><\/p>\n<\/td>\n

    		\n

    The current users entire recycle bin<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    PURGE DBA_RECYCLEBIN;<\/span><\/p>\n<\/td>\n

    		\n

    The whole recycle bin<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    \u2022 \uc608\uc81c 2
    \n<\/strong><\/span><\/p>\n

    \ud734\uc9c0\ud1b5(recyclebin)\uc5d0 \uac19\uc740 \uc774\ub984\uc758 table\uc774 \uc5ec\ub7ec\uac1c \uc788\uc744 \ub54c PURGE and FLASHBACK TO BEFORE DROP \ubc29\ubc95
    \n\t\t\t\t<\/span><\/p>\n

    \uac19\uc740 \uc774\ub984\uc744 \uac00\uc9c0\ub294 table\uc774 \ud734\uc9c0\ud1b5(recyclebin)\uc5d0 \ud558\ub098 \uc774\uc0c1\uc788\uc744 \uacbd\uc6b0 \ub2e4\ub8e8\ub294 \ubc29\ubc95\uc785\ub2c8\ub2e4.
    table\uc744 PURGE \ud558\ub294 \uacbd\uc6b0 \uac00\uc7a5 \uc624\ub798\ub41c table\uc774 \ud734\uc9c0\ud1b5\uc5d0\uc11c PURGE \ub418\uace0 table\uc744 restore(FLASHBACK BEFORE DROP)\ud558\ub294 \uacbd\uc6b0 \uac00\uc7a5 \ucd5c\uadfc\uc758 table\uc774 \uc800\uc7a5\ub429\ub2c8\ub2e4.<\/p>\n

    Example
    ========
    5\uac1c\uc758 table\uc744 \uc0dd\uc131\ud558\uace0 drop \ud558\uc790..<\/p>\n

    $ sqlplus scott\/tiger
    SQL> CREATE TABLE t1(a NUMBER);
    SQL> DROP TABLE t1;
    SQL> CREATE TABLE t1(a varchar2(10));
    SQL> DROP TABLE t1;
    \n<\/span><\/p>\n

    SQL> CREATE TABLE t1(a date);
    SQL> DROP TABLE t1;
    \n<\/span><\/p>\n

    SQL> CREATE TABLE t1(a varchar2(5));
    \n<\/span><\/p>\n

    SQL> DROP TABLE t1;
    \n<\/span><\/p>\n

    SQL> CREATE TABLE t1(a number);
    SQL> DROP TABLE t1;<\/p>\n

    SQL> SELECT object_name,original_name,droptime,dropscn FROM recyclebin;<\/p>\n

    <\/span>OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN
    —————————— ————- ————————— ————————–
    BIN$14s6rPqoHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039154
    BIN$14s6rPqmHQngMEWYESkRng==$0 T1 2004-04-08:17:40:19 2039107
    BIN$14s6rPqnHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039133
    BIN$14s6rPqpHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039231
    BIN$14s6rPqqHQngMEWYESkRng==$0 T1 2004-04-08:17:40:22 2039252
    \n\t\t\t\t<\/span><\/p>\n

    \ub9cc\uc77c table t1 \uc744 purge \ud55c\ub2e4\uba74 dropscn=2039107 \uac00 purge \ub420\uac83\uc774\ub2e4.<\/p>\n

    SQL> PURGE TABLE t1;<\/p>\n

    Table purged.<\/p>\n

    SQL> SELECT object_name,original_name,droptime,dropscn FROM recyclebin;<\/p>\n

    <\/span>OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN
    —————————— —————- ———————- ———-
    BIN$14s6rPqoHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039154
    BIN$14s6rPqnHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039133
    BIN$14s6rPqpHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039231
    BIN$14s6rPqqHQngMEWYESkRng==$0 T1 2004-04-08:17:40:22 2039252
    <\/p>\n

    \ub9cc\uc77c table t1 \uc744 restore \ud55c\ub2e4\uba74 dropscn=2039252 \uc774 restore \ud560 \uac83\uc774\ub2e4.<\/p>\n

    SQL> FLASHBACK TABLE t1 TO BEFORE DROP ;<\/p>\n

    Flashback complete.<\/p>\n

    SQL> SELECT object_name,original_name,droptime,dropscn FROM recyclebin;<\/p>\n

    <\/span>OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN
    —————————— ————— ———————– ———-
    BIN$14s6rPqoHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039154
    BIN$14s6rPqnHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039133
    BIN$14s6rPqpHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039231
    \n\t\t\t\t<\/span><\/p>\n


    =>\uc774 \ubb38\uc81c\ub97c \ud574\uacb0\ud558\uae30 \uc704\ud574\uc11c..
    ================================
    \uc774 \ubb38\uc81c\ub97c \uadf9\ubcf5\ud558\uae30\uc704\ud574\uc11c \uc6b0\ub9ac\ub294 original \uc774\ub984 \ub300\uc2e0\uc5d0 drop\ub41c object name\uc744 \uc0ac\uc6a9\ud558\uba74 \ub41c\ub2e4.
    object name \uc740 unique \ud558\ubbc0\ub85c \uc6d0\ud558\ub294 \uac83\uc744 purge \uc640 restore \ud560 \uc218 \uc788\ub2e4.<\/p>\n

    Examples:<\/p>\n

    SQL> FLASHBACK TABLE “BIN$14s6rPqoHQngMEWYESkRng==$0” TO BEFORE DROP RENAME TO t2;<\/p>\n

    Flashback complete.<\/p>\n

    SQL> select tname from tab;<\/p>\n

    TNAME
    ——————————
    DEPT
    EMP
    BIN$14s6rPqnHQngMEWYESkRng==$0
    T1
    BIN$14s6rPqpHQngMEWYESkRng==$0
    T2<\/p>\n

    6 rows selected.<\/p>\n

    SQL> SELECT object_name,original_name,droptime,dropscn FROM recyclebin;<\/p>\n

    <\/span>OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN
    —————————— ————– ——————- ———-
    BIN$14s6rPqnHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039133
    BIN$14s6rPqpHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039231
    <\/p>\n

    \ube44\uc2b7\ud55c \ubc29\ubc95\uc73c\ub85c purge \ud560\uc218 \uc788\ub2e4.<\/p>\n

    SQL> PURGE TABLE “BIN$14s6rPqnHQngMEWYESkRng==$0”;<\/p>\n

    Table purged.<\/p>\n

    SQL> SELECT object_name,original_name,droptime,dropscn FROM recyclebin;<\/p>\n

    <\/span>OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN
    —————————— ————– ——————- ———-
    BIN$14s6rPqpHQngMEWYESkRng==$0 T1 2004-04-08:17:40:21 2039231
    \n<\/span><\/p>\n

    \n <\/p>\n

    \n <\/p>\n<\/li>\n

  11. \n

    Flashback Versions Query
    \n<\/h3>\n

    \u25b3 \uacfc\uac70\uc758 \uc5b4\ub5a4\uc2dc\uc810\uc758 \uc815\ubcf4\ub97c \uc2dc\uac04\uacfc SCN(SystemChange Number)\ub97c \uc774\uc6a9\ud558\uc5ec Query\ud558\ub294 \uae30\ub2a5.
    \n<\/span><\/span><\/p>\n

    \u25b3 9i \ubd80\ud130\uc9c0\uc6d0\ub41c Flashback Query\uac00 \uc788\uc73c\uba70, 10g\uc5d0\uc11c\ub294 \uadf8 \uae30\ub2a5\uc774 \ud655\uc7a5\ub418\uc5b4 Versions between\uc744 \uc774\uc6a9\ud574\uc11c \uc77c\uc815\uc2dc\uc810\uc774 \uc544\ub2cc \uc2dc\uac04\uac04\uaca9\uc758 \ub370\uc774\ud130\ub97c \uc870\ud68c\ud560 \uc218 \uc788\ub294 \uae30\ub2a5.
    \n<\/span><\/span><\/p>\n

    \u25b3 Flashback versions query\uc5d0 \uc758\ud574 \ucd94\ucd9c\ub41c row\ub4e4\uc740 transaction\uc5d0 \uc758\ud574 \ubcc0\ud654\ub41c row\ub4e4\uc758 history\ub97c \ubcf4\uc5ec\uc90c. \uc774 \uae30\ub2a5\uc740 data\uac00 \uc5b4\ub5bb\uac8c \ubc14\ub00c\uc5c8\ub294\uc9c0 auditing \uae30\ub2a5\uc744 \uac00\ub2a5\ud558\uac8c \ud558\uba70 commit\ub41c \ub370\uc774\ud130\ub9cc \ucd94\ucd9c \ud568.
    \n<\/span><\/span><\/p>\n

    \u25b3 Flashback versions query\ub97c \ud1b5\ud574\uc11c \uc54c\uc218 <\/span>\uc788\ub294<\/span> transaction id\ub97c \ud1b5\ud558\uc5ec \ub354 \ucd94\uac00\uc801\uc778 \uc815\ubcf4\ub97c Flashback Transaction Query\ub97c \ud1b5\ud574 \uc5bb\uc744\uc218 \uc788\ub2e4.
    \n<\/span><\/span><\/p>\n

    \u25b3 DDL\uc774 \uc218\ud589\ub418\uc5b4 table\uc758 \uad6c\uc870\uac00 \ubc14\ub00c\uba74 \uc0ac\uc6a9\ubd88\uac00.
    \n<\/span><\/span><\/p>\n

    \u25b3 Flashback versions query\ub294 undo\ub97c \uc774\uc6a9\ud558\uc5ec \uacfc\uac70 \ub370\uc774\ud130\ub97c \uc77d\uc5b4\uc624\ub294 \uac83\uc740 undo_retention \uac12\uacfc undo size\uc5d0 \uc758\ud574 \uc790\ub3d9\uc73c\ub85c \uad00\ub9ac\ub428. \ub9cc\uc57d undo_retention\uc774 \uc544\uc8fc \ud06c\ub2e4\uace0 \ud558\ub354\ub77c\ub3c4, undo size\uac00 \uc791\uc544\uc11c undo\ub97c \ubcf4\uad00\ud558\uc9c0 \uc54a\uace0 \uc7ac\uc0ac\uc6a9\ud558\uac8c \ub418\uba74 flashback versions query\uac00 \uc218\ud589\ub418\uc9c0 \uc54a\uc744 \uc218 \uc788\uc74c.
    \n<\/span><\/span><\/p>\n

    \u25b3 Versions between\uc740 \uc2dc\uac04\uacfc SCN\uc73c\ub85c \uc9c0\uc815 \ud560 \uc218 \uc788\uc74c
    \n<\/span><\/span><\/p>\n

    \u25b3 \uc774\uae30\ub2a5\uc744 \uc9c0\uc6d0\ud558\uae30 \uc704\ud574 scn_to_timestamp \uc640 timestamp_to_scn function\uc774 \uc9c0\uc6d0\ub41c\ub2e4.
    \n<\/span><\/span><\/p>\n

    \uacfc\uac70\uc758 \uc2dc\uc810\uc5d0 \ub300\ud55c SCN\uc54c\uae30.
    \n<\/span><\/p>\n

    select timestamp_to_scn(to_timestamp(‘20060213 171201’, ‘yyyymmdd hh24miss’)) t from dual;
    \n<\/span><\/p>\n

    =>8268801520810
    \n<\/span><\/p>\n

    \uacfc\uac70\uc758 SCN\uc744\uc774\uc6a9\ud558\uc5ec Time\uc54c\uae30
    \n<\/span><\/p>\n

    select scn_to_timestamp(8268801520810) from dual;
    \n<\/span><\/p>\n

    => 2006\/02\/13 17:12:01.000000000
    \n<\/span><\/p>\n

    \u25b3 Versions Query\uc758 Pseudo column (Select\uc808\uc5d0 \uc0ac\uc6a9\ud560 \uc218 \uc788\uc74c)
    \n<\/span><\/span><\/p>\n

    \u2022 Versions_startscn,
    \n<\/span><\/p>\n

    \u2022 Versions_starttime
    \n<\/span><\/p>\n

    \u2022 Versions_endscn
    \n<\/span><\/p>\n

    \u2022 Versions_endtime
    \n<\/span><\/p>\n

    \u2022 Versions_xid
    \n<\/span><\/p>\n

    \u2022 Versions_operation
    \n<\/span><\/p>\n

    \u25b3 \uc8fc\uc758 : undo retention \ubcf4\ub2e4 \uc774\uc804\uc758 version\uc744 query\ud558\uba74 ora-30052 : invalid lower limit snapshot expression \ubc1c\uc0dd\ud568.
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    \u2022 \uc608\uc81c
    \n<\/strong><\/span><\/p>\n

    Data \ubcc0\uacbd(Update) \ud558\uae30
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> conn scott\/tiger
    \n<\/span><\/p>\n

    Connected.
    \n<\/span><\/p>\n

    SQL> select * from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ———— ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 17-DEC-80 800 20
    \n<\/span><\/p>\n

    7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    \n<\/span><\/p>\n

    7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 02-APR-81 2975 20
    \n<\/span><\/p>\n

    7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    \n<\/span><\/p>\n

    7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
    \n<\/span><\/p>\n

    7782 CLARK MANAGER 7839 09-JUN-81 2450 10
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
    \n<\/span><\/p>\n

    7839 KING PRESIDENT 17-NOV-81 5000 10
    \n<\/span><\/p>\n

    7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 12-JAN-83 1100 20
    \n<\/span><\/p>\n

    7900 JAMES CLERK 7698 03-DEC-81 950 30
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 03-DEC-81 3000 20
    \n<\/span><\/p>\n

    7934 MILLER CLERK 7782 23-JAN-82 1300 10
    \n<\/span><\/p>\n

    \n <\/p>\n

    14 rows selected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> update emp
    \n<\/span><\/p>\n

    2 set comm=1111
    \n<\/span><\/p>\n

    3 where empno=7934;
    \n<\/span><\/p>\n

    \n <\/p>\n

    1 row updated.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> update emp
    \n<\/span><\/p>\n

    2 set comm=2222
    \n<\/span><\/p>\n

    3 where empno=7934;
    \n<\/span><\/p>\n

    \n <\/p>\n

    1 row updated.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> rollback;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Rollback complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> update emp
    \n<\/span><\/p>\n

    2 set comm=2222
    \n<\/span><\/p>\n

    3 where empno=7934;
    \n<\/span><\/p>\n

    \n <\/p>\n

    1 row updated.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> update emp
    \n<\/span><\/p>\n

    2 set comm=3333
    \n<\/span><\/p>\n

    3 where empno=7934;
    \n<\/span><\/p>\n

    \n <\/p>\n

    1 row updated.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ——————- ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
    \n<\/span><\/p>\n

    7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
    \n<\/span><\/p>\n

    7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
    \n<\/span><\/p>\n

    7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
    \n<\/span><\/p>\n

    7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
    \n<\/span><\/p>\n

    7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
    \n<\/span><\/p>\n

    7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
    \n<\/span><\/p>\n

    7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
    \n<\/span><\/p>\n

    7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
    \n<\/span><\/p>\n

    7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10
    \n<\/span><\/p>\n

    \n <\/p>\n

    14 rows selected.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    2007 2\uc6d4 25\uc77c 15\uc2dc 50\ubd84 ~ 16\uc2dc 00\ubd84 \uae4c\uc9c0 empno\uac00 7934\uc778 data\uac00 \ubcc0\ud55c \ub0b4\uc5ed\uc870\ud68c
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> col start_time format a21
    \n<\/span><\/p>\n

    SQL> col end_time format a21
    \n<\/span><\/p>\n

    SQL> set lines 200
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> SELECT empno, ename, sal, comm, deptno,
    \n<\/span><\/p>\n

    2 versions_xid AS XID,
    \n<\/span><\/p>\n

    3 versions_operation as operation,
    \n<\/span><\/p>\n

    4 versions_startscn AS START_SCN,
    \n<\/span><\/p>\n

    5 versions_starttime as start_time,
    \n<\/span><\/p>\n

    6 versions_endscn AS END_SCN,
    \n<\/span><\/p>\n

    7 versions_endtime as end_time
    \n<\/span><\/p>\n

    8 FROM emp VERSIONS BETWEEN TIMESTAMP
    \n<\/span><\/p>\n

    9 TO_TIMESTAMP(‘2007-04-25 15:50:00′,’YYYY-MM-DD HH24:MI:SS’)
    \n<\/span><\/p>\n

    10 AND TO_TIMESTAMP(‘2007-04-25 16:00:00′,’YYYY-MM-DD HH24:MI:SS’)
    \n<\/span><\/p>\n

    11 WHERE empno=7934;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME SAL COMM DEPTNO XID O START_SCN START_TIME END_SCN END_TIME
    \n<\/span><\/p>\n

    —— ——– —— ——- ——– —————- – ———- ——————— ———- ———————
    \n<\/span><\/p>\n

    7934 MILLER 1300 3333<\/strong><\/span> 10 0009000000000988 U 25837411 25-APR-07 03.58.31 PM
    \n<\/span><\/p>\n

    7934 MILLER 1300 2222<\/strong><\/span> 10 0003000D00000994 U 25837379 25-APR-07 03.57.01 PM 25837411 25-APR-07 03.58.31 PM
    \n<\/span><\/p>\n

    7934 MILLER 1300 1111<\/strong><\/span> 10 0006001C00000985 U 25837354 25-APR-07 03.56.04 PM 25837379 25-APR-07 03.57.01 PM
    \n<\/span><\/p>\n

    7934 MILLER 1300 10 25837354 25-APR-07 03.56.04 PM
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> SELECT empno, ename, sal, comm, deptno,
    \n<\/span><\/p>\n

    2 versions_xid AS XID,
    \n<\/span><\/p>\n

    3 versions_operation as operation,
    \n<\/span><\/p>\n

    4 versions_startscn AS START_SCN,
    \n<\/span><\/p>\n

    5 versions_starttime as start_time,
    \n<\/span><\/p>\n

    6 versions_endscn AS END_SCN,
    \n<\/span><\/p>\n

    7 versions_endtime as end_time
    \n<\/span><\/p>\n

    8 FROM emp VERSIONS BETWEEN TIMESTAMP
    \n<\/span><\/p>\n

    9 systimestamp – interval ’10’ minute and systimestamp – interval ‘1’ minute
    \n<\/span><\/p>\n

    10 WHERE empno=7934;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME SAL COMM DEPTNO XID O START_SCN START_TIME END_SCN END_TIME
    \n<\/span><\/p>\n

    —— ——– —— ——- ——– —————- – ———- ——————— ———- ———————
    \n<\/span><\/p>\n

    7934 MILLER 1300 3333<\/strong><\/span> 10 0009000000000988 U 25837411 25-APR-07 03.58.31 PM
    \n<\/span><\/p>\n

    7934 MILLER 1300 2222<\/strong><\/span> 10 0003000D00000994 U 25837379 25-APR-07 03.57.01 PM 25837411 25-APR-07 03.58.31 PM
    \n<\/span><\/p>\n

    7934 MILLER 1300 1111<\/strong><\/span> 10 0006001C00000985 U 25837354 25-APR-07 03.56.04 PM 25837379 25-APR-07 03.57.01 PM
    \n<\/span><\/p>\n

    7934 MILLER 1300 10 25837354 25-APR-07 03.56.04 PM
    \n<\/span><\/p>\n

    \n <\/p>\n

    \uc870\ud68c\uacb0\uacfc\ub85c Null \u00e8<\/span> 1111\u00e8<\/span> 2222 \u00e8<\/span> 3333 \uc73c\ub85c \ubcc0\uacbd\ub41c \ub0b4\uc5ed\uc744 \ubcfc \uc218 \uc788\ub2e4.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    \n <\/p>\n<\/li>\n

  12. \n

    Flashback Query
    \n<\/h3>\n

    \u25b3 Oracle9i\uc5d0\uc11c \ubd80\ud130 \uc9c0\ub09c \uc2dc\uc810\uc758 \ub370\uc774\ud130\ub97c \uc9c8\uc758 \ud558\uae30 \uc704\ud55c DBMS_PACKAGE\ub97c \uc81c\uacf5 \ud588\uc73c\uba70 10g\uc5d0\uc11c\ub294 \ud6e8\uc52c \uae30\ub2a5\uc744 \uc720\uc5f0\ud558\uac8c \ubc1c\uc804 \uc2dc\ucf30\uc2b5\ub2c8\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 Flashback Query\ub294 AS OF \uc808\uc744 \uc0ac\uc6a9\ud558\uc5ec \ud574\ub2f9 \uc2dc\uc810\uc5d0\uc11c\uc758 \ub370\uc774\ud130 \uac12\uc5d0 \ub300\ud55c \uc9c8\uc758\uac00 \uac00\ub2a5\ud558\uba70, \uc774 \uae30\ub2a5\uc740 DBMS_FLASHBACK \ud328\ud0a4\uc9c0\uc758 \uae30\ub2a5\uacfc \uc720\uc0ac \ud569\ub2c8\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 Flashback versions query\ub294 \uacfc\uac70\uc758 \uc77c\uc815 \uc2dc\uac04\uad6c\uac04\uc5d0\uc11c \uc870\ud68c\ud558\ub294 \uac83\uc5d0 \ube44\ud574 Flashback query\ub294, \uacfc\uac70\uc758 \uc77c\uc815\ud55c \uc2dc\uac04\uc5d0\uc11c query\ub97c \ud558\ub294 \uac83.
    \n<\/span><\/p>\n

    \u25b3 Database\ub294 \ud604\uc7ac\uc758 \uc2dc\uac04\uc774\uc9c0\ub9cc, \uc218\ud589\ud558\ub294 SQL\uc740 \ud63c\uc790 \uacfc\uac70\uc758 \uc815\ubcf4\ub97c \ubcf4\uac8c \ub428.
    \n<\/span><\/p>\n

    \n <\/p>\n

    \u2022 \uc608\uc81c
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    — Data \uc0ad\uc81c(\uc7a5\uc560 \ub9cc\ub4e4\uae30)
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ——————- ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
    \n<\/span><\/p>\n

    7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
    \n<\/span><\/p>\n

    7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
    \n<\/span><\/p>\n

    7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
    \n<\/span><\/p>\n

    7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
    \n<\/span><\/p>\n

    7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
    \n<\/span><\/p>\n

    7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
    \n<\/span><\/p>\n

    7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
    \n<\/span><\/p>\n

    7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
    \n<\/span><\/p>\n

    7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    14 rows selected.
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    SQL> delete emp
    \n<\/span><\/p>\n

    2 where empno=7934;
    \n<\/span><\/p>\n

    \n <\/p>\n

    1 row deleted.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ——————- ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
    \n<\/span><\/p>\n

    7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
    \n<\/span><\/p>\n

    7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
    \n<\/span><\/p>\n

    7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
    \n<\/span><\/p>\n

    7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
    \n<\/span><\/p>\n

    7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
    \n<\/span><\/p>\n

    7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
    \n<\/span><\/p>\n

    7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
    \n<\/span><\/p>\n

    7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
    \n<\/span><\/p>\n

    \n <\/p>\n

    13 rows selected. (1\uac74\uc774 \uc0ad\uc81c \ub418\uc5c8\uc74c)
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    — 1\uc2dc\uac04 \uc804 Data\ub97c \uad6c\ud558\uae30
    \n<\/strong><\/span><\/p>\n

    SQL> select * from emp as of timestamp ( systimestamp – interval ‘1’ hour)<\/strong><\/span>;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ——————- ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
    \n<\/span><\/p>\n

    7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
    \n<\/span><\/p>\n

    7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
    \n<\/span><\/p>\n

    7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
    \n<\/span><\/p>\n

    7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
    \n<\/span><\/p>\n

    7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
    \n<\/span><\/p>\n

    7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
    \n<\/span><\/p>\n

    7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
    \n<\/span><\/p>\n

    7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
    \n<\/span><\/p>\n


    \n\t\t\t\t\t\t\t\t\t\t\t<\/span> 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    14 rows selected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    — 1\ubd84 \uc804 Data\ub97c \uad6c\ud558\uae30
    \n<\/strong><\/span><\/p>\n

    \u203b delete \ud6c4 \ubc14\ub85c \uc870\ud68c\ud558\uba74 \uc544\uc9c1 delete \ub418\uc9c0 \uc54a\uc740 \uac83\uc73c\ub85c \ubcf4\uc778\ub2e4.
    \n<\/span><\/p>\n

    SQL> select * from emp as of timestamp ( systimestamp – interval ‘1’ minute)<\/strong><\/span>;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ——————- ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
    \n<\/span><\/p>\n

    7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
    \n<\/span><\/p>\n

    7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
    \n<\/span><\/p>\n

    7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
    \n<\/span><\/p>\n

    7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
    \n<\/span><\/p>\n

    7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
    \n<\/span><\/p>\n

    7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
    \n<\/span><\/p>\n

    7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
    \n<\/span><\/p>\n

    7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
    \n<\/span><\/p>\n


    \n\t\t\t\t\t\t\t\t\t\t 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    14 rows selected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    \n <\/p>\n

    — 1\uc2dc\uac04\uc804 Data\uc640 \ud604\uc7ac Data\uc758 \ucc28\uc774\ub97c \uc54c\uace0 \uc2f6\uc744\ub54c.
    \n<\/strong><\/span><\/p>\n

    — \uc989, 1\uc2dc\uac04\uc804\uacfc \uac19\uc9c0 \uc54a\uc740 \ub370\uc774\ud130\ub97c \ubaa8\ub450 \ucc3e\ub294\ub2e4.
    \n<\/span><\/p>\n

    SQL> select * from emp as of timestamp ( systimestamp – interval ‘1’ hour)
    \n<\/span><\/p>\n

    2 minus
    \n<\/span><\/p>\n

    3 select * from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ——————- ———- ———- ———-
    \n<\/span><\/p>\n

    7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    \u203b 1\uc2dc\uac04 \uc804\uc758 Table\uc744 Backup \ud574 \ub193\uc744\uc218 \uc788\ub2e4.
    \n<\/span><\/p>\n

    SQL> create table emp_back
    \n<\/span><\/p>\n

    2 as select * from emp as of timestamp ( systimestamp – interval ‘1’ hour);
    \n<\/span><\/p>\n

    \n <\/p>\n

    Table created.
    \n<\/span><\/p>\n

    \n <\/p>\n

    — \uae09\ud558\uac8c \ubcf5\uad6c\ub97c \ud574\uc57c \ud560\ub54c. \uc57d 1\uc2dc\uac04\uc804\uc5d0 \ub9ce\uc740 \uac74\uc218\ub97c \uc0ad\uc81c\ud55c \uacbd\uc6b0.
    \n<\/strong><\/span><\/p>\n

    SQL> insert into emp
    \n<\/span><\/p>\n

    2 select * from emp as of timestamp ( systimestamp – interval ‘1’ hour)
    \n<\/span><\/p>\n

    3 minus
    \n<\/span><\/p>\n

    4 select * from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    1 row created.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ——————- ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
    \n<\/span><\/p>\n

    7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
    \n<\/span><\/p>\n

    7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
    \n<\/span><\/p>\n

    7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
    \n<\/span><\/p>\n

    7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
    \n<\/span><\/p>\n

    7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
    \n<\/span><\/p>\n

    7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
    \n<\/span><\/p>\n

    7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
    \n<\/span><\/p>\n

    7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
    \n<\/span><\/p>\n

    7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 3333 10
    \n<\/strong><\/span><\/p>\n

    \n <\/p>\n

    14 rows selected.<\/strong><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    \n <\/p>\n<\/li>\n

  13. \n

    Flashback transaction query
    \n<\/h3>\n

    \u25b3 FlashBack Transaction Query\ub77c\uace0 \ud558\ub294 \uac83\uc740 Flashback Version Query\uc758 \uacb0\uacfc\ub85c \ub098\ud0c0\ub09c \ud574\ub2f9 Transaction\uc5d0 \ub300\ud574 \ud2b9\ubcc4\ud55c \uc815\ubcf4\ub97c \uc5bb\uc744 \uc218 \uc788\ub294 \uac83 \uc815\ub3c4\ub85c \ubcf4\uc2dc\uba74 \ub429\ub2c8\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 VERSIONS_XID \uac12\uc774 \ud2b8\ub79c\uc7ad\uc158\uc758 ID\ub77c\uace0 \ud588\ub294\ub370, \uc774 \uac12\uc744 FLASHBACK_TRANSACTION_QUERY\uc758 \uc778\uc790 \uac12\uc73c\ub85c \uc918\uc11c \ucffc\ub9ac\ub97c \uc2e4\ud589 \ud558\uba74 \ud574\ub2f9 \ud2b8\ub79c\uc7ad\uc158\uc5d0 \ub300\ud55c \uc815\ubcf4\ub97c \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.
    \n<\/span><\/p>\n

    \uc608\ub97c \ub4e4\uba74 \uc5b4\ub5a4 DML\uc744 \uc774\uc6a9\ud588\uc73c\uba70 \uc5b4\ub5a0\ud55c SQL\uc774 \uc2e4\ud589 \ub418\uc5c8\ub294\uc9c0 \ud558\ub294 \uac83\uc774 \ud655\uc778 \uac00\ub2a5 \ud569\ub2c8\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 Flashback transaction query\ub294 Transaction level\uc5d0\uc11c Data\uc758 \ubcc0\uacbd\uc0ac\ud56d\uc744 \ucd94\uc801\ud558\uae30 \uc704\ud55c \uae30\ub2a5
    \n<\/span><\/span><\/p>\n

    \u25b3 Transaction\uc758 \ubd84\uc11d\uacfc \uc9c4\ub2e8\uc744 \ud558\ub294 \uae30\ub2a5 \uc784.
    \n<\/span><\/span><\/p>\n

    \u25b3 \ubcc0\uacbd\uc0ac\ud56d \ubfd0\ub9cc \uc544\ub2c8\ub77c, Undo SQL\uc744 \uc0dd\uc131\ud560 \uc218 \uc788\uc73c\uba70, \uc774 SQL\uc744 \uc774\uc6a9\ud558\uc5ec Transaction level\uc758 \uc791\uc5c5\uc744 rollback\ud560 \uc218 \uc788\uc74c
    \n<\/span><\/span><\/p>\n

    \u25b3 undo data\ub97c index access \ubc29\uc2dd\uc73c\ub85c \uc870\ud68c\ud558\ubbc0\ub85c logminor
    \n<\/span><\/span><\/p>\n

    \uc8fc\uc758 : xid column\uc5d0 \uc870\uac74\uc744 \uc904 \ub54c \ubc18\ub4dc\uc2dc hextoraw function\uc744 \uc0ac\uc6a9\ud574\uc57c \ub9cc fixed view\uc758 index\ub97c \uc0ac\uc6a9\ud568.
    \n<\/span><\/p>\n

    \u25b3 Flashback versions query\uc640 \ub9c8\ucc2c\uac00\uc9c0\ub85c undo data\ub97c \uc774\uc6a9\ud568.
    \n<\/span><\/span><\/p>\n

    \u25b3 Flashback Transaction query\ub97c \uc0ac\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 Database level\uc5d0 logging\uc774 enable\ub418\uc5b4\uc57c \ud55c\ub2e4.
    \n<\/span><\/span><\/p>\n

    Alter database add supplemental log data;
    \n<\/span><\/p>\n

    \ud655\uc778\ubc29\ubc95 : select supplemental_log_data_min from v$database ( YES \uac00\uc815\uc0c1)
    \n<\/span><\/p>\n

    \u25b3 \ud544\uc694 \uad8c\ud55c : grant select any transaction to XXX;
    \n<\/span><\/span><\/p>\n

    \u25b3 \uae30\ubcf8\uc801\uc73c\ub85c flashback_transaction_query \ub77c\ub294 view table\uc744 \uc774\uc6a9\ud558\uc5ec query\ud55c\ub2e4.
    \n<\/span><\/span><\/p>\n

    \u25b3 flashback_transaction_query columns.
    \n<\/span><\/span><\/p>\n

    XID          RAW(8)     Transaction identifier
    \n<\/span><\/p>\n

    START_SCN      NUMBER     Transaction start system change number (SCN)
    \n<\/span><\/p>\n

    START_TIMESTAMP DATE         Transaction start timestamp
    \n<\/span><\/p>\n

    COMMIT_SCN      NUMBER     Transaction commit system change number (null for active transactions)
    \n<\/span><\/p>\n

    COMMIT_TIMESTAMP DATE         Transaction commit timestamp (null for active transactions)
    \n<\/span><\/p>\n

    LOGON_USER      VARCHAR2(30)     Logon user for the transaction
    \n<\/span><\/p>\n

    UNDO_CHANGE#      NUMBER     Undo system change number (1 or higher)
    \n<\/span><\/p>\n

    OPERATION      VARCHAR2(32) Forward-going DML operation performed by the transaction:
    \n<\/span><\/p>\n

    D – Delete
    \n<\/span><\/p>\n

    I – Insert
    \n<\/span><\/p>\n

    U – Update
    \n<\/span><\/p>\n

    B – UNKNOWN
    \n<\/span><\/p>\n

    TABLE_NAME      VARCHAR2(256)     Name of the table to which the DML applies
    \n<\/span><\/p>\n

    TABLE_OWNER      VARCHAR2(32)     Owner of the table to which the DML applies
    \n<\/span><\/p>\n

    ROW_ID          VARCHAR2(19)     Rowid of the row that was modified by the DML
    \n<\/span><\/p>\n

    \n <\/p>\n

    \u2022 emp\uc640 dept\ub97c \uac01\uac01 \uc218\uc815\ud55c \ud6c4, \uc774\uc5d0 \ub300\ud55c transaction query\ub97c \ud558\ub294 \uc608\uc81c.
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> select * from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ———— ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 17-DEC-80 800 20
    \n<\/span><\/p>\n

    7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    \n<\/span><\/p>\n

    7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 02-APR-81 2975 20
    \n<\/span><\/p>\n

    7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    \n<\/span><\/p>\n

    7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
    \n<\/span><\/p>\n

    7782 CLARK MANAGER 7839 09-JUN-81 2450 10
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
    \n<\/span><\/p>\n

    7839 KING PRESIDENT 17-NOV-81 5000 10
    \n<\/span><\/p>\n

    7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 12-JAN-83 1100 20
    \n<\/span><\/p>\n

    7900 JAMES CLERK 7698 03-DEC-81 950 30
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 03-DEC-81 3000 20
    \n<\/span><\/p>\n

    7934 MILLER CLERK 7782 23-JAN-82 1300 10
    \n<\/span><\/span><\/strong><\/span><\/p>\n

    \n <\/p>\n

    14 rows selected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from dept;
    \n<\/span><\/p>\n

    \n <\/p>\n

    DEPTNO DNAME LOC
    \n<\/span><\/p>\n

    ———- ————– ————-
    \n<\/span><\/p>\n

    10 ACCOUNTING NEW YORK
    \n<\/span><\/p>\n

    20 RESEARCH DALLAS
    \n<\/span><\/p>\n

    30 SALES CHICAGO
    \n<\/span><\/p>\n

    40 OPERATIONS
    \n\t\t\t\t\t\t\t\t\t\t\t\t<\/span>BOSTON
    \n\t\t\t\t\t\t\t\t\t\t\t\t<\/span><\/span><\/strong><\/span><\/p>\n

    \n <\/p>\n

    SQL> update emp set sal = 9999, job=’2030′ where empno=7934;
    \n<\/span><\/p>\n

    \n <\/p>\n

    1 row updated.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> update dept set dname = ‘ADMIN’ where deptno = 40;
    \n<\/span><\/p>\n

    \n <\/p>\n

    1 row updated.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ———— ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 17-DEC-80 800 20
    \n<\/span><\/p>\n

    7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    \n<\/span><\/p>\n

    7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 02-APR-81 2975 20
    \n<\/span><\/p>\n

    7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    \n<\/span><\/p>\n

    7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
    \n<\/span><\/p>\n

    7782 CLARK MANAGER 7839 09-JUN-81 2450 10
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
    \n<\/span><\/p>\n

    7839 KING PRESIDENT 17-NOV-81 5000 10
    \n<\/span><\/p>\n

    7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 12-JAN-83 1100 20
    \n<\/span><\/p>\n

    7900 JAMES CLERK 7698 03-DEC-81 950 30
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 03-DEC-81 3000 20
    \n<\/span><\/p>\n

    7934 MILLER 2030 7782 23-JAN-82 9999 10
    \n<\/span><\/span><\/strong><\/span><\/p>\n

    \n <\/p>\n

    14 rows selected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from dept;
    \n<\/span><\/p>\n

    \n <\/p>\n

    DEPTNO DNAME LOC
    \n<\/span><\/p>\n

    ———- ————– ————-
    \n<\/span><\/p>\n

    10 ACCOUNTING NEW YORK
    \n<\/span><\/p>\n

    20 RESEARCH DALLAS
    \n<\/span><\/p>\n

    30 SALES CHICAGO
    \n<\/span><\/p>\n

    40 ADMIN BOSTON
    \n<\/span><\/span><\/strong><\/span><\/p>\n

    \n <\/p>\n

    — flashback versions query\ub97c \uc774\uc6a9\ud558\uc5ec xid\ub97c \ucc3e\ub294\ub2e4.
    \n<\/strong><\/span><\/p>\n

    SQL> col versions_starttime format a30
    \n<\/span><\/p>\n

    SQL> col versions_endtime format a30
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, empno, sal
    \n<\/span><\/p>\n

    2 from scott.emp versions between timestamp minvalue and maxvalue
    \n<\/span><\/p>\n

    3 where empno = 7934
    \n<\/span><\/p>\n

    4 order by VERSIONS_STARTTIME;
    \n<\/span><\/p>\n

    \n <\/p>\n

    VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V EMPNO SAL
    \n<\/span><\/p>\n

    —————————— —————————— —————- – ———- ———-
    \n<\/span><\/p>\n

    25-APR-07 05.13.51 PM 000A00270000076B<\/strong> U 7934 9999
    \n<\/span><\/span><\/span><\/p>\n

    25-APR-07 05.13.51 PM 7934 1300
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, deptno, dname
    \n<\/span><\/p>\n

    2 from scott.dept versions between timestamp minvalue and maxvalue
    \n<\/span><\/p>\n

    3 where deptno = 40
    \n<\/span><\/p>\n

    4 order by VERSIONS_STARTTIME;
    \n<\/span><\/p>\n

    \n <\/p>\n

    VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V DEPTNO DNAME
    \n<\/span><\/p>\n

    —————————— —————————— —————- – ———- ————–
    \n<\/span><\/p>\n

    25-APR-07 05.13.51 PM 000A00270000076B<\/strong> U 40 ADMIN
    \n<\/span><\/span><\/span><\/p>\n

    25-APR-07 05.13.51 PM 40 OPERATIONS
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> col OPERATION format a12
    \n<\/span><\/p>\n

    SQL> col LOGON_USER format a12
    \n<\/span><\/p>\n

    SQL> col UNDO_SQL format a100
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> SELECT xid, operation,logon_user, undo_sql
    \n<\/span><\/p>\n

    2 FROM flashback_transaction_query
    \n<\/span><\/p>\n

    3 WHERE xid = HEXTORAW(‘000A00270000076B’<\/strong>); — hextoraw\ub97c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uc73c\uba74,
    \n<\/span><\/span><\/span><\/p>\n

    undo tablespace\uc758 \ud06c\uae30\uc5d0 \ub530\ub77c 10\ubd84\uc774\uc0c1 \uac78\ub9bc<\/span>.
    \n<\/span><\/p>\n

    \n <\/p>\n

    XID OPERATION LOGON_USER UNDO_SQL
    \n<\/span><\/p>\n

    —————- ———— ———— ———————————————————————————————
    \n<\/span><\/p>\n

    000A00270000076B UPDATE SCOTT update “SCOTT”.”DEPT” set “DNAME” = ‘OPERATIONS’ where ROWID = ‘AAAQLHAAHAAACfeAAD’;
    \n<\/span><\/p>\n

    000A00270000076B UPDATE SCOTT update “SCOTT”.”EMP” set “JOB” = ‘CLERK’, “SAL” = ‘1300’ where ROWID = ‘AAAQLGAAHAAACfWAAN’;
    \n<\/span><\/p>\n

    000A00270000076B BEGIN SCOTT
    \n<\/span><\/p>\n

    \n <\/p>\n

    — \ud574\ub2f9 Transaction\uc744 rollback\ud558\uae30 \uc704\ud574\uc11c\ub294 \uc544\ub798\uc640 \uac19\uc774 undo_sql\uc744 \uc218\ud589\ud55c\ub2e4.
    \n<\/span><\/p>\n

    SQL> update “SCOTT”.”DEPT” set “DNAME” = ‘OPERATIONS’ where ROWID = ‘AAAQLHAAHAAACfeAAD’;
    \n<\/span><\/p>\n

    1 row updated.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> update “SCOTT”.”EMP” set “JOB” = ‘CLERK’, “SAL” = ‘1300’ where ROWID = ‘AAAQLGAAHAAACfWAAN’;
    \n<\/span><\/p>\n

    1 row updated.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    \n<\/span><\/p>\n

    ———- ———- ——— ———- ———— ———- ———- ———-
    \n<\/span><\/p>\n

    7369 SMITH CLERK 7902 17-DEC-80 800 20
    \n<\/span><\/p>\n

    7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    \n<\/span><\/p>\n

    7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    \n<\/span><\/p>\n

    7566 JONES MANAGER 7839 02-APR-81 2975 20
    \n<\/span><\/p>\n

    7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    \n<\/span><\/p>\n

    7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
    \n<\/span><\/p>\n

    7782 CLARK MANAGER 7839 09-JUN-81 2450 10
    \n<\/span><\/p>\n

    7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
    \n<\/span><\/p>\n

    7839 KING PRESIDENT 17-NOV-81 5000 10
    \n<\/span><\/p>\n

    7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    \n<\/span><\/p>\n

    7876 ADAMS CLERK 7788 12-JAN-83 1100 20
    \n<\/span><\/p>\n

    7900 JAMES CLERK 7698 03-DEC-81 950 30
    \n<\/span><\/p>\n

    7902 FORD ANALYST 7566 03-DEC-81 3000 20
    \n<\/span><\/p>\n

    7934 MILLER CLERK 7782 23-JAN-82 1300 10
    \n<\/span><\/span><\/strong><\/span><\/p>\n

    \n <\/p>\n

    14 rows selected.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select * from dept;
    \n<\/span><\/p>\n

    \n <\/p>\n

    DEPTNO DNAME LOC
    \n<\/span><\/p>\n

    ———- ————– ————-
    \n<\/span><\/p>\n

    10 ACCOUNTING NEW YORK
    \n<\/span><\/p>\n

    20 RESEARCH DALLAS
    \n<\/span><\/p>\n

    30 SALES CHICAGO
    \n<\/span><\/p>\n

    40 OPERATIONS BOSTON<\/span><\/span><\/strong><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    \n <\/p>\n<\/li>\n

  14. \n

    Flashback Table
    \n<\/h3>\n

    \u25b3 Flashback Table\uc740 \uc798\ubabb\ub41c \ub370\uc774\ud130 \ucc98\ub9ac\ub97c \ud55c \uacbd\uc6b0, \uc791\uc5c5\uc804\uc758 \uc2dc\uc810\uc73c\ub85c \ube60\ub974\uac8c \ub3cc\ub824\uc8fc\uae30 \uc704\ud55c\uae30\ub2a5. (SCN or \uc2dc\uac04)
    \n<\/span><\/span><\/p>\n

    \u25b3 <\/span>Flashback Table \uba85\ub839\uc744 \ud1b5\ud574 \uac1c\ubcc4\uc801\uc778 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 \uc2dc\uac04\uc5d0 \uc900\ud55c \ubcf5\uad6c\ub97c \uc704\ud574\uc11c\ub294 \uc544\ub798\uc5d0 \uc788\ub294 \uc870\uac74\uc744 \ub9cc\uc871 \ud574\uc57c \ud569\ub2c8\ub2e4. \ud14c\uc774\ube14\uc758 \ub370\uc774\ud130\uc5d0 \ub300\ud574 \uacfc\uac70 \uc2dc\uc810\uc73c\ub85c \ub3cc\uc544\uac00\uc11c \uac12\ub4e4\uc744 \ud655\uc778 \ud558\ub294 \uac83\uc774 \uac00\ub2a5 \ud569\ub2c8\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 <\/span>Flashback any table \ub610\ub294 \ud574\ub2f9 Table\uc5d0 \ub300\ud55c Flashback object privilege\ub97c \uac00\uc9c0\uace0 \uc788\uc5b4\uc57c \ud569\ub2c8\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 <\/span>\ud14c\uc774\ube14\uc5d0 \ub300\ud55c SELECT, INSERT, DELETE, ALTER \uad8c\ud55c\uc774 \uc788\uc5b4\uc57c \ud569\ub2c8\ub2e4.
    \n<\/span><\/p>\n

    \u25b3 <\/span>ROW MOVEMENT\uc758 \uacbd\uc6b0 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 ALTER TABLE tablename ENABLE ROW MOVEMENT;\uac00 \uc124\uc815 \ub418\uc5b4 \uc788\uc5b4\uc57c \ud569\ub2c8\ub2e4.
    \n<\/span><\/p>\n

    \n <\/p>\n

    \u25b3 Backup\uc758 restore\uc5c6\uc774 Table\uc744 \uc9c0\uc815\ud55c \uc2dc\uc810\uae4c\uc9c0 \ub418\ub3cc\ub824 \uc90c.
    \n<\/span><\/span><\/p>\n

    \u25b3 Table\uc758 \ub370\uc774\ud130\ub9cc\uc744 \uacfc\uac70\uc2dc\uc810\uc758 \ub370\uc774\ud130\ub85c \ub3cc\ub824\uc8fc\uba70, Table\uacfc \uad00\ub828\ud55c \ubaa8\ub4e0 object (index, constrains, trigger)\ub4f1\uc740 \ud604\uc7ac\uc2dc\uc810\uc73c\ub85c \uc720\uc9c0\ub428
    \n<\/span><\/span><\/p>\n

    \u25b3 Table\uc774 Flashback \ud558\ub294 \ub3d9\uc548\uc5d0\ub294 exclusive lock\uc744 \uc7a1\uac8c\ub428.
    \n<\/span><\/span><\/p>\n

    \u25b3 Flashback \ud55c\ud6c4, \ub2e4\uc2dc \ud604\uc7ac \uc2dc\uc810\uc758 Data\ub85c \ub3cc\uc544\uc62c \uc218 \uc788\uc74c. \uadf8\ub7ec\ub098 \ud604\uc7ac\uc758 SCN\uc744 \uc54c\uace0 \uc788\uc5b4\uc57c \ud568.
    \n<\/span><\/span><\/p>\n

    SELECT current_scn FROM v$database; — \ud604\uc7ac SCN\uc54c\uae30
    \n<\/span><\/p>\n

    \u25b3 \ub2e4\uc74c\uc758 Object\ub4e4\uc5d0\ub294 Flashback table \uc548\ub428.
    \n<\/span><\/span><\/p>\n

    Cluster, Mview, AQ tables, static data dictionary, system tables, remote tables
    \n<\/span><\/p>\n

    \u25b3 Undo Data\ub97c \uc774\uc6a9\ud568.
    \n<\/span><\/span><\/p>\n

    \u25b3 undo retention \uc774\uc804\uc758 \ub370\uc774\ud130\ub294 \ubcf5\uad6c \uc548\ub428.
    \n<\/span><\/span><\/p>\n

    \u25b3 flashback versions query\ub85c\ubd80\ud130 \uc6d0\ud558\ub294 SCN\uc744 \ucc3e\uc544\uc11c flashback table\uc744 \ud560 \uc218 \uc788\uc74c.
    \n<\/span><\/span><\/p>\n

    (VERSIONS_STARTSCN, VERSIONS_ENDSCN)
    \n<\/span><\/p>\n

    \u25b3 \ud544\uc694 \uad8c\ud55c : flashback object, flashback any table, \ud574\ub2f9 table\uc5d0 \ub300\ud55c select, insert, update, delete, alter table \uad8c\ud55c.
    \n<\/span><\/span><\/p>\n

    \u25b3 flashback table\uc744 \ud558\uae30 \uc704\ud574\uc11c\ub294 row movement \ub97c enable\ud574 \uc8fc\uc5b4\uc57c \ud568.
    \n<\/span><\/span><\/p>\n

    alter table XXXX enable row movement;
    \n<\/span><\/p>\n

    \u25b3Table\uc5d0 DDL\uc758 \ubcc0\uacbd \uc791\uc5c5\uc774 \uc788\uc5c8\ub2e4\uba74, flashback \ubd88\uac00 (moving, truncate, add, modify, drop,merging, split, coalescing)
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    \u2022 Flashback Table \uc608\uc81c: SCN\uc744 \uc774\uc6a9\ud55c \uacfc\uac70\uc2dc\uc810\uc73c\ub85c Flashback \ud558\uae30
    \n<\/strong><\/span><\/p>\n

    \n\n\n<\/colgroup>\n\n\n
    \n

    SQL> select count(*) from emp;
    \n<\/span><\/p>\n

    \n <\/p>\n

    COUNT(*)
    \n<\/span><\/p>\n

    ———-
    \n<\/span><\/p>\n

    14
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select current_scn from v$database;
    \n<\/span><\/p>\n

    \n <\/p>\n

    CURRENT_SCN
    \n<\/span><\/p>\n

    ———–
    \n<\/span><\/p>\n

    25842839
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> delete from emp where rownum < 5; — \uc798\ubabb\ub41c transaction\uc744 \uc218\ud589\ud568.
    \n<\/span><\/p>\n

    \n <\/p>\n

    4 rows deleted.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> commit;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Commit complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select current_scn from v$database;
    \n<\/span><\/p>\n

    \n <\/p>\n

    CURRENT_SCN
    \n<\/span><\/p>\n

    ———–
    \n<\/span><\/p>\n

    25842867
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> alter table emp enable row movement; — flashback table\uc744 \ud558\uae30 \uc704\ud574 enable \uc2dc\ud0b4.
    \n<\/span><\/p>\n

    \n <\/p>\n

    Table altered.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> flashback table emp to scn 25842839;
    \n<\/span><\/p>\n

    \n <\/p>\n

    Flashback complete.
    \n<\/span><\/p>\n

    \n <\/p>\n

    SQL> select count(*) from emp; <\/span>\u00e8<\/span> Flashback\ud6c4, delete\uc804\uc758 \ub370\uc774\ud130\uac00 \ub428.
    \n<\/span><\/span><\/p>\n

    \n <\/p>\n

    COUNT(*)
    \n<\/span><\/p>\n

    ———-
    \n<\/span><\/p>\n

    14<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    \n <\/p>\n

    \n <\/p>\n<\/li>\n

  15. \n

    Flashback Use Case
    \n<\/h3>\n

    \u2022 \uc7a5\uc560\uc758\uacbd\uc6b0\uc5d0 \ub530\ub77c Use Case\ub97c\uc0ac\uc6a9\ud558\uc5ec \uc2e0\uc18d\ud788 \ubcf5\uad6c\ud55c\ub2e4.<\/strong>
    \n\t\t\t\t<\/span><\/p>\n

    \n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n
    \n

    \uc7a5\uc560 Case<\/strong><\/span><\/p>\n<\/td>\n

    		\n

    Case \uc0c1\uc138<\/strong><\/span><\/p>\n<\/td>\n

    		\n

    \ubcf5\uad6c \ubc29\ubc95<\/strong><\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    Table\uc774 Drop\ub41c\uacbd\uc6b0<\/span><\/p>\n<\/td>\n

    		 <\/td>\n\n

    Recyclebin\uc744 \uc870\ud68c\ud558\uc5ec drop\ud55c table\uc758 \ubcf5\uad6c\uac00\ub2a5\uc131\uc744 \ud655\uc778 \ud55c\ub2e4.
    \n<\/span><\/p>\n

    Flashback Drop\uc744 \uc774\uc6a9\ud558\uc5ec \ubcf5\uad6c \ud55c\ub2e4.<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    Table\uc5d0 \ub370\uc774\ud130\ub97c \uc798\ubabb
    \n<\/span><\/p>\n

    \ubcc0\uacbd\ud558\uace0 commit\ud55c \uacbd\uc6b0<\/span><\/p>\n<\/td>\n

    		\n

    \ub9ce\uc740 \ub370\uc774\ud130 \ubcc0\uacbd\uc2dc<\/span><\/p>\n<\/td>\n

    		\n

    \ubcc0\uacbd\uc2dc\uc810\uc73c\ub85c Table\uc744 flashback\ud558\ub294 \ubc29\ubc95.<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    \uc801\uc740 \ub370\uc774\ud130 \ubcc0\uacbd\uc2dc<\/span><\/p>\n<\/td>\n

    		\n

    Table\uc5d0 \ub300\ud574 Version query\ub97c \uc774\uc6a9\ud558\uc5ec \ud574\ub2f9data\uc758 \ubcc0\uacbd tx\ub97c \ucc3e\ub294 \ubc29\ubc95.<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    Program\uc774 \uc798\ubabb \uc218\ud589\ub418\uc5b4 \uc5ec\ub7ec\uac1c\uc758 table\uc5d0 \ubcc0\uacbd\ub418\uc5c8\uc744 \uacbd\uc6b0.<\/span><\/p>\n<\/td>\n

    		\n

    Commit\uc774
    \n<\/span><\/p>\n

    \ud55c\ubc88\uc77c \uacbd\uc6b0<\/span><\/p>\n<\/td>\n

    		\n

    \ud558\ub098\uc758 Table\uc5d0\uc11c, \ubcc0\uacbd\ub41c Data\uc5d0 \ub300\ud55c Versions query\ub97c \ud558\uc5ec Transaction\uc744 \ucc3e\uc740 \ud6c4, Transaction\uc5d0 \ub300\ud55c undo\ub97c \ubf51\uc544 \ubcf5\uad6c.<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    Commit\uc774 \uc5ec\ub7ec \ubc88\uc778 \uacbd\uc6b0<\/span><\/p>\n<\/td>\n

    		\n

    Flashback query\ub97c \ud1b5\ud574 \uc5ec\ub7ec Table\uc744 Select\ud558\uc5ec backup\ubcf8 \uad6c\uc131.<\/span><\/p>\n<\/td>\n<\/tr>\n

    \n

    \ub370\uc774\ud130\uc5d0 \ub300\ud55c \ubcc0\uacbd\uc774\ub825 \ucd94\uc801\uc2dc<\/span><\/p>\n<\/td>\n

    		 <\/td>\n\n

    Flashback Version Query\ub97c \uc774\uc6a9\ud558\uc5ec \ubcc0\uacbd \uc774\ub825 \ucd94\uc801<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n


    \n\t\t\t\t<\/span> <\/p>\n<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

      Flashback Flashback\uc774\ub780? \uc0ac\uc6a9\uc790 \uc2e4\uc218\uc5d0 \uc758\ud55c \uc190\uc0c1\ub41c \ub370\uc774\ud130\ub97c Database\uc758 \ud06c\uae30\uc640 \uc0c1\uad00\uc5c6\uc774 \ubcf5\uad6c\ub97c \ud560\uc218 \uc788\ub294\uae30\ub2a5\uc774\ub2e4. \uc774 Flashback \uae30\ub2a5\uc740 \uc77c\ubc18\uc801\uc778 \ubcf5\uad6c\uc5d0\uc11c \uc6b0\ub824\ub418\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc758\ud06c\uae30\ub97c \uac71\uc815\ud558\uc9c0 \uc54a\uc544\ub3c4 \ub41c\ub2e4. \ubcf4\ud1b5\uc758 \uc0ac\uc6a9\uc790 \uc2e4\uc218\ub294 \ucee4\ub2e4\ub780\uc2dc\uc2a4\ud15c \uc7a5\uc560\uac00\uc218\ubc18\ub418\uba70, \uc774\ub97c \ubcf5\uad6c\ud558\uae30 \uc704\ud574\uc11c\ub294\ub9ce\uc740 \uc790\uc6d0\uacfc \uc2dc\uac04\uc774 \ud544\uc694\ud558\ub2e4. \ud558\uc9c0\ub9cc 9i\uc5d0\uc11c \uc9c0\uc6d0\ub418\ub294 flashback query\uc640 10g\uc5d0\uc11c \uc9c0\uc6d0\ud558\ub294 \ub2e4\uc591\ud55c flashback\uc744\ud1b5\ud558\uc5ec \uc190\uc27d\uac8c \uc0ac\uc6a9\uc790\uc2e4\uc218\ub97c \uc190\uc27d\uac8c \ubcf5\uad6c\ud55c\ub2e4. Oracle 9i \ubd80\ud130\ub294 AUM \ud658\uacbd\ud558\uc5d0\uc11c Flashback \uae30\ub2a5\uc744 […]<\/p>\n","protected":false},"author":1,"featured_media":556,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[279,280,281,282,283],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/1\/cfile1.uf.213EAB4A5639C51D0F5BD0.png?fit=3%2C14","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/555"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=555"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/555\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/556"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=555"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}