EXP Missing Tables on 11.2

Made an exp and some table is missing, right? The database is 11.2+? The tables missing have no rows in source dabase, right? Bingo!
This happen because Oracle implemented a space saving feature on 11.2 called Deffered Segment Creation.

This feature basically makes that the first segment of a table is only allocated when the first row is inserted. It was implemented because Oracle realized is not rare to find databases with lots of tables that haven’t ever had a row.

The situation occurs because the EXP client uses dab_segments as index to exporting, and, this feature makes that no segment be allocated. For Oracle, it’s not a problem, considering the use of Datapump (EXPDP/IMPDP).

But (there always exist a “but”), let’s suppose you have to export the file to a different location not accessible by directory nor has local space, or either, your table has a long column (yes, it’s deprecated, I know… but let’s suppose this is a legacy system…). Then, you can do:

1) For all tables that has no rows, allocate an extent:
alter table owner.tabela allocate extent;

To generate, the script:

select 'alter table '||owner||'.'||table_name||' allocate extent;' from all_tables where num_rows=0;

2) Export using clausule VERSION=11.1 or lower on EXP. 🙂

More about Deffered Segment Creation: https://oracle-base.com/articles/11g/segment-creation-on-demand-11gr2

Hope It helped.
See ya!
Matheus.