Oracle 11G when exporting with exp, empty table cannot be exported to resolve

• First, the cause of the problem:
  A new feature in 11G that does not allocate segment when the table has no data to save space
  
  1, insert a row, and then rollback will produce segment. The method is to insert data into the empty table and then delete it, resulting in segment. When exporting, you can export empty tables.

  2. Set Deferred_segment_creation parameters
  
  Sql> Show Parameter Deferred_segment_creation
  
  NAME TYPE VALUE
  ------------------------------------ ----------- ----------
  Deferred_segment_creation Boolean TRUE

  Sql> alter system set DEFERRED_SEGMENT_CREATION=FALSE;

  The system has changed.

  Sql> Show Parameter Deferred_segment_creation

  NAME TYPE VALUE

  ------------------------------------ ----------- ----------
  Deferred_segment_creation Boolean FALSE

  The parameter value is true by default, and segment is assigned when false, whether it is an empty table or a non-empty table.

  It is important to note that this value is set to no effect on previously imported empty tables, and still cannot be exported, but can only work on the new tables that are created later. If you want to export the empty table before, you can only use the 1th method or the following workaround.

• Second, the solution:

  1, first check all the empty table under the current user

  Select table_name from user_tables where num_rows=0;

  2, use the following sentence to find the empty table
  
  Select ' ALTER TABLE ' | | table_name| | ' allocate extent; ' from user_tables where num_rows = 0 and table_name like ' uflo_% ';

  Export the query results and execute the exported statement

  ' Altertable ' | | table_name| | ' allocateextent; '

  -----------------------------------------------------------
  1 ALTER TABLE Uflo_calendar allocate extent;
  2 ALTER TABLE uflo_calendar_date allocate extent;
  3 ALTER TABLE Uflo_d_node_attribute allocate extent;
  4 ALTER TABLE Uflo_d_node_entry allocate extent;
  5 ALTER TABLE Uflo_d_process_attribute allocate extent;
  6 ALTER TABLE Uflo_d_process_entry allocate extent;
  7 ALTER TABLE Uflo_d_process_entry_assignee allocate extent;
  8 ALTER TABLE Uflo_form allocate extent;
  9 ALTER TABLE Uflo_table_column allocate extent;
  Ten ALTER TABLE uflo_table_definition allocate extent;
  ALTER TABLE Uflo_task_appointor allocate extent;
  ALTER TABLE Uflo_task_reminder allocate extent;
  
  3, and then execute

  EXP username/Password @ database name File=/home/oracle/exp.dmp log=/home/oracle/exp_smsrun.log success!

=============================================================================================================== ===

Note:

1, the use of allocate extent instructions

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

• With EXPDP and IMPDP, there will be no change.

Create directory Expdp_dir as '/DATA/APP1/DP ';
Grant Read,write on directory Expdp_dir to Drgn_owner;

EXPDP drgn_owner/drgn_owner directory=expdp_dir dumpfile=drgn_owner.dmp schemas=drgn_owner logfile=DRGN_ OWNERexpdp.log

Create directory Impdp_dir as '/DATA/APP1/DP ';
Grant Read,write on directory Impdp_dir to Drgn_owner;

IMPDP Drgn_owner/drgn_owner Directory=impdp_dir dumpfile=drgn_owner.dmp logfile=drgn_owner.dmpimpdp.log

The empty table has already been imported

I personally recommend that once an empty database is established, execute the
Alter system set Deferred_segment_creation=flase Sscope=spfile;
SHUTDOWM Immediate
Startup

Oracle 11G when exporting with exp, empty table cannot be exported to resolve