Oracle Database migration (cross-version) and empty tables are not exported

1. Oracle 11g has a feature. If exp/imp is used to import and export the database, no data table cannot be exported,

I have also encountered this problem. After research, I think it is best to use expdp/impdp to export and import data to avoid this problem. (The disadvantage of this command is that it must be executed on the server)

2. Migration of Oracle versions with different data versions usually prompts incompatibility errors (here, porting refers to porting from high to low, and migrating from low to high)

The author provides a solution: Use expdp for export (must be 10 Gb or later, or expdp is not supported), add the version parameter to the Export command, the value is the version number of the target database.

For example, to port the database from oracle version 11.2.0.1.0 to 10.2.0.1.0, you only need to set version = '10. 2.0.1.0 'during export. You do not need to set the version parameter during import.

The first problem is that empty data tables cannot be exported, and a solution to this problem is circulating on the Internet: (this method is not recommended by the author)

There is a new feature in 11G. 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 at www.bkjia.com.

2. Set the deferred_segment_creation parameter.

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

 

The default value of this parameter is TRUE. When it is set to FALSE, segment is assigned to both empty tables and non-empty tables.

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

After a long time, I finally found this method.

First, query all empty tables of the current user.

Select table_name from user_tables where NUM_ROWS = 0;

Use the following statement to find an empty table:

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

Export the query result and execute the exported statement.

'Altertable' | TABLE_NAME | 'allocateextent ;'
-----------------------------------------------------------
Alter table AQ $ _ MEM_MC_H allocate extent;
Alter table AQ $ _ MEM_MC_G allocate extent;
Alter table AQ $ _ MEM_MC_ I allocate extent;
Alter table AQ $ _ AQ_PROP_TABLE_T allocate extent;
Alter table AQ $ _ AQ_PROP_TABLE_H allocate extent;
Alter table AQ $ _ AQ_PROP_TABLE_G allocate extent;
Alter table AQ $ _ AQ_PROP_TABLE_ I allocate extent;
Alter table AQ $ _ KUPC $ DATAPUMP_QUETAB_T allocate extent;
Alter table AQ $ _ KUPC $ DATAPUMP_QUETAB_H allocate extent;
Alter table AQ $ _ KUPC $ DATAPUMP_QUETAB_G allocate extent;
Alter table AQ $ _ KUPC $ DATAPUMP_QUETAB_ I allocate extent;

'Altertable' | TABLE_NAME | 'allocateextent ;'
-----------------------------------------------------------
Alter table AQ $ _ SYS $ SERVICE_METRICS_TAB_T allocate extent;
Alter table AQ $ _ SYS $ SERVICE_METRICS_TAB_H allocate extent;
Alter table AQ $ _ SYS $ SERVICE_METRICS_TAB_G allocate extent;
Alter table AQ $ _ SYS $ SERVICE_METRICS_TAB_ I allocate extent;

Then execute

Exp username/password @ database name file =/home/oracle/exp. dmp log =/home/oracle/exp_smsrun.log successful!