Creating a Flashback Data Archive


Create a new tablespace or use existing tablespace – tablespace needs to be ASSM
Specify the FBDA as the default (optional)
Assign a quota for the FBDA (optional)
Assign a retention period for the FBDA
Retention period integer denoting days,months or years
Enable flashback archive for a specific table via the CREATE TABLE or ALTER TABLE clause. By default it is turned off.
SQL> CREATE TABLESPACE his_data_1
 2 DATAFILE '+data' SIZE 500M;
Tablespace created.
SQL> CREATE FLASHBACK ARCHIVE DEFAULT anarfb1
 2 TABLESPACE his_data_1
 3 RETENTION 2 DAY;
Flashback archive created.
SQL> SELECT FLASHBACK_ARCHIVE_NAME, to_char(CREATE_TIME,'dd-mon-yyyy') Created,
 2 RETENTION_IN_DAYS,STATUS FROM DBA_FLASHBACK_ARCHIVE;
FLASHBACK_ARCHIVE_NA CREATED RETENTION_IN_DAYS STATUS
-------------------- ----------- ----------------- -------
anarfb1 02-nov-2015 2 DEFAULT
SQL> GRANT FLASHBACK ARCHIVE ON anarfb1 TO scott;
Grant succeeded.
SQL> ALTER TABLE mysales FLASHBACK ARCHIVE anarfb1;
Table altered.
SQL> CREATE TABLE
 2 EMPSAL_HIS
 3 (EMPNO number,
 4 ENAME VARCHAR2(10),
 5 SAL NUMBER,
 6 FLASHBACK ARCHIVE;
Table created.
SQL> select TABLE_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS
 2 from USER_FLASHBACK_ARCHIVE_tables;
TABLE_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME STATUS
----------- -------------------- -------------------- ------------
MYSALES anarfb1 SYS_FBA_HIST_77429 ENABLED
EMPSAL_HIS anarfb1 SYS_FBA_HIST_77419 ENABLED
SQL> select object_id from dba_objects where object_name=‘YKB';
OBJECT_ID
----------
 73201
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
YKB USERS
SYS_FBA_DDL_COLMAP_73201 HIS_DATA_1
SYS_FBA_TCRV_73201 HIS_DATA_1
SYS_FBA_HIST_73201
SQL> desc SYS_FBA_HIST_73201
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 RID VARCHAR2(4000)
 STARTSCN NUMBER
 ENDSCN NUMBER
 XID RAW(8)
 OPERATION VARCHAR2(1)
EMPNO NUMBER(4)
 ENAME VARCHAR2(10)
 MGR NUMBER(10)
YKBNO NUMBER(2)
 JOB VARCHAR2(20)
SQL> SELECT TABLE_NAME,PARTITION_NAME,COMPRESSION from USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
SYS_FBA_HIST_77429 HIGH_PART ENABLED
SYS_FBA_HIST_77419 HIGH_PART ENABLED
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: