Thursday, May 09, 2013

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.

If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk.



No comments: