Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Getting different blockid ...

Posted on 2009-04-02
4
Medium Priority
?
285 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 48

Accepted Solution

by:
schwertner earned 500 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 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

885 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