Thursday, November 12, 2009

ORA-02304: invalid object identifier literal during Data Pump Import

Problem:
DPImport into production:

ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "XXX1"."resource352_T" OID '6C5F1FBB7BE11FA7E0440003BA2D252E' AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T","resourcename"
VARCHAR2(4000 CHAR),"resourceindex" VARCHAR2(4000 CHAR),"resourceclass" VARCHAR2(4000 CHAR),"resourceclassname" VARCHAR2(4000 CHAR),"string
" "string354_COLL","attribute" "attribute356_COLL")FINAL INSTANTIABLE

Description:
ORA-02304: invalid object identifier literal
Cause: An attempt was made to enter an object identifier literal for CREATE TYPE that is either:
- not a string of 32 hexadecimal characters

- an object identifier that already identifies an existing object

- an object identifier different from the original object identifier already assigned to the type

Solution:

use transform=oid:n

Tuesday, November 10, 2009

enq: TX - contention

Problem:

ALTER TABLESPACE XXX READ ONLY;

Session moves into a state of waiting with Wait Event "enq: TX - contention".

Description:
This is possible if parallel INSERTs are running and one of these INSERTs triggered an AUTOEXTEND operation and must wait for "Data file init write/read" (Note 619188). In this case, the other INSERTs wait for "enq: TX - contention".

In my particular situation there was an INSERT in parallel. Blocking session was in "db file sequential read" state and was reading system datafile.

Solution:
wait until session dies itself or kill it brutally.