Handling Oracle table export failures

Handling Oracle table export failures
1. Cause

By default, no segment is assigned to the empty table at Oracle11g. Therefore, when you use exp to export the Oracle11g database, the empty table is not exported.

2. Processing Method

After the deferred_segment_creation parameter is set to FALSE, segment is assigned to both empty and non-empty tables.
In sqlplus, run the following command:

SQL> alter system set deferred_segment_creation=false scope=both;SQL> show parameter deferred_segment_creation;

After this value is set, it only applies to the newly added tables and does not apply to the empty tables created previously.

You can manually allocate Extent for empty tables to solve the problem of creating empty tables before export.
You can use allocate extent to ALLOCATE Extent to database objects. The syntax is as follows:

ALLOCATE EXTENT { SIZE integer [K | M] | DATAFILE ‘filename’ | INSTANCE integer }

You can manually allocate Extent for data tables, indexes, and materialized views.
Example of allocate extent:

ALLOCATE EXTENT
Allocate extent (SIZE integer [K | M])
Allocate extent (DATAFILE 'filename ')
Allocate extent (INSTANCE integer)
Allocate extent (SIZE integer [K | M] DATAFILE 'filename ')
Allocate extent (SIZE integer [K | M] INSTANCE integer)
The complete syntax for data table operations is as follows:

ALTER TABLE [schema.]table_name ALLOCATE EXTENT [({ SIZE integer [K | M] | DATAFILE ‘filename’ | INSTANCE integer})]

Create a command to allocate space for empty tables

SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' allocate extent;' FROM DBA_ALL_TABLES WHERE OWNER = 'BUS'AND NUM_ROWS=0;

3. Optimized Solution

The above method also has a problem. If the table contains 100 data records, all data will be deleted later (in the TRUNCATE mode, delete from will not change the high level. TRUNCATE changes the high level, but the analysis table is required to change the records in the dictionary table ). In this way, the actual number of records in the table is 0, but the number of records in DBA_ALL_TABLES is still 100. The statement generated using the preceding SQL statement does not contain this table Shard, but the table structure of this table shard cannot be exported.
Therefore, the following method is used to traverse the tables to be exported, count the number of records, and print the table with the record 0.

DECLARE  CURSOR C_TABLES   IS  SELECT OWNER,TABLE_NAME FROM DBA_ALL_TABLES WHERE OWNER='GJDS';  C_ROW C_TABLES%ROWTYPE;  P_SQL                   VARCHAR2(200);  P_COUNT                 NUMBER;  P_TABLE                 VARCHAR2(100);BEGIN  FOR C_ROW IN C_TABLES LOOP    P_TABLE := C_ROW.OWNER||'.'||C_ROW.TABLE_NAME;    P_SQL := 'SELECT COUNT(*) FROM '||P_TABLE;    EXECUTE IMMEDIATE P_SQL INTO P_COUNT;    IF P_COUNT = 0 THEN      DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||P_TABLE||' ALLOCATE EXTENT;');    END IF;  END LOOP;END;

After the preceding processing, Oracle assigns a segment to the empty table. In this way, you can use the exp command to export the empty table.

Note:
Part of the content is taken from the network. If any errors or errors are found in this article, please inform us. Thank you!