Ask The Real Tom

June 22, 2010



ORA-31623: a job is not attached to this session via the specified handle

Filed under: oracle — Tags: , , , , , , — tkalo @ 11:23 am

We recently faced a strange error when trying to import a schema through a database link on 11gR2.

shell> impdp system/*** SCHEMAS=LFXQAA02 directory=DATA_PUMP_DIR LOGFILE=liflexdp1.log NETWORK_LINK=tliflex01.world PARALLEL=2

Import: Release 11.2.0.1.0 – Production on Tue Jun 22 11:07:58 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning and Automatic Storage Management options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3263
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4488
ORA-06512: at line 1

After serval approaches we found out that streams_pool_size=0 was set.

alter system set streams_pool_size=40M scope=spfile;

restart DB and run import again..

There is also a Metalink note on this topic: 1080775.1

January 5, 2010



Oracle Import / Export Tables with flashback archive enabled

Filed under: Uncategorized — Tags: , , , , , , — admin @ 9:43 am

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;

ID VAL
---------- ----------
1 abc1
2 abc2
30 test30
31 test31
--> wrong!!!! (flashback archive not enabled)

connect tkrim/tkrim1
ALTER TABLE demo FLASHBACK ARCHIVE fla1;

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

Powered by WordPress