Home » RDBMS Server » Server Utilities » Problems with DATAPUMP EXP
Problems with DATAPUMP EXP [message #257886] Thu, 09 August 2007 07:34 Go to next message
Creems
Messages: 70
Registered: July 2007
Location: Johannesburg
Member
Hi Experts,

Can you please help me with this problem. Actually I have Oracle 10g 10.1.0.2.0 installed on my windows XP and I have two databases on this server named: TEXAS and REC. I have schema named PUBS on TEXAS.

I want to export schema PUBS and all it's object to REC database.

This is what I did:

conn /as sysdba

1 CREATE DIRECTORY dpump_dir AS 'G:\datadumps';
2 GRANT read, write ON DIRECTORY dump_dir TO PUBS;

and I ran this from command prompt:

C:\> expdp pubs/pubs DIRECTORY=dpump_dir DUMPFILE=pubs.dmp SCHEMAS=pubs JOB_NAME=myPUBS VESION=COMPATIBLE

I got this error below:

ORA-39002: invalid opearation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

I'm more than a bit confused can you please put me through step by step.

Thanks in good anticipation.

Regards,

Cherish
Re: Problems with DATAPUMP EXP [message #257893 is a reply to message #257886] Thu, 09 August 2007 07:45 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

did you set UTL_FILE parameter ?

please post below query output

SQL> show parameter UTL_FILE

Re: Problems with DATAPUMP EXP [message #257925 is a reply to message #257893] Thu, 09 August 2007 11:05 Go to previous messageGo to next message
Creems
Messages: 70
Registered: July 2007
Location: Johannesburg
Member
Dear Tag,

Here is it.

SQL> show parameter UTL_FILE;

NAME TYPE VALUE
----------------------- --------------- --------------------
utl_file_dir string




Cherish
Re: Problems with DATAPUMP EXP [message #257928 is a reply to message #257925] Thu, 09 August 2007 11:14 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
SQL> create directory Dp as '/export/home/oracle/';

Directory created.

SQL> grant read ,write on directory DP to oracle;

Grant succeeded.

SQL> !expdp oracle/espsvcsoracle dumpfile=dp:tab.dmp tables=del_history

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 09 August, 2007 16:11:16

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "ORACLE"."SYS_EXPORT_TABLE_01":  oracle/******** dumpfile=dp:tab.dmp tables=del_history
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ORACLE"."DEL_HISTORY"                      28.50 KB     890 rows
Master table "ORACLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ORACLE.SYS_EXPORT_TABLE_01 is:
  /export/home/oracle/tab.dmp
Job "ORACLE"."SYS_EXPORT_TABLE_01" successfully completed at 16:11:41


SQL>  !expdp oracle/espsvcsoracle directory=dp dumpfile=dp:tab1.dmp tables=del_history

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 09 August, 2007 16:12:44

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "ORACLE"."SYS_EXPORT_TABLE_01":  oracle/******** directory=dp dumpfile=dp:tab1.dmp tables=del_history
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ORACLE"."DEL_HISTORY"                      28.50 KB     890 rows
Master table "ORACLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ORACLE.SYS_EXPORT_TABLE_01 is:
  /export/home/oracle/tab1.dmp
Job "ORACLE"."SYS_EXPORT_TABLE_01" successfully completed at 16:13:02


SQL>  !expdp oracle/espsvcsoracle directory=dp dumpfile=tab2.dmp  tables=del_history

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 09 August, 2007 16:13:35

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "ORACLE"."SYS_EXPORT_TABLE_01":  oracle/******** directory=dp dumpfile=tab2.dmp tables=del_history
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ORACLE"."DEL_HISTORY"                      28.50 KB     890 rows
Master table "ORACLE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ORACLE.SYS_EXPORT_TABLE_01 is:
  /export/home/oracle/tab2.dmp
Job "ORACLE"."SYS_EXPORT_TABLE_01" successfully completed at 16:13:52
Re: Problems with DATAPUMP EXP [message #257929 is a reply to message #257928] Thu, 09 August 2007 11:18 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
VESION=COMPATIBLE


Its VERSION

SQL> !expdp oracle/espsvcsoracle dumpfile=dp:tab0.dmp tables=del_history JOB_NAME=myPUBS VErsion=COMPATIBLE

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 09 August, 2007 16:18:52

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "ORACLE"."MYPUBS":  oracle/******** dumpfile=dp:tab0.dmp tables=del_history JOB_NAME=myPUBS VErsion=COMPATIBLE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ORACLE"."DEL_HISTORY"                      28.50 KB     890 rows
Master table "ORACLE"."MYPUBS" successfully loaded/unloaded
******************************************************************************
Dump file set for ORACLE.MYPUBS is:
  /export/home/oracle/tab0.dmp
Job "ORACLE"."MYPUBS" successfully completed at 16:19:05

[Updated on: Thu, 09 August 2007 11:19]

Report message to a moderator

Re: Problems with DATAPUMP EXP [message #257942 is a reply to message #257893] Thu, 09 August 2007 11:51 Go to previous messageGo to next message
Creems
Messages: 70
Registered: July 2007
Location: Johannesburg
Member
Sorry about that.

SQL> show parameter UTL_FILE;

NAME TYPE VALUE
----------------------- --------------- --------------
utl_file_dir string string

Re: Problems with DATAPUMP EXP [message #257945 is a reply to message #257942] Thu, 09 August 2007 12:00 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Check your Command

Its Not VESION=COMPATIBLE

Its VERSION=COMPATIBLE
Re: Problems with DATAPUMP EXP [message #258559 is a reply to message #257886] Mon, 13 August 2007 01:20 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Hai, in your directory you don't have permission to export file. Give permission in your specified file and directory and perform operation.

from sharing and security give permission to the user.
Re: Problems with DATAPUMP EXP [message #258561 is a reply to message #257886] Mon, 13 August 2007 01:24 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Also you can manually create 'G:\datadumps' directory as OS user.
and then use,

CREATE DIRECTORY dpump_dir AS 'G:\datadumps';
Previous Topic: sqlldr errors
Next Topic: External Table - Wrong line number in error log.
Goto Forum:
  


Current Time: Mon Dec 05 10:42:01 CST 2016

Total time taken to generate the page: 0.07620 seconds