Ask The Real Tom

I created a simple schema with one table and flashback archive.

exp test

exp file=test.dmp owner=TKRIM

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TKRIM
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TKRIM
About to export TKRIM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TKRIM's tables via Conventional Path ...
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

exp seem not be working with orcle flashback archive.

An export with datapump expdp works. But it does not export the history.

expdp schemas=TKRIM dumpfile=test.dp

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=TKRIM dumpfile=test.dp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TKRIM"."DEMO":"E1" 5.421 KB 2 rows
. . exported "TKRIM"."DEMO":"E2" 5.429 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u00/app/oracle/admin/DIGATE20/dpdump/test.dp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:19:26


the import look more difficutl already

impdp schemas=TKRIM dumpfile=test.dp TABLE_EXISTS_ACTION=replace

..
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39121: Table "TKRIM"."DEMO" can't be replaced, data will be skipped. Failing error is:
ORA-55610: Invalid DDL statement on history-tracked table
ORA-00955: name is already used by an existing object
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at 09:22:03


disabling flashback archive on the table does the trick.

ALTER TABLE tkrim.demo NO FLASHBACK ARCHIVE;
drop table tkrim.demo;

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TKRIM"."DEMO":"E1" 5.421 KB 2 rows
. . imported "TKRIM"."DEMO":"E2" 5.429 KB 2 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 09:24:05

but the table stays without flashback archiving enable. a query to the flashback archive still works.


select * from tkrim.demo as of scn 827813;

connect tkrim/tkrim1
ALTER TABLE demo FLASHBACK ARCHIVE fla1;


It can be enabled again - but this behavior leaves room to do easy mistakes.