ORA-39083 and ORA-00439

I created a table with deferred segment creation in an Oracle 11.2.0.1.0 Enterprise Edition database:

 

SQL> create table andrew.tab1 (col1 number)

  2  segment creation deferred;

 

Table created.

 

SQL>

 

Then I created the following Data Pump parameter file:

 

C:\Users\AJ0294094>more datapump1.params

content=all

directory=data_pump_dir

dumpfile=andrew.dmp

logfile=andrew.log1

tables=andrew.tab1

 

C:\Users\AJ0294094>

 

… and used Data Pump to export the table I had just created:

 

C:\Users\AJ0294094>expdp system/secret_password1 parfile=datapump1.params

 

Export: Release 11.2.0.1.0 - Production on Fri Aug 30 18:07:35 2013

 

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, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** parfile=datapump1.params

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "ANDREW"."TAB1"                                 0 KB       0 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

  D:\ORACLE\11.2.0\ADMIN\MDMDEV1\DPDUMP\ANDREW.DMP

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 18:07:40

 

C:\Users\AJ0294094>

 

I copied the dump file (andrew.dmp) from the source database’s Data Pump directory to the target database’s Data Pump directory. I assume you know how to do this yourself. Then I pointed my ORACLE_SID to the target database, which was set up using Oracle 11.2.0.1.0 Standard Edition, and created a new file of Data Pump parameters as follows:

 

C:\Users\AJ0294094>more datapump2.params

content=all

directory=data_pump_dir

dumpfile=andrew.dmp

logfile=andrew.log2

 

C:\Users\AJ0294094>

 

When I tried to import the table, the job failed as Standard Edition does not support deferred segment creation:

 

C:\Users\AJ0294094>impdp system/secret_password2 parfile=datapump2.params

 

Import: Release 11.2.0.1.0 - Production on Fri Aug 30 18:12:08 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** parfile=datapump2.params

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"ANDREW"."TAB1" failed to create with error:

ORA-00439: feature not enabled: Deferred Segment Creation

Failing sql is:

CREATE TABLE "ANDREW"."TAB1" ("COL1" NUMBER) SEGMENT CREATION DEFERRED PCTFREE 1

0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS"

 

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 18:12:11

 

 

C:\Users\AJ0294094>

 

I created a third Data Pump parameter file as shown below using the sqlfile parameter:

 

C:\Users\AJ0294094>more datapump3.params

content=metadata_only

directory=data_pump_dir

dumpfile=andrew.dmp

include=table

logfile=andrew.log3

sqlfile=andrew.sql

 

C:\Users\AJ0294094>

 

Then I ran the Data Pump import again:

 

C:\Users\AJ0294094>impdp system/secret_password2 parfile=datapump3.params

 

Import: Release 11.2.0.1.0 - Production on Fri Aug 30 18:13:41 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** parfile=datapump3.params

Processing object type TABLE_EXPORT/TABLE/TABLE

Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 18:13:43

 

C:\Users\AJ0294094>

 

This created a file of SQL in the Data Pump directory to precreate the table. Its contents were as follows. Note that the table is to be precreated with segment creation deferred:

 

-- CONNECT SYSTEM

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: TABLE_EXPORT/TABLE/TABLE

CREATE TABLE "ANDREW"."TAB1"

   (  "COL1" NUMBER

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  TABLESPACE "USERS" ;

 

I amended the SQL to create the table with segment creation immediate and then I was able to run it. If you have this problem, and your file contains several create table statements, you should make this change using a global replacement command:

 

C:\Users\AJ0294094>sqlplus system/secret_password2

 

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 30 18:19:09 2013

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

 

SQL> -- CONNECT SYSTEM

SQL> ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

 

Session altered.

 

SQL> ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

 

Session altered.

 

SQL> ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

 

Session altered.

 

SQL> ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

 

Session altered.

 

SQL> ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

 

Session altered.

 

SQL> ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

 

Session altered.

 

SQL> -- new object type path: TABLE_EXPORT/TABLE/TABLE

SQL> CREATE TABLE "ANDREW"."TAB1"

  2     (       "COL1" NUMBER

  3     ) SEGMENT CREATION IMMEDIATE

  4    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  5    TABLESPACE "USERS" ;

 

Table created.

 

SQL>

 

Finally, I created another parameter file and imported the data into the table. Of course in this case it was not necessary as there was only one table and it was empty. However, I am showing this step because if you have to do this, your dump file may well contain a mix of tables, some with data and some without.

 

C:\Users\AJ0294094>more datapump4.params

content=data_only

directory=data_pump_dir

dumpfile=andrew.dmp

 

C:\Users\AJ0294094>impdp system/secret_password2 parfile=datapump4.params

 

Import: Release 11.2.0.1.0 - Production on Fri Aug 30 18:32:14 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** parfile=datapump4.params

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "ANDREW"."TAB1"                                 0 KB       0 rows

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 18:32:18

 

C:\Users\AJ0294094>

ORA-12899

If you try to update a column with a value which is too big, you get an ORA-12899. If this happens in a PL/SQL block, all the updates done in that block will be rolled back, not just the one which failed. You can see what I mean in the example below, which I ran on an Oracle 11.2 database:

SQL> create table tab1 (first_name varchar2(10))

  2  /

Table created. 

SQL> insert into tab1 values ('Brian')

  2  /

1 row created.

SQL> insert into tab1 values ('Colin')

  2  /

1 row created.

SQL> begin

  2   update tab1

  3    set first_name = 'Boris'

  4     where first_name = 'Brian';

  5   update tab1

  6    set first_name = 'Christopher'

  7     where first_name = 'Colin';

  8  end;

  9  /

begin

*

ERROR at line 1:

ORA-12899: value too large for column

"ANDREW"."TAB1"."FIRST_NAME" (actual: 11, maximum: 10)

ORA-06512: at line 5

SQL> select * from tab1

  2  /

FIRST_NAME

----------

Brian

Colin

SQL>