add foreign key constraints - HELP!!!

[leonard905]

Hi all,
Can someone please help me in adding a foreign key constraint to a table. Below is the error message that I got whenever I tried to execute the ddl:

SVRMGR> ALTER TABLE change_1
2> ADD FOREIGN KEY (PKG_POOL_ID) REFERENCES OM_AO_PKG_POOL( PKG_POOL_ID);
ALTER TABLE change_1
*
ORA-00054: resource busy and acquire with NOWAIT specified

How do I get around this busy message.

Thanks,
Leonard905

[bkrish]

Hi,

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.

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

[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

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