cancel
Showing results for 
Search instead for 
Did you mean: 

Exclusive locks during online table reorganization

peter_strauss
Participant
0 Kudos

Hello,

Oracle 9.2.0.6.

We recently (yesterday) had a problem where an exclusive lock was placed on a table for a long period of time.

When we investigated we found that the customer had been performing an online table reorganization at the time.

Note 869521 suggests that this can happen during an online index rebuild.

Is the issue described in note 869521 also possible for online table reorganization?

Kind regards,

Peter

Accepted Solutions (1)

Accepted Solutions (1)

stefan_koehler
Active Contributor
0 Kudos

Hello Peter,

i have also investigated this topic in a blog in detail.

/people/stefan.koehler/blog/2008/06/19/oracle-create-rebuild-index-online--table-lock

> We recently (yesterday) had a problem where an exclusive lock was placed on a table for a long period of time.

What kind of lock? TM Lock?

Normally you shouldn't run into a hanging situation, if the event "10629 trace name context forever, level <n>" is set.

Regards

Stefan

peter_strauss
Participant
0 Kudos

Hello Stephan,

>Normally you shouldn't run into a hanging situation, if the event "10629 trace name context forever, level <n>" is set.

This event was not set; however we will set it now.

Thank you for the link to your blog. Your blog entries are very useful so thank you for taking the time to maintain them.

>What kind of lock? TM Lock?

Hmmm. I didn't identify a TM Lock. I did a terrible job of documenting the problem so I'm not sure if there might have been one.

What we did notice:

Many WPs stopped inserting to table A

Many locks on table B (these locks were TX - 6)

The oldest lock holder held a lock on SNAP$, which I gather is used to refresh he materialized views used in the online table reorganization.

We worked through note 20071 to identify the oracle session causing the lock; we showed that there was no client OS-PID active for that session so we killed it. This solved the problem.

When the problem occurred the we had been doing an online reorganization of table A. We noticed that a lock was stopping SAP WPs so we cancelled the reorganization. I guess that something went awry and prevented the corresponding Oracle session from finishing correctly, as described in note 20071.

My theory assumes that the problem you describe in your blog occurred; however your blog describes an online rebuild, whereas in this case we were doing an online table reorganization.

Do they mean the same thing (more or less)? I assume that during an online table reorganization an online index rebuild must occur at some point. Do you know whether this is true?

Either way we will deal with the problem as you have suggested; by setting parameter event 10629. I expect that we won't see the problem anymore after the parameter is set.

Kind regards,

Peter

stefan_koehler
Active Contributor
0 Kudos

Hello Peter,

as you said that the event was not set ... the probability is quite high that you hit this situation.

As you also said that the table was reorganized online ... i assume that the customer was using the BR*Tools and respective the PL/SQL package DBMS_REDEFINITION. Until now i have never investigated this package into the deep, but the documentation also points to a similiar behavior as an index rebuild online.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#i998731

FINISH_REDEF_TABLE Procedure

This procedure completes the redefinition process. Before this step, you can create new indexes, triggers, grants, and constraints on the interim table. The referential constraints involving the interim table must be disabled. After completing this step, the original table is redefined with the attributes and data of the interim table. The original table is locked briefly during this procedure.

Regards

Stefan

peter_strauss
Participant
0 Kudos

Hello Stefan,

That answers my question perfectly.

Kind regards,

Peter

Answers (0)