Oracle 11g R2: Deferred Segment Creation + imp + table with LOB column = Bug

Posted on May 7, 2010 by Ronny Egner

This is a short posting about an error i found today.

This errors prevents the import of tables with lob columns when using traditional import, deferred segment creation and an altered tablespace name.

For this i created a test case in which we

  1. In the source database (10.2.0.4.0 on 64-bit Linux):
    1. create a user named TEST1 having a default tablespace named TEST1
    2. create a small table with a CLOB in it
    3. export the schema with traditional import
    4. transfer the dump to the destination database
  2. In the destination database (11.2.0.1.0 on 64-bit Linux)
    1. create a user names TESTNEU with a default tablespace TESTNEU
      NOTE: There is no tablespace TEST1 in the destination database; this is important
    2. trying to import the table… hitting a bug documented below in detail

Note: While blogging this for a fried i was in hurry; i will rewrite some paragraphs with evening… but the problem should become clear while reading…

In the source database (10.2.0.4.0)

Create user and tablespace

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 7 15:25:27 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace test1 datafile '/u01/oradata/ORA10P/test1.dbf' size 100M;
Tablespace created.

SQL> create user test1 identified by test1 default tablespace test1 quota unlimited on test1;
User created.

SQL> grant create session to test1;
Grant succeeded.

SQL> grant create table to test1;
Grant succeeded.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Create a table with a clob column and insert one row

-bash-3.2$ sqlplus test1/test1
SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 7 15:17:51 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table testtab (col1 varchar2(100), col2 clob);
Table created.

SQL> insert into testtab values ('x','clob text');
1 row created.

SQL> commit;
Commit complete.

SQL> exit

Export the schema for migration

-bash-3.2$ exp system/manager file=test1.dmp log=test1.log owner=test1 consistent=y buffer=100000

Export: Release 10.2.0.4.0 - Production on Fri May 7 15:18:34 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST1
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST1
About to export TEST1's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST1's tables via Conventional Path ...
. . exporting table                        TESTTAB          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Migrate to 11g R2

Create tablespace and user

NOTE: Instead of creating the same user and same tablespace we ALTER username and tablespace name. If you do not alter username and tablespace name you will not hit this bug.

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 7 15:20:50 2010

Copyright (c) 1982, 2009, Oracle.  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

SQL> create tablespace testneu datafile '/u01/oradata/ORA11R2P/testneu.dbf' size 100M;

Tablespace created.

SQL>create user testneu identified by testneu default tablespace testneu quota unlimited on testneu;
User created.

SQL>grant create session to testneu;
Grant succeeded.

SQL>grant create table to testneu;
Grant succeeded.

SQL> SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Import the data

bash-3.2$ imp system/manager file=test1.dmp fromuser=test1 touser=testneu

Import: Release 11.2.0.1.0 - Production on Fri May 7 15:27:44 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, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing TEST1's objects into TESTNEU
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "TESTTAB" ("COL1" VARCHAR2(100), "COL2" CLOB)  PCTFREE 10 PCTU"
"SED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST G"
"ROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST1" LOGGING NOCOMPRESS LOB ("CO"
"L2") STORE AS  (TABLESPACE "TEST1" ENABLE STORAGE IN ROW CHUNK 8192 RETENTI"
"ON NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUF"
"FER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TEST1' does not exist
Import terminated successfully with warnings.

Well, this error is in import/export utilities since 9i. When importing tables containing lob columns into a database with different tablespace names import still tries to create the table in the same tablespace as in the source database. A workaround for this is to create the table prior doing the import and appending “ignore=y” to the import command. So lets do it:

Importing the data by pre-creating the table and appending “ignore=y” to import command

-bash-3.2$ sqlplus testneu/testneu

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 7 15:28:07 2010

Copyright (c) 1982, 2009, Oracle.  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

SQL> desc testtab;
ERROR:
ORA-04043: object testtab does not exist

SQL> create table testtab (col1 varchar2(100), col2 clob);

Table created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ssssssssssssssssss

-bash-3.2$ imp system/manager file=test1.dmp fromuser=test1 touser=testneu ignore=y

Import: Release 11.2.0.1.0 - Production on Fri May 7 15:28:41 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, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing TEST1's objects into TESTNEU
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "TESTTAB" ("COL1" VARCHAR2(100), "COL2" CLOB)  PCTFREE 10 PCTU"
"SED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST G"
"ROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST1" LOGGING NOCOMPRESS LOB ("CO"
"L2") STORE AS  (TABLESPACE "TEST1" ENABLE STORAGE IN ROW CHUNK 8192 RETENTI"
"ON NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUF"
"FER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TEST1' does not exist
Import terminated successfully with warnings.

Importing the data by pre-creating the table and appending “ignore=y” to import command and inserting at least ONE row prior the import

-bash-3.2$ sqlplus testneu/testneu

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 7 15:29:53 2010

Copyright (c) 1982, 2009, Oracle.  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

SQL> select * from testtab;

no rows selected

SQL> insert into testtab values ('x','xx');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-bash-3.2$ imp system/manager file=test1.dmp fromuser=test1 touser=testneu ignore=y

Import: Release 11.2.0.1.0 - Production on Fri May 7 15:30:19 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, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing TEST1's objects into TESTNEU
. . importing table                      "TESTTAB"          1 rows imported
Import terminated successfully without warnings.

CAUSE: DEFERRED SEGMENT CREATION

So lets turn if off….

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 7 15:30:43 2010

Copyright (c) 1982, 2009, Oracle.  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

SQL> drop user test1 cascade;
drop user test1 cascade
*
ERROR at line 1:
ORA-01918: user 'TEST1' does not exist

SQL> drop user testneu cascade;

User dropped.

SQL> show parameter deferred;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> alter system set deferred_segment_creation=false;

System altered.

SQL> create user testneu identified by testneu default tablespace testneu quota unlimited on testneu;
grant create session to testneu;
grant create table to testneu;

User created.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

And do the import once again (without pre-creating the table)

-bash-3.2$ imp system/manager file=test1.dmp fromuser=test1 touser=testneu

Import: Release 11.2.0.1.0 - Production on Fri May 7 15:31:41 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, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing TEST1's objects into TESTNEU
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "TESTTAB" ("COL1" VARCHAR2(100), "COL2" CLOB)  PCTFREE 10 PCTU"
"SED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST G"
"ROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST1" LOGGING NOCOMPRESS LOB ("CO"
"L2") STORE AS  (TABLESPACE "TEST1" ENABLE STORAGE IN ROW CHUNK 8192 RETENTI"
"ON NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUF"
"FER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TEST1' does not exist
Import terminated successfully with warnings.

–> not working,ok

And do the import once again (WITH pre-creating the table)

-bash-3.2$ sqlplus testneu/testneu
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 7 15:32:05 2010
Copyright (c) 1982, 2009, Oracle.  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
SQL> desc testtab;
ERROR:
ORA-04043: object testtab does not exist
SQL> create table testtab (col1 varchar2(100), col2 clob);
Table created.
SQL> exit
-bash-3.2$ imp system/manager file=test1.dmp fromuser=test1 touser=testneu ignore=y
Import: Release 11.2.0.1.0 - Production on Fri May 7 15:32:44 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, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing TEST1's objects into TESTNEU
. . importing table                      "TESTTAB"          1 rows imported
Import terminated successfully without warnings.
This entry was posted in Oracle in general. Bookmark the permalink.

2 Responses to Oracle 11g R2: Deferred Segment Creation + imp + table with LOB column = Bug

  1. foxmask says:
    June 8, 2011 at 12:03

    Hi,
    I’m facing this issue but as I’m getting a customer dump I cant create a record “for nothing” before importing.
    So How can I do my import in that case ?
    regards

    Reply
  2. LK says:
    November 4, 2011 at 09:21

    Great to share this

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *