Home » RDBMS Server » Server Utilities » import the table
import the table [message #561169] Thu, 19 July 2012 04:49 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello experts,
there is problem to import a schema table from one database to another database. i have done export successfully, but problem in importing it to a user.

cmd--

C:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 14:04:27 2012

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

Enter user-name: sys@testdb 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> create user test_user identified by flair22 default tablespace small_tbl quota 10m on small_tbl
2 /

User created.

SQL> grant connect, create session to test_user
2 /

Grant succeeded.

SQL> grant imp_full_database to test_user
2 /

Grant succeeded.

SQL> conn sys@chkdb as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock
2 /

User altered.

SQL> grant exp_full_database to scott
2 /

Grant succeeded.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>expdp scott/tiger@chkdb dumpfile=scott_tbls.dmp tables=dept,emp

Export: Release 10.2.0.1.0 - Production on Thursday, 19 July, 2012 14:11:14

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@chkdb dumpfile=scott_
tbls.dmp tables=dept,emp
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/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 6.164 KB 17 rows
. . exported "SCOTT"."EMP" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
C:\APP\ADMINISTRATOR\ADMIN\chkdb\DPDUMP\SCOTT_TBLS.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:19:09


C:\>impdp test_user/flair22@testdb dumpfile=scott_tbls.dmp tables=emp,dept

Import: Release 10.2.0.1.0 - Production on Thursday, 19 July, 2012 14:14:30

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-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "C:\app\Administrator/admin/testdb/dpdump/
scott_tbls.dmp" for read
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.



C:\>

what is the problem in importing the table.
thanx in advance....
Re: import the table [message #561172 is a reply to message #561169] Thu, 19 July 2012 04:54 Go to previous messageGo to next message
Littlefoot
Messages: 21595
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Data pump documentation. I believe you "forgot" the directory (an Oracle object).
Re: import the table [message #561173 is a reply to message #561169] Thu, 19 July 2012 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The message is clear there is no file "scott_tbls.dmp" in directory "C:\app\Administrator/admin/testdb/dpdump/".
Check and fix your DATA_PUMP_DIR (in dba_directories) or create your own Oracle directory and specify it in impdp command.

Regards
Michel
Re: import the table [message #561179 is a reply to message #561173] Thu, 19 July 2012 05:33 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thank u so much sir, for your kind response,
and i tried that u said, to create your own directory like that-

C:\Users\Neetesh>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 15:45:35 2012

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

Enter user-name: sys@testdb 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> create user test_user identified by flair22
2 /

User created.


SQL> alter user test_user default tablespace small_tbl quota 10m on small_tbl
2 /

User altered.

SQL> grant imp_full_database to test_user
2 /

Grant succeeded.

SQL> grant connect, create session to test_user
2 /

Grant succeeded.

SQL> conn sys@chkdb as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock
2 /

User altered.

SQL> grant exp_full_database to scott
2 /

Grant succeeded.

SQL> create directory exp_imp_filedir as 'd:/tbldata'
2 /

Directory created.

SQL> grant read,write on directory EXP_IMP_FILEDIR TO SCOTT
2 /

Grant succeeded.

SQL> EXIT;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Neetesh>expdp scott/tiger@chkdb tables=emp,dept directory=EXP_IMP_FILEDIR dumpfile=exp_imp_tbls.dmp

Export: Release 10.2.0.1.0 - Production on Thursday, 19 July, 2012 15:53:28

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.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-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation



C:\Users\Neetesh>

there is something wrong , but dont know what is that..
thanks again....
Re: import the table [message #561180 is a reply to message #561179] Thu, 19 July 2012 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does 'd:/tbldata' exist?
Note that in Windows you have to use backslash \ and not forward slash /
Isn't this an answer Littlefoot gave you in another topic?

Regards
Michel
Re: import the table [message #561181 is a reply to message #561180] Thu, 19 July 2012 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 21595
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, pay attention to the fact that directory (file system) is located on a database server, not your PC (unless you have installed that database on your PC, of course - then these are the same machines).
Re: import the table [message #561185 is a reply to message #561180] Thu, 19 July 2012 06:10 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
Michel Cadot wrote on Thu, 19 July 2012 05:38
Does 'd:/tbldata' exist?
Note that in Windows you have to use backslash \ and not forward slash /


thanks sir, for your help and i also tried the existence of directory on disk like that-

C:\Users\Neetesh>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 16:31:24 2012

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

Enter user-name: sys@aepqafcg as sysdba
Enter password:

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

SQL> create directory mydir as 'd:\dirdata'
2 /

Directory created.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Neetesh>d:

D:\>dir /p
Volume in drive D is New Volume
Volume Serial Number is 744F-88A0

Directory of D:\

05/23/2012 05:08 PM <DIR> app
05/23/2012 03:44 PM <DIR> DataBase_Diff
07/18/2012 05:14 PM 422 function.plb
07/18/2012 05:12 PM 284 function.txt
07/06/2012 08:21 PM <DIR> groove backup
07/11/2012 12:48 PM <DIR> jdk
12/01/2006 11:37 PM 904,704 msdia80.dll
07/18/2012 04:46 PM <DIR> my local oracle notes
05/23/2012 03:37 PM <DIR> neetesh
05/23/2012 05:26 PM <DIR> oracle
07/11/2012 04:53 PM <DIR> passwords
05/28/2012 01:51 PM <DIR> Program Files
07/16/2012 04:59 PM <DIR> set ups
06/03/2010 05:55 PM 22,971,688 SkypeSetupFull.exe
05/23/2012 05:49 PM <DIR> software
05/21/2012 05:19 PM <DIR> softwares
05/23/2012 03:45 PM <DIR> TEMP
05/08/2012 04:14 PM <DIR> toad
07/16/2012 02:11 PM <DIR> work
4 File(s) 23,877,098 bytes
15 Dir(s) 175,605,829,632 bytes free

D:\>


i think directory is not being saved on os. so there is problem in detecting by oracle.

Michel Cadot wrote on Thu, 19 July 2012 05:38

Isn't this an answer Littlefoot gave you in another topic?


sir, i followed littlefoot's instruction, then i tried to done exp using directory.

thanks sir...


Re: import the table [message #561186 is a reply to message #561181] Thu, 19 July 2012 06:16 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
Littlefoot wrote on Thu, 19 July 2012 05:48
Also, pay attention to the fact that directory (file system) is located on a database server, not your PC (unless you have installed that database on your PC, of course - then these are the same machines).


thanks alot sir.
actully these database are on another pc , so the directory location is not on my pc, thanks again.
then can i exp/imp from my pc or not?
if yes,then what will be change in expdp/impdp statement as point of view of directory.

sql>expdp scott/tiger@chkdb tables=dept,emp directory=FILE_DIR dumpfile=exprt.dmp
sql>impdp test_user/flair22@testdb tables=dept,emp directory=FILE_DIR dumpfile=exprt.dmp

thanks again.......


Re: import the table [message #561188 is a reply to message #561185] Thu, 19 July 2012 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i think directory is not being saved on os.


You confuse Oracle directory and OS directory.
An Oracle directory is just a named pointer to an OS directory.
When you create an Oracle directory you do NOT create an OS directory you just create a pointer to this OS directory, pointer which has the name of the Oracle directory.
So you must first create the OS directory, then the Oracle directory object pointing to this OS directory.

Regards
Michel

[Updated on: Thu, 19 July 2012 06:25]

Report message to a moderator

Re: import the table [message #561189 is a reply to message #561186] Thu, 19 July 2012 06:25 Go to previous messageGo to next message
Littlefoot
Messages: 21595
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Once again: directory (file system directory, the one you create from DOS prompt or Windows Explorer) must reside on a computer that runs the database (i.e. the database server). You said that it is not your PC, which means that either you (if you have access) or someone who can connect to that computer should create a directory.

Then, connected to the database as a privileged user, you have to create a directory (Oracle database object) which points to a directory you previously created on the database server.

The next step is to grant privileges (usually READ and WRITE) to user who is going to use that directory.

Finally, in your data pump export/import, you need to use the above Oracle directory object.
Re: import the table [message #561190 is a reply to message #561186] Thu, 19 July 2012 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then can i exp/imp from my pc or not?
if yes,then what will be change in expdp/impdp statement as point of view of directory.


You can but the (OS) directory must reside on the database server.

Regards
Michel
Re: import the table [message #561193 is a reply to message #561190] Thu, 19 July 2012 06:45 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks littlefoot and michel sir,
yes, directory is being saved on my database server,then what should be the changes in exp/imp statement

sql>expdp scott/tiger@chkdb tables=dept,emp directory=FILE_DIR dumpfile=exprt.dmp
sql>impdp test_user/flair22@testdb tables=dept,emp directory=FILE_DIR dumpfile=exprt.dmp

if i run these statement from the command prompt of my pc because after executing these statement errors are occured like that-

C:\Users\Neetesh>expdp scott/tiger@chkdb tables=emp,dept directory=EXP_IMP_FILEDIR dumpfile=exp_imp_tbls.dmp

Export: Release 10.2.0.1.0 - Production on Thursday, 19 July, 2012 15:53:28

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.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-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation

or i have to run these commands from the server( i think it will not the solution of it).

thanks again...
Re: import the table [message #561196 is a reply to message #561193] Thu, 19 July 2012 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SCOTT must have the privilege to read and write the Oracle directory:
GRANT read, write ON DIRECTORY <directory name> TO scott;

Note that you once use:
expdp ... directory=FILE_DIR ...
and the other one:
expdp ... directory=EXP_IMP_FILEDIR
which one is true?

Regards
Michel

Re: import the table [message #561197 is a reply to message #561196] Thu, 19 July 2012 07:05 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
Michel Cadot wrote on Thu, 19 July 2012 06:49

expdp ... directory=EXP_IMP_FILEDIR
which one is true?



thanks sir,
actully i have made so many directories , but i deleted all of them .and i created new directory named EXP_IMP_FILEDIR, now i am using EXP_IMP_FILEDIR .
but oracle gives errors when i execute exp/imp statement from my local system cmd to find that directory located on server. is there no need to change anything in my statements, although i have gave read,write privilege to exported tables owner.it gave errors....


thanks again.....
Re: import the table [message #561199 is a reply to message #561197] Thu, 19 July 2012 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you missed something but as you posted many things that is not what it actually is nor what you actually does it is now impossible to diagnose.
Delete everything and restart from the beginning.
And copy and paste ALL what you do and get, do NOT interpret.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: import the table [message #561272 is a reply to message #561169] Fri, 20 July 2012 01:48 Go to previous message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
ok thanks sir,
i will follow the forums guidelines.
and i start the exp/imp process from the beginning again.

thanks again.......
Previous Topic: Export Multiple tables only
Next Topic: Field in data file exceeds maximum length
Goto Forum:
  


Current Time: Sun Sep 27 13:09:39 CDT 2020