Reason analysis and solution for ORACLE exp not exporting empty table _oracle

One, cannot export the reason of empty table

1, oracle11g default to empty table does not assign segment, so use EXP export oracle11g database, empty table will not export.

2, set the Deferred_segment_creation parameter is false, whether it is empty table or Non-empty table, are allocated segment.

In Sqlplus, execute the following command:

Sql>alter system set Deferred_segment_creation=false;

View:

Sql>show parameter deferred_segment_creation;

This value is set to work only on the newly added table, which does not affect the previously created empty table.

Ii. Solutions

You can use a manual method of assigning extent to empty tables to resolve the problem of empty tables that were created before exporting

①select ' ALTER TABLE ' | | table_name| | ' allocate extent ' from user_tables where num_rows=0 or num_rows is null

Use the above statement to assign a extent statement to an empty table

However, there are many empty tables in the view user_tables num_rows not equal to 0 (reason: The table has previous data, after the deletion of Oracle statistics, the view User_tables data is not updated), so the above method does not allocate data segments for all empty tables, The workaround is as follows

②select ' Analyze table ' | | table_name| | ' compute statistics; ' from User_tables

Analyze table TableName COMPUTE statistics
Equivalent to analyze table TableName compute statistics for table for all indexes to all columns
The statistics for table exist in the view: User_tables, All_tables, Dba_tables
The statistics for the all indexes exist in the view: User_indexes, All_indexes, dba_indexes
The statistics for the all columns exist in an attempt to: User_tab_columns, All_tab_columns, Dba_tab_columns

After the execution, the Num_rows value in the View User_tables is updated, then the ① is executed, and the data segment can be assigned to all the empty tables

However, when executing the Analyze Table TableName Compute Statistics, Oracle reports the object Statictis are locked (the statistics of these tables are locked) and unlocks in the following way

③select ' exec dbms_stats.unlock_table_stats (' | | | ') Jxdemo ' ' | | | table_name| | ""); From User_tables

(③ results need to be performed on Sqlplus, and errors are performed in SQL windows in Pl/sql Developer)

③ after the execution of the results obtained after the execution of the ② there is no object Statictis are locked error prompted