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