OraFAQ Forum: Server Utilities » error in exp/imp in datapump utility

Home » RDBMS Server » Server Utilities » error in exp/imp in datapump utility

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

error in exp/imp in datapump utility [message #560647]

Mon, 16 July 2012 03:21

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

hello experts,
i get an error when i exp/imp tables of a user to another user through datapump utility. code is-

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 16 13:39:45 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant exp_full_database to scott
2 /

Grant succeeded.

SQL> grant imp_full_database to newuser
2 /

Grant succeeded.

SQL> create directory dir as 'c:/dir4'
2 /

Directory created.

SQL> conn scott/tiger
Connected.
SQL> expdp scott/tiger@localdb tables=emp,dept,bonus,salgrade directory=DIR dum
pfile=SCOTT_INFO.dmp logfile=expdpSCOTT_INFO.log
SP2-0734: unknown command beginning "expdp scot..." - rest of line ignored.

i can't understand what is the error at last line.
your help will highly be appreciated .
thanks in advance....

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560651 is a reply to message #560647]

Mon, 16 July 2012 03:45

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Export data pump is an operating system utility, not a SQL*Plus command. Exit SQL*Plus and run EXPDP command from operating system command prompt.

P.S. Also, note that directory (an Oracle object) must point to a directory (file system) located on the database server. It is NOT your local directory.

[Updated on: Mon, 16 July 2012 03:46]

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560663 is a reply to message #560651]

Mon, 16 July 2012 04:59

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

thanks for your guidance sir,
and i run this query from windows 2007 cmd-

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Neetesh>expdp scott/tiger@localdb tables=emp,dept directory=DIR3 dumpfi
le=emp_datas.dmp logfile=emp_datas.log

Export: Release 10.2.0.1.0 - Production on Monday, 16 July, 2012 15:10:07

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCOTT.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-20101: you can not create it

sir, now what is the problem here . is there anything is missing from my side?
thanks again....

C:\Users\Neetesh>

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560664 is a reply to message #560663]

Mon, 16 July 2012 05:02

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Can't tell for sure, but - you didn't grant any privileges on the directory object to SCOTT, such as

grant read, write on directory dir to scott;

Besides, you created a directory named "dir", but in EXPDP you used "directory=dir3" - that's not going to work.

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560668 is a reply to message #560664]

Mon, 16 July 2012 05:18

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

User must have the CREATE TABLE privilege.

Regards
Michel

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560671 is a reply to message #560668]

Mon, 16 July 2012 05:32

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

thanks to littlefoot and michel sir to provide more knowledge on exp/imp like-user should have create table privilege.

but i have tried again like-

sql*plus--

SQL> show user
USER is "SYS"

SQL> create user john identified by flair22
2 /

User created.

SQL> grant create session to john
2 /

Grant succeeded.

SQL> grant create table to john
2 /

Grant succeeded.

SQL> grant exp_full_database to scott
2 /

Grant succeeded.

SQL> grant imp_full_database to john
2 /

Grant succeeded.

SQL> create directory exp_dir as 'd:/exp_imp.dir'
2 /

Directory created.

SQL> grant read,write on directory EXP_DIR to scott
2 /

Grant succeeded.

SQL> grant read,write on directory EXP_DIR to john
2 /

Grant succeeded.

SQL>

on command prompt--

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Neetesh>expdp scott/tiger@localdb tables=emp,dept directory=EXP_DIR dum
pfile=local_data.dmp logfile=local_data.log

Export: Release 10.2.0.1.0 - Production on Monday, 16 July, 2012 15:50:04

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCOTT.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-20101: you can not create it

C:\Users\Neetesh>

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560672 is a reply to message #560671]

Mon, 16 July 2012 05:42

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

See if ORA-31633: unable to create master table helps.

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560674 is a reply to message #560672]

Mon, 16 July 2012 05:54

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Also it is a good practice to assign a default tablespace to your account and to explicitly give it some quota (see CREATE USER and ALTER USER statements).

Regards
Michel

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560675 is a reply to message #560674]

Mon, 16 July 2012 06:15

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

thank u very much sir, i have read that link and-

See if ORA-31633: unable to create master table helps.

SQL> select table_name from dba_tables where table_name like 'table\_05' escape '\'
2 /

no rows selected

SQL> select job_name, owner_name,operation ,job_mode,state,attached_sessions fro
m dba_datapump_jobs where job_name not like 'bin$%' order by 1,2
2 /

no rows selected

SQL>

but at last same result.

i think is there may be issue in

" Export: Release 10.2.0.1.0 - Production on Monday, 16 July, 2012 15:10:07

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit "

may be version compatibility problem

thank you very much .........

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560676 is a reply to message #560674]

Mon, 16 July 2012 06:22

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

Michel Cadot wrote on Mon, 16 July 2012 05:54

Also it is a good practice to assign a default tablespace to your account and to explicitly give it some quota (see CREATE USER and ALTER USER statements).

Regards
Michel

sir, but problem is in export time and it is being done from scott's schema, issue of tablespace quota may arise during import(in new user) i think.

thank you very much sir.....

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560678 is a reply to message #560676]

Mon, 16 July 2012 06:33

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No, with Data Pump it is ALSO an issue at export time because Data Pump needs to create a table to describe the work workers have to do.

Regards
Michel

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560680 is a reply to message #560678]

Mon, 16 July 2012 06:53

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

Michel Cadot wrote on Mon, 16 July 2012 06:33

No, with Data Pump it is ALSO an issue at export time because Data Pump needs to create a table to describe the work workers have to do.

Regards
Michel

sir,no effect .same result after giving quota on default tablespace--

SQL>ALTER USER JOHN
DEFAULT TABLESPACE "CDOC_DATA"
QUOTA 10M ON "CDOC_DATA";
2 /

user altered.

on cmd--

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Neetesh>expdp scott/tiger@localdb directory=DIR dumpfile=db_data.dmp logfile=db_data.log

Export: Release 10.2.0.1.0 - Production on Monday, 16 July, 2012 17:21:45

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCOTT.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-20101: you can not create it

C:\Users\Neetesh>

but sir no effect in result.

thanks again.....

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560690 is a reply to message #560680]

Mon, 16 July 2012 07:57

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Check the following and post it as I do:

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Lun. Juil. 16 14:44:45 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

?=?/? ?> @c
Connected.
SQL> drop user test cascade;

User dropped.

SQL> create user test identified by test
  2  default tablespace ts_d01 quota unlimited on ts_d01;

User created.

SQL> grant create session, create table to test;

Grant succeeded.

SQL> grant exp_full_database to test;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

C:\>expdp test/test dumpfile=test.dmp schemas=scott

Export: Release 10.2.0.4.0 - Production on Lundi, 16 Juillet, 2012 14:45:57

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** dumpfile=test.dmp schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.671 KB       4 rows
. . exported "SCOTT"."EMP"                               7.835 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  C:\ORACLE\ORA1024\RDBMS\LOG\TEST.DMP
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:51:15

Regards
Michel

[Edit: Remove one useless statement execution]

[Updated on: Mon, 16 July 2012 10:24]

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560709 is a reply to message #560690]

Mon, 16 July 2012 08:52

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

thank you so much sir,
and i followed your instruction, then

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Neetesh>expdp john/flair22@localdb dumpfile=info_data.dmp schemas=scott

Export: Release 10.2.0.1.0 - Production on Monday, 16 July, 2012 18:53:50

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JOHN"."SYS_EXPORT_SCHEMA_01": john/********@localdb dumpfile=info_dat
a.dmp schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.937 KB 4 rows
. . exported "SCOTT"."EE" 5.007 KB 1 rows
. . exported "SCOTT"."EMP" 8.570 KB 14 rows
. . exported "SCOTT"."P_S_EMP2" 5.015 KB 1 rows
. . exported "SCOTT"."SALGRADE" 5.867 KB 5 rows
. . exported "SCOTT"."TEMP_DEPT" 5.945 KB 4 rows
. . exported "SCOTT"."TEMP_EMP" 8.578 KB 14 rows
. . exported "SCOTT"."TESTY" 5.507 KB 12 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."TEST1234321" 0 KB 0 rows
. . exported "SCOTT"."TEST321" 0 KB 0 rows
Master table "JOHN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JOHN.SYS_EXPORT_SCHEMA_01 is:
D:\APP\NEETESH\ADMIN\LOCALDB\DPDUMP\INFO_DATA.DMP
Job "JOHN"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:54:36

C:\Users\Neetesh>impdp john/flair22@localdb dumpfile=info_data.dmp schemas=scott

Import: Release 10.2.0.1.0 - Production on Monday, 16 July, 2012 19:15:01

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39145: directory object parameter must be specified and non-null

but at time of import the schema , its giving an error,then how to import it in john's schema

thanks agin sir.......

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560716 is a reply to message #560709]

Mon, 16 July 2012 09:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>ORA-39145: directory object parameter must be specified and non-null

bcm@bcm-laptop:~$ oerr ora 39145
39145, 00000, "directory object parameter must be specified and non-null"
// *Cause:  No directory object was provided in either an DBMS_DATAPUMP.ADD_FILE
//          API call or to the directory parameter used by the Data Pump
//          command line clients.
// *Action: Correct the directory object parameter and retry the operation.
//

Report message to a moderator

Re: error in exp/imp in datapump utility [message #560718 is a reply to message #560716]

Mon, 16 July 2012 09:06

neetesh87
Messages: 280
Registered: September 2011
Location: bhopal

Senior Member

ok.

thanks a lot sir.

Report message to a moderator

Previous Topic:	problem to import table data
Next Topic:	Export Multiple tables only

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:37:58 CST 2025