DROP USER / Flashback Data Archive

Troubleshooting

SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES;

COLUMN owner FORMAT a30

COLUMN object_name FORMAT a30

SELECT object_type, owner, object_name, object_id

FROM dba_objects

WHERE object_name LIKE '%127394%';

SET LINESIZE 120

COLUMN owner FORMAT a10

COLUMN segment_name FORMAT a30

COLUMN partition_name FORMAT a30

COLUMN tablespace_name FORMAT a20

SELECT segment_type, owner, segment_name, partition_name, tablespace_name

FROM dba_segments

WHERE segment_name LIKE '%127394%';

SELECT COUNT(*) FROM MYSCHEMA.SYS_FBA_DDL_COLMAP_127394;

SELECT COUNT(*) FROM MYSCHEMA.SYS_FBA_HIST_127394;

SELECT COUNT(*) FROM MYSCHEMA.SYS_FBA_TCRV_127394;

SELECT COUNT(*) FROM sys.SYS_MFBA_NHIST_127394;

SELECT segment_type, owner, segment_name, partition_name, tablespace_name

FROM dba_segments

WHERE tablespace_name = 'FB_ARCHIVE'

SELECT * FROM dba_flashback_archive;

COLUMN owner_name FORMAT a10

COLUMN flashback_archive_name FORMAT a30

SELECT owner_name, flashback_archive_name FROM dba_flashback_archive;

DROP FLASHBACK ARCHIVE myfba;

DROP USER myschema CASCADE;

SELECT o.object_id, o. owner, o.object_name, t.property

FROM dba_objects_ae o, tab$ t

WHERE o.object_type = 'TABLE'

AND o.object_id = t.obj#

AND t.property = 9126805504

AND o.object_id IN ('898036','898040','898038','898037','898039','898041','898042');

UPDATE tab$

SET property = 536870912

WHERE property = 9126805504

AND obj# IN ('898036','898040','898038','898037','898039','898041','898042');

COMMIT;

DROP TABLE MYSCHEMA.SYS_FBA_TCRV_127394;

DROP USER myschema CASCADE;

shutdown immediate

startup

DROP USER myschema CASCADE;

Questions:

1. Would bouncing the instance earlier in the process still have resolved the issue?

2. What caused the spurious FBA objects to hang around even after the Archive was dropped?

3. Has the direct update on TAB$ caused/left any other issues?

Bibliography & References