Solved

Problem with Datapump Export

Posted on 2006-10-31
9
10,940 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
  • 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 47

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
 
LVL 12

Expert Comment

by:jwahl
ID: 17841253
sorry, should be

...
schemas=myuser
include=TABLE:”=’Service_Table’”
...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now