version Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
I have set up flashback data archive on one table as follows :
( connected user has flashback archive administer privilege but not flashback archive privilege).
CREATE FLASHBACK ARCHIVE compliance_archive_1
TABLESPACE recall
QUOTA 10 M
RETENTION 3 MONTH;
Flashback ARCHIVE created.
alter table compliance.checksheet flashback archive compliance_archive_1;
Table COMPLIANCE.CHECKSHEET altered.
select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME STATUS
-------------------- -------------------- -------------------- ----------------------------------------------------- -------------
CHECKSHEET COMPLIANCE COMPLIANCE_ARCHIVE_1 SYS_FBA_HIST_359264 ENABLED
But I can't find the actual table
select * from dba_objects where object_name = 'SYS_FBA_HIST_359264';
no rows selected
select * from compliance.SYS_FBA_HIST_359264;
Error starting at line : 26 in command -
select * from compliance.SYS_FBA_HIST_359264
Error at Command Line : 26 Column : 26
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
select * from dba_segments where owner = 'COMPLIANCE';
SEGMENT_NAME
--------------------------------------------------------------------------------------------------------------------------------
CHECKSHEET
CHECKSHEET_KEYS
CHECKSHEET_RESULTS
CHK_KEYS_UK1
CHK_RES_PK
CHK_RES_UK1
6 rows selected
I made some changes (deletes and updates) to the compliance.checksheet table.
Queries work as expected but still using UNDO or SCN?
select count(*) from compliance.checksheet ;
COUNT(*)
----------
12
select count(*) from compliance.checksheet as of timestamp trunc(sysdate)-0.1;
COUNT(*)
----------
11
select count(*) from compliance.checksheet as of timestamp trunc(sysdate-1);
RA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3_1488666216$" too small
select count(*) from compliance.checksheet as of timestamp trunc(sysdate)-0.1;
COUNT(*)
----------
11
select count(*) from compliance.checksheetas of timestamp trunc(sysdate)-10;
ORA-08180: no snapshot found based on specified time
08180. 00000 - "no snapshot found based on specified time"
*Cause: Could not match the time to an SCN from the mapping table.
*Action: try using a larger time.
ORA-08180: no snapshot found based on specified time
08180. 00000 - "no snapshot found based on specified time"
*Cause: Could not match the time to an SCN from the mapping table.
*Action: try using a larger time.
Questions :
1. Where is the FBA table?
2. How can I tell that it is being used?
3. Do I need to grant privileges for the user querying the data in order for them to use Flashback data archive?
Thanks
Kathryn