Solved

Getting different blockid ...

Posted on 2009-04-02
4
278 Views
Last Modified: 2012-05-06
Dear All,

I am trying to generate rowid for locking sessions.
In my case, I have two session. From these two session i fired same query.

ses 1 :
MLXWTIBSLDB (IBSLDB1) SQL >select rowid,CUSTINFO_CUSTOMERID from ptest where CUSTINFO_CUSTOMERID=404591 for update;

ROWID              CUSTINFO_CUSTOMERID
------------------ -------------------
AAAfbiAABAAAMOCAAA              404591

ses 2 :

MLXWTIBSLDB (IBSLDB1) SQL >select rowid,CUSTINFO_CUSTOMERID from ptest where CUSTINFO_CUSTOMERID=404591 for update;




ses 3 :

When I checked for v$session it's showing me different block number for these sessions.

       SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ------------- -------------- --------------- -------------
       904        128738              1           50050             0
      2852        128738              1           51925             0


Also these is no block exists with number 51925 for file 1.

MLXWTIBSLDB (IBSLDB1) SQL >select SEGMENT_NAME,FILE_ID,BLOCK_ID from dba_extents where SEGMENT_NAME ='PTEST';

SEGMENT_NAME                                                                         FILE_ID   BLOCK_ID
--------------------------------------------------------------------------------- ---------- ----------
PTEST                                                                                      1      51849
PTEST                                                                                      1      51721
PTEST                                                                                      1      51593
PTEST                                                                                      1      51465
PTEST                                                                                      1      51337
PTEST                                                                                      1      51209
PTEST                                                                                      1      51081
PTEST                                                                                      1      50953
PTEST                                                                                      1      50825
PTEST                                                                                      1      50697
PTEST                                                                                      1      50569
PTEST                                                                                      1      50457
PTEST                                                                                      1      50449
PTEST                                                                                      1      50441
PTEST                                                                                      1      50049

Below are the rowid generation for ses1 and ses2:

MLXWTIBSLDB (IBSLDB1) SQL >select do.object_name,
  2  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) from v$session s, dba_objects do where sid=&a and s.ROW_WAIT_OBJ# = do.OBJECT_ID
  3  /
Enter value for a: 904
OBJECT_NAME                                                                                                                      ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
-------------------------------------------------------------------------------------------------------------------------------- ------------- -------------- --------------- -------------
DBMS_ROWID.ROWID_C
------------------
PTEST                                                                                                                                   128738              1           50050             0
AAAfbiAABAAAMOCAAA

MLXWTIBSLDB (IBSLDB1) SQL >select do.object_name,
  2  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) from v$session s, dba_objects do where sid=&a and s.ROW_WAIT_OBJ# = do.OBJECT_ID
  3  /
Enter value for a:2852
OBJECT_NAME                                                                                                                      ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
-------------------------------------------------------------------------------------------------------------------------------- ------------- -------------- --------------- -------------
DBMS_ROWID.ROWID_C
------------------
PTEST                                                                                                                                   128738              1           51925             0
AAAfbiAABAAAMrVAAA


Can you please clarify me why am getting different block id for ses1 and ses2 as I am tring to update same record.

Thanks in advance.
0
Comment
Question by:someone_genius
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 125 total points
ID: 24047347
The locking information is not in the data file.
It is in the dictionary that is placed in SYSTEM tablespace.
Possibly the session checks the Dictionary for locked rows.
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 125 total points
ID: 24049411
1) I will point out the answer to the 1st question, DBA_EXTENTS does not include each and every block. It includes the starting BLOCK of the extend, plus the number of BLOCKS for that extent. See below.

I cannot answer the second part about the differente blocks / rowids but I may poke around and try it myself.

select SEGMENT_NAME,FILE_ID,BLOCK_ID,BLOCK_ID+BLOCKS-1 AS END_BLOCK_ID from dba_extents where SEGMENT_NAME ='PTEST';

Open in new window

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24049616
When I test this scenario I do not see the same results. I don't see 2 sessions waiting for the same object, I see 1. Since the 1st object acquires the lock, I don't expect to see 2 rows, only 1, which is what I get.

When I create a 3rd sessiion and try to lock the row, then I see 2 session waiting on the same block #.

I think maybe you are looking at the wrong sessions?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now