Oracle 11g exports less empty tables, reason analysis

Oracle 11g exports less empty tables

When using the EXP command, there will be fewer tables, because in the 11g version if a table is empty, in order to save space, the default is not to allocate space for the table, in the export of the empty table will not be exported, naturally imported into another library when there will be fewer tables. But the missing table, although there is no data, but sometimes it will affect the execution of the application, so you have to have all the tables to be directed out.

So when you export the table normally, be sure to pay attention.

Cause Analysis:
Because Oracle 11g has a new parameter: "Deferred_segment_creation" "segment deferred creation", the default value is true.

What is paragraph:
A segment (segment) is any entity that consumes physical storage space in a database (a segment may exist in more than one data file because the physical data file is the basic physical storage unit that makes up the logical table space). Simply put segment as a unit that occupies a physical storage space.

Deferred_segment_creation:
If this parameter is set to True, when you create a new table table1 and do not insert data into it, the table is not immediately allocated extent, that is, no data space, the table is not allocated segment to save space, so the tables are not exported. You can also see in the system table user_tables that the segment_created field is "NO" or "YES" to indicate whether a table is assigned a segment. It is plainly intended to save a small amount of space.

SQL statements:

• To view the value of the Deferred_segment_creation parameter:

SQL>　show parameter deferred_segment_creation;

• Set the Deferred_segment_creation parameter to False to disable "segment deferred creation, which is to create segment directly, whether it is an empty table or a non-empty table, assign segment" (permission is granted). Modifying a parameter only takes effect on a table that is created later, and does not take effect on the previous table.

SQL> alter system set deferred_segment_creation=false;

• Find tables in all tables under users for which the Segment_created field value is "NO":

SQL> Select segment_created,table_name from user_tables where segment_created = ‘NO‘;

Workaround:

• Queries all empty tables under the user and generates statement output that generates a physical space for the empty table. Take the output statement and run it again.

SQL> select ‘alter table ‘||table_name||‘ allocate extent;‘ from user_tables where num_rows=0;

After execution, you can use the command to export all the tables under the user.

Oracle 11g exports less empty tables, reason analysis