| 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; |
