Home » RDBMS Server » Server Utilities » import the table
| import the table [message #561169] |
Thu, 19 July 2012 04:49  |
 |
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 #561179 is a reply to message #561173] |
Thu, 19 July 2012 05:33  |
|
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 #561185 is a reply to message #561180] |
Thu, 19 July 2012 06:10 |
|
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 19 July 2012 05:38Does '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 |
|
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
|
Senior Member |
|
|
Littlefoot wrote on Thu, 19 July 2012 05:48Also, 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 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #561193 is a reply to message #561190] |
Thu, 19 July 2012 06:45 |
|
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 #561272 is a reply to message #561169] |
Fri, 20 July 2012 01:48 |
|
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.......
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 16:54:55 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
