Monday, January 31, 2011

Segment Creation Behavior Change Between 11.2.0.1 and 11.2.0.2 When Using EXPDP/IMPDP

In 11.2 the initialization parameter DEFERRED_SEGMENT_CREATION controls whether segment space is allocated at the time of the segment creation. This is set to true by default both on enterprise edition and standard edition. But on standard edition this parameter has no effect as this feature is not enabled. Initialization parameter behavior could be override at table with
SQL> CREATE TABLE x(a number) SEGMENT CREATION IMMEDIATE; 
or
SQL> CREATE TABLE x(a number) SEGMENT CREATION DEFERRED;
But on standard edition this would still give an error.
SQL>  CREATE TABLE x(a number) SEGMENT CREATION DEFERRED;
CREATE TABLE x(a number) SEGMENT CREATION DEFERRED
*
ERROR at line 1:
ORA-00439: feature not enabled: Deferred Segment Creation
Problem happens when exporting some empty tables from an enterprise edition database to a standard edition database. According to the default behavior on enterprise edition when tables have no rows there won't be any segments. But default behavior on standard edition is that, tables are always created with segments.

In 11.2.0.1 when importing tables from enterprise to standard edition tables with no segments throws an error while in 11.2.0.2 tables get created with segments without an error.

Exporting from 11.2.0.1 Enterprise Edition to 11.2.0.1 Standard Edition

1. Create a test user on both databases (enterprise and standard editions)
SQL> create user test identified by test default tablespace users temporary tablespace temp quota unlimited on users;
2. Connect to the enterprise edition database and create two tables. Populate one with data and leave the other empty.
SQL> create table x (a number);
Table created.

SQL> create table y (a number);
Table created.

SQL> select table_name,segment_created from user_tables;

TABLE_NAME                     SEG
------------------------------ ---
X                              NO
Y                              NO

SQL> begin for i in 1 .. 100 loop insert into x values (i); end loop; end;
2  /
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

SQL> select table_name,segment_created from user_tables;

TABLE_NAME                     SEG
------------------------------ ---
X                              YES
Y                              NO
As expected tables get created without any segment space allocated but after inserting some rows table get some space allocated.

4. Export these two tables using expdp
expdp test/test directory=dumpdir dumpfile=ent.dmp schemas=test flashback_time=\"$(date +%F' '%T)\"

Export: Release 11.2.0.1.0 - Production on Mon Jan 31 18:29:07 2011

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 "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** directory=dumpdir dumpfile=ent.dmp schemas=test flashback_time="2011-01-31 18:29:07"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "TEST"."Y"                                      0 KB       0 rows
. . exported "TEST"."X"                                  5.679 KB     100 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dumpdir/ent.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:31:26
5. Import the same tables to standard edition
impdp test/test directory=dumpdir dumpfile=ent.dmp full=y

Import: Release 11.2.0.1.0 - Production on Mon Jan 31 18:32:18 2011

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 "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** directory=dumpdir dumpfile=ent.dmp full=y
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"TEST"."Y" failed to create with error:
ORA-00439: feature not enabled: Deferred Segment Creation
Failing sql is:
CREATE TABLE "TEST"."Y" ("A" NUMBER) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS"

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."X"                                  5.679 KB     100 rows
Job "TEST"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 18:33:24
The import tries to create the files usind the same ddl as in the enterprise without suppressing it and gets and error.

Exporting from 11.2.0.2 Enterprise Edition to 11.2.0.2 Standard Edition

The erroneous behavior is changed on 11.2.0.2. Assuming the tables were created as above export from enterprise edition.
expdp test/test directory=dumpdir dumpfile=ent.dmp schemas=test flashback_time=\"$(date +%F' '%T)\"

Export: Release 11.2.0.2.0 - Production on Mon Jan 31 13:29:46 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** directory=dumpdir dumpfile=ent.dmp schemas=test flashback_time="2011-01-31 13:29:46"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "TEST"."X"                                  5.679 KB     100 rows
. . exported "TEST"."Y"                                      0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/ssdoracle/dpdumps/ent.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:30:08
Import into the standared edition
impdp test/test directory=dumpdir dumpfile=ent.dmp full=y

Import: Release 11.2.0.2.0 - Production on Mon Jan 31 13:32:37 2011

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

Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** directory=dumpdir dumpfile=ent.dmp full=y
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."X"                                  5.679 KB     100 rows
. . imported "TEST"."Y"                                      0 KB       0 rows
Job "TEST"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:32:39
There's no error and import goes through and all the tables get created with segment space.
SQL> select table_name,segment_created from user_tables;

TABLE_NAME                     SEG
------------------------------ ---
X                              YES
Y                              YES
For 11.2.0.1 one way to avoid the issue is to allocate segment manually before the export. This could be done with
alter table  allocate extent;
Metalink notes for similar issues

1. Init.ora Parameter "DEFERRED_SEGMENT_CREATION" Reference Note [ID 1216282.1]
2. IMP-00003, ORA-00959 While Importing Data Into Existing Table Of 11gR2 Using Traditional Import [ID 1180873.1]
3. Bug 8795792 - DBMS_METADATA.get_ddl generates wrong keywords for compressed indexes in 11.2 [ID 8795792.8]
4. Exporting Schema In 11.2 Database With 10g Client Gives ORA-1455 [ID 1083330.1]