Oracle 11g export an empty table using the exp command

1. 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. 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;

View:
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.

3. You can manually allocate Extent for empty tables to solve the problem of empty tables created before export. Description:

3.1 instructions on using ALLOCATE EXTENT

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})]
-----------

Therefore, you need to build the following simple SQL command:

-----------
Alter table aTabelName allocate extent
-----------

3.2 construct an SQL command to allocate space to empty tables,

Query all empty tables under the current user (one user is recommended to correspond to one default tablespace ). The command is as follows:

-----------
SQL> select table_name from user_tables where NUM_ROWS = 0;
-----------

Based on the above query, you can build a command statement for allocating space to empty tables, as follows:

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

Output the SQL statements generated in batches and create C: \ createsql. SQL. The content is as follows:

-----------
Set heading off;
Set echo off;
Set feedback off;
Set termout on;
Spool C: \ allocate. SQL;
Select 'alter table' | table_name | 'allocate extent; 'from user_tables where num_rows = 0;
Spool off;
-----------

Run C: \ createsql. SQL. The command is as follows:
-----------
SQL> @ C: \ createsql. SQL;
-----------

After execution, the C: \ allocate. SQL file is obtained.

Open the file and you will see that you have obtained the SQL statement for allocating space to all empty tables.

3.4 run the SQL command to allocate space for empty tables:

Run C: \ allocate. SQL. The command is as follows:
-----------
SQL> @ C: \ allocate. SQL;
-----------

The execution is complete and the table has been changed.

3.4 execute the exp command to export all tables including empty tables.

Oracle automatic backup case on Linux platform (using exp tool)

Solutions to garbled comment during database migration using exp/IMP

Exp-00091 error when exporting database dmp file in Linux

Talking about Oracle exp backup

EXP/IMP data migration