OraFAQ Forum: Server Utilities » Problems with DATAPUMP EXP

Home » RDBMS Server » Server Utilities » Problems with DATAPUMP EXP

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problems with DATAPUMP EXP [message #257886]

Thu, 09 August 2007 07:34

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

Report message to a moderator

Re: Problems with DATAPUMP EXP [message #257893 is a reply to message #257886]

Thu, 09 August 2007 07:45

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

Report message to a moderator

Re: Problems with DATAPUMP EXP [message #257925 is a reply to message #257893]

Thu, 09 August 2007 11:05

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

Report message to a moderator

Re: Problems with DATAPUMP EXP [message #257928 is a reply to message #257925]

Thu, 09 August 2007 11:14

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

Report message to a moderator

Re: Problems with DATAPUMP EXP [message #257929 is a reply to message #257928]

Thu, 09 August 2007 11:18

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

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

Report message to a moderator

Re: Problems with DATAPUMP EXP [message #257945 is a reply to message #257942]

Thu, 09 August 2007 12:00

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Check your Command

Its Not VESION=COMPATIBLE

Its VERSION=COMPATIBLE

Report message to a moderator

Re: Problems with DATAPUMP EXP [message #258559 is a reply to message #257886]

Mon, 13 August 2007 01:20

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.

Report message to a moderator

Re: Problems with DATAPUMP EXP [message #258561 is a reply to message #257886]

Mon, 13 August 2007 01:24

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';

Report message to a moderator

Previous Topic:	sqlldr errors
Next Topic:	External Table - Wrong line number in error log.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 22 21:57:11 CDT 2024