Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Unhappy Unanswered: Add constraint Problem ?!?!?!?!?!?

    hi all

    I have 2 tabls
    the first one x (no data) and the second y (about 8000 records) in running system

    I would like to add constraint on table x references by table y like this:

    sql>alter table x add constraint plaplapla foreign key (field4)
    references by y (field1)
    /

    (field1 in y is the primary key)

    its take alot of time then it gives this message :

    sql> timeout occurred while waiting to lock object y

    I tried to make manual lock for table y like this:

    sql> lock table y in excelusive mode nowait
    sql> table locked

    but when I add the constraint its not working again

    can any one help me to manage this problem


    thanx

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    same thing with this?

    I doubt it's possible that table X is waiting for a commit

    PHP Code:
    ALTER TABLE X
           ADD  
    FOREIGN KEY (FIELD4)
                                 
    REFERENCES Y ) ; 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Unhappy Problem still there

    hi

    I did what duck sugest, but the problem still there

    Can anyone help me in this ?

  4. #4
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Unhappy I need ur help Andrewst ....... where R u

    I need ur help Andrewst ....... where R u

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I'm not sure why it's failing to lock, but as for taking a long time. Do you have an index on field1 in table?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hi,

    Don't you have a program constantly performing DML operations on table y ? I experienced such trouble on a table having a CLOB column : you have to lock the table so as to insert or update CLOBs, or the lock may be implicit in the program (SELECT ... FOR UPDATE). If this is the case, you might try to stop your program while you alter table x, and then restart it.

    Hope that helps...

  7. #7
    Join Date
    Aug 2004
    Posts
    9
    What si the exact error that you get? Is it:
    ORA-00054: resource busy and acquire with NOWAIT specified

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    From a different window run the following SQL
    SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
    id1, id2, lmode, request, type
    FROM V$LOCK
    WHERE (id1, id2, type) IN
    (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
    ORDER BY id1, request
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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