DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: add foreign key constraints - HELP!!!

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    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
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Sep 2001
    Location
    Dallas, TX
    Posts
    27

    Foreign Key Constraint Issues

    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.

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    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.

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width