A new way for Oracle to export empty tables (completely resolved)

Background

Using the EXP command after Oracle 11g does not export empty table (rowcount=0), is recently encountered in the work of a very pit problem, and even has been a pit more than once, so this lesson from the bitter experience, ready to solve this problem completely. The reason is called the new method, that must have the old method, this method is a Bo friend very early proposed, the following is the original text, actually also explained the cause of the problem

oracle11g Exporting empty tables using exp (those of programmers)

Problem root cause: "oracle11g default to Empty table is not assigned segment, so when using exp export oracle11g database, empty table does not export"

Problem secondary reason: in the development of maintenance period often need to do the database import and export operations, the use of old methods can really solve the problem, but one time forget the total tragedy

Problem extension: I found that sometimes some type, pipe type can not be exported, resulting in process, function compilation does not pass

Solution Solutions

The solution is very simple, is to use the EXPDP,IMPDP command instead of the EXP,IMP command to completely solve the problem, the experiment is as follows:

1. Create Empty table

--Create table
CREATE TABLE Testempty
(
ID number,
Name Number
)
Tablespace xxxtemp
Storage
(
Initial 1M
Next 1M
Minextents 1
Pctincrease 0
);

2. EXP Export Statement

Exp System/[email protected] file=x.dmp Owner=someuser

3, by the pit

4, the solution, using EXPDP to export

EXPDP Scott/[email protected] Schemas=scott dumpfile=expdp.dmp directory=dpdata1;

(for the use of EXPDP,IMPDP, please self-Baidu or refer to the article below the external chain)

Happy process such as

The problem has been completely resolved.

Accidental harvesting

1, improve the export import speed, in the use of EXPDP and IMPDP Oracle database Import and export, will also bring you some unexpected gains, such as the export speed has a significant increase, and the output of the log is more accurate and beautiful, In fact, with ordinary exp do 50w row record export table is simply a weak violence tool (from the speed and SQL Server is not known a few streets)

2, can be the table space Export (exp can actually also)

The price of a comeback

Looks very good, but EXPDP,IMPDP actually also has the restriction, is must be on the server to carry on the IO operation (specifically is the directory this parameter is related, here does not explain in detail, oneself sees the tutorial to go)

Valuable links

EXPDP and IMPDP parameter usage

Exp and EXPDP contrast (speed)

Transport table space completes data migration using the EXPDP/IMPDP tool's transport table space

Exp/imp and EXPDP/IMPDP Differences (grammar)

Written in the last

Write bad, do not like can be rewarded continue to encourage, like can point recommended to give support and casually, open a joke, share out just hope my little experience is useful, avoid you into the pit.

A new way for Oracle to export empty tables (completely resolved)

A new way for Oracle to export empty tables (completely resolved)