Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Clark.

Asked: February 04, 2008 - 11:23 am UTC

Last updated: May 09, 2023 - 3:52 pm UTC

Version: 10.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi,

I need to know if there is a way to return the name of the partition that a row of data is coming back from.

Regards,

Clark

and Tom said...

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t select to_date( '11-mar-2003', 'dd-mon-yyyy' )+rownum, rownum, rownum from all_users where rownum <= 3;

3 rows created.

ops$tkyte%ORA10GR2> select t.dt, uo.subobject_name
  2    from t, user_objects uo
  3   where dbms_rowid.rowid_object(t.rowid) = uo.object_id
  4  /

DT        SUBOBJECT_NAME
--------- ------------------------------
13-MAR-03 PART2
12-MAR-03 PART1
14-MAR-03 JUNK

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

data_object_id

Sai, February 05, 2008 - 1:29 pm UTC

Tom,

I thought data_object_id needs to be used for dbms_rowid.rowid_object.

select t.dt, uo.subobject_name
from t, user_objects uo
where dbms_rowid.rowid_object(t.rowid) = uo.data_object_id;

Thanks
Tom Kyte
February 05, 2008 - 2:06 pm UTC

doh, I knew that.

thanks!

yes - it would be an issue if you truncate:

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2> select subobject_name, object_id, data_object_id from user_objects where object_name = 'T';

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
JUNK                               153914         153914
PART1                              153912         153912
PART2                              153913         153913
                                   153911

ops$tkyte%ORA10GR2> insert into t select to_date( '11-mar-2003', 'dd-mon-yyyy' )+rownum, rownum, rownum from all_users where rownum <= 3;

3 rows created.

ops$tkyte%ORA10GR2> truncate table t;

Table truncated.

ops$tkyte%ORA10GR2> select subobject_name, object_id, data_object_id from user_objects where object_name = 'T';

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
JUNK                               153914         153917
PART1                              153912         153915
PART2                              153913         153916
                                   153911

ops$tkyte%ORA10GR2> insert into t select to_date( '11-mar-2003', 'dd-mon-yyyy' )+rownum, rownum, rownum from all_users where rownum <= 3;

3 rows created.

ops$tkyte%ORA10GR2> select t.dt, uo.subobject_name, dbms_rowid.rowid_object(t.rowid)
  2    from t, user_objects uo
  3   where dbms_rowid.rowid_object(t.rowid) = uo.object_id(+)
  4  /

DT        SUBOBJECT_NAME                 DBMS_ROWID.ROWID_OBJECT(T.ROWID)
--------- ------------------------------ --------------------------------
14-MAR-03                                                          153917
13-MAR-03                                                          153916
12-MAR-03                                                          153915

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select t.dt, uo.subobject_name, dbms_rowid.rowid_object(t.rowid)
  2    from t, user_objects uo
  3   where dbms_rowid.rowid_object(t.rowid) = uo.data_object_id(+)
  4  /

DT        SUBOBJECT_NAME                 DBMS_ROWID.ROWID_OBJECT(T.ROWID)
--------- ------------------------------ --------------------------------
13-MAR-03 PART2                                                    153916
12-MAR-03 PART1                                                    153915
14-MAR-03 JUNK                                                     153917

ops$tkyte%ORA10GR2>

A reader, February 06, 2008 - 5:39 pm UTC

SQL> select  dbms_rowid.rowid_object('AZA6b1AApAAAZZZAAC') from dual;

DBMS_ROWID.ROWID_OBJECT('AZA6B
------------------------------
                     419669749

SQL> select * from dba_objects where data_object_id=419669749;

OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------


why is it 0 rows for the second SELECT ?

Tom Kyte
February 07, 2008 - 7:42 am UTC

because the rowid is made up?

ops$tkyte%ORA10GR2> select  dbms_rowid.rowid_object('ABCDABCDABCDABCDAB') from dual;

DBMS_ROWID.ROWID_OBJECT('ABCDABCDABCDABCDAB')
---------------------------------------------
                                     17313793


I just made that rowid up.

interesting lessions learnt !

Sokrates, February 07, 2008 - 8:02 am UTC

however (also @ORA10GR2):

CREATE TABLE t
  2    (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30),
  6    primary key(dt, x)
  7    )
  8    organization index
  9    PARTITION BY RANGE (dt)
 10    (
 11    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy','NLS_DATE_LANGUAGE=AMERICAN')) ,
 12   PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy','NLS_DATE_LANGUAGE=AMERICAN')) ,
 13   PARTITION junk VALUES LESS THAN (MAXVALUE)
 14*  )
SQL> /

Table created.

SQL> insert into t select to_date( '11-mar-2003', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE=AMERICAN')+rownum, 
  2  rownum, rownum from all_users where rownum <= 3;

3 rows created.

SQL> select t.dt, uo.subobject_name
  2  from t, user_objects uo
  3  where dbms_rowid.rowid_object(t.rowid) = uo.object_id
  4  /
where dbms_rowid.rowid_object(t.rowid) = uo.object_id
      *
ERROR at line 3:
ORA-06553: PLS-306: wrong number or types of arguments in call to
'ROWID_OBJECT'


any workaround ?

Tom Kyte
February 07, 2008 - 9:32 am UTC

IOTs do not have real rowids, they have logical rowids

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3935

... One difference between physical and logical rowids is that logical rowids cannot be used to see how a table is organized. ...



the logical rowid has the primary key and a rowid guess, but the guesses go stale

rowids made up

A reader, February 07, 2008 - 10:59 am UTC

select dbms_rowid.rowid_object('ABCDABCDABCDABCDAB') from dual;


The rowids were indeed made up

But then, why should the function dbms_rowid.rowid_object return a row for this made up rowid ? I thought ideally it should not have returned anything
Tom Kyte
February 07, 2008 - 11:44 am UTC

if you make up data that could be a rowid, it doesn't care.

why would it?

a rowid is just data, these functions work on that data and return bits of it.

made up rowids

A reader, February 07, 2008 - 1:17 pm UTC

so you mean to say that this is not a minor "bug" ?
Tom Kyte
February 07, 2008 - 1:39 pm UTC

nope, not at all.

A rowid is just information encoded in a string.

dbms_rowid just extracts that information.

As long as the string matches the specification of a rowid - dbms_rowid just extracts the bits from it.

sort of like substr.

Is there a way to return the name of the SUBpartition that a row of data is coming back from

Steve Givens, April 26, 2023 - 10:10 pm UTC

I'm curious if there is a way to extend this method using dbms_rowid.rowid_object(t.rowid) and data_object_id to the subpartition level? I believe the problem is getting to the subobject of the subobject level.

For example,
select t.dt, uo.subobject_name, dbms_rowid.rowid_object(t.rowid)
from t, user_objects uo
where dbms_rowid.rowid_object(t.rowid) = uo.data_object_id(+);

provides the values for the partition names. But if I then subpartition the table, this query no longer retrieves the subpartition name (blank).

Do I need to rewrite the query with a connect by or something?

Ideas appreciated.

Thanks,
Steve
Chris Saxon
April 27, 2023 - 1:17 pm UTC

Care to share a demo?

This looks to be getting the subpartition names to me:

create table t (
  c1 int, c2 int
) partition by hash ( c1 ) 
  subpartition by hash ( c2 )
  subpartitions 2
  partitions 2;
  
select subpartition_name from user_tab_subpartitions
where  table_name = 'T';

SUBPARTITION_NAME 
------------------
SYS_SUBP26218
SYS_SUBP26219
SYS_SUBP26220
SYS_SUBP26221
  
insert into t 
with rws as (
  select level x from dual
  connect by level <= 5
)
  select x, x from rws;
  
select t.*, uo.subobject_name, dbms_rowid.rowid_object(t.rowid)
from t, user_objects uo
where dbms_rowid.rowid_object(t.rowid) = uo.data_object_id(+);

        C1         C2 SUBOBJECT_NAME       DBMS_ROWID.ROWID_OBJECT(T.ROWID)
---------- ---------- -------------------- --------------------------------
         1          1 SYS_SUBP26221                                  384585
         3          3 SYS_SUBP26221                                  384585
         4          4 SYS_SUBP26221                                  384585
         2          2 SYS_SUBP26218                                  384582
         5          5 SYS_SUBP26218                                  384582

A reader, May 05, 2023 - 9:08 pm UTC

Thanks Chris. I'm afraid I don't know what to say! ;)

I tried your example and it worked, and then I went back and rebuilt mine and that worked too. Not sure why I wasn't seeing values before.
Chris Saxon
May 09, 2023 - 3:52 pm UTC

Blame Heisenberg's uncertainty principle ;)

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.