11g solutions that cannot export empty tables

There is a new feature in 11 that does not allocate segment when there is no data in the table to save space

Workaround:

　1, insert a line, and then rollback will produce segment.

The method is to insert data in an empty table and then delete it, resulting in segment. When you export, you can export an empty table.

　2. Set Deferred_segment_creation parameters

Show Parameter Deferred_segment_creation

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Deferred_segment_creation Boolean TRUE
Sql> alter system set DEFERRED_SEGMENT_CREATION=FALSE;

The system has changed.

Sql> Show Parameter Deferred_segment_creation

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Deferred_segment_creation Boolean FALSE

The parameter value defaults to True, and when it is changed to False, the segment is allocated, whether it is an empty table or a non-empty table.

Note that this value is set to have no effect on the previously imported empty table, still cannot be exported, and can only have a effect on the table that is added later. If you want to export an empty table before, you can only use the first method.

It took me a long time to find this method at last.

Check all the empty tables under the current user first

Select table_name from user_tables where num_rows=0;

Look for an empty table with the following sentence

select ' ALTER TABLE ' | | table_name| | ' allocate extent ' from User_tables where num_rows=0

Export the query results and execute the exported statements

And then perform exp

Batch output the above generated SQL statement, establish C:\CREATESQL.SQL, its contents are as follows:       -----------     set heading off;      set Echo off;     set feedback off;     set termout on;    &NB Sp;spool c:\allocate.sql;     select ' ALTER TABLE ' | | table_name| | ' allocate extent ' from User_tables where num_rows=0;     spool off;       -----------        Execute C:\createsql.sql, command as follows:     -----------     sql>@ c:\createsql.sql;     -----------        after execution, get C:\ Allocate.sql files.         Opening the file will see that a command SQL statement has been obtained for allocating space to all empty tables.     3.4 Execute SQL command, allocate space to empty table:        Execute C:\allocate.sql, command as follows:     -----------      sql>@ c:\allocate.sql;     -----------         executed, the table has been changed.     3.4 The exp command is executed at this time, and all tables, including the empty table, can be exported normally.