11g - Flashback Data Archive (Oracle Total Recall) :
=====================================================================
DEMO DEMO DEMO DEMO DEMO DEMO DEMO
=====================================================================
SQL> sho user
USER is "SYS"
SQL> select name,user from v$database;
NAME USER
--------- ------------------------------
ASKM SYS
SQL> CREATE TABLESPACE fda_ts DATAFILE '/u01/app/oracle/oradata/askm/fda_ts01.dbf' SIZE 24M;
Tablespace created.
SQL> GRANT FLASHBACK ARCHIVE ADMINISTER TO sh;
Grant succeeded.
SQL> GRANT FLASHBACK ANY TABLE TO sh;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO sh;
Grant succeeded.
USER is "SYS"
SQL> select name,user from v$database;
NAME USER
--------- ------------------------------
ASKM SYS
SQL> CREATE TABLESPACE fda_ts DATAFILE '/u01/app/oracle/oradata/askm/fda_ts01.dbf' SIZE 24M;
Tablespace created.
SQL> GRANT FLASHBACK ARCHIVE ADMINISTER TO sh;
Grant succeeded.
SQL> GRANT FLASHBACK ANY TABLE TO sh;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO sh;
Grant succeeded.
SQL> conn sh/sh
Connected.
SQL> CREATE FLASHBACK ARCHIVE fda TABLESPACE fda_ts QUOTA 1M RETENTION 7 DAY;
Flashback archive created.
SQL> create table rate_conv (currency varchar2(6),Rate number(15,4));
Table created.
SQL> ALTER TABLE sh.rate_conv FLASHBACK ARCHIVE fda;
Table altered.
SQL> insert into rate_conv values ('IND-Rs',48.1012);
1 row created.
SQL> commit;
Commit complete.
SQL> update rate_conv set rate=49.0120;
1 row updated.
SQL> commit;
Commit complete.
SQL> update rate_conv set rate=48.2012;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete rate_conv;
1 row deleted.
SQL> commit;
Commit complete.
SQL> insert into rate_conv values ('IND-Rs',50.0021);
1 row created.
SQL> commit;
Commit complete.
SQL> update rate_conv set rate=49.0120;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from rate_conv;
CURREN RATE
------ ----------
IND-Rs 49.012
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
------------------------- ------------------------- ---------------- - ----------
01-OCT-09 08.56.25 AM 01-OCT-09 08.56.28 AM 0A0020009B020000 I 48.1012
01-OCT-09 08.56.28 AM 01-OCT-09 08.56.31 AM 060011002A030000 U 49.012
01-OCT-09 08.56.31 AM 01-OCT-09 08.56.40 AM 03000F003A030000 U 48.2012
01-OCT-09 08.56.40 AM 050005006D030000 D 48.2012
01-OCT-09 08.56.44 AM 01-OCT-09 08.56.48 AM 0900090027030000 I 50.0021
01-OCT-09 08.56.48 AM 0200010018030000 U 49.012
6 rows selected.
SQL> desc dba_FLASHBACK_ARCHIVE_TABLES;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
OWNER_NAME NOT NULL VARCHAR2(30)
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)
ARCHIVE_TABLE_NAME VARCHAR2(53)
STATUS VARCHAR2(8)
SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME
-----------------------------------------------------
RATE_CONV SH
FDA
SYS_FBA_HIST_74714
SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME
---------- ---------- ------------------------- --------------------
RATE_CONV SH FDA SYS_FBA_HIST_74714
SQL> desc SYS_FBA_HIST_74714
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
RID VARCHAR2(4000)
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(8)
OPERATION VARCHAR2(1)
CURRENCY VARCHAR2(6)
RATE NUMBER(15,4)
SQL> select count(1) from SYS_FBA_HIST_74714;
COUNT(1)
----------
4
SQL> desc rate_conv
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
CURRENCY VARCHAR2(6)
RATE NUMBER(15,4)
SQL> select * from SYS_FBA_HIST_74714;
RID STARTSCN ENDSCN XID O CURREN RATE
-------------------- --------- --------- ------------------------- - ------ ----------
AAASPaAAEAABhUvAAA 1234564 1234570 0A0020009B020000 I IND-Rs 48.1012
AAASPaAAEAABhUvAAA 1234570 1234573 060011002A030000 U IND-Rs 49.012
AAASPaAAEAABhUvAAA 1234573 1234577 03000F003A030000 U IND-Rs 48.2012
AAASPaAAEAABhUvAAB 1234588 1234591 0900090027030000 I IND-Rs 50.0021
SQL> select * from rate_conv;
CURREN RATE
------ ----------
IND-Rs 49.012
Connected.
SQL> CREATE FLASHBACK ARCHIVE fda TABLESPACE fda_ts QUOTA 1M RETENTION 7 DAY;
Flashback archive created.
SQL> create table rate_conv (currency varchar2(6),Rate number(15,4));
Table created.
SQL> ALTER TABLE sh.rate_conv FLASHBACK ARCHIVE fda;
Table altered.
SQL> insert into rate_conv values ('IND-Rs',48.1012);
1 row created.
SQL> commit;
Commit complete.
SQL> update rate_conv set rate=49.0120;
1 row updated.
SQL> commit;
Commit complete.
SQL> update rate_conv set rate=48.2012;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete rate_conv;
1 row deleted.
SQL> commit;
Commit complete.
SQL> insert into rate_conv values ('IND-Rs',50.0021);
1 row created.
SQL> commit;
Commit complete.
SQL> update rate_conv set rate=49.0120;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from rate_conv;
CURREN RATE
------ ----------
IND-Rs 49.012
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
------------------------- ------------------------- ---------------- - ----------
01-OCT-09 08.56.25 AM 01-OCT-09 08.56.28 AM 0A0020009B020000 I 48.1012
01-OCT-09 08.56.28 AM 01-OCT-09 08.56.31 AM 060011002A030000 U 49.012
01-OCT-09 08.56.31 AM 01-OCT-09 08.56.40 AM 03000F003A030000 U 48.2012
01-OCT-09 08.56.40 AM 050005006D030000 D 48.2012
01-OCT-09 08.56.44 AM 01-OCT-09 08.56.48 AM 0900090027030000 I 50.0021
01-OCT-09 08.56.48 AM 0200010018030000 U 49.012
6 rows selected.
SQL> desc dba_FLASHBACK_ARCHIVE_TABLES;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
OWNER_NAME NOT NULL VARCHAR2(30)
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)
ARCHIVE_TABLE_NAME VARCHAR2(53)
STATUS VARCHAR2(8)
SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME
-----------------------------------------------------
RATE_CONV SH
FDA
SYS_FBA_HIST_74714
SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME
---------- ---------- ------------------------- --------------------
RATE_CONV SH FDA SYS_FBA_HIST_74714
SQL> desc SYS_FBA_HIST_74714
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
RID VARCHAR2(4000)
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(8)
OPERATION VARCHAR2(1)
CURRENCY VARCHAR2(6)
RATE NUMBER(15,4)
SQL> select count(1) from SYS_FBA_HIST_74714;
COUNT(1)
----------
4
SQL> desc rate_conv
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
CURRENCY VARCHAR2(6)
RATE NUMBER(15,4)
SQL> select * from SYS_FBA_HIST_74714;
RID STARTSCN ENDSCN XID O CURREN RATE
-------------------- --------- --------- ------------------------- - ------ ----------
AAASPaAAEAABhUvAAA 1234564 1234570 0A0020009B020000 I IND-Rs 48.1012
AAASPaAAEAABhUvAAA 1234570 1234573 060011002A030000 U IND-Rs 49.012
AAASPaAAEAABhUvAAA 1234573 1234577 03000F003A030000 U IND-Rs 48.2012
AAASPaAAEAABhUvAAB 1234588 1234591 0900090027030000 I IND-Rs 50.0021
SQL> select * from rate_conv;
CURREN RATE
------ ----------
IND-Rs 49.012
SQL> conn /as sysdba
Connected.
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/app/oracle/oradata/askm/undotbs2_01.dbf' SIZE 2M;
Tablespace created.
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTBS2' SCOPE=BOTH;
System altered.
Connected.
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/app/oracle/oradata/askm/undotbs2_01.dbf' SIZE 2M;
Tablespace created.
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTBS2' SCOPE=BOTH;
System altered.
SQL> conn sh/sh
Connected.
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
----------------------------------- ----------------------------------- ---------------- - ----------
01-OCT-09 08.56.25.000000000 AM 01-OCT-09 08.56.28.000000000 AM 0A0020009B020000 I 48.1012
01-OCT-09 08.56.28.000000000 AM 01-OCT-09 08.56.31.000000000 AM 060011002A030000 U 49.012
01-OCT-09 08.56.31.000000000 AM 01-OCT-09 08.56.40.000000000 AM 03000F003A030000 U 48.2012
01-OCT-09 08.56.44.000000000 AM 01-OCT-09 08.56.48.000000000 AM 0900090027030000 I 50.0021
01-OCT-09 08.56.48.000000000 AM 0200010018030000 U 49.012
SQL> select rate from rate_conv as of timestamp to_timestamp('2009-10-01 08:56:29','yyyy-mm-dd hh24:mi:ss');
RATE
----------
48.1012
SQL> explain plan for select rate from rate_conv as of timestamp to_timestamp('2009-10-01 08:56:29','yyyy-mm-dd hh24:mi:ss');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3276248349
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 65 | 9 (12)| 00:00:01 | | |
| 1 | VIEW | | 5 | 65 | 9 (12)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | FILTER | | | | | | | |
| 4 | PARTITION RANGE SINGLE| | 1 | 39 | 3 (0)| 00:00:01 | KEY | 1 |
|* 5 | TABLE ACCESS FULL | SYS_FBA_HIST_74714 | 1 | 39 | 3 (0)| 00:00:01 | KEY | 1 |
|* 6 | FILTER | | | | | | | |
|* 7 | HASH JOIN OUTER | | 4 | 8212 | 6 (17)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | RATE_CONV | 4 | 100 | 2 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | SYS_FBA_TCRV_74714 | 2 | 4056 | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01 08:56:29.000000000')<1242042)
5 - filter("ENDSCN"<=1242042 AND "ENDSCN">"TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01
08:56:29.000000000') AND ("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01
08:56:29.000000000')))
6 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01 08:56:29.000000000') OR "STARTSCN"
IS NULL)
7 - access("T".ROWID=CHARTOROWID("RID"(+)))
8 - filter("T"."VERSIONS_STARTSCN" IS NULL)
9 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>1242042) AND ("STARTSCN"(+) IS NULL OR
"STARTSCN"(+)<1242042))
Note
-----
- dynamic sampling used for this statement (level=2)
34 rows selected.
SQL>
SQL> alter table rate_conv NO FLASHBACK ARCHIVE;
Table altered.
Connected.
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
----------------------------------- ----------------------------------- ---------------- - ----------
01-OCT-09 08.56.25.000000000 AM 01-OCT-09 08.56.28.000000000 AM 0A0020009B020000 I 48.1012
01-OCT-09 08.56.28.000000000 AM 01-OCT-09 08.56.31.000000000 AM 060011002A030000 U 49.012
01-OCT-09 08.56.31.000000000 AM 01-OCT-09 08.56.40.000000000 AM 03000F003A030000 U 48.2012
01-OCT-09 08.56.44.000000000 AM 01-OCT-09 08.56.48.000000000 AM 0900090027030000 I 50.0021
01-OCT-09 08.56.48.000000000 AM 0200010018030000 U 49.012
SQL> select rate from rate_conv as of timestamp to_timestamp('2009-10-01 08:56:29','yyyy-mm-dd hh24:mi:ss');
RATE
----------
48.1012
SQL> explain plan for select rate from rate_conv as of timestamp to_timestamp('2009-10-01 08:56:29','yyyy-mm-dd hh24:mi:ss');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3276248349
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 65 | 9 (12)| 00:00:01 | | |
| 1 | VIEW | | 5 | 65 | 9 (12)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | FILTER | | | | | | | |
| 4 | PARTITION RANGE SINGLE| | 1 | 39 | 3 (0)| 00:00:01 | KEY | 1 |
|* 5 | TABLE ACCESS FULL | SYS_FBA_HIST_74714 | 1 | 39 | 3 (0)| 00:00:01 | KEY | 1 |
|* 6 | FILTER | | | | | | | |
|* 7 | HASH JOIN OUTER | | 4 | 8212 | 6 (17)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | RATE_CONV | 4 | 100 | 2 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | SYS_FBA_TCRV_74714 | 2 | 4056 | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01 08:56:29.000000000')<1242042)
5 - filter("ENDSCN"<=1242042 AND "ENDSCN">"TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01
08:56:29.000000000') AND ("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01
08:56:29.000000000')))
6 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"(TIMESTAMP' 2009-10-01 08:56:29.000000000') OR "STARTSCN"
IS NULL)
7 - access("T".ROWID=CHARTOROWID("RID"(+)))
8 - filter("T"."VERSIONS_STARTSCN" IS NULL)
9 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>1242042) AND ("STARTSCN"(+) IS NULL OR
"STARTSCN"(+)<1242042))
Note
-----
- dynamic sampling used for this statement (level=2)
34 rows selected.
SQL>
SQL> alter table rate_conv NO FLASHBACK ARCHIVE;
Table altered.
Flashback Data Archives information from Data Dictionary :
SQL> conn /as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> SELECT table_name FROM dict WHERE table_name LIKE '%FLASHBACK_ARCHIVE%';
TABLE_NAME
------------------------------
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES
SQL> SELECT flashback_archive_name, create_time, status FROM dba_flashback_archive;
FLASHBACK_ CREATE_TIME STATUS
---------- --------------------------------------------- ----------
FDA 01-OCT-09 08.55.34.000000000 AM
SQL> SELECT * FROM dba_flashback_archive_ts;
FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME
---------- ------------------ ------------------------------
QUOTA_IN_MB
----------------------------------------
FDA 1 FDA_TS
1
SQL> SELECT * FROM dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_
------------------------------ ------------------------------ ----------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- ----------
RATE_CONV SH FDA
SYS_FBA_HIST_74714 ENABLED
Connected.
SQL> sho user
USER is "SYS"
SQL> SELECT table_name FROM dict WHERE table_name LIKE '%FLASHBACK_ARCHIVE%';
TABLE_NAME
------------------------------
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES
SQL> SELECT flashback_archive_name, create_time, status FROM dba_flashback_archive;
FLASHBACK_ CREATE_TIME STATUS
---------- --------------------------------------------- ----------
FDA 01-OCT-09 08.55.34.000000000 AM
SQL> SELECT * FROM dba_flashback_archive_ts;
FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME
---------- ------------------ ------------------------------
QUOTA_IN_MB
----------------------------------------
FDA 1 FDA_TS
1
SQL> SELECT * FROM dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_
------------------------------ ------------------------------ ----------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- ----------
RATE_CONV SH FDA
SYS_FBA_HIST_74714 ENABLED
Maintaining Flashback Data Archive (FBDAs):
SQL> ALTER FLASHBACK ARCHIVE fda MODIFY TABLESPACE fda_ts QUOTA 2M;
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE fda MODIFY RETENTION 90 DAY;
Flashback archive altered.
SQL> CREATE TABLESPACE fda_ts2 DATAFILE '/u01/app/oracle/oradata/askm/fda_ts02.dbf' SIZE 16M;
Tablespace created.
SQL> ALTER FLASHBACK ARCHIVE fda ADD TABLESPACE fda_ts2;
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE fda PURGE BEFORE TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY);
Flashback archive altered.
SQL> DROP FLASHBACK ARCHIVE fda;
Flashback archive dropped.
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE fda MODIFY RETENTION 90 DAY;
Flashback archive altered.
SQL> CREATE TABLESPACE fda_ts2 DATAFILE '/u01/app/oracle/oradata/askm/fda_ts02.dbf' SIZE 16M;
Tablespace created.
SQL> ALTER FLASHBACK ARCHIVE fda ADD TABLESPACE fda_ts2;
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE fda PURGE BEFORE TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY);
Flashback archive altered.
SQL> DROP FLASHBACK ARCHIVE fda;
Flashback archive dropped.
11g - Flashback Data Archive (Oracle Total Recall)
Posts
Post a Comment
Thank you for visiting our site and leaving your valuable comment.