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.

Advertisements

Got something to say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s