DBA_SEGMENTS misleading PARTITION_NAME column

Whilst writing some code that referenced the DBA_SEGMENTS dictionary view today, I realised that the contents of the PARTITION_NAME column actually contains the name of the subpartition when the table is a subpartitioned table…a script and results to illustrate:

drop table jeff_unpartitioned purge
/
drop table jeff_partitioned purge
/
drop table jeff_subpartitioned purge
/
create table jeff_unpartitioned(col1 number,col2 number)
/
create table jeff_partitioned(col1 number,col2 number)
partition by range(col1)
(partition p1 values less than(100)
,partition p2 values less than(maxvalue)
)
/
create table jeff_subpartitioned(col1 number,col2 number)
partition by range(col1)
subpartition by list(col2)
subpartition template
(subpartition sub1 values(1)
,subpartition sub2 values(2)
)
(partition p1 values less than(100)
,partition p2 values less than(maxvalue)
)
/
select segment_name,partition_name,segment_type
from   user_segments
where  segment_name like 'JEFF%'
order by segment_name
/

…gives the following results…

Table dropped.

Table dropped.


They're too busy winning Pulitzer Prizes to be bothered of, as they are buy viagra australia  not serious. Motor vehicle accident cialis brand  along with sports incidents tend to be the most prevalent cause of whiplash. Keep  viagra prescription australia the parent informed about the development of the child. Simply looking at an advertisement in buy cialis levitra  a newspaper or Yellow Pages won't get you the best erectile dysfunction pill at lower prices. Table dropped.


Table created.


Table created.


Table created.


                        Partition
SEGMENT_NAME             Name                 SEGMENT_TYPE
------------------------ ------------------
JEFF_PARTITIONED         P2                   TABLE PARTITION
JEFF_PARTITIONED         P1                   TABLE PARTITION
JEFF_SUBPARTITIONED      P1_SUB1              TABLE SUBPARTITION
JEFF_SUBPARTITIONED      P2_SUB2              TABLE SUBPARTITION
JEFF_SUBPARTITIONED      P1_SUB2              TABLE SUBPARTITION
JEFF_SUBPARTITIONED      P2_SUB1              TABLE SUBPARTITION
JEFF_UNPARTITIONED                            TABLE

7 rows selected.

As you can see, the subpartitioned table shows the subpartition name in the PARTITION_NAME column. It’s not a big deal and I only noticed because I assumed there would be a SUBPARTITION_NAME column whilst I was writing my code…the failure in compilation led me to track this slightly erroneous situation down.

Why does this occur?

Well, if you delve into the code behind DBA_SEGMENTS you’ll see it refers to another view in the SYS schema called SYS_DBA_SEGS. The SQL behind SYS_DBA_SEGS selects all levels (Table, Partition and subpartitions) from the SYS.OBJ$ view, but then “loses” the partitions when joining to SYS.SYS_OBJECTS (the OBJ# from SYS.OBJ$ does not map to any row in SYS.SYS_OBJECTS via the OBJECT_ID column). The SQL then “loses” the table when joining to SYS.SEG$ – exactly why it does this I don’t fully understand, but I’m guessing it’s because those components of the composite object don’t actually have their own segment to physically store anything in since there are lower levels – in this case the subpartitions.

In any event, it’s a little bit of a gotcha and the column could probably do with being renamed to SUB_SEGMENT_NAME perhaps.