Home » RDBMS Server » Server Utilities » Import dump file without 2 tables (10.2.0.4.0 , SunOS 5.8)
Import dump file without 2 tables [message #537972] Tue, 03 January 2012 04:23 Go to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

I want to import dump file (without 2 tables) .The dump file contains 100 tables,indexes and constraints.
So out of 100 tables i want to import 98 tables from dump file (without 2 tables).

Can you guys provide me the step to perform this import?

I hope my question is clear to all.
Re: Import dump file without 2 tables [message #537973 is a reply to message #537972] Tue, 03 January 2012 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
List the tables you want.
And yes this is the only (supported) way.
Too bad you didn't use Data Pump.

Export and import questions should be in "Server Utilities" forum.

Regards
Michel

[Updated on: Tue, 03 January 2012 04:29]

Report message to a moderator

Re: Import dump file without 2 tables [message #537976 is a reply to message #537973] Tue, 03 January 2012 04:33 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Quote:
And yes, you posted once more in the wrong forum. Export and import questions should be in "Server Utilities" forum.


Sorry Michel,By mistake i posted export/import questions in Server administration instead of server utilities.

Should i post the same question in server utilities forum?





[Updated on: Tue, 03 January 2012 04:33]

Report message to a moderator

Re: Import dump file without 2 tables [message #537981 is a reply to message #537976] Tue, 03 January 2012 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is now in the correct forum, I moved it.

Regards
Michel
Re: Import dump file without 2 tables [message #537986 is a reply to message #537981] Tue, 03 January 2012 05:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps it would be simpler if you imported all 100 tables and then drop 2 tables you don't need.
Re: Import dump file without 2 tables [message #538090 is a reply to message #537986] Tue, 03 January 2012 23:45 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Quote:
Perhaps it would be simpler if you imported all 100 tables and then drop 2 tables you don't need.


Little foot,

Those 2 tables should not be dropped at any cost in target database because the tables
plays an important role in database connection string .

[Updated on: Tue, 03 January 2012 23:47]

Report message to a moderator

Re: Import dump file without 2 tables [message #538092 is a reply to message #538090] Wed, 04 January 2012 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see; didn't know that target schema isn't empty.

However, if you might try to rename these tables in the target schema, import 100 tables, drop 2 you don't need, rename those 2 tables back to their original names.

Or, if these 2 tables contain the same data set (no differences at all) and contain unique/primary keys, import all tables (100 of them) - these 2 will fail to import anyway because of violated uniqueness.
Re: Import dump file without 2 tables [message #538125 is a reply to message #538092] Wed, 04 January 2012 03:24 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Quote:
Or, if these 2 tables contain the same data set (no differences at all) and contain unique/primary keys, import all tables (100 of them) - these 2 will fail to import anyway because of violated uniqueness.


Little foot,

The count for below 2 tables in target schema is

PAR_TAB -- 7000
USER_TAB -- 2000

Based on above result , i would say the data set is not same in both the tables.

I used the following query and found constraint type returned with 'C'.Therefore it means there are no unique and primary keys in tables.

select * from user_constraints where table_name in ('USER_TAB','PAR_TAB') and owner='RIMSPO'


Could you please confirm my findings are correct ?


If my findings are correct, then i have to follow the first step of yours.

Thank you

Re: Import dump file without 2 tables [message #538126 is a reply to message #538125] Wed, 04 January 2012 03:30 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
The count for below 2 tables in target schema is

PAR_TAB -- 7000
USER_TAB -- 2000

Based on above result , i would say the data set is not same in both the tables.

Obviously, 7000 <> 2000.

However, I'm afraid you misunderstood what I said. You should have checked
table name     source schema      target schema
----------     -------------      -------------
par_tab        number_A           7000
user_tab       number_B           2000
What I meant was: if number_A = 7000 and number_B = 2000 then it is *possible* that these tables contain the same data set (obviously, the fact that there are 7000 records in both tables doesn't mean that they are equal - you should check it first).

Anyway, as there are no unique/primary keys/indexes there, you should probably NOT import all 100 tables as these two (PAR_TAB and USER_TAB) might, at the end, contain unexpected records.
Re: Import dump file without 2 tables [message #538129 is a reply to message #538126] Wed, 04 January 2012 03:52 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member

Sorry for the confusion!!

Below is the actual count of Source and Target schema's.

Source Schema
---------------------
PAR_PARAMETER_TAB -- 6921

USER_PREF_TAB -- 2020


Target schema
---------------
PAR_PARAMETER_TAB -- 6926

USER_PREF_TAB -- 1671


Based on above result, please tell me whether tables contain the same data set ?


Re: Import dump file without 2 tables [message #538130 is a reply to message #538129] Wed, 04 January 2012 04:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you think?
Re: Import dump file without 2 tables [message #538131 is a reply to message #538130] Wed, 04 January 2012 04:32 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member

I think table does not contain same data set.
Re: Import dump file without 2 tables [message #538133 is a reply to message #538131] Wed, 04 January 2012 04:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I agree.
Re: Import dump file without 2 tables [message #538135 is a reply to message #538133] Wed, 04 January 2012 04:53 Go to previous message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Quote:
Anyway, as there are no unique/primary keys/indexes there, you should probably NOT import all 100 tables as these two (PAR_TAB and USER_TAB) might, at the end, contain unexpected records.


Adding to your above notes, we should probably NOT import all 100 tables as these two tables at the end would contain unexpected errors.

Hence follow the below note of yours

Quote:
try to rename these tables in the target schema, import 100 tables, (Should not drop tables instead rename them) , rename those 2 tables back to their original names.


@Littlefoot
Thank you for your wonderful support and suggestions.
Previous Topic: Datapump: importing check constraints
Next Topic: export fails
Goto Forum:
  


Current Time: Thu Mar 28 14:57:35 CDT 2024