Oracle Flashback Archive offers simple archiving with simple administration.
During some testing on Oracle 11.2 with Flashback Archive I exprerianced some heavy problems.
The day began with a simple testcase:
create user tkrim identified by tkrim1;
grant connect to tkrim;
grant resource to tkrim;
grant FLASHBACK ARCHIVE administer to tkrim;
connect tkrim/tkrim1
col scn for 999999999999999999999999
create table demo (id number, val varchar(200) ) ;
ALTER TABLE demo FLASHBACK ARCHIVE fla1;
insert into demo values (1,'test1');
insert into demo values (2,'test2');
insert into demo values (3,'test3');
commit;
select dbms_flashback.get_system_change_number scn from dual;
update demo set val='abc1' where id=1;
update demo set val='abc2' where id=2;
update demo set val='abc3' where id=3;
commit;
select dbms_flashback.get_system_change_number scn from dual;
delete from demo where id=3;
commit;
select dbms_flashback.get_system_change_number scn from dual;
select * from demo;
select * from demo as of scn 55710824751 ;
After a my UNDO tablespace was full I played a bit with undo_retention and changing the undo tablespace which case following error.
ORA-01555 caused by SQL statement below (SQL ID: 1p36ta7p3d7fu, Query Duration=0 sec, SCN: 0x000c.f7ca7c8f):
select file#, block#, ts#, blocks from seg$ where type# = 3
ORA-01555 caused by SQL statement below (SQL ID: 1p36ta7p3d7fu, Query Duration=1 sec, SCN: 0x000c.f7ca7d8f):
select file#, block#, ts#, blocks from seg$ where type# = 3
ORA-01555 caused by SQL statement below (SQL ID: 1p36ta7p3d7fu, Query Duration=0 sec, SCN: 0x000c.f7ca7ee1):
select file#, block#, ts#, blocks from seg$ where type# = 3
Somehow not really bulletprof, this oracle falshback archive.
Anyway the real trouble started when i tried to drop the user and the flashback archive.
ALTER TABLE demo NO FLASHBACK ARCHIVE;
Worked, but leafes some tables named like TKRIM.SYS_FBA_HIST_* and SYS_FBA_TCRV_*.
At this stage it’s impossible to drop the Flashback archive tablepspace or the user tkrim aswell this tables.
After doing some hacks
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;
UPDATE tab$
SET property = 536870912
WHERE property = 9126805504;
COMMIT;
I was able to drop the table SYS_FBA_TCRV_*.
But it was still impossible to drop the HIST Table.
SQL > drop table TKRIM.SYS_FBA_HIST_15517640;
drop table TKRIM.SYS_FBA_HIST_15517640
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [],
[], [], [], [], [], []
At the end I was not able to cleanup the flashback archive or the user.