Export command giving only those tables which has data.

[mallikarjun dontamsetti]

I have schema which has number of tables, some of them are empty. Now my problem is when i try to export this schema it is exporting only tables which has data. An remaining tables are not exporting. I have done my export like bellow.

    EXP USER1/PASSWORD GRANTS=Y FILE=TEST_EXP.DMP LOG=TEST_EXP.LOG OWNER=USER1

PLEASE HELP ME ON THIS. THANKS IN ADVANCE.

[Martin Vajsar]

You're using EXP on Oracle 11g. The EXP is no longer supported, you need to use EXPDP.

The problem is that 11g by default does not create segments for tables immediately, only when data is added to the table for the first time. Consequently, EXP does not export these tables, because they do not have segments. There is a parameter which makes 11g create segments for created tables like the previous versions did. If you set this parameter and recreate the empty tables, EXP would start to work. My advice is not to do this, as EXP is clearly doomed anyway and EXPDP can be significantly faster and allows much greater control over the contents of the export.

You may need to alter your processes, though, as EXPDP can only write to directories on the DB server, while EXP generally writes files to a directory on the client.

[mallikarjun dontamsetti]

Martin Vajsar wrote:
You may need to alter your processes, though, as EXPDP can only write to directories on the DB server, while EXP generally writes files to a directory on the client.

I didn't get this. we can mention directory(virtual directory oracle) in expdp. What about exp? what do you mean by client side directory? Is it directory on same machine where oracle client installed?

[Martin Vajsar]

When you export the data using EXP, the EXP process connects to the database, reads the data and writes them to some directory on a machine on which it runs.

When you're using EXPDP, the export process actually runs in the database, EXPDP is just a program which configures and starts the process (you can configure and start the export processes using some DBMS packages via PL/SQL, you don't actually need the EXPDP executable). The dump can be created only in directories configured in the database (see CREATE DIRECTORY), that is directories the DB server can see.

In both cases, you can use network drives/connections to access the dump. With EXP, you can always access the dump file from the machine you've run run the EXP on. However, if you run EXPDP on a machine which cannot directly see directories the DB server can see, you may have trouble accessing the dump files. This can be troublesome in more restricted environments.