Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

import using data pump

Posted on 2006-11-08
8
Medium Priority
?
961 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

722 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