| |
Oracle 11g empty table can not export the problem:
Data found Oracle 11g has a new feature: Added a parameter "deferred_segment_creation" meaning that the segment delay to create, the default is true.
What does that mean?
Deferred_segment_creation, that is, when the establishment of the table, the table will not immediately allocate extent, which is not accounted for data space, that is, the table does not allocate segments to save space. The data structure is recorded directly in the dictionary. And only when there is real data when the allocation of space. This approach is very effective for deploying tens of thousands of tables as large as SAP. The default is turned on for the ture, need to close alter system set deferred_segment_creation = false;
In the system table user_tables can also be seen in the segment_treated field is "NO" or "YES" indicates that a table is allocated segment.
With the following SQL statement query, you can find the table does not export its segment_created field value is 'NO'.
1 Select segment_created, table_name from user_tableswhere segment_created = 'NO';
Solution:
1, the most primitive of the most stupid way (not recommended): insert his party, and then rollback or deleted on the creation of a segment.
The method is to insert the data in the empty table, and then delete, then segment. When exporting, you can export an empty table.
2, set deferred_segment_creation parameters:
Set the deferred_segment_creation parameter to FALSE to disable "segment deferred creation" (that is, to create segments directly), whether segmented or non-empty.
In sqlplus, the implementation of the following order:
SQL> alter system setdeferred_segment_creation = false;
View:
SQL> show parameterdeferred_segment_creation;
Note: This value is set only after the new table has an effect on the previously established empty table (already exists) does not work, still can not export.
And to restart the database, so that parameters take effect.
3, the use of ALLOCATE EXTENT, can be derived before the already empty table.
Use ALLOCATE EXTENT database object can be assigned to each table Extent (note for each table, that is, a table requires a SQL code), but if each table to write a statement, then too much trouble, in order to facilitate our Use the SQL command to spell out the alter statement for each table.
Build SQL commands to allocate space to empty tables.
Queries the current user under all the empty table (a user corresponds to a default table space). The commands are 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 an empty table, as follows:
SQL> Select 'alter table' || table_name || 'allocate extent;
The above code can generate batch SQL statements to modify the extent of the table (the number of empty tables on the number of generated), we only need to generate all of the sql code to execute, you can already exist for each table to allocate segment, OK the.
4, the implementation of the allocation of space on the SQL table space orders.
Alter table WJDCPERSON allocate extent;
Alter table VERSION_NUMBER allocate extent;
5, the implementation of the above command, and then export the way to export the database, you can complete the export, including the empty table database. |
|
|
|