Oracle 11g2 does NOT export empty tables when using exp.exe

http://tsells.wordpress.com/2011/02/10/oracle-11g2-does-not-export-empty-tables-when-using-exp-exe/

Out of habit I have been using the older data import / export functions in lieu of using the Data Pump commands that Oracle recommends.  As of 11g2 I can no longer do this.  The exp.exe no longer exports tables that do not have an entry in DBA_SEGMENTS.  This is due to a new feature named “deferred segment creation”.

The syntax for the new Data Pump commands are different and require some additional setup / maneuvering on the Oracle Server itself to complete.  The files when importing now must be placed in the Data Pump Directory.  This can be found by running the following query against the database server.

select owner, directory_name, directory_path from dba_directories

================


The cause is a new feature of Oracle 11.2: deferred segment creation.
All newly created tables that has no rows in it (ever!), has no corresponding row in DBA_SEGMENTS, so somewhy won't get exported with (deprecated) exp utility. Use expdp instead.


================

select 'alter table '||table_name||' allocate extent;' from user_tables where segment_created = 'NO';



set autocommit on;
set line 124;

select 'ALTER TABLE ' || TABLE_NAME || ' ALLOCATE EXTENT;' from user_tables where SEGMENT_CREATED = 'NO';
select 'ALTER TABLE '|| TABLE_NAME || ' ALLOCATE EXTENT;' from user_lobs where SEGMENT_CREATED = 'NO';
select 'ALTER INDEX '|| INDEX_NAME || ' ALLOCATE EXTENT;' from user_indexes where SEGMENT_CREATED = 'NO';

================

Don't forget: the deferred segment creation applies also to indexes and lobs and Indexes inherit table characteristics.
You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes and lobs, this column shows YES, if the segment is created.
However you should not take care of LOBs and LOB indexes extent allocations since they will be allocated automatically by the database once you allocate the corresponding table extent.

================Export was always failing due to the empty tables. Here is my dynamic sql======

DECLARE
CURSOR cur IS
SELECT 'ALTER TABLE ' || table_name || ' ALLOCATE EXTENT ' vSQL
FROM user_tables
where SEGMENT_CREATED = 'NO';
BEGIN
FOR c IN cur LOOP
BEGIN
EXECUTE IMMEDIATE c.vSQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 250));
END;
END LOOP;
END;
/