• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11902
  • Last Modified:

Problem with Datapump Export

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
espadana
Asked:
espadana
  • 4
  • 3
  • 2
1 Solution
 
jwahlCommented:
try

expdp BACKUP/password TABLES=Service_Table SCHEMAS=MYUSER DUMPFILE=tabledump.dmp
0
 
espadanaAuthor Commented:
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
 
schwertnerCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jwahlCommented:
sorry, should be

...
schemas=myuser
include=TABLE:”=’Service_Table’”
...
0
 
espadanaAuthor Commented:
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
 
schwertnerCommented:
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
 
espadanaAuthor Commented:
jwahl, is the syntax for include correct?

I end up with this error
LRM-00116: syntax error at 'TABLE:' following '='
0
 
jwahlCommented:
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
 
espadanaAuthor Commented:
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now