Oracle 11g cannot export empty tables in multiple ways

Oracle 11g cannot export empty tables in multiple ways

Oracle 11g empty table cannot be exported:

Data Query shows a new feature in Oracle 11G: The new parameter "deferred_segment_creation" indicates that the segment delay is created. The default value is true.

What does it mean?

Deferred_segment_creation: When a table is created, extent is not allocated immediately, that is, no data space is occupied, that is, no segment is allocated to the table to save space. The data structure is recorded directly in the dictionary. Space is allocated only when data is actually available. This method is very effective when thousands of tables need to be deployed in systems such as SAP. By default, true is enabled. You need to disable alter system set deferred_segment_creation = false;

In the system table user_tables, we can also see that the field "NO" or "YES" in the segment_treated field shows whether a table has been allocated a segment.

You can use the following SQL statement to query and find that the values of the segment_created field of a table not exported are 'no '.

1 Select segment_created, table_name from user_tableswhere segment_created = 'no ';

Solution:

1. the most primitive and stupid method (not recommended): insert a row, and then roll back or delete it 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:

Set the deferred_segment_creation parameter to FALSE to disable "segment deferred creation" (that is, directly creating a segment). segment is allocated to both empty tables and non-empty tables.

In sqlplus, run the following command:

SQL> alter system setdeferred_segment_creation = false;

View:

SQL> show parameterdeferred_segment_creation;

Note: After setting this value, it only applies to the newly added tables. It does not take effect for the previously created empty table (which already exists) and cannot be exported.

Restart the database to make the parameters take effect.

3. Use allocate extent to export an existing empty table.

Allocate extent can be used to ALLOCATE Extent to each table of the database object (note that an SQL code is required for each table ), however, it would be too troublesome to write a statement for each table. To facilitate the use of SQL commands, We can spell out the alter statement for each table.

Create an SQL command to allocate space to an empty table.

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_tableswhere 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 or num_rows is null (Note: This is not available in many tutorials, it may be null)

The above code can generate SQL statements for modifying the extent table in batches (How many empty tables are generated). We only need to execute all the SQL code generated by the Code, you can assign a segment to each existing table.

4. Run the SQL command to allocate space to empty tables.

Alter table WJDCPERSON allocate extent;

Alter table VERSION_NUMBER allocate extent;

5. After executing the preceding commands and exporting the database using exp, you can completely export the database including empty tables.