How to troubleshoot an Oracle 11G R2 with exp cannot export empty table problem

Oracle 11G when exported with export, empty table can not guide 11G R2 has a new feature, when the table has no data, do not allocate segment to save space

Workaround:

Insert a row, 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.

Sets the Deferred_segment_creation parameter, which defaults to True and assigns segment when it is changed to false, whether it is an empty table or a non-empty table. To modify an SQL statement:

Alter system set DEFERRED_SEGMENT_CREATION=FALSE Scope=both Note that this value is set to have no effect on previously imported empty tables, and cannot be exported, only to be useful for subsequent tables. If you want to export an empty table before, you can only use the first method.

Use the following sentence to find an empty table select ' ALTER TABLE ' | | table_name| | ' allocate extent; ' from User_tables where num_rows=0;

Export the query results, execute the exported statements, forcibly modify the segment value, and then export the empty table,

Note: Modify the 11G_R2 parameter to export an empty table before inserting data into the database

Find empty table Select ' ALTER TABLE ' | | table_name| | ' allocate extent ' from User_tables where num_rows=0

Oracle 10g has added EXPDP and IMPDP tools, and you can also export empty tables with this tool

Oracle EXPDP/IMPDP usage to create a logical directory that does not create a real directory on the operating system, preferably with an administrator such as system.

Create directory Db_bak as ' d:\test\dump '; View the Administrator directory (see also if the operating system exists because Oracle does not care if the directory exists, and if it does not, an error)

SELECT * from Dba_directories;

Give the system user permission to operate in the specified directory, preferably with administrators such as system.

Grant Read,write on directory Db_bak to system;

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/