CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Flashback Archive
Version 11.1
 
General
Related Data Dictionary Objects
DBA_FLASHBACK_ARCHIVE SYS_FBA_BARRIERSCN
DBA_FLASHBACK_ARCHIVE_TS SYS_FBA_FA
DBA_FLASHBACK_ARCHIVE_TABLES SYS_FBA_TRACKEDTABLES
Related Privileges FLASHBACK ARCHIVE ADMINISTER
GRANT flashback archive administer TO uwclass;

This change should be made for the demonstration purposes only. It should not be done in a production environment.
set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%retention%';

ALTER SYSTEM SET undo_retention = 1 SCOPE=MEMORY;

-- after the demo return to the default value
ALTER SYSTEM SET undo_retention = 900 SCOPE=MEMORY;
 
Create Flashback Archive

Flashback Archive Creation
CREATE FLASHBACK ARCHIVE [DEFAULT] <flashback_archive_name>
TABLESPACE <tablespace_name>
[QUOTA <integer_value <M | G | T | P>]
RETENTION <retention_value> <YEAR | MONTH | DAY>;
See demo at page bottom
 
Alter Flashback Archive
Set an archive as the default archive for the database ALTER FLASHBACK ARCHIVE <archive_name> SET DEFAULT;
ALTER FLASHBACK ARCHIVE uw_archive SET DEFAULT;
Add a tablespace to an existing archive ALTER FLASHBACK ARCHIVE <archive_name> ADD TABLESPACE <tablespace_name>;
SELECT tablespace_name
FROM user_tablespaces;

ALTER FLASHBACK ARCHIVE uw_archive ADD TABLESPACE examples;
Modify a tablespace quota on an existing archive tablespace ALTER FLASHBACK ARCHIVE <archive_name>
MODIFY TABLESPACE <tablespace_name>
[QUOTA <integer_value <M | G | T | P>];
SELECT *
FROM dba_flashback_archive_ts;

ALTER FLASHBACK ARCHIVE uw_archive ADD TABLESPACE examples QUOTA 10M;

SELECT *
FROM dba_flashback_archive_ts;
Remove a tablespace from a flashback archive ALTER FLASHBACK ARCHIVE <archive_name>
REMOVE TABLESPACE <tablespace_name>
SELECT *
FROM dba_flashback_archive_ts;

ALTER FLASHBACK ARCHIVE uw_archive REMOVE TABLESPACE examples;

SELECT *
FROM dba_flashback_archive_ts;
Change the archive's retention policy ALTER FLASHBACK ARCHIVE <archive_name>
MODIFY RETENTION <retention_value> <YEAR | MONTH | DAY>;
SELECT *
FROM dba_flashback_archive;

ALTER FLASHBACK ARCHIVE
uw_archive MODIFY RETENTION 1 MONTH;

SELECT *
FROM dba_flashback_archive;

Purge a flashback archive based on SCN
ALTER FLASHBACK ARCHIVE <archive_name>
PURGE BEFORE SCN <scn_value>;
desc sys_fba_hist_70439

SELECT DISTINCT startscn AS SCN
FROM sys_fba_hist_70439
UNION
SELECT DISTINCT endscn AS SCN
FROM sys_fba_hist_70439
ORDER BY 1;

ALTER FLASHBACK ARCHIVE uw_archive PURGE BEFORE SCN 8872847;

SELECT DISTINCT startscn AS SCN
FROM sys_fba_hist_70439
UNION
SELECT DISTINCT endscn AS SCN
FROM sys_fba_hist_70439
ORDER BY 1;

Purge a flashback archive based on TIMESTAMP
ALTER FLASHBACK ARCHIVE <archive_name>
PURGE BEFORE TIMESTAMP <timestamp_value>;
desc sys_fba_hist_70439

SELECT DISTINCT SCN_TO_TIMESTAMP(startscn) AS SCNTS
FROM sys_fba_hist_70439
UNION
SELECT DISTINCT SCN_TO_TIMESTAMP(endscn) AS SCNTS
FROM sys_fba_hist_70439
ORDER BY 1;

ALTER FLASHBACK ARCHIVE uw_archive
PURGE BEFORE TIMESTAMP TO_TIMESTAMP('29-JUN-07 11.19.39.000000000 PM');

SELECT DISTINCT SCN_TO_TIMESTAMP(startscn) AS SCNTS
FROM sys_fba_hist_70439
UNION
SELECT DISTINCT SCN_TO_TIMESTAMP(endscn) AS SCNTS
FROM sys_fba_hist_70439
ORDER BY 1;

Purge a flashback archive of all contents
ALTER FLASHBACK ARCHIVE <archive_name> PURGE ALL;
SELECT COUNT(*)
FROM sys_fba_hist_70439;

ALTER FLASHBACK ARCHIVE
uw_archive PURGE ALL;

SELECT COUNT(*)
FROM sys_fba_hist_70439;
 
Drop Flashback Archive

Drop a flashback archive
DROP FLASHBACK ARCHIVE <archive_name>;
SELECT COUNT(*)
FROM dba_flashback_archive_ts;

SELECT COUNT(*)
FROM dba_flashback_archive;

DROP FLASHBACK ARCHIVE uw_archive;

SELECT COUNT(*)
FROM dba_flashback_archive_ts;

SELECT COUNT(*)
FROM dba_flashback_archive;
 
Demo

Technology Demo
DROP FLASHBACK ARCHIVE <archive_name>;
conn uwclass/uwclass

desc dba_flashback_archive_ts

set linesize 121
col flashback_archive_name format a25
col flashback_archive# format 999
col tablespace_name format a20
col quota_in_mb format a15

SELECT COUNT(*)
FROM dba_flashback_archive_ts;

desc user_flashback_archive_tables

SELECT COUNT(*)
FROM user_flashback_archive_tables;

CREATE FLASHBACK ARCHIVE uw_archive
TABLESPACE uwdata
QUOTA 10M
RETENTION 30 DAY;

SELECT *
FROM dba_flashback_archive_ts;

col create_time format a31
col last_purge_time format a31

SELECT flashback_archive_name, retention_in_days,
create_time, last_purge_time
FROM dba_flashback_archive;

ALTER FLASHBACK ARCHIVE uw_archive MODIFY RETENTION 2 MONTH;

SELECT flashback_archive_name, retention_in_days,
create_time, last_purge_time
FROM dba_flashback_archive;

ALTER TABLE servers FLASHBACK ARCHIVE uw_archive;

col table_name format a30
col owner_name format a10
col flashback_archive_name format a30
col archive_table_name format a30

SELECT *
FROM user_flashback_archive_tables;

SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE table_name LIKE 'SYS_FBA%';

UPDATE servers
SET srvr_id = srvr_id+1;

COMMIT;

UPDATE servers
SET srvr_id = srvr_id-3;

COMMIT;

DELETE FROM servers
WHERE srvr_id > 599;

COMMIT;

-- need to wait for undo to age out and then ...
conn / as sysdba

desc sys_fba_ddl_colmap_70439

Name                         Null?    Type
---------------------------- -------- -------------
STARTSCN                              NUMBER
ENDSCN                                NUMBER
XID                                   RAW(8)
OPERATION                             VARCHAR2(1)
COLUMN_NAME                           VARCHAR2(255)
TYPE                                  VARCHAR2(255)
HISTORICAL_COLUMN_NAME                VARCHAR2(255)

desc sys_fba_tcrv_70439

Name                         Null?    Type
---------------------------- -------- -----------
RID                                   ROWID
STARTSCN                              NUMBER
ENDSCN                                NUMBER
XID                                   RAW(8)
OP                                    VARCHAR2(1)

desc sys_fba_hist_70439

Name                         Null?    Type
---------------------------- -------- --------------
RID                                   VARCHAR2(4000)
STARTSCN                              NUMBER
ENDSCN                                NUMBER
XID                                   RAW(8)
OPERATION                             VARCHAR2(1)
SRVR_ID                               NUMBER(10)
NETWORK_ID                            NUMBER(10)
STATUS                                VARCHAR2(1)
LATITUDE                              FLOAT(20)
LONGITUDE                             FLOAT(20)
NETADDRESS                            VARCHAR2(15)

SELECT COUNT(*)
FROM sys_fba_hist_70439;

SELECT *
FROM user_flashback_archive_tables;

DELETE FROM sys_fba_hist_70439;

UPDATE sys_fba_hist_70439
SET netaddress = 'Oops';

DROP TABLE sys_fba_hist_70439;

ALTER TABLE servers NO FLASHBACK ARCHIVE;

SELECT *
FROM user_flashback_archive_tables;

SELECT flashback_archive_name, status
FROM dba_flashback_archive;

DROP FLASHBACK ARCHIVE uw_archive;

SELECT flashback_archive_name, status
FROM dba_flashback_archive;

Warning
/* Should a table created by sys to support Flashback Archive fail to drop when the table is converted to NO FLASHBACK ARCHIVE the table can not be dropped with normal SQL. */

-- The solution is to do the following:


SELECT o.object_id, o. owner, o.object_name, t.property
FROM dba_objects o, tab$ t
WHERE o.object_type = 'TABLE'
AND o.object_id = t.obj#
AND t.property = 9126805504;

-- If the object returned is the one you are trying to drop then:

UPDATE tab$
SET property = 536870912
WHERE property = 9126805504;

COMMIT;

-- You will then be able to drop the table. For example:

DROP TABLE SYS_FBA_TCRV_73674;
 
Related Topics
Flashback Query
Flashback Table
Flashback Transaction Backout
Flashback Transaction Query
Flashback Version Query
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [83 users online]    © 2010 psoug.org