Solved

Problem with Datapump Export

Posted on 2006-10-31
9
11,196 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 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
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!

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL SQL Developer 7 67
Problem with duplicate records in Oracle query 16 51
Where Does Time Value Come From for Database Insert or Update 4 36
update using pipeline function 3 31
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

680 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