OraFAQ Forum: Server Utilities » imp / exp problem with sequence no

Home » RDBMS Server » Server Utilities » imp / exp problem with sequence no

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

imp / exp problem with sequence no [message #222850]

Tue, 06 March 2007 13:04

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...

Report message to a moderator

Re: imp / exp problem with sequence no [message #222851 is a reply to message #222850]

Tue, 06 March 2007 13:10

Frank Naude
Messages: 4587
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.

Report message to a moderator

Re: imp / exp problem with sequence no [message #222853 is a reply to message #222851]

Tue, 06 March 2007 13:19

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..

Report message to a moderator

Re: imp / exp problem with sequence no [message #222856 is a reply to message #222853]

Tue, 06 March 2007 13:25

Frank Naude
Messages: 4587
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.

Report message to a moderator

Re: imp / exp problem with sequence no [message #222863 is a reply to message #222856]

Tue, 06 March 2007 14:30

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...

Report message to a moderator

Re: imp / exp problem with sequence no [message #222865 is a reply to message #222863]

Tue, 06 March 2007 14:40

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

Report message to a moderator

Re: imp / exp problem with sequence no [message #222866 is a reply to message #222865]

Tue, 06 March 2007 14:57

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..??

Report message to a moderator

Re: imp / exp problem with sequence no [message #222928 is a reply to message #222866]

Wed, 07 March 2007 00:11

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

Report message to a moderator

Previous Topic:	IMP-00017 Error
Next Topic:	Update Remote Databases

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 25 20:28:26 CST 2024