Oracle export warning & quot; exp-00003: storage definition of segment (0,0) not found & quot; resolved

Time:2021-3-2

Environmental Science:Centos7.4 oracle11.2.0.4 (building RAC cluster)

Problem Description:When using exp command to export, some tables prompt “exp-00003: storage definition of segment (0,0) not found” warning.

Cause of the problem:This problem is caused by the exported data table is empty. Oracle 11gr2 does not allocate segments to save space when there is no data in the table, so empty tables cannot be exported when exporting with export.

resolvent:

1. Use the following statement to find the empty table, export the query result, modify the segment value forcibly, and then export to export the empty table.

select ‘alter table ‘||table_name||’ allocate extent;’ from user_tables where num_rows=0;

Note: the statements may not find all the tables (some may be missing). If there is any missing, you can execute the following alert statement according to the export log.

2. Insert a record into the corresponding empty table and delete it (or roll back). This produces segments. When exporting, empty tables can be exported.

3. Set deferred_ segment_ The creation parameter. The default value of this parameter is true. When it is changed to false, segment is assigned to both empty and non empty tables.

alter system setdeferred_segment_creation=false scope=both;

Note: after the value is set, it will not affect the previously imported empty tables, but can not be exported. It can only affect the newly added tables. If you need to export the previous empty table, you can only use the first two methods.

 

reference resources:https://blog.csdn.net/u013991521/article/details/52576473

Recommended Today

Practice analysis of rust built-in trait: partialeq and EQ

Abstract:Rust uses traits in many places, from simple operator overloading to subtle features like send and sync. This article is shared from Huawei cloud community《Analysis of rust built-in trait: partialeq and EQ》Author: debugzhang Rust uses traits in many places, from simple operator overloading to subtle features like send and sync. Some traits can be automatically […]