How to export an empty table in Oracle 11g

How to export an empty table in Oracle 11g

Because of the new features created by the delay segment of Oracle 11g, Oracle does not allocate data segments when there is no data insertion, leading to exp being unable to export the empty table of the 11g database.

Of course, there is no such problem with EXPDP.

EXPDP hr/hr schemas=hr dumpfile=expdp.dmp directory=dbtest

Conn HR/HR

Select Table_name,num_rows from User_tables;

TABLE_NAME Num_rows

------------------------------ ----------

Locations 23

Emp_1 0

Part_time_employees 0

TEST3 5

TEST1 5

TEST 5

Pc_well_test 2

Pc_alarm_sort_test 1

Mview_pc_well_test 2

Mv_capabilities_table 14

T 0

TEST2

Sys_export_schema_01

Sys_export_schema_02

Hourly_employees 0

COUNTRIES 25

Admin_ext_employees

Admin_work_area

EMPLOYEES 107

Departments 27

DIGITS 2

Regions 4

Job_history 10

JOBS 19

Selected rows.

Why is the num_rows here empty?

That's because the table is just set up, and the data dictionary doesn't have any statistics on the table.

Sql> select ' ALTER TABLE ' | | table_name| | ' allocate extent ' from user_tables where

Num_rows=0

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

-----------------------------------------------------------

ALTER TABLE part_time_employees allocate extent;

ALTER TABLE emp_1 allocate extent;

ALTER TABLE T allocate extent;

ALTER TABLE hourly_employees allocate extent;

So for some online on the oracle11g on the first use of manual distribution extent and then use EXP to guide the database, in fact, the effect is not very much, but is a number of direct use of the EXPDP to guide Oracle 11g Database Hollow table to more convenient.

Of course, for those who want to start from the 11g low version, still can use this approach, but note that all the relevant tables are analyzed, and then use the above batch script. Or do not analyze the table, directly manually with the UE edit assigned extent command.

Sql> Select Table_name,num_rows from User_tables where num_rows=0;

TABLE_NAME Num_rows

------------------------------ ----------

Part_time_employees 0

Emp_1 0

T 0

Hourly_employees 0

Additional Information:

User_tables describes the relational TABLES owned by the current user. Its columns (except

For OWNER) are the same as those in All_tables. To gather statistics with this view and use the

Dbms_stats package.

Collect statistics for tables:

Analyze table XXX compute statistics;

Or

exec dbma_stats.gather_table_stats (' USER ', ' table ');