Shulou(Shulou.com)06/01 Report--

Problems with empty tables that cannot be exported:

Check the data and found that there is a new feature in Oracle 11g: a new parameter "deferred_segment_creation" means segment delay creation, and the default is true.

What does it mean exactly?

Deferred_segment_creation, that is, when a table is created, the table does not immediately allocate extent, that is, it does not take up data space, that is, the table does not allocate segment to save space. Record the data structure directly in the dictionary. Space is allocated only when there is real data. This approach is very effective for a system as large as SAP that requires the deployment of thousands of tables. Ture is enabled by default, and alter system set deferred_segment_creation=false needs to be disabled.

In the system table user_tables, you can also see that "NO" or "YES" in the field of segment_treated indicates whether a table is assigned segment or not.

Using the following SQL statement to query, you can find that the segment_created field values of the unexported tables are all 'NO'.

Select segment_created,table_name from user_tables where segment_created = 'NO'

Solution:

1. The most primitive and stupid method (not recommended): insert one line, and then rollback or delete to generate segment.

This method inserts data into an empty table and then deletes it, resulting in a segment. An empty table can be exported on export.

2. Set deferred_segment_creation parameters:

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

In sqlplus, execute the following command:

SQL > alter system set deferred_segment_creation=false

View:

SQL > show parameter deferred_segment_creation

Note: after this value is set, it only has an effect on the newly added tables, but has no effect on the previously established empty tables (which already exist) and still cannot be exported.

And restart the database for the parameters to take effect.

3. With ALLOCATE EXTENT, you can export empty tables that already exist.

Using ALLOCATE EXTENT, you can assign Extent to each table of a database object (note that each table requires a piece of SQL code), but if it is too troublesome to write a statement for each table, it is convenient for us to use the SQL command to spell out the alter statement for each table.

Build the SQL command that allocates space to the empty table.

Query all empty tables under the current user (one user preferably corresponds to a 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 to allocate space for an empty table, as follows:

SQL > Select 'alter table' | | table_name | | 'allocate extent;' from user_tables where num_rows=0 or num_rows is null (Note: many tutorials are not here, this may be empty)

The above code can generate a batch of SQL statements to modify the table extent (as many empty tables as there are), we only need to execute all the sql code generated by it, and we can assign segment to each existing table, which is OK.

4. Execute the SQL command to allocate space to the empty table.

Alter table WJDCPERSON allocate extent;alter table VERSION_NUMBER allocate extent

5, after the execution of the above command, and then use exp to export the database, you can completely export the database including empty tables.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.