차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

--- dbms:oracle:admin:flashback [2015/06/20 16:36] – [HR 계정으로 접속] starlits
+++ dbms:oracle:admin:flashback [2025/10/03 22:30] (현재) – 바깥 편집 127.0.0.1
@@ 줄 7: / 줄 7: @@
   - <code> export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'</code>
 ==== HR 계정 활성화 ====
-  - <code> SQL> alter user scott identified by oracle account unlock; </code>
+  - <code> SQL> alter user hr identified by oracle account unlock; </code>
-  - <code> SQL> grant execute on dbms_flashback to scott; </code>
+  - <code> SQL> grant execute on dbms_flashback to hr; </code>
 ==== HR 계정으로 접속 ====
-  - <code> sqlplus scott/oracle </code>
+  - <code> sqlplus hr/oracle </code>
   - <code> SQL> select systimestamp from dual; </code>
+==== HR 계정으로 AS OF SCN ====
+  - <code> SQL> create table emp2 as select * from emp; </code>
   - <code> SQL> select dbms_flashback.get_system_change_number() from dual; </code>
-  - <code> SQL> create table empty as select * from emp; </code>
+  - <code> SQL> select count(*) from emp2; </code>
-  - <code> SQL> select count(*) from emp; </code>
+  - <code> SQL> delete from emp2; </code>
-  - <code> SQL> delete from emp; </code>
+  - <code> SQL> select count(*) from emp2; </code>
-  - <code> SQL> select count(*) from emp; </code>
+  - <code> SQL> select count(*) from emp2 as of scn <scn_number>; </code>
-  - <code> SQL> select count(*) from emp as of scn <scn_number>; </code>
+  - <code> SQL> insert into emp2 select * from emp2 as of scn <scn_number>; </code>
+  -
+==== HR 계정으로 AS OF TIMESTAMP ====
+  - <code> SQL> select systimestamp from dual; </code>
+  - <code> SQL> select count(1) from emp2; </code>
+  - <code> SQL> delete from emp2 where deptno=30; </code>
+  - <code> SQL> commit; </code>
+  - <code> SQL> select systimestamp from dual; </code>
+  - <code> SQL> select count(*) from emp2; </code>
+  - <code> SQL> select count(*) from emp2
+as of timestamp(to_timestamp('2015-06-20 16:42:00','YYYY-MM-DD HH24:MI:SS')); </code>
+  - <code> SQL> select count(*) from emp2
+as of timestamp(to_timestamp('2015-06-20 16:42:00','YYYY-MM-DD HH24:MI:SS')) where deptno=30; </code>
+  - <code> SQL> insert into emp2 select * from emp2
+as of timestamp(to_timestamp('2015-06-20 16:42:00','YYYY-MM-DD HH24:MI:SS')) where deptno=30; </code>
+==== HR 계정 flashback version query ====
+  - <code>
+SQL> create table emp as select * from scott.emp;
+SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
+-06-20 16:57:28
+SQL> update emp set comm=1234 where empno=7782;
+SQL> commit;
+SQL> update emp set comm=3456 where empno=7782;
+SQL> rollback;
+SQL> update emp set comm=5678 where empno=7782;
+SQL> commit;
+SQL> update emp set comm=7890 where empno=7782;
+SQL> commit;
+SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
+-06-20 17:01:08
+SQL>
+set lines 200
+column ename format a10
+column start_time format a20
+column end_time format a20
+SELECT empno, ename, sal, comm, deptno,
+    versions_xid AS XID,
+    versions_operation as operation,
+    versions_startscn AS START_SCN,
+    versions_starttime as start_time,
+    versions_endscn AS END_SCN,
+    versions_endtime as end_time
+FROM emp VERSIONS BETWEEN TIMESTAMP
+        TO_TIMESTAMP('2015-06-20 16:57:27','YYYY-MM-DD HH24:MI:SS')
+   AND TO_TIMESTAMP('2015-06-20 17:01:09','YYYY-MM-DD HH24:MI:SS')
+WHERE empno=7782
+order by versions_starttime nulls first;
+</code>

adminschool.net wiki

사용자 도구

사이트 도구

차이

문서 도구