Solved

Problem with Datapump Export

Posted on 2006-10-31
9
11,386 Views
Last Modified: 2008-01-09
Hi, I am quite new to oracle, and have been playing with datapump feature.  There is this specific problem that I have been struggling with for a while, and would appreciate any help.

I have no problem using expdp to export a whole database or a schema.  However, I cant seem to be able to export individual tables.

 I have created a user called BACKUP and have given the user IMPORT FULL DATABASE, EXPORT FULL DATABASE, SYSDBA system privilages.  The user has the following roles: CONNECT, RESOURCE, EXP_FULL_DATABASE, IMP_FULL_DATABASE

I have a set of tables under another user called MYUSER.  Tables are called Service_Table, and ActiveCodes.

If I try to export the whole MYUSER schema into a dump file, everything works fine.  However, the problem I have is with exporting (or importing) individual tables.  When I try to execute the following command:

expdp BACKUP/password TABLES="MYUSER"."Service_Table" DUMPFILE=tabledump.dmp

I get the following error
-------------
Starting "BACKUP"."SYS_EXPORT_TABLE_01":  BACKUP/******** TABLES=MYUSER.Service_Table DUMPFILE=tabledump.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39165: Schema MYUSER was not found.
ORA-39166: Object SERVICE_TABLE was not found.
ORA-31655: no data or metadata objects selected for job
Job "BACKUP"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at 10:41:36
---------------

However I know that the MYUSER schema exists.  As I mentioned.  I have  no problem importing, or exporting schemas.  I have tried removing the double-quotes from around the table name, etc. but no luck

FYI I have oracle 10.2.0


I would be very grateful for any help
0
Comment
Question by:espadana
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 12

Expert Comment

by:jwahl
ID: 17841140
try

expdp BACKUP/password TABLES=Service_Table SCHEMAS=MYUSER DUMPFILE=tabledump.dmp
0
 

Author Comment

by:espadana
ID: 17841163
Thanks for the response.  Unfortunately that does not work.  It comes up with the following error

----------
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
UDE-00010: multiple job modes requested, schema and tables.
---------
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17841189
Do not try to use Data Pump until you upgrade to rel. 2 of 10g - there are more then enpugh bugs.

It is good practice to pump using SYS account.

I do not see DIRECTORY parameter.

Example

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tables.dmp
TABLES=employees,jobs,departments
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:jwahl
ID: 17841253
sorry, should be

...
schemas=myuser
include=TABLE:”=’Service_Table’”
...
0
 

Author Comment

by:espadana
ID: 17841271
Thanks, but is this bug documented anywhere?

my assumption was that if I dont have a DIRECTORY object specified, it would save the dump file in the default directory specified in the environment variable DATA_PUMP_DIR, which seems to work fine when I do a schema export
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17841303
Is it the same user?
I think it is not the same user.
User has to be granted rights on the directory.

From my archive:


connect system/manager@....
create directory pump as '/u01/app/oracle/product/10.2.0/m/pump_directory';
grant read,write on directory pump to sys;

This is logical directory. Now you have to create physical directory using MidNight comander or Windows Explorer.

See all directories:
select * from dba_directories;
0
 

Author Comment

by:espadana
ID: 17841342
jwahl, is the syntax for include correct?

I end up with this error
LRM-00116: syntax error at 'TABLE:' following '='
0
 
LVL 12

Accepted Solution

by:
jwahl earned 75 total points
ID: 17841404
in linux/unix use a backslash (\) as an escape character before a special character, such as a parenthesis, so that the character is not treated as a special character by the operating system:

...
INCLUDE=table:\"= \'Service_Table\' \"
...
0
 

Author Comment

by:espadana
ID: 17841460
Thanks jawl it worked.  The wierd thing is I am running oracle on windows server 2003.  Im surprised why I had to use the linux syntax on it.

Also I have managed to get it working with TABLES too.  All I had to do was to escape the double-quotes around the Service_Table, like this:

expdp BACKUP/password TABLES="MYUSER".\"Service_Table\"  DUMPFILE=tabledump.dmp


Cheers guys
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question