Adding a foreign key constraint ona table locks it since it needs to verify the data with the primary data source for the constraint to be enabled automatically.

One way would be shutdown the instance (release locks on all db objects). Open the DB in restricted session, setup the constraint and open to public.

Option 2: If you have any monitoring tool kill the offending session (minimal impact), wait for about 3 minutes for SMON to clean up the killed session and rerun your statement.

Hope this helps.

BK.

09-07-2001, 11:07 AM

kgb

It means that your table is modified by someone else who holds lock (updating, deleting).
For avoiding that problem you shall make exclusive lock first.

Best wishes.
Dmitri.

09-07-2001, 12:20 PM

sambavan

Why would you want to work as a SYS? If the schema is different then you would have to specify the schema name in too...

Sam

09-07-2001, 07:07 PM

jmodic

Hm, Sam, why do you have a feeling he is doing anything connected as SYS here? I can't see any clue that leads to that conclusion. Because he is using svrmgrl? That means absolutely nothing, he can be connected as SCOTT, SYSTE, SYS or whatever user...