[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.