Solved

Import feature in Pl/Sql Developer

Posted on 2010-08-16
5
3,383 Views
Last Modified: 2013-12-19

I am new using Import/Export features in Oracle. Right now, am using Pl/Sql Developer tool. I was working a dummy table to learn the Import feature in this tool.

I understood that "Import Tables"  load data into the same table whatever the source table is..

Eg: I have created .dmp file exporting the data from Test_001 from schema A. I logged into schema B and then used the .dmp file which I exported for importing the data. It loaded the data in the same Test_001 table in Schema B.

Can I use the same .dmp file generated using Oracle export for loading into some table 'X'.. i.e not the table which has the same tablename from where .dmp got generated..

I tried that in Pl/Sql developer tool but it is not giving me option to load the .dmp file into a particular table..

Can someone guide me
0
Comment
Question by:meera78
[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
5 Comments
 
LVL 2

Expert Comment

by:yianniscy84
ID: 33449782
No you can't. In the dump file there is the table definition that defines explicitly the table name.
0
 
LVL 7

Expert Comment

by:sumit2906
ID: 33451259
you can try this:
create a view with name Test_001 on table Test_002 in schema B. also make sure that view has same aliases for column names available in export dump table.
now run import with ignore=y option. never tried this, let me know if this works.
0
 
LVL 3

Expert Comment

by:P2O
ID: 33456578
Import/Export (.dmp ) is used by DBA, if you are developer you can use .dmp file to load data, but not through PL/SQL you have to use sqlloader to load the data.
If you are developer and if you want to read flat file then PL/SQL provides UTL_FILE option and SQL gives you external table option.
Read it here : http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch12.htm

0
 
LVL 2

Accepted Solution

by:
j_coreil earned 250 total points
ID: 33460117
Another option is to create a DB Link between the two databases at the schema level to get the data across.

Once the DB Link is created, just do the following on the destination database:

create table <table_name> as Select * from SchemaA.<table_name>@<source database>;

This method will allow you to change the table name in the process as well if you want to "import" the data into a different schema or table than the dump file would create.
0
 

Author Closing Comment

by:meera78
ID: 33504377

Good for practice!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create file system directory from Oracle 10g 4 33
format dd/mm/yyyy parameter 16 49
Migration from SQL server to oracle (XML input) 4 55
pl/sql parameter is null sometimes 2 26
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 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