| Import data from dat file to table [message #63598] |
Tue, 19 October 2004 21:24  |
Smita
Messages: 10
Registered: July 2002
|
Junior Member |
|
|
Hello,
I have exported the oracle table data using the exp utility from oracle 9i Db into a .dmp file as
exp parfile=$datPath/exp_TRAcER_TrackMaster_stage.dat
and the dat file content this information
file=psc_msgflowtrack_master.dmp
tables=(PSC_MSGFLOWTRACKMASTER)
query="WHERE MSGFLOWTRACKID in (Select MSGFLOWTRACKID from PSC_MSGFLOWARCHIVEINFO)"
log=TRAcER_TrackMaster_stage.log
userid=tibdev/tibdev01
After export i have deleted the exported table data from PSC_MSGFLOWTRACKMASTER.
Now from .dmp file i want to import the only the exported data back to table. Can any one suggest me/ guide me how to do it using imp utility of oracle.
Thanks in advance.
Smita
C
|
|
|
|
|
|
| Re: Import data from dat file to table [message #63601 is a reply to message #63599] |
Tue, 19 October 2004 22:45  |
Smita
Messages: 10
Registered: July 2002
|
Junior Member |
|
|
thanks deepak for ur reply.
But i want to use the .dmp file which i have got while exporting the table data for import purpose.
B'coz i will be deleteing the the exported data from table and in future if i need i want to load the exported data back to table using import utility in the same DB table with same user.
Can You tell me how to achive this?
Thanks in advance for your valuable input.
Smita
|
|
|
|
| Re: Import data from dat file to table [message #63603 is a reply to message #63601] |
Tue, 19 October 2004 23:17 |
Deepak Panda
Messages: 24
Registered: May 2004
|
Junior Member |
|
|
Hi Smita,
I couldnot understand properly, but what I could figure out that, you wanted to keep the existing table data in a dump file as a backup and you like to know what is to be done if you want to restore the data into the same shema.
Well there are several cases, like
1> if you have rows in the table and you want to do the import from the dump file then only those rows will be imported which does not violate the primary key constraint.
2> If the table does not have a primary key then all the rows from the dump will be imported.
3> If you want to restore the original table then simply truncate the table and do the import.
Hope this helps. If your requirement is something different kindly mention it again.
Regards
Deepak
|
|
|
|
| Re: Import data from dat file to table [message #63604 is a reply to message #63603] |
Wed, 20 October 2004 03:39 |
Smita
Messages: 10
Registered: July 2002
|
Junior Member |
|
|
Deepak,
yes you are right that i would like to restore the data back to same schema.
In my case what you explained above in case 1. will be. I will be having some rows in the table but not same record. so there won't be any chance for constraint violations.
can you guide me what will be the command for import from .dmp file???
One more thing i want to clarify.
When i exported data from table to .dmp file. I can see that data got exported in a binary form with create table, alter table and constraints and insert query in it with record.
Can you tell me when i do import from .dmp file, will the import utility will try to create a table again if table already exist?? B'coz i can see the create table query as well in the .dmp file. or it will simply escape the create query and execute insert query from the .dmp file?
Your valuable input is very helpful for me.
Waiting for your early response.
Thanks,
Smita
|
|
|
|
| Re: Import data from dat file to table [message #63612 is a reply to message #63604] |
Wed, 20 October 2004 09:33 |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
When i exported data from table to .dmp file. I can see that data got exported in a binary form with create table, alter table and constraints and insert query in it with record.
Can you tell me when i do import from .dmp file, will the import utility will try to create a table again if table already exist?? B'coz i can see the create table query as well in the .dmp file. or it will simply escape the create query and execute insert query from the .dmp file?
Answer:
you will need to use import as follows:
imp user/pwd file=xx.dmp IGNORE=Y
so it will ignore to re-create table.
If you dont specify that, import will fail cancel without insert rows.
Besides, be aware that if no primary keys exists on table, most probably that import will duplicate rows if table already have rows loaded.
Best luck.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
