Oracle export and deferred segment creation

A colleague told me about an issue when using export on an Oracle 11.2.0.1 database. He said that if the database contains tables created with segment creation deferred, those tables will not be exported. I decided to check this out. First I created a couple of tables in an Oracle 11.2.0.1 database, one with segment creation deferred and one with segment creation immediate: 

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

 

User created.

 

SQL> grant

  2  create session,

  3  create table to andrew

  4  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> create table scd (col1 number)

  2  segment creation deferred

  3  /

 

Table created.

 

SQL> create table sci (col1 number)

  2  segment creation immediate

  3  /

 

Table created.

 

SQL>

 

Then I exported the Andrew schema. Here is the log file produced. As you can see, only the table with segment creation immediate was exported:

 

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 done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P15 character set (possible charset conversion)

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user ANDREW

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user ANDREW

About to export ANDREW's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export ANDREW's tables via Conventional Path ...

. . exporting table                            SCI          0 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.

 

I had a look at the page about export in Oracle’s 11.2 documentation on the Internet and saw the following:

 

The original Export utility is no longer being updated to support new datatypes.

 

So I repeated the process in an Oracle 11.2.0.2.7 database. First I created the tables as before:

 

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

 

User created.

 

SQL> grant

  2  create session,

  3  create table to andrew

  4  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> create table scd (col1 number)

  2  segment creation deferred

  3  /

 

Table created.

 

SQL> create table sci (col1 number)

  2  segment creation immediate

  3  /

 

Table created.

 

SQL>

 

Then I did the export and, to my surprise, both tables were copied. You can see what I mean in the log below:

 

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

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user ANDREW

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user ANDREW

About to export ANDREW's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export ANDREW's tables via Conventional Path ...

. . exporting table                            SCD          0 rows exported

. . exporting table                            SCI          0 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.