Solutions for Oracle 11g exporting empty Tables

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

Workaround:

1) Insert a row, and then rollback will produce segment.

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

2) Set Deferred_segment_creation parameters

Sql> 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

It is important to note that this value is set to no effect on previously imported empty tables, and still cannot be exported, but can only work on the new tables that are created later. You can only use the first method if you want to export an empty table before.

3) batch processing of empty tables

First, use the following SQL statement to query all empty tables under the current user

Select table_name from user_tables where num_rows=0;

Then use the SQL statement to execute the query

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

Assuming we have a blank table tbl_1,tbl_2,tbl_3,tbl_4 here, the query results are as follows:

ALTER TABLE Tbl_1 allocate Extent;alter table tbl_2 allocate extent;alter table Tbl_3 allocate Extent;alter table Tbl_4 al locate extent;

Finally, we will execute the above SQL statement.

Solutions for Oracle 11g exporting empty Tables