Home » RDBMS Server » Server Utilities » Is it possible to import only the master tables? (Oracle, 9iR2, RHEL AS 3)
Is it possible to import only the master tables? [message #330423] Mon, 30 June 2008 02:57 Go to next message
sathyguy
Messages: 31
Registered: January 2006
Member
Friends,

Is it possible to import only the master tables using import utility.
Because when i try to import some 70 tables i am getting the foreign key violation error.
without the child tables or referring tables can i import only the master tables?


thanks
Re: Is it possible to import only the master tables? [message #330435 is a reply to message #330423] Mon, 30 June 2008 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the "TABLES" keyword.
See "imp help=y"

Regards
Michel
Re: Is it possible to import only the master tables? [message #330444 is a reply to message #330423] Mon, 30 June 2008 03:30 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
hi,

i have used the tables keyword.i checked the help.....
but my problem is ....when i import "employee" table its giving foreign constraint violation error.
thats why i asked, is it possible to import only the master tables without child table?

thanks


Re: Is it possible to import only the master tables? [message #330447 is a reply to message #330444] Mon, 30 June 2008 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have this error then this is because you are importing a child table.
You have to know your model to import the correct tables.

Regards
Michel
Re: Is it possible to import only the master tables? [message #330486 is a reply to message #330423] Mon, 30 June 2008 05:00 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
I know that i have to include all the relevant tables.
Also, i have included all the tables.unfortunately the imp command is importing the tablenames in an ascending order.
for example.
i have tab1,tab2,tab3...
tab3 is the master table.
tab1 is the child table
when i execute the import command. first the tab1 is inserting data and its throwing the foreign key error.

my question is... can i execute the imp command using the order which im giving?
for example
imp username/password file=file.dmp log=log.dmp tables=(tab3,tab5,tab2)
Re: Is it possible to import only the master tables? [message #330491 is a reply to message #330486] Mon, 30 June 2008 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the tables already exist yo have to manually disable and enable the foreign keys otherwise import handles them.

Regards
Michel
Re: Is it possible to import only the master tables? [message #330504 is a reply to message #330423] Mon, 30 June 2008 05:42 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
yes, in my case
1. im importing all the 500 table structures.
2. after that im importing only 70 tables data.
1st part no problem....
2nd part is the problem.
can i disable all the constraints at a time and after importing the data's can i enable all the constraints?
or i have to disable/enable when ever im getting an error?
how can i disable/enable constraints?

thanks

[Updated on: Mon, 30 June 2008 05:43]

Report message to a moderator

Re: Is it possible to import only the master tables? [message #330505 is a reply to message #330504] Mon, 30 June 2008 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you could try in the first import: constraints=N.
Then in the second one, use constraints=Y (default value).

Regards
Michel
Re: Is it possible to import only the master tables? [message #330527 is a reply to message #330423] Mon, 30 June 2008 06:52 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
thanks a lot....
its working now.....
one more doubt......
those 70 tables will be imported with constraints...
no problem....
suppose a user want to insert some records in the 75th table(i.e., the table which i have imported without constraints)that 75th table was having a FK constraint in 80th table. In that situation as a DBA what should i do? Am i have to import the 75th and 80th table with constraints and delete the records from the table?

i hope you got my point....

thanks


Re: Is it possible to import only the master tables? [message #330542 is a reply to message #330527] Mon, 30 June 2008 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can add a third step to import with rows=N constraints=y and ignore=y to import the other constraints. In this case, in the second step you can use constraints=N.
In the end, you have all constraints enabled in the loaded and not loaded tables.
Or you can inmport the wanted table with or without the constraints and/or rows depending on the final result you want.
I'm not fully understand what you mean but you have to ask yourself, what does I want in the end?
- all constrainst enabled?
- some enabled and some disabled?
- data in master tables and detailed ones? or just master? Which ones?
...
Depending on all these you will know which table data to load and then which constraints to enable.

You can load load all constraints in the third step I mentioned and disable/enable the relevant ones during loads.

Regards
Michel
Re: Is it possible to import only the master tables? [message #330551 is a reply to message #330542] Mon, 30 June 2008 07:34 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
this is what i want.....
1. Import all tables(nearly 500 tables) structures without data.
2. Import only 70 tables with data.

for this, i did......
1. Imported all tables(nearly 500 tables) structures without data without constraints.
2. Imported 70 tables with data with constraints.
3. Imported again those 500 tables without data and with constraints.(here im getting the error as "table already exists and constraints already exists" for the 70 tables but imported successfully with the above warnings). i think there is no harm in that. is there any harm?

at last i want to enable all the constraints.....

thanks

[Updated on: Mon, 30 June 2008 07:34]

Report message to a moderator

Re: Is it possible to import only the master tables? [message #330552 is a reply to message #330551] Mon, 30 June 2008 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
.(here im getting the error as "table already exists and constraints already exists" for the 70 tables but imported successfully with the above warnings). i think there is no harm in that. is there any harm?

No, this is wy I said: use ignore=Y in the third step

Regards
Michel
Re: Is it possible to import only the master tables? [message #330760 is a reply to message #330552] Tue, 01 July 2008 02:32 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
Actually, i imported with ignore=y and indexfile=file.sql in the 3rd step.
after that i run the file.sql in the sqlplus.
so....its throwing error.

but...if i run the import directly with ignore=y and without indexfile. then its importing successfully without warnings.

so.....in the 3rd step i have to run it directly without indexfile. please correct me if i am wrong.

thanks
Re: Is it possible to import only the master tables? [message #330763 is a reply to message #330760] Tue, 01 July 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correct.

Regards
Michel
Re: Is it possible to import only the master tables? [message #330766 is a reply to message #330763] Tue, 01 July 2008 02:53 Go to previous messageGo to next message
sathyguy
Messages: 31
Registered: January 2006
Member
thank you so much....
Re: Is it possible to import only the master tables? [message #331272 is a reply to message #330444] Wed, 02 July 2008 16:44 Go to previous messageGo to next message
gaperumal
Messages: 8
Registered: June 2008
Location: Chennai India
Junior Member
add this parameter. The default value is 'Y'

constraints=N

Arangaperumal G
Chennai,India
Re: Is it possible to import only the master tables? [message #331300 is a reply to message #331272] Wed, 02 July 2008 23:32 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has already been said and repeated... and is wrong for all the steps, OP wants the constrains at some steps.

Regards
Michel
Previous Topic: export tables
Next Topic: SQL*Loader-466: Column FIELD1 does not exist in table UTENTI.
Goto Forum:
  


Current Time: Wed Dec 07 22:41:59 CST 2016

Total time taken to generate the page: 0.08479 seconds