Oracle查询行对应block_id,file

Oracle查询行对应block_id,file_id

阅读量：453 次

发布时间：2019-03-06

本文共 2473 字，大约阅读时间需要 8 分钟。


       
    从SCOTT.T表中查询行ID信息如下：
    
            
     SELECT dbms_rowid.rowid_object(rowid) "OBJECT#", dbms_rowid.rowid_relative_fno(rowid) "FILE#", dbms_rowid.rowid_block_number(rowid) "BLOCK#", dbms_rowid.rowid_row_number(rowid) "ROW#" FROM SCOTT.T WHERE ID IN (1, 300000);
    
    
    
            
     结果如下：
        
     
                                                                     ID                             ROWID                             OBJECT#                             FILE#                             BLOCK#                             ROW#                        
                  
                                                                     1                             AAAWb0AAEAACvPCAAp                             91892                             4                             717762                             41                        
                                                   300000                             AAWb0AAEAACvN8AAw                             91892                             4                             717692                             48                        
                  
             
    
    
    
    
    
    关于表分段和分区的信息查询结果如下：
    
            
     SELECT t.segment_name, t.partition_name, t.BLOCK_ID, (t.BLOCKS + t.BLOCK_ID - 1) "MAX_BLOCK_ID" FROM SYS.DBA_EXTENTS t, DBA_TAB_PARTITIONS p WHERE t.PARTITION_NAME = p.partition_name AND p.TABLE_OWNER = 'SCOTT' AND p.table_name = 'T';
    
    
    
            
     结果如下：
        
     
                                                                     SEGMENT_NAME                             PARTITION_NAME                             BLOCK_ID                             MAX_BLOCK_ID                        
                  
                                                                     T                             SYS_P109                             714368                             715391                        
                                                   T                             SYS_P110                             715392                             716415                        
                                                   T                             SYS_P111                             716416                             717439                        
                                                   T                             SYS_P112                             717440                             718463