Oracle 11g solutions for exporting empty and small tables

Oracle 11g solutions for exporting empty and small tables

Oracle 11g has a new feature. When the table has no data, no segment is allocated to save space.

Solution:

1) insert a row and then roll back to generate a segment.

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

2) set the deferred_segment_creation Parameter

SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
-----------------------------------------------------------------------------
Deferred_segment_creation boolean TRUE
SQL> alter system set deferred_segment_creation = false;
The system has been changed.
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
-----------------------------------------------------------------------------
Deferred_segment_creation boolean FALSE

It should be noted that the value setting does not affect the empty tables previously imported and cannot be exported. It can only be used for the newly added tables. To export an empty table, you can only use the first method.

3) Batch Process empty tables

First, use the following SQL statement to query all empty tables of the current user.

Select table_name from user_tables where NUM_ROWS = 0;

Run the SQL statement

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

Assume that the table TBL_1, TBL_2, TBL_3, and TBL_4 are empty here, the query result is as follows:

Alter table TBL_1 allocate extent;

Alter table TBL_2 allocate extent;

Alter table TBL_3 allocate extent;

Alter table TBL_4 allocate extent;

Finally, we can execute the preceding SQL statement.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Install Oracle 11.2.0.4 x64 in Oracle Linux 6.5