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

Source: Internet
Author: User
Tags create directory empty

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/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.