Microsoft Windows [Version 10.0.17763.503]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\rajam>sqlplus sys as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 7 18:09:40 2019
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> set lines 100
SQL> set pages 50
SQL> create table flash_test as select * from emp;
Table created.
SQL> select * from flash_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select count(*) from flash_test;
COUNT(*)
----------
14
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ddate,dbms_flashback.get_system_change_number() scn from dual;
DDATE SCN
-------------------- ----------
07-06-2019 18:11:12 12292660
SQL> delete from flash_test;
14 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from flash_test;
no rows selected
SQL> -- to view the data of the table at the specified timestamp
SQL> select count(*) from flash_test as of timestamp to_timestamp('07-06-2019 18:11:12','dd-mm-yyyy hh24:mi:ss');
COUNT(*)
----------
14
SQL> -- to view the data of the table at the specified scn
SQL> select count(*) from flash_test as of scn 12292660;
COUNT(*)
----------
14
SQL> -- To view the data of the table as it was 8 minutes ago
SQL> select systimestamp,count(*) from flash_test as of timestamp (systimestamp -interval '8' minute);
SYSTIMESTAMP COUNT(*)
--------------------------------------------------------------------------- ----------
07-JUN-19 06.19.21.585000 PM +05:30 14
SQL> -- to convert scn to timestamp and timestamp to scn
SQL> select scn_to_timestamp(12292660) ddate, timestamp_to_scn(to_timestamp('07-06-2019 18:11:12','dd-mm-yyyy hh24:mi:ss')) scn from dual;
DDATE SCN
--------------------------------------------------------------------------- ----------
07-JUN-19 06.11.11.000000000 PM 12292660
SQL> select * from flash_test as of scn 12292660;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Microsoft Windows [Version 10.0.17134.765]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\rajam>sqlplus system/admin
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 27 10:18:03 2019
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> set define off
SQL> set lines 200
SQL> SELECT * FROM EMP_DETAILS;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
27-MAY-19 10.18.28.935000 AM +05:30
SQL> UPDATE EMP_DETAILS SET COMM=500 WHERE EMPNO=7782;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM EMP_DETAILS;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
27-MAY-19 10.23.03.328000 AM +05:30
SQL> SELECT * FROM EMP_DETAILS AS OF TIMESTAMP TO_TIMESTAMP('27-MAY-2019 10:19:34 AM','DD-MON-YYYY HH:MI:SS AM');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SELECT * FROM EMP_DETAILS AS OF TIMESTAMP TO_TIMESTAMP('27-MAY-2019 10:19:34 AM','DD-MON-YYYY HH:MI:SS AM') WHERE EMPNO=7782;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL>
No comments:
Post a Comment