OraFAQ Forum: Server Utilities » Import dump file without 2 tables

Home » RDBMS Server » Server Utilities » Import dump file without 2 tables (10.2.0.4.0 , SunOS 5.8)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Import dump file without 2 tables [message #537972]

Tue, 03 January 2012 04:23

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.

Report message to a moderator

Re: Import dump file without 2 tables [message #537973 is a reply to message #537972]

Tue, 03 January 2012 04:27

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is now in the correct forum, I moved it.

Regards
Michel

Report message to a moderator

Re: Import dump file without 2 tables [message #537986 is a reply to message #537981]

Tue, 03 January 2012 05:01

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Import dump file without 2 tables [message #538090 is a reply to message #537986]

Tue, 03 January 2012 23:45

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Import dump file without 2 tables [message #538125 is a reply to message #538092]

Wed, 04 January 2012 03:24

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

Report message to a moderator

Re: Import dump file without 2 tables [message #538126 is a reply to message #538125]

Wed, 04 January 2012 03:30

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Import dump file without 2 tables [message #538129 is a reply to message #538126]

Wed, 04 January 2012 03:52

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 ?

Report message to a moderator

Re: Import dump file without 2 tables [message #538130 is a reply to message #538129]

Wed, 04 January 2012 04:01

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What do you think?

Report message to a moderator

Re: Import dump file without 2 tables [message #538131 is a reply to message #538130]

Wed, 04 January 2012 04:32

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

I think table does not contain same data set.

Report message to a moderator

Re: Import dump file without 2 tables [message #538133 is a reply to message #538131]

Wed, 04 January 2012 04:37

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I agree.

Report message to a moderator

Re: Import dump file without 2 tables [message #538135 is a reply to message #538133]

Wed, 04 January 2012 04:53

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.

Report message to a moderator

Previous Topic:	Datapump: importing check constraints
Next Topic:	export fails

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 07:03:00 CST 2024