Shulou(Shulou.com)06/01 Report--

Unable to export empty table with exp solution / reasons for table loss when exporting data with exp

Using oracle 11g to export data found that some tables were lost due to:

A new feature in 11GR2 is that when tables have no data, segment is not allocated to save space, but empty tables cannot be exported when exported with EXPORT, which results in the loss of some tables during migration and the invalidation of stored procedures

Solution:

First, insert one line, and then rollback will produce segment.

This method inserts data into an empty table and then deletes it, resulting in a segment. An empty table can be exported on export.

2. Set deferred_segment_creation parameters

The default value of this parameter is TRUE, and when changed to FALSE, segment is assigned to both empty and non-empty tables. Modify the SQL statement:

Alter system set deferred_segment_creation=false scope=both

It should be noted that this value has no effect on the previously imported empty tables and still cannot be exported, but can only have an effect on the newly added tables. If you want to export a previous empty table, you can only use the first method.

Use the following sentence to find the empty table and allocate space

Select 'alter table' | | table_name | | 'allocate extent;' from user_tables where SEGMENT_CREATED='NO'

Export the results of the SQL query, then execute the exported statement, force the table to allocate space to modify the segmentation value, and then export to export the empty table.

The following is a script that outputs the sql of the unallocated table directly to the result.txt file (direct execution of sql in result.txt allocates space for the unallocated table)

Set linesize 120 * * set pagesize 2000 * Spool / home/oracle/result.txt;SELECT 'ALTER TABLE' | | TABLE_NAME | | 'ALLOCATE EXTENT;' SQLSTR FROM USER_TABLES WHERE SEGMENT_CREATED='NO';spool off;exit

-- there will be a format like type

ALTER TABLE JW ALLOCATE EXTENT

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.