Oracle: Export empty tables

   

When taking a database dump from Oracle 11g recently, I found that the export did not include tables that had 0 rows. The Oracle11g instance implements a space saving measure where if your table has no data, it will not be exported. Space is allocated only when you add data to the table.

A workaround to the issue is to pre-allocate some space to such empty tables. Execute the following oracle queries to allocate space for all tables before taking the export:

select 'alter table '||table_name||' allocate extent;'
from dba_tables
where segment_created = 'NO'
and owner = 'DB_USER;

Running the above query will generate some alter table statements for all tables with empty data. Executing these SQL statements will allocate space to the empty tables and allow you to export and import such tables.