DBMS_ROWID

• ROWID값으로부터 Object Id, File #, Block #, Row #를 얻고자 하는 경우 사용하는 패키지 이다.
• 반대로 Object Id, File #, Block #, Row #를 사용하여 ROWID를 만들 수도 있다.
• 다음과 같이 현재 경합이 발생하는 rowid를 먼저 추출해 본다.

SQL> col obj_no new_value obj_no
SQL> col file_no new_value file_no
SQL> col block_no new_value block_no
SQL> col row_no new_value row_no
SQL> select sid, row_wait_obj# as obj_no, row_wait_file# as file_no,
            row_wait_block# as block_no, row_wait_row# as row_no
       from v$session
      where event = 'enq: TX - row lock contention';

• rowid_type 1값은 DBMS_ROWID.ROWID_TYPE_EXTENDED 을 말한다.
• 다른값으로 DBMS_ROWID.ROWID_TYPE_RESTRICTED가 있고 이 값은 0이다.
• Extended RowID는 8.Xi버전 이상에서만 사용된다.

SQL> col rid new_value rid
SQL> select dbms_rowid.rowid_create(rowid_type => 1,
            object_number => &obj_no, relative_fno => &file_no,
            block_number => &block_no, row_number => &row_no) as rid
       from dual;

위에서 추출한 값을 기반으로 어떤 로우에 해당하는지 검색할 수 있다.

SQL> select * from t1 where rowid = '&rid';

• 반대로 rowid값으로부터 Object Id, File #, Block #, Row #를 얻을 수도 있다.

SQL> select
dbms_rowid.rowid_type('&rid') as rtype,
dbms_rowid.rowid_object('&rid') as obj_no,
dbms_rowid.rowid_relative_fno('&rid') as file_no,
dbms_rowid.rowid_block_number('&rid') as block_no,
dbms_rowid.rowid_row_number('&rid') as row_no
from dual;

• 테스트는 다음과 같다.

SQL >col obj_no new_value obj_no
SQL >col file_no new_value file_no
SQL >col block_no new_value block_no
SQL >col row_no new_value row_no
SQL >select sid, row_wait_obj# as obj_no, row_wait_file# as file_no,
    row_wait_block# as block_no, row_wait_row# as row_no
    from v$session
    where event = 'enq: TX - row lock contention';

       SID     OBJ_NO    FILE_NO   BLOCK_NO     ROW_NO
---------- ---------- ---------- ---------- ----------
      1149      65277          7         83          0

SQL >col rid new_value rid
SQL >select dbms_rowid.rowid_create(rowid_type => 1,
object_number => &obj_no, relative_fno => &file_no,
block_number => &block_no, row_number => &row_no) as rid
from dual;
old   2: object_number => &obj_no, relative_fno => &file_no,
new   2: object_number =>      65277, relative_fno =>          7,
old   3: block_number => &block_no, row_number => &row_no) as rid
new   3: block_number =>         83, row_number =>          0) as rid

RID
------------------
AAAP79AAHAAAABTAAA

SQL >select * from oracle.t1 where rowid = '&rid';
old   1: select * from oracle.t1 where rowid = '&rid'
new   1: select * from oracle.t1 where rowid = 'AAAP79AAHAAAABTAAA'

        C1 C2
---------- ----
         1 many

SQL >select
dbms_rowid.rowid_type('&rid') as rtype,
dbms_rowid.rowid_object('&rid') as obj_no,
dbms_rowid.rowid_relative_fno('&rid') as file_no,
dbms_rowid.rowid_block_number('&rid') as block_no,
dbms_rowid.rowid_row_number('&rid') as row_no
from dual;
old   2: dbms_rowid.rowid_type('&rid') as rtype,
new   2: dbms_rowid.rowid_type('AAAP79AAHAAAABTAAA') as rtype,
old   3: dbms_rowid.rowid_object('&rid') as obj_no,
new   3: dbms_rowid.rowid_object('AAAP79AAHAAAABTAAA') as obj_no,
old   4: dbms_rowid.rowid_relative_fno('&rid') as file_no,
new   4: dbms_rowid.rowid_relative_fno('AAAP79AAHAAAABTAAA') as file_no,
old   5: dbms_rowid.rowid_block_number('&rid') as block_no,
new   5: dbms_rowid.rowid_block_number('AAAP79AAHAAAABTAAA') as block_no,
old   6: dbms_rowid.rowid_row_number('&rid') as row_no
new   6: dbms_rowid.rowid_row_number('AAAP79AAHAAAABTAAA') as row_no

     RTYPE     OBJ_NO    FILE_NO   BLOCK_NO     ROW_NO
---------- ---------- ---------- ---------- ----------
         1      65277          7         83          0

SQL >