ORACLE 11g exp export empty table method

EXP is very different from the previous 10G and 9I versions when exporting a database of 11G, that is, tables without data will not allocate space.

Since Oracle 11.2.0.1, Oracle provides a new space allocation method: when creating a non-partition Table, this Table Segment is not created immediately, instead, the Segment is created only when the first row of the record is inserted. This is different from the method used to create and allocate space for the Segment. such a segment is also called a delay segment.

(1) Reduce the overhead of space: When you create hundreds of tables at a time, because many tables are not used in a short time, you can save a lot of disk overhead.

(2) Accelerate Application Deployment: because no Segment is allocated, you only need to operate data dictionaries when creating a table, without Designing space allocation. Therefore, the efficiency is naturally much higher.

2. Set the deferred_segment_creation parameter.

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. It can only be used for the newly added tables.

When deferred segment is enabled and empty tables are exported using exp, The EXP-00011: 'table name' does not exist is reported. That is, empty tables are not exported.

If you want to assign a segment when creating a table, you can use the following SQL: createtable B _tab (id number, text varchar2 (10) segment creation immediate;

Empty table: the rows of user_talbes is 0, and the allocated segment is user_segments.

select'altertable '||table_name ||'allocate extent size 64K;'  from user_tables where table_name not in       (select segment_name from user_segments where segment_type ='TABLE')