as of timestamp 구문으로 과거시점의 데이터를 조회 할 수가 있다.
몇가지 테스트를 해보았다.
1. as of 의 시점은 commit 시 인가? db입력 시 인가?
create table test_fb
( A varchar2(10),
B date
);
SQL> insert into test_fb values('aaa',sysdate);
1 row created.
SQL> select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2018-10-23 15:18:17
SQL> insert into test_fb values('bbb',sysdate);
select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
1 row created.
SQL> SQL>
TO_CHAR(SYSDATE,'YY
-------------------
2018-10-23 15:37:04
SQL>
SQL> insert into test_fb values('ccc',sysdate);
select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
1 row created.
SQL> SQL>
TO_CHAR(SYSDATE,'YY
-------------------
2018-10-23 15:40:18
SQL> select a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted from test_fb;
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 15:39:00', 'YYYY-MM-DD HH24:MI:SS');
no rows selected
### 두번째와 세번쨰 데이터 입력시점 사이로 as of timestamp 를 써보았지만 결과가 없다.
SQL> commit;
Commit complete.
SQL> select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2018-10-23 15:41:29
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 15:40:00', 'YYYY-MM-DD HH24:MI:SS');
no rows selected
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:00:00', 'YYYY-MM-DD HH24:MI:SS');
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 15:45:00', 'YYYY-MM-DD HH24:MI:SS');
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 15:41:00', 'YYYY-MM-DD HH24:MI:SS');
no rows selected
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 15:42:00', 'YYYY-MM-DD HH24:MI:SS');
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
### 관찰 : statement 완료시점 아닌 commit 시점으로 flashback 한다.
2. 시간차를 두고 데이터를 입력해보고 원하는 시점으로 돌아갈수 있는지 보자.
SQL> insert into test_fb values('ddd',sysdate);
commit;
select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
1 row created.
SQL>
Commit complete.
SQL>
TO_CHAR(SYSDATE,'YY
-------------------
2018-10-23 16:03:02
SQL> insert into test_fb values('eee',sysdate);
commit;
select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
1 row created.
SQL>
Commit complete.
SQL>
TO_CHAR(SYSDATE,'YY
-------------------
2018-10-23 16:14:02
SQL> insert into test_fb values('fff',sysdate);
commit;
select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
1 row created.
SQL>
Commit complete.
SQL>
TO_CHAR(SYSDATE,'YY
-------------------
2018-10-23 16:19:18
SQL>
SQL>
SQL> select a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted from test_fb
2 ;
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
ddd 2018-10-23 16:03:00
eee 2018-10-23 16:13:59
fff 2018-10-23 16:19:18
6 rows selected.
*** aaa, bbb,ccc 는 입력시점은 다르지만 커밋 시점이 같다.
ddd,eee,fff 는 커밋 시점이 다르다.
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:15:00', 'YYYY-MM-DD HH24:MI:SS');
2
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
ddd 2018-10-23 16:03:00
eee 2018-10-23 16:13:59
*** 예상대로 16시19분 커밋된 fff 는 나오지 않는다.
조금 더 테스트를 해보았다. 어느 순간에 eee 가 안보일지..
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:14:00', 'YYYY-MM-DD HH24:MI:SS');
2
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
ddd 2018-10-23 16:03:00
eee 2018-10-23 16:13:59
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:13:59', 'YYYY-MM-DD HH24:MI:SS');
2
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
ddd 2018-10-23 16:03:00
eee 2018-10-23 16:13:59
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:13:58', 'YYYY-MM-DD HH24:MI:SS');
2
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
ddd 2018-10-23 16:03:00
eee 2018-10-23 16:13:59
*** ㅇㅇ?? 뭔가 이상하다.
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:13:57', 'YYYY-MM-DD HH24:MI:SS');
2
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
ddd 2018-10-23 16:03:00
SQL> SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb
AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:13:58', 'YYYY-MM-DD HH24:MI:SS');
2
A TIME_INSERTED
------ -------------------
aaa 2018-10-23 15:18:10
bbb 2018-10-23 15:37:04
ccc 2018-10-23 15:40:17
ddd 2018-10-23 16:03:00
eee 2018-10-23 16:13:59
*** 예상과 다르게 16시13분59초에 커밋된 데이터가 16시13분58초 시점으로 flashback 했을때도 보였다.
*** sysdate 의 시간과 flashback 이 사용하는 시간이 다른듯하다.
'생계 > Oracle' 카테고리의 다른 글
| 오라클 like 검색에서 특수문자 사용하기 (0) | 2019.06.04 |
|---|---|
| tnsnames.ora 를 통한 DB 접속 테스트 (0) | 2018.11.05 |
| 다른 owner 의 테이블 truncate (0) | 2018.05.31 |
| 리눅스환경 오라클 삭제 linux (0) | 2018.05.24 |
| 구글 클라우드 플랫폼에 오라클 12c 설치 연결 테스트 (0) | 2018.04.10 |
