How to find the partition by ROWID in oracle?

Question

I created the partitions for the table (test) by Hijri months (date) , How to match a particular partition by ROWID in oracle?

you can extract file_id and block_id from ROWID (by calling some dbms_package). Then you can find the "closest" extent header by querying dba_extents. — ibre5041, Nov 10 '15 at 12:27

Rahul Tripathi · Answer 1 · 2015-11-10T12:06:05.847

1

Try this:

SELECT dbms_rowid.rowid_object(ROWID) data_object_id
FROM test;

To get the partition name you can use

SELECT partition_name 
from user_tab_partitions
where table_name='test'

edited Nov 10 '15 at 12:06

answered Nov 10 '15 at 11:46

Rahul Tripathi

how I can get the name of the partition? – mohsen.nour Nov 10 '15 at 12:04
@mohsen.noor:- Updated my answer. Please check! – Rahul Tripathi Nov 10 '15 at 12:06
I want to give Rowid and oracle returns to me partition My record belongs to which partition SELECT partition_name from user_tab_partitions -- I don't know which table it is? where ROWID = 'xxx' – mohsen.nour Nov 10 '15 at 12:56

score 1 · Answer 2 · answered Nov 10 '15 at 13:04

1

select object_name tab_name, subobject_name parttition   from user_objects where object_id =dbms_rowid.rowid_object('xxxxx');

answered Nov 10 '15 at 13:04

score 1 · Accepted Answer · answered Nov 10 '15 at 13:14

Quite straightforward, using DBMS_RowID and dba_objects.

select *
from   dba_objects
where  data_object_id = dbms_rowid.rowid_object(ROWID_OF_THE_ROW)

3 Answers3