Skip to Main Content

Info

The value to use for NLS_LANG in the examples can be queried like this:

EXP/IMP examples

Note: "When tables are manually created [or already exist] before data is imported, the CREATE TABLE statement in the export dump file will fail because the table already exists. To avoid this failure and continue loading data into the table, set the Import parameter IGNORE=y. Otherwise, no data will be loaded into the table because of the table creation error."

EXPDP/IMPDP examples

More info: http://www.oracle-base.com/articles/10g/OracleDataPump10g.php#SchemaExpImp

EXP: ORA-04031: unable to allocate x bytes of shared memory (shared pool)

When using EXPDP this error occurs:
UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 16 bytes of shared memory ("shared pool","SELECT job_id FROM v$datapum...","SQLA","tmp")
ORA-06512: at "SYS.KUPV$FT_INT", line 2904
ORA-06512: at "SYS.KUPC$QUE_INT", line 572
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1

Cause of the Error

The ORA-04031 error returns whenever oracle attempts to allocate a large piece of contiguous memory in the shared pool but fails to allocate. Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request the ORA-04031 error is returned.

Since in 10g automatic memory management in enabled and this allows the dba to reserve a pool of shared memory that is used to allocate the shared pool, the buffer cache, the java pool and the large pool with the single parameter SGA_TARGET. So simply increase of SGA_TARGET likely solve the problem.

Solution

Let's see the sga_target value.
SQL> show parameter sga_t
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 100M

And SGA_MAX_SIZE value,
SQL> show parameter sga_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 100M

We see these two parameter settings are low. So we increase it and restart database. As setting of SGA_TARGET is depend on SGA_MAX_SIZE and to change SGA_MAX_SIZE we need to bounce the database in order to effect.
SQL> alter system set sga_max_size=300M scope=spfile;

System altered.

SQL> alter system set sga_target=300M scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1248768 bytes
Variable Size 96469504 bytes
Database Buffers 209715200 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 300M

Now the export operation runs smoothly as shared pool finds enough memory to do the operation.
SQL> $expdp full=y dumpfile=fulldb.dmp directory=d userid=system/a

Source: http://arjudba.blogspot.com/2008/12/expdp-fails-with-ora-39125-ora-04031.html

EXP: PLS-00302: component 'SET_NO_OUTLINES' must be declared

When using EXP this error occurs:
ORA-06550: line 1, column 41:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully
Cause: The version of EXP used is newer than the database. E.g. EXP version 10gR2 with a 10gR1 database.
Solution: Use the correct EXP version.

EXP: ORA-00439: feature not enabled: Deferred Segment Creation

When using EXP this error occurs:
ORA-39083: Object type TABLE:"ESB_OWNER"."ESB_CONTROLS_JN" failed to create with error:
ORA-00439: feature not enabled: Deferred Segment Creation
Failing sql is:
CREATE TABLE "ESB_OWNER"."ESB_CONTROLS_JN" ("CONTINUE_LISTENING_YN" VARCHAR2(1 BYTE) NOT NULL ENABLE, "DATE_UPDATED" DATE NOT NULL ENABLE, "APP_USER" VARCHAR2(100 BYTE), "PK_ID" NUMBER NOT NULL ENABLE) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BU

Cause: "after exporting with EXPDP a schema from an Oracle Enterprise Edition 11gR2 database and importing it on an Oracle Standard Editon 11gR2 you are getting errors…"

Solution:
-- on the source instance
EXPDP user/pwd dumpfile=somename.dmp directory=DATA_DUMP_DIR nologfile=Y version=10.2
-- on the target instance
IMPDP user/pwd dumpfile=somename.dmp directory=DATA_DUMP_DIR nologfile=Y version=10.2

If there are any 11g features that need to be preserved across the import, then you will need to find an alternate solution, like artificially populating the empty tables that seem to trigger this problem or re-create manually the failing objects, until Oracle comes up with some command line switch for EXPDP or IMPDP to turn off or ignore the deferred segments option.

Source: http://oraclequirks.blogspot.com/2011/02/ora-00439-feature-not-enabled-deferred.html

UDI-31626: operation generated ORACLE error 31626 (ORA-31626, ORA-39086)

When using IMPDP on XE11g this error occurs:
UDI-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1
In the import log file:
ORA-39097: Data Pump job encountered unexpected error -30094
ORA-39065: unexpected master process exception in DISPATCH
ORA-30094: failed to find the time zone data file for version 11 in $ORACLE_HOME/oracore/zoneinfo
Cause: Missing timezone files on XE11g: "Check the timezone file setting in your XE target, it should contain timezone files up through v14, an unpatched 11.2.0.1 install has timezone files through v11."
Solutuion: Copyy all .dat timezone files from the origina 11gR2 server to the XE11g server (this only works if both platforms as compatible of course).
cd $ORACLE_HOME/oracore/zoneinfo
ls -l *.dat
Source: https://forums.oracle.com/forums/thread.jspa?threadID=2303497&tstart=0

ORA-39070: Unable to open the log file (ORA-39002, ORA-39070, ORA-29283)

When using EXPDP (in this example when running script /backup/backup.sh) this error occurs:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
Solution: Change permissions of directory /backup to 1777 (i.e. turn on sticky bit).