| General |
| Related Data Dictionary Objects |
| FLASHBACK_ARCHIVED_TABLES |
FLASHBACK_ARCHIVE_TABLESPACES |
| FLASHBACK_ARCHIVES |
USER_FLASHBACK_ARCHIVED_TABLES |
|
| 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 = 2 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>; |
conn uwclass/uwclass
desc flashback_archive_tablespaces
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 flashback_archive_tablespaces;
desc user_flashback_archived_tables
SELECT COUNT(*)
FROM user_flashback_archived_tables;
CREATE FLASHBACK ARCHIVE uw_archive
TABLESPACE uwdata
QUOTA 10 M
RETENTION 30 DAY;
SELECT *
FROM flashback_archive_tablespaces;
SELECT *
FROM flashback_archives;
ALTER FLASHBACK ARCHIVE
uw_archive MODIFY RETENTION 2 MONTH;
SELECT *
FROM flashback_archives;
ALTER TABLE servers FLASHBACK ARCHIVE uw_archive;
SELECT *
FROM user_flashback_archived_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
set linesize 80
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; |
| |
| 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 flashback_archive_tablespaces;
ALTER FLASHBACK ARCHIVE
uw_archive ADD TABLESPACE examples QUOTA
10 M;
SELECT *
FROM flashback_archive_tablespaces; |
| Remove a tablespace
from a flashback archive |
ALTER FLASHBACK ARCHIVE <archive_name>
REMOVE TABLESPACE <tablespace_name> |
SELECT *
FROM flashback_archive_tablespaces;
ALTER FLASHBACK ARCHIVE
uw_archive REMOVE TABLESPACE examples;
SELECT *
FROM flashback_archive_tablespaces; |
| Change the archive's
retention policy |
ALTER FLASHBACK ARCHIVE <archive_name>
MODIFY
RETENTION <retention_value> <YEAR | MONTH | DAY>; |
SELECT *
FROM flashback_archives;
ALTER FLASHBACK ARCHIVE
uw_archive MODIFY RETENTION 1 MONTH;
SELECT *
FROM flashback_archives; |
| 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 flashback_archive_tablespaces;
SELECT COUNT(*)
FROM flashback_archives;
DROP FLASHBACK ARCHIVE uw_archive;
SELECT COUNT(*)
FROM flashback_archive_tablespaces;
SELECT COUNT(*)
FROM flashback_archives; |