[20210317]如何知道索引塊地址2.txt

[20210317]如何知道索引塊地址2.txt

--//有時候學習需要知道索引塊地址，一般是透過跟蹤獲得。如何透過查詢獲得呢？
--//可以使用sys_op_lbid 函式。

1.環境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試：
--//首先要知道索引的OBJECT_ID號。
SCOTT@book> select object_name, object_id from dba_objects where object_name = 'PK_DEPT';
OBJECT_NAME           OBJECT_ID
-------------------- ----------
PK_DEPT                   87107

--//sys_op_lbid函式，引數1對應索引的OBJECT_ID，引數2 'L',引數3對應資料塊rowid。
SCOTT@book> select sys_op_lbid (87107, 'L', dept.ROWID) c30 from dept;
C30
------------------------------
AAAVRDAAEAAAACLAAA
AAAVRDAAEAAAACLAAA
AAAVRDAAEAAAACLAAA
AAAVRDAAEAAAACLAAA
--//後面3位都是AAA。

SCOTT@book> @ rowid AAAVRDAAEAAAACLAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     87107          4        139          0  0x100008B           4,139                alter system dump datafile 4 block 139 ;

SCOTT@book> select * from dba_segments where owner=user and segment_name='PK_DEPT'
  2  @ prxx
==============================
OWNER                         : SCOTT
SEGMENT_NAME                  : PK_DEPT
PARTITION_NAME                :
SEGMENT_TYPE                  : INDEX
SEGMENT_SUBTYPE               : ASSM
TABLESPACE_NAME               : USERS
HEADER_FILE                   : 4
HEADER_BLOCK                  : 138
BYTES                         : 65536
BLOCKS                        : 8
EXTENTS                       : 1
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
RETENTION                     :
MINRETENTION                  :
PCT_INCREASE                  :
FREELISTS                     :
FREELIST_GROUPS               :
RELATIVE_FNO                  : 4
BUFFER_POOL                   : DEFAULT
FLASH_CACHE                   : DEFAULT
CELL_FLASH_CACHE              : DEFAULT
PL/SQL procedure successfully completed.