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 CreationProblem 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 NOAs 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:265. 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:24The 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:08Import 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:39There'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 YESFor 11.2.0.1 one way to avoid the issue is to allocate segment manually before the export. This could be done with
alter tableMetalink notes for similar issuesallocate extent;
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]
