in Oracle 11g R2, it was found that the traditional exp could not export the empty table, and then queried,find the following steps to take, notes. Oracle 11g has a new parameter: deferred_segment_creation, meaning segment delay creation, which is true by default. What does it mean exactly? If this parameter is set to True, you create a new table T1, and you do not insert data into it, the table will not immediately allocate extent, that is, it does not occupy the data space, only when you insert the data to allocate space. This can save a little space. Solutions 1 when the deferred_segment_creation parameter is set to False, both the empty table and the non-empty table are assigned segment. in Sqlplus, execute the following command: sql>alter system set Deferred_segment_creation=false; View:sql>show parameter deferred_segment_creation; This value is set only after the newly added table, which has no effect on the previously established empty table. Note and you want to restart the database for the parameters to take effect 2 Instructions for using allocate extent Use allocate extent to assign extent to database objects. Its syntax is as follows: ----------- ALLOCATE EXTENT {SIZE integer [K | M] | datafile ' filename ' | INSTANCE integer} ----------- You can manually assign extent to data tables, indexes, materialized views, and so on. Examples of ALLOCATE extent use: ALLOCATE EXTENTALLOCATE EXTENT (SIZE integer [K | M])ALLOCATE EXTENT (datafile ' filename ')ALLOCATE EXTENT (INSTANCE integer) www.2cto.comALLOCATE EXTENT (SIZE integer [K | M] datafile ' filename ')ALLOCATE EXTENT (SIZE integer [K | M] INSTANCE integer) the complete syntax for data table operations is as follows: ----------- ALTER TABLE [schema.] table_name ALLOCATE EXTENT [({SIZE integer [K | M] | datafile ' filename ' | INSTANCE integer})] ----------- Therefore, you need to build a simple SQL command like this: ----------- ALTER TABLE Atabelname allocate extent ----------- 3.2 Build a SQL command that allocates space to empty tables, queries all empty tables under the current user (a user preferably corresponds to a default tablespace). The command is as follows: ----------- Sql>select table_name from user_tables where num_rows=0; ----------- based on the above query, you can construct a command statement that allocates space for empty tables, as follows: ----------- sql>select ' ALTER TABLE ' | | table_name| | ' allocate extent; ' from User_tables where num_rows=0 ----------- batch Output The above generated SQL statements, establish C:\CREATESQL.SQL, the contents are as follows: ----------- set heading off;set echo off;set feedback off;set termout on;spool C:\allocate.sql;Select ' ALTER TABLE ' | | table_name| | ' allocate extent; ' from User_tables where num_rows=0; spool off; Www.2cto.com ----------- execute the C:\createsql.sql command as follows: ----------- sql>@ C:\createsql.sql; ----------- after execution, get the C:\allocate.sql file. opening the file will see that you have already obtained a command SQL statement that allocates space for all empty tables. 3.4 Execute SQL command to allocate space for empty table: execute the C:\allocate.sql command as follows: ----------- sql>@ C:\allocate.sql; ----------- The table has been changed after execution. 3.4 by executing the EXP command at this point, all tables, including the empty table, can be exported normally. Transfer from http://www.2cto.com/database/201203/125469.html
Oracle 11g cannot export an empty table workaround
