Home » RDBMS Server » Server Utilities » imp / exp problem with sequence no
imp / exp problem with sequence no [message #222850] Tue, 06 March 2007 13:04 Go to next message
Mitr
Messages: 9
Registered: January 2007
Junior Member
Hi All:

i am a newbie and have a problem with imp and exp utility..
I exported data from a table and this table has sequence no generated on a particular column X...
i had to delete the data from this table ...i deleted and then did an import using the dump i exported earlier..it gave me a message saying imported data successfully...but seems to me like it generated new no's for the column X...so how can i suppress the sequence no and then do an import...so that i can have the old sequence no's for the column X...


thanks a lot in advacne...
Re: imp / exp problem with sequence no [message #222851 is a reply to message #222850] Tue, 06 March 2007 13:10 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Drop the sequence before you import the data. The import will then recreate the sequence object.

Alternatively, alter the sequence to reset its "start with" value.
Re: imp / exp problem with sequence no [message #222853 is a reply to message #222851] Tue, 06 March 2007 13:19 Go to previous messageGo to next message
Mitr
Messages: 9
Registered: January 2007
Junior Member
Thankq Frank..

I cant reset the sequence no 'cos the data i exported is not for the table...i exported the data using a where clause..so they are a bunch of rows which are not in any sequential order of the column X...
I am not the DBA of the database..so will I still be able to drop the sequence using the parfile and if so how??

thanks a lot again..
Mitr..
Re: imp / exp problem with sequence no [message #222856 is a reply to message #222853] Tue, 06 March 2007 13:25 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Can't you "SELECT MAX(col)+1 FROM tab" and reset the sequence to that number?

Syntax is something like: ALTER SEQUENCE ... START WITH ...;

Do a "SELECT * FROM SESSION_PRIVS" to see if you have the required privs.
Re: imp / exp problem with sequence no [message #222863 is a reply to message #222856] Tue, 06 March 2007 14:30 Go to previous messageGo to next message
Mitr
Messages: 9
Registered: January 2007
Junior Member
probably i am not very clear with my Question...I am sorry about that...

I have a table A which is a Master table and I have a table B which is the sub table of table A.
Now I have to delete some rows from table B as well as from table A depending upon the same where clause...
so I exported the data from the two tables into different dmp files using the same where clause...
now i deleted the data from table B using the same where clause and then deleted the data from table A using the same where clause...
now i inserted data to table A from the dmp and it said successfully imported and then imported to table b from its dmp file...but whn doing so ..table B import is not successful..cos col X of table is looking for a value in table A col X ...since it is the FK for table B...

from this it seems to me that when doing the import to Table A ...the import generated new values for col X..and so table B is not able to find the rows for FK in table A..

so how can i resolve this issue...

pls let me know if i am not still clear...


thanks a lot...


Re: imp / exp problem with sequence no [message #222865 is a reply to message #222863] Tue, 06 March 2007 14:40 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is there, perhaps, an enabled database trigger which inserts sequential numbers into that column (which is, upon insert, messing things up)? If so, disable it before running the Import. When it is done, enable the trigger.
Re: imp / exp problem with sequence no [message #222866 is a reply to message #222865] Tue, 06 March 2007 14:57 Go to previous messageGo to next message
Mitr
Messages: 9
Registered: January 2007
Junior Member
yes there is a trigger ..how shuld i disable it ..i mean can i do it using the parfile... i mean the same parfile i use to do the import..??
Re: imp / exp problem with sequence no [message #222928 is a reply to message #222866] Wed, 07 March 2007 00:11 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ALTER TRIGGER trigger_name DISABLE;
As this is a command which should be issued in SQL, I don't think you can do it using parfile. Perhaps you can, but ... I've never done that, and - at the moment - I'm not in the mood to check the documentation. But you could!
Previous Topic: IMP-00017 Error
Next Topic: Update Remote Databases
Goto Forum:
  


Current Time: Sat Dec 03 22:11:08 CST 2016

Total time taken to generate the page: 0.07543 seconds