1, oracle11g default to Empty table is not assigned segment, so use EXP export oracle11g database, empty table is not exported.
2. After setting the Deferred_segment_creation parameter 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.
3. You can use the method of assigning extent to empty tables manually to solve the problem of empty tables established before export. The description is as follows:
3.1 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 EXTENT
ALLOCATE EXTENT (SIZE integer [K | M])
ALLOCATE EXTENT (datafile ' filename ')
ALLOCATE EXTENT (INSTANCE integer)
ALLOCATE 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;
-according to 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;
-----------
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.
In addition: oracle11g, the password is case-sensitive, that is, the letters in the password are case-sensitive.
In oracle10g and before, the letter case in the password doesn't matter.
Go to Oracle 11g export empty table