--- How to identify the partiton?
-- JN 25.5.2006
---------------------------------
set echo on
-- range partitioning
drop table t;
create table t (c1,c2)
partition by range (c1)
  (
  PARTITION p1 VALUES LESS THAN (5),
  PARTITION p2 VALUES LESS THAN (MAXVALUE)
  )
as
select rownum, rownum from dual connect by level < 10;
--
select subobject_name, data_object_id
       from   user_objects
       where  object_name = 'T' and
              object_type = 'TABLE PARTITION'
;              
with   parts as
      (select --+ materialize
              data_object_id,
              subobject_name
       from   user_objects
       where  object_name = 'T' and
              object_type = 'TABLE PARTITION')
select subobject_name,
      c1,c2
from   T,
      parts
where  data_object_id = DBMS_MView.PMarker(t.rowid)
order by 1;
--- hash partitioning
drop table t;
create table t (c1,c2)
partition by hash (c1)
(partition  a, partition b, partition c, partition d)
-- partitions 4 -- alternativ syntax
as
select rownum, rownum from dual connect by level < 10;
--
select subobject_name, data_object_id
       from   user_objects
       where  object_name = 'T' and
              object_type = 'TABLE PARTITION'
;              
with   parts as
      (select --+ materialize
              data_object_id,
              subobject_name
       from   user_objects
       where  object_name = 'T' and
              object_type = 'TABLE PARTITION')
select subobject_name,
      c1,c2
from   T,
      parts
where  data_object_id = DBMS_MView.PMarker(t.rowid)
order by 1;
--- now subpartitions
drop table t;
create table t (c1,c2)
partition by range (c1)
subpartition by list (c2)
SUBPARTITION TEMPLATE 
(subpartition aa  values (1,7, null),
 subpartition bb  values (default)
 )
  (
  PARTITION p1 VALUES LESS THAN (5),
  PARTITION p2 VALUES LESS THAN (MAXVALUE)
  )
as
select rownum, rownum from dual connect by level < 10;
--
select subobject_name, data_object_id
       from   user_objects
       where  object_name = 'T' and
              object_type = 'TABLE SUBPARTITION'
;              
with   parts as
      (select --+ materialize
              data_object_id,
              subobject_name
       from   user_objects
       where  object_name = 'T' and
              object_type = 'TABLE SUBPARTITION')
select subobject_name,
      c1,c2
from   T,
      parts
where  data_object_id = DBMS_MView.PMarker(t.rowid)
order by 1;