How to export empty tables from Oracle

How to export empty tables from Oracle
Background

Using the Exp command after Oracle 11g does not export an empty table (rowcount = 0), which is a very bad problem recently encountered in the work and has even been pitted more than once, therefore, this time, I have to think about it and want to solve it completely. There must be an old method for calling a new method. This method was proposed by a blogger for a long time. The following is the original article, which also illustrates the cause of the problem.

Export an empty table using exp at Oracle11g

Root Cause: "No segment is assigned to empty tables by default for Oracle11g. Therefore, empty tables are not exported when you use exp to export Oracle11g databases"

Secondary cause: during the development and maintenance period, you often need to perform database import and export operations frequently. The old method can solve the problem, but one time you forget it, it is a complete tragedy.

Problem Extension: I found that sometimes some types and MPs queue types cannot be exported, resulting in process and function compilation failure

Solution

The solution is actually very simple. The expdp and impdp commands are used to replace exp. The imp command completely solves this problem. The experiment is as follows:

1. Create an 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/a@orcl225 File=x.dmp owner=someuser

3. pitfall

4. Solution: Use expdp for export

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;

(For the use of expdp and impdp, use baidu or refer to the external links provided in the lower part of the article)

Happy process like

The problem has been completely solved.

Unexpected gains

1. Improve the Export and Import speed. When using expdp and impdp to import and export Oracle databases, it will also bring you some unexpected gains, such as a significant increase in the export speed, and the output logs are more accurate and beautiful, in fact, using normal exp to export a table with 50 million rows of records is simply a weak brute-force tool (I don't know how many streets are between speed and SQL server)

2. You can export tablespaces (exp can also be used)

Price of comeback

It looks pretty good, but expdp and impdp are actually limited, that is, IO operations must be performed on the server (specifically, the DIRECTORY parameter is related, which is not explained in detail here, read the tutorial by yourself)

Valuable links

Expdp and impdp parameter usage

Comparison between EXP and expdp

[Transfer tablespace] use the EXPDP/IMPDP tool's transfer tablespace to complete data migration

Difference between exp/imp and expdp/impdp (syntax)

Conclusion

If you do not like it well, you can enjoy it and continue to encourage it. If you like it, you can give it support and enjoy it at will. Just kidding, I just hope that some of my experiences will be useful and you will not be confused.