Question about the exp empty table in oracle11g

The data is exported from Oracle10g. After importing oracle11g data, everything runs normally for a period of time. You need to export the data from 11g exp and use this backup to import the data to 11g again. A lot of tables are missing.

I thought it was a problem with export. Through n tests and searches, it was found that this was a problem of 11 GB.

 

When Oracle 11g is exported using export, empty tables cannot be exported.
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.

2. Set the deferred_segment_creation parameter.

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. It can only be used for the newly added tables. To export an empty table, you can only use the first method.

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
SQL>

 

/// // ///////////////////

SQL> alter system set sort_area_size = 10000;
Alter system set sort_area_size = 10000
*
Row 3 has an error:
ORA-02096: the specified initialization parameter for this option cannot be modified

SQL & gt; Alter system set sort_area_size = 10000 scope = spfile;

The system has been changed.
It indicates that it takes effect only after restart.

SQL & gt; Alter system set sort_area_size = 20000 deferred;

The system has been changed.

Cancel the settings in spfile. Sometimes you need to restore the default settings of the original parameters, but forget the original values. You can use reset to reset the original default values.

Alter system reset
1. There are several restrictions that Sid must be specified

2. The sequence must be scope = spfile, SID =
3. scope cannot be equal to both. Memory cannot be modified in memory when Sid is specified.

SQL> alter system reset open_cursors;
Alter system reset open_cursors
*
Row 3 has an error:
ORA-00905: Missing keywords

SQL> alter system reset open_cursors SID = '*';
Alter system reset open_cursors SID = '*'
*
Row 3 has an error:
ORA-32009: memory value for instance * cannot be reset (from instance xhtest)

SQL> alter system reset open_cursors SID = '*' scope = spfile;
Alter system reset open_cursors SID = '*' scope = spfile
*
Row 3 has an error:
ORA-00933:SQLCommand ended incorrectly

SQL> alter system reset open_cursors scope = spfile SID = '*';

The system has been changed.

We can see that the SID keyword is to be added. The sequence cannot be wrong. If the SID is specified but scope = spfile is not written, scope = both is used by default (including modification in memory) but it is not allowed to modify in memory when specifying Sid so = both, = memory will not report an error (ORA-32009: cannot reset the memory value of instance * (from instance xhtest ))