Solved

Problem with Datapump Export

Posted on 2006-10-31
9
11,285 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
add more rows to hierarchy 3 45
format dd/mm/yyyy parameter 16 58
Excess Redo 3 47
scheduler notification 9 77
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

732 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