SQL> SQL> -- How do I find out which partition a particular row belongs? SQL> -- Easy for range or list partiotioned table. What about hash? SQL> SQL> column object_name format a20 SQL> column subobject_name format a20 SQL> SQL> drop table part_range_hash; drop table part_range_hash * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> create table part_range_hash (c1 number(3), c2 varchar2(2), c3 char(1)) 2 partition by range (c1) 3 subpartition by hash (c3) 4 (partition p1 values less than (100) 5 (subpartition p1_d1, 6 subpartition p1_d2, 7 subpartition p1_d3), 8 partition p2 values less than (200), 9 partition p3 values less than (maxvalue) 10 ); Table created. SQL> SQL> SQL> insert into part_range_hash values(50,'xx','1'); 1 row created. SQL> insert into part_range_hash values(60,'xx','2'); 1 row created. SQL> insert into part_range_hash values(70,'xx','3'); 1 row created. SQL> insert into part_range_hash values(150,'yy','b'); 1 row created. SQL> insert into part_range_hash values(250,'ww','c'); 1 row created. SQL> SQL> select rowid, c1, c2, c3 from PART_RANGE_HASH; ROWID C1 C2 C ------------------ ---------- -- - AAAN5UAAJAAAAP4AAA 60 xx 2 AAAN5VAAJAAAAQAAAA 50 xx 1 AAAN5VAAJAAAAQAAAB 70 xx 3 AAAN5XAAJAAAAXAAAA 150 yy b AAAN5YAAJAAAAXIAAA 250 ww c SQL> SQL> select object_name, subobject_name, data_object_id, object_id 2 from user_objects where object_name='PART_RANGE_HASH'; OBJECT_NAME SUBOBJECT_NAME DATA_OBJECT_ID OBJECT_ID -------------------- -------------------- -------------- ---------- PART_RANGE_HASH P1 56913 PART_RANGE_HASH P1_D1 56916 56916 PART_RANGE_HASH P1_D2 56917 56917 PART_RANGE_HASH P1_D3 56918 56918 PART_RANGE_HASH P2 56914 PART_RANGE_HASH P3 56915 PART_RANGE_HASH SYS_SUBP129 56919 56919 PART_RANGE_HASH SYS_SUBP130 56920 56920 PART_RANGE_HASH 56912 9 rows selected. SQL> SQL> set serveroutput on SQL> declare 2 cursor cur1 is select rowid, c2 from part_range_hash; 3 p1 number; 4 p2 number; 5 p3 number; 6 p4 number; 7 p5 number; 8 begin 9 for x in cur1 loop 10 dbms_rowid.rowid_info(x.rowid,p1,p2,p3,p4,p5); 11 dbms_output.put_line('ROWID= '||x.rowid||', rowid_type='||p1||', data_object_id='||p2|| 12 ', relative_fno='||p3||', block_number='||p4||', row_number='||p5); 13 end loop; 14 end; 15 / ROWID= AAAN5UAAJAAAAP4AAA, rowid_type=1, data_object_id=56916, relative_fno=9, block_number=1016, row_number=0 ROWID= AAAN5VAAJAAAAQAAAA, rowid_type=1, data_object_id=56917, relative_fno=9, block_number=1024, row_number=0 ROWID= AAAN5VAAJAAAAQAAAB, rowid_type=1, data_object_id=56917, relative_fno=9, block_number=1024, row_number=1 ROWID= AAAN5XAAJAAAAXAAAA, rowid_type=1, data_object_id=56919, relative_fno=9, block_number=1472, row_number=0 ROWID= AAAN5YAAJAAAAXIAAA, rowid_type=1, data_object_id=56920, relative_fno=9, block_number=1480, row_number=0 PL/SQL procedure successfully completed. SQL> SQL> drop table part_range_hash_temp; Table dropped. SQL> create table part_range_hash_temp (c1 number(3), c2 varchar2(2), c3 char(1)) 2 partition by hash (c3) 3 partitions 3; Table created. SQL> SQL> insert into part_range_hash_temp values(80,'xx','1'); 1 row created. SQL> insert into part_range_hash_temp values(60,'xx','2'); 1 row created. SQL> insert into part_range_hash_temp values(70,'xx','3'); 1 row created. SQL> SQL> alter table part_range_hash exchange partition p1 with table part_range_hash_temp; Table altered. SQL> SQL> -- Note that obejct_id and data_object_id are now different for partition p1 SQL> select object_name, subobject_name, data_object_id, object_id 2 from user_objects where object_name='PART_RANGE_HASH'; OBJECT_NAME SUBOBJECT_NAME DATA_OBJECT_ID OBJECT_ID -------------------- -------------------- -------------- ---------- PART_RANGE_HASH P1 56913 PART_RANGE_HASH P1_D1 56922 56916 PART_RANGE_HASH P1_D2 56923 56917 PART_RANGE_HASH P1_D3 56924 56918 PART_RANGE_HASH P2 56914 PART_RANGE_HASH P3 56915 PART_RANGE_HASH SYS_SUBP129 56919 56919 PART_RANGE_HASH SYS_SUBP130 56920 56920 PART_RANGE_HASH 56912 9 rows selected. SQL> SQL> -- You get data_object_id, not object_id, from dmbs_rowid SQL> set serveroutput on SQL> declare 2 cursor cur1 is select rowid, c2 from part_range_hash; 3 p1 number; 4 p2 number; 5 p3 number; 6 p4 number; 7 p5 number; 8 begin 9 for x in cur1 loop 10 dbms_rowid.rowid_info(x.rowid,p1,p2,p3,p4,p5); 11 dbms_output.put_line('ROWID= '||x.rowid||', rowid_type='||p1||', data_object_id='||p2|| 12 ', relative_fno='||p3||', block_number='||p4||', row_number='||p5); 13 end loop; 14 end; 15 / ROWID= AAAN5aAAJAAAALIAAA, rowid_type=1, data_object_id=56922, relative_fno=9, block_number=712, row_number=0 ROWID= AAAN5bAAJAAAAPgAAA, rowid_type=1, data_object_id=56923, relative_fno=9, block_number=992, row_number=0 ROWID= AAAN5bAAJAAAAPgAAB, rowid_type=1, data_object_id=56923, relative_fno=9, block_number=992, row_number=1 ROWID= AAAN5XAAJAAAAXAAAA, rowid_type=1, data_object_id=56919, relative_fno=9, block_number=1472, row_number=0 ROWID= AAAN5YAAJAAAAXIAAA, rowid_type=1, data_object_id=56920, relative_fno=9, block_number=1480, row_number=0 PL/SQL procedure successfully completed. SQL> SQL> drop table PART_RANGE_HASH; Table dropped. SQL> SQL> spool off