Solve the problem that Oracle11g cannot export empty tables. oracle11g exports empty tables

Solve the problem that Oracle11g cannot export empty tables. oracle11g exports empty tables
ORACLE 11g export database file backup using the exp command, it is found that only a part of the table can be exported and no error is prompted, the solution can not be found before the export of the table can only be re-created. Later it was found that all empty tables were not exported. So I want to check it out, because there is no such problem in the previous 10G version.

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?

If this parameter is set to true, you create a new table named Table1 and do not insert data into it, the table will not be allocated extent immediately, that is, it does not occupy data space, that is, tables do not allocate segment to save space. Therefore, these tables cannot be exported. 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. To put it bluntly, we want to save a small amount of space.

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 '.

Select segment_created, table_name from user_tables where segment_created = 'no ';

Solution: add the following statement for each empty table:

Alter table TableName allocate extent

1. query all empty tables under the current user (one user is better suited to one default tablespace ). The command is as follows:

SQL> select table_name from user_tables where NUM_ROWS = 0;

2. Based on the preceding query, you can build a command statement for allocating space to empty tables, as shown below:

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

Num_rows = 0 or num_rows is null or segment_created = 'no'

3. Execute all the statements in 2.