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

When exporting data from oracle11.2.0.2 's server, an error was reported that "EXP-00003: storage definition for segment not found (0jin0)". The preliminary analysis is due to the fact that the data table is empty.

When Oracle 11G is exported with EXPORT, an empty table cannot be exported. There is a new feature in 11GR2. When the table has no data, segment is not allocated to save space.

-solution:

First, insert one line, and then rollback will generate 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. When changed to FALSE, segment is assigned to both empty and non-empty tables. Modify the SQL statement:

Alter system setdeferred_segment_creation=false scope=both

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

Third, use the following sentence to find the empty table:

Select'alter table'| | table_name | | 'allocate extent;' from user_tables wherenum_rows=0

# Export the query results, execute the exported statement, forcibly modify the segmentation value, and then export to export the empty table

-- you can generate sql statements for database updates in the following ways:

Execute in the command window of pl/sql

Set heading off

Set echo off

Set feedback off

Set termout on

Spool C:\ alterTableSql.sql

Select 'alter table' | | table_name | | 'allocate extent;' from user_tables where num_rows=0

Spool off

# automatically generate the empty table update sql to the alterTableSql.sql file in the root directory of the C disk. Then execute the sql file to update the database.

Select 'alter table' | | table_name | | 'allocate extent;' from user_tables where num_rows=0

It can also be replaced by:

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

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.