my dog learned polymorphism*
The moose likes Oracle/OAS and the fly likes Export command giving only those tables which has data. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Export command giving only those tables which has data." Watch "Export command giving only those tables which has data." New topic
Author

Export command giving only those tables which has data.

mallikarjun dontamsetti
Ranch Hand

Joined: Mar 18, 2011
Posts: 243

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.

PLEASE HELP ME ON THIS. THANKS IN ADVANCE.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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

Joined: Mar 18, 2011
Posts: 243

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
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Export command giving only those tables which has data.
 
Similar Threads
DbUnit + memory HSQLDB
Error calling Stored Procedure "SELECT in a stored procedure must have INTO "
No sockets? Design considerations
getTables in DatabaseMetadata not giving proper results
copying all of the objects in one schema over to another schema