Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Clark.

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

Last updated: February 07, 2008 - 1:39 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

  (5 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.

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.