Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DDL + nowait

Nuno RanitoNov 7 2018 — edited Nov 10 2018

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

Comments

EdStevens

You don't give much to go on. Please copy and paste the text of the entire statement/procedure/script. Please explain in detail how it is being submitted.

User_DDYV1

Due to security issues, I may not be able to copy the entire scripts here. But I'm trying to run a merge statement which is something like below. Usually this statement takes a minute to run and merge the data into the dimension table, but every first of month it runs forever and hangs there.
merge into table d using
( select * from (
select key , id , id1 , sysdate , row_number() over (partition by key order by v.id) as row_num
from f_transaction f,
(select ID from f_import v where date = 'Today'sDate' ) v
where f.id = v.id
and f.date = 'today's date' )
where row_num = 1 ) x
on (d_key = x.key
when matched then update set
d.id = x.id
, d.dw_update_date = x.dw_update_date
when not matched then
insert ( id , Key, Date)
values ( x.id , x.key , x.date);

When running this merge statement in toad, it executes till 23 seconds, then it stays there and stops counting. Toad then get hanged. I checked in the session monitor for any locks and made sure no other jobs are running parallelly.
So need some help here to know how to troubleshoot this.

JohnWatson2

And if you run the statement from SQL*Plus?

tsangsir

And are the numbers of rows inf_transaction and f_import more or less the same throughout the month?

User_DDYV1

@johnwatson2 , The same issue with SQL plus as well.
@tsangsir , the rows gets added each day , so these two tables keeps growing. These two tables already have millions of records, we do have index on these tables as well.

JohnWatson2

JohnWatson2 , The same issue with SQL plus as well.
Do you mean that it runs to completion in 23 seconds? Or do you mean that it hangs forever?
A simple way to gather a bit more information, in SQL*Plus, would be to

set timing on
set autotrace on
merge into......

and copy/paste it all here.

EdStevens

and copy/paste it all here.
And please be sure to post it as code-formatted

User_DDYV1

@johnwatson2 , @edstevens1 Thanks , when I run the same query in SQL plus, it hangs there as well.
the thing is, I ran it today, the same merge statement and I got the result in 23 seconds. But I'm facing this issue only during the first of month (Every time).

1 - 8

Post Details

Added on Nov 7 2018
10 comments
3,713 views