- 3,714,556 Users
- 2,242,576 Discussions
- 7,844,931 Comments
Forum Stats
Discussions
Categories
- 12 Data
- 362.2K Big Data Appliance
- 6 Data Science
- 1.5K Databases
- 440 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.7K ORDS, SODA & JSON in the Database
- 415 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 135K Development Tools
- 7 DevOps
- 3K QA/Testing
- 231 Java
- 4 Java Learning Subscription
- 10 Database Connectivity
- 65 Java Community Process
- Java 25
- 8 Java APIs
- 141.1K Java Development Tools
- 5 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 134 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 118 LiveLabs
- 28 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
DDL + nowait

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
Answers
-
Want to try NOVALIDATE ? There are examples in Ask Tom.
-
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.
-
NoValidate gives the same error...
-
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.
-
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.
-
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
-
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;
-
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.
-
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
-
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.