Solved

import using data pump

Posted on 2006-11-08
8
946 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 250 total points
Comment Utility
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
Comment Utility
Thanks
0
 

Expert Comment

by:Lville
Comment Utility
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
 

Author Comment

by:xoxomos
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:Lville
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now