Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

import using data pump

Posted on 2006-11-08
8
Medium Priority
?
964 Views
Last Modified: 2008-01-09
I've used expdp to export a schema on server a.  Now i want to use impdp to import onto another machine to duplicate the schema.  Do i need to first create the tablespace, user etc before i import or will that be done from the .dmp file created?
0
Comment
Question by:xoxomos
  • 4
  • 3
8 Comments
 
LVL 13

Accepted Solution

by:
anand_2000v earned 1000 total points
ID: 17904076
Yes follow the same produres wat you have followed before importing using imp utility.

In this case,you need to create the user allocated the user with a tablespace.

ex :
 
expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Refer,
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php
0
 

Author Comment

by:xoxomos
ID: 17908772
Thanks
0
 

Expert Comment

by:Lville
ID: 20151724
Hi,
 
I was trying to do a similer job using expdp/impdp. Instead of a schema, I was trying to copy EMP table from  database A to  database B. I have created a new user in database B with it's own tablespace. Also, the user has dba priviledge. Please note, I was able to export successfully. However, while doing impdp I am getting the following error:

Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'USERS' does not exist

Therefore, please advise a possible solution to this error.

Thank you.




0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:xoxomos
ID: 20151817
ORA-00959: tablespace 'USERS' does not exist

I'd say on the instance from whence you exported, USERS was the name of the tablespace assigned to the  user that owned the table.  I suspect you need to modify the CREATE TABLE to specifically name the new tablespace you've created.
0
 

Expert Comment

by:Lville
ID: 20153171
Hi,
On my source database (A), from where I am exporting table EMP the user's default tbsp is USERS. You are correct.
On the other hand, on my target database (B) where I am importing table EMP to, the user has it's own tablespace (tbsp0) and it was already specified during user creation. I did not create any additional table. So, please clarify which CREATE TABLE you are reffering to modify?
0
 

Expert Comment

by:Lville
ID: 20153172
Hi,
On my source database (A), from where I am exporting table EMP the user's default tbsp is USERS. You are correct.
On the other hand, on my target database (B) where I am importing table EMP to, the user has it's own tablespace (tbsp0) and it was already specified during user creation. I did not create any additional table. So, please clarify which CREATE TABLE you are reffering to modify?
0
 

Author Comment

by:xoxomos
ID: 20157107
When you import, part of the import is CREATE TABLE and it will specify the tablespace where the table resided  on the instance from which the export was made.  I believe if you do an import (original) with the parameter show=y you will see the DDL statements.  You can make changes there an just run from SQL prompt or script.  If you are using datapump import/export it is a different parm which escapes me right now.
0
 

Author Comment

by:xoxomos
ID: 20157155
Response from expert of equivalent of show=y when using datapump

schwertner:
The answer is YES. See the SQLFILE parameter.

SQLFILE
Default: none
Purpose
Specifies a file into which all of the SQL DDL that Import would have executed, based
on other parameters, is written.
Syntax and Description
SQLFILE=[directory_object:]file_name
The file_name specifies where the import job will write the DDL that would be
executed during the job. The SQL is not actually executed, and the target system
remains unchanged. The file is written to the directory object specified in the
DIRECTORY parameter, unless another directory_object is explicitly specified
here. Any existing file that has a name matching the one specified with this parameter
is overwritten.
Note that passwords are not included in the SQL file. For example, if a CONNECT
statement is part of the DDL that was executed, it will be replaced by a comment with
only the schema name shown. In the following example, the dashes indicate that a
comment follows, and the hr schema name is shown, but not the password.
-- CONNECT hr
Therefore, before you can execute the SQL file, you must edit it by removing the
dashes indicating a comment and adding the password for the hr schema (in this case,
the password is also hr), as follows:
CONNECT hr/hr
For Streams and other Oracle database options, anonymous PL/SQL blocks may
appear within the SQLFILE output. They should not be executed directly.
Example
The following is an example of using the SQLFILE parameter. You can create the
expfull.dmp dump file used in this example by running the example provided for
the Export FULL parameter. See FULL on page 2-17.
> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp
SQLFILE=dpump_dir2:expfull.sql
A SQL file named expfull.sql is written to dpump_dir2.
back to top

   
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question