Subobject of NRIV when DB01 lock happen?

[Former Member]

Sometimes we are suffering the number range lock of table NRIV. We know the approach is to user a bigger buffer number but we do not know which sub object's is affected.

We try to find this information in DB01 according to note 572905, but unfortunately we can not find "Number range object, subobject, interval number, Toyear and process number are displayed" which is describe in this note.

We also try to trace the active SQL, we can only get below information:

SELECT /*+ FIRST_ROWS */ * FROM "NRIV" WHERE "CLIENT" = :A0 AND "OBJECT" = :A1 AND "SUBOBJECT" = :A2 AND "NRRANGENR" = :A3 AND "TOYEAR" = :A4 FOR UPD

Obviously, this still can not tell us which subobject should be adjusted.

Anybody get experience for this?

Thanks in advance,

James

[Former Member · Accepted solution]

If you still have the SQL trace (from transaction ST05 ?) then all you need to do is click on the sql statement and it will show the bind variables. :A2 will be the subobject.

Cheers Michael

[Former Member · Accepted solution]

Good suggestion.

I usually perform a SQL trace on DB level as the system hang so it is difficult to trace in ST05.

[Former Member · Accepted solution]

For WAS 7.0, in st04 SQL --> Oracle Session --> Execution plan, there is a "Bind Variant" button which can provide this information.

We are stilling running on Basis 640 for some systems.

[Former Member · Accepted solution]

Well to trace the bind variables on db level, you can use the undocumented [DBMS_System.Set_Ev|http://www.oracle-base.com/articles/8i/DBMS_System.php#Set_Ev] to set event 10046.

SQL> EXEC DBMS_System.Set_Ev(sid, serial#, 10046, 4, '');

With trace level 4 binds are captured, they will be listed before the PARSING section. To turn the trace off set the level to 0 again.

Cheers Michael