Oracle 11g R2 exp cannot export empty table Solution

Oracle 11g realbench 2 (11.2.0.1) reports an error when exporting an empty table. After checking the cause, it turns out that an optimization was made in this version. During table creation, no storage space is allocated. The storage space is allocated only when data is inserted for the first time. This saves a lot of storage space when there are many empty tables in the system. However, if the system only has a small amount of space, it is unnecessary. This optimization will also cause a Quota error (Quota Errors). For details, refer to here. This problem was fixed in 11.2.0.2.

Therefore, oracle provides a deferred_segment_creation parameter for configuration. The default value of this parameter is true. Therefore, if you do not set this parameter to false when creating a table, no data is added after the table is created, and an error occurs when you use exp to export the table. Refer to the official documentation for explanation:

Property	Description
Parameter type	Boolean
Default value	True
Modifiable	Alter session, ALTER SYSTEM
Range of values	True | false
Basic	No

DEFERRED_SEGMENT_CREATION specifies the semantics of deferredsegment creation. if set to true, then segments for non-partitioned tables and their dependent objects (LOBs, indexes) will not be created untilthe first row is inserted into the table.

Before creating a set of tables, if it is known that asignificant number of them will not be populated, then consider setting thisparameter to true. This saves disk space and minimizes install time.

If the value of this parameter is set to true, the non-partition table and its dependent objects (LOB, index) will not be allocated storage space until the first data is inserted into the table.

If you do not know whether to write data to the table before creating the table, you can set this parameter to true. This saves storage space and reduces the table creation time.

Okay. Now you know the reason. Let's look at the solution:

1. the dumbest way is to write a piece of data to the table, delete it, or roll back it, and then export it. However, this is a problem. If there are many tables, it would take too much time. Use the second method.

2. Modify the deferred_segment_creation parameter.

View the value of this parameter:

SQL> showparameter deferred_segment_creation;
NAME TYPE VALUE
-----------------------------------------------------------------------------
Deferred_segment_creation boolean TRUE

Set this parameter:

Alter system set deferred_segment_creation = false;

The syntax for setting this parameter is as follows:

Set deferred_segment_creation = [TRUE | FALSE]

You can also specify it when creating a table. The syntax is as follows:

Segment creation {IMMEDIATE | DEFERRED}

After this setting, the segment will be allocated when the table is created later. However, the previously created table is not allocated. You also need to forcibly allocate space to the previous table.

Alter table <tablename> allocate extent

Since there may be many tables, you can use the following method to modify them in batches:

Select 'altertable' | table_name | 'allocate extent ;'
From user_tables
Where num_rows = 0;

Then export the query results and run the exported SQL statement. OK.

Read more: Oracle 11g R2 cannot export empty tables with exp Solution