DDL + nowait — oracle-tech

    Forum Stats

  • 3,714,556 Users
  • 2,242,576 Discussions
  • 7,844,931 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

Categories

DDL + nowait

Nuno R
Nuno R Member Posts: 157 Red Ribbon
edited November 2018 in SQL & PL/SQL

Hello

I (we) have a situation in which I need to perform some DDL on a table without any downtime.

The table in question must me referenced by a new table (which is to create), and then it must have a new column.

During the course of the ddl script execution, the ora-00054 resource busy and acquire with NOWAIT specified or timeout expired showed up and the db contention skyrocketed.

Of course that is consequence of a transaction in process, which causes the lock.

Is there any way of performing those ddl operations without downtime (preventing access to the table)?

I created some scenario

CREATE TABLE temP_mytable (id number primary key);

insert into temp_mytable values (1);

insert into temp_mytable values (2);

insert into temp_mytable values (3);

----------------------------------------------------------------

(other session)

create table temp_myRefTable (id number primary key,

                              refid number,

             constraint RefT_Fk foreign key (refid) references temp_myTable(id)

             );

            

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

alter system kill session '132,6'; /* previous session */

Table TEMP_MYREFTABLE created.

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Thank you for any help

Ahmed HaroonDejan T.
· Share on TwitterShare on Facebook

Answers

  • Saubhik
    Saubhik Member Posts: 5,795 Silver Crown
    edited November 2018

    Want to try NOVALIDATE ? There are examples in Ask Tom.

    · Share on TwitterShare on Facebook
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited November 2018

    create table temp_myRefTable (id number primary key,

                                  refid number);

                 alter table myreftable add constraint RefT_Fk foreign key (refid) references temp_myTable(id);

    Repeat ALTER TABLE statement until it succeeds.

    Ahmed Haroon
    · Share on TwitterShare on Facebook
  • Nuno R
    Nuno R Member Posts: 157 Red Ribbon
    edited November 2018

    NoValidate gives the same error...

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited November 2018 
    Is there any way of performing those ddl operations without downtime (preventing access to the table)?

    No - and that is EXACTLY what the exception is telling you.

    Certain DDL operations need exclusive access to the table.

    Such changes are typically very fast but you will need to find a small window to do them.

    · Share on TwitterShare on Facebook
  • Saubhik
    Saubhik Member Posts: 5,795 Silver Crown
    edited November 2018 
    Nuno R wrote:NoValidate gives the same error...

    This way --> https://asktom.oracle.com/pls/apex/asktom.search?tag=table-lock-adding-foreign-key

    with Alter table (assuming you are not in some unsupported version). Remember this is BLOCKED, not give you an error immediately but table will not be altered until other sessions completes the transaction.

    Ahmed Haroon
    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,512 Gold Crown
    edited November 2018

    Create the table without the constraint, then alter the table to add the constraint with the NOVALIDATE option.

    The alter table will wait until there are no active tranasctions on the two tables and then execute, but while it is waiting other transaction affecting the tables will still be able to execute.

    After this use the alter table command to validate the constraint

    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook
  • DBQuest
    DBQuest Member Posts: 135
    edited November 2018

    Try this option using dbms_utility oracle built in package.

    DECLARE

      l_bool BOOLEAN;

      l_scn NUMBER;

    BEGIN

      l_bool := dbms_utility.wait_on_pending_dml

                  ( tables => 'temP_mytable', 

                    timeout => 500,

                    scn => l_scn );

      --

      IF l_bool THEN

        EXECUTE IMMEDIATE 'create table temp_myRefTable (id number primary key,

                                 refid number,

                 constraint RefT_Fk foreign key (refid) references temp_myTable(id))';

      END IF;

    END;

    · Share on TwitterShare on Facebook
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited November 2018 
    Jonathan Lewis wrote:Create the table without the constraint, then alter the table to add the constraint with the NOVALIDATE option.The alter table will wait until there are no active tranasctions on the two tables and then execute, but while it is waiting other transaction affecting the tables will still be able to execute.After this use the alter table command to validate the constraintRegardsJonathan Lewis

    Existing transactions that have already got their TM lock on the table (ie they’ve already touched it) will be able to continue to apply DML to table, BUT new transactions or transactions that haven’t done any DML against the table will be blocked when they go to do so while the DDL is waiting to allocate the lock.

    At least this is the case when modifying an existing disable novalidate constraint to enable validate. I dont think there’s a reason that it would be different for alter table add constraint.

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,512 Gold Crown
    edited November 2018

    @Andrew,

    Maybe there's a version dependency there. I dropped back to 11.2.0.4 to do the following:

    Session 1:

    --------------

    SQL> create table parent (id number primary key , n1 number);

    Table created.

    SQL> insert into parent values(1,1);

    1 row created.

    SQL> insert into parent values(2,2);

    1 row created.

    SQL> insert into parent values(3,3);

    1 row created.

    SQL> create table child (id_p number, n1 number);

    Table created.

    Session 2:

    --------------

    SQL> insert into child values (1,1);

    1 row created.

    Session 1: (started waiting)

    -------------------------------------

    SQL> alter table child add constraint c_fk_p foreign key(id_p) references parent novalidate;

    Session 3:

    ---------------

    SQL> insert into child values (2,2);

    1 row created.

    Session 2:

    ---------------

    SQL> commit;

    Commit complete.

    (session 1 still waiting)

    Session 3:

    --------------

    SQL> commit;

    Commit complete.

    -------------  (session 1 continues)

    Session 2

    -------------

    SQL> insert into child values (3,1);

    1 row created.

    Session 1

    -------------

    SQL> alter table child modify constraint c_fk_p validate;

    Table altered.

    The code also behaved if sessions 2 and 3 also inserted a parent with a matching chid row.

    Regards

    Jonathan Lewis

    Dejan T.
    · Share on TwitterShare on Facebook
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited November 2018

    Thanks for that Jonathan.

    I tested using in 18.3 but I can replicate your results there (add constraint enable novalidate) - which just waits on the TX lock (i.e. just the existing DML) and then the validate doesn't need to wait around.

    When I do it slightly differently:

    add constraint disable novalidate - no locks

    modify constraint enable - waits on a TM lock mode 4 (which does prevent other DML to the table).

    It looks like my prior comment

    "At least this is the case when modifying an existing disable novalidate constraint to enable validate. I dont think there’s a reason that it would be different for alter table add constraint."

    Was relevant.

    I guess this is just another case where a slightly different approach (which could be mistaken for each other) can cause quite annoying problems.

    · Share on TwitterShare on Facebook
Sign In or Register to comment.