Oracle 11g and export empty tables

If we try to use the old export utility, we will notice that no empty tables will be included into export file. To avoid this, we can use different aproaches. In first place, we should know that there is a parameter deferred_segment_creation which by default is set to TRUE. If we change it to FALSE, than all NEW created (creater after parameter was changed) will be included to the export file.

Here the question is how to include also the previously created empty tables? To do this we need to allocate extents to all empty tables and for this we can use following script:

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 TABLE ',TABLE_NAME,'ALLOCATE EXTENT;' from user_indexes where SEGMENT_CREATED ='NO';

This will generate a nice output sql script which can be used to allocate extents to all user empty tables, lobs and indexes and we are able to do export which will include all tables, also those with zero rows.