Monday, November 30, 2009

Cannot export empty tables in 11.2

In 11g there is no segment allocated to the table until you insert, so the EXP utility fails to export empty tables.

[oracle@dev-oranode-221 ~]$ sqlplus a/a

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 14:38:06 2009

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, Oracle Database Vault
and Real Application Testing options
SQL> create table imp_test(id integer primary key);

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, Oracle Database Vault
and Real Application Testing options
[oracle@dev-oranode-221 ~]$ exp a/a

Export: Release 11.2.0.1.0 - Production on Mon Nov 30 14:37:17 2009

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, Oracle Database Vault
and Real Application Testing options
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp >

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > imp_test

EXP-00011: A.IMP_TEST does not exist
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully with warnings.
[oracle@dev-oranode-221 ~]$ sqlplus a/a

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 14:38:06 2009

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, Oracle Database Vault
and Real Application Testing options

SQL> insert into imp_test values(1);

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, Oracle Database Vault
and Real Application Testing options
[oracle@dev-oranode-221 ~]$ exp a/a

Export: Release 11.2.0.1.0 - Production on Mon Nov 30 14:38:32 2009

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, Oracle Database Vault
and Real Application Testing options
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp >

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > imp_test

. . exporting table IMP_TEST 1 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >