Select A.*
  From Dba_Tab_Partitions a
  Join Dba_Objects b
    On (A.Table_Owner = B.Owner And A.Table_Name = B.Object_Name And
       A.Partition_Name = B.Subobject_Name)
 Where B.Object_Id In (Select A.Obj#
                         From Sys.Tabpartv$ a
                         Join Sys.Obj$ b
                           On (A.Obj# = B.Obj#)
                         Join Sys.User$ c
                           On (B.Owner# = C.User#)
                        Where Upper(Sys.Dbms_Metadata_Util.Long2varchar(10,
                                                                        'SYS.TABPARTV$',
                                                                        'HIBOUNDVAL',
                                                                        A.Rowid)) =
                              'DEFAULT'
                          And C.Name = 'HXL' --schema name)

SQL> Select A.*
  2    From Dba_Tab_Partitions a
  3    Join Dba_Objects b
  4      On (A.Table_Owner = B.Owner And A.Table_Name = B.Object_Name And
  5         A.Partition_Name = B.Subobject_Name)
  6   Where B.Object_Id In (Select A.Obj#
  7                           From Sys.Tabpartv$ a
  8                           Join Sys.Obj$ b
  9                             On (A.Obj# = B.Obj#)
 10                           Join Sys.User$ c
 11                             On (B.Owner# = C.User#)
 12                          Where Upper(Sys.Dbms_Metadata_Util.Long2varchar(10,
 13                                                                          'SYS.TABPARTV$',
 14                                                                          'HIBOUNDVAL',
 15                                                                          A.Rowid)) =
 16                                'DEFAULT'
 17                            And C.Name = 'HXL' --schema name)
 18  
SQL> /
                          And C.Name = 'HXL' --schema name)
                                                          *
ERROR at line 17:
ORA-00907: missing right parenthesis

 Select A.*
   From Dba_Tab_Partitions a
   Join Dba_Objects b
     On (A.Table_Owner = B.Owner And A.Table_Name = B.Object_Name And
        A.Partition_Name = B.Subobject_Name)
  Where B.Object_Id In (Select A.Obj#
                          From Sys.Tabpartv$ a
                          Join Sys.Obj$ b
                            On (A.Obj# = B.Obj#)
                          Join Sys.User$ c
                            On (B.Owner# = C.User#)
                         Where Upper(Sys.Dbms_Metadata_Util.Long2varchar(10,
                                                                         'SYS.TABPARTV$',
                                                                         'HIBOUNDVAL',
                                                                         A.Rowid)) =
                               'DEFAULT'
                           And C.Name = 'OSS03' --schema name
                           )

SQL> create table TB_HXL_USER
  2  (
  3    STATEDATE DATE not null,
  4    PROVCODE NUMBER not null,
  5    USERNUMBER VARCHAR2(13) not null,
  6    REM1 VARCHAR2(1024),
  7    CREATE_DATE DATE,
  8    CREATE_BY NUMBER,
  9    LAST_UPDATE_DATE DATE,
 10    LAST_UPDATE_BY NUMBER
 11  )
 12  partition by range (statedate) subpartition by list(provcode)
 13  subpartition template (
 14   subpartition p1 values (1) ,
 15   subpartition p2 values (2) ,
 16   subpartition p3 values (3) ,
 17   subpartition p4 values (4) ,
 18   subpartition p5 values (5) ,
 19   subpartition p_default values (default)
 20   )
 21  (
 22    partition HXL_USER_20111117 values less than 
(TO_DATE('2011-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGOR
IAN'))
 23  );

Table created.

SQL> set long 10000000;

SQL> Select a.subpartition_name,a.high_value
  2    From dba_tab_subpartitions a
  3    Join Dba_Objects b
  4      On (a.Table_Owner = b.Owner
  5          And a.Table_Name = b.Object_Name
  6          And a.subPartition_Name = b.Subobject_Name
  7         )
  8   Where b.Object_Id In
  9         (Select a.Obj#
 10            From sys.Tabsubpart$ a
 11            Join Sys.Obj$ b
 12              On (a.Obj# = b.Obj#)
 13            Join Sys.User$ c
 14              On (b.Owner# = c.User#)
 15           Where Upper(Long_To_Char(a.Rowid, 'SYS', 'TABSUBPART$', 'HIBOUNDVAL'))
 16           In ('DEFAULT','MAXVALUE')
 17             And c.Name = 'HXL' --schema name
 18          )
 19     And a.table_name = 'TB_HXL_USER'
 20     /

SUBPARTITION_NAME
------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
HXL_USER_20111117_P_DEFAULT
default

Create Or Replace Function Long_To_Char(In_Rowid      Rowid,
                                        In_Owner      Varchar,
                                        In_Table_Name Varchar,
                                        In_Column     Varchar2)
  Return Varchar As
  Text_C1 Varchar2(32767);
  Sql_Cur Varchar2(2000);
  --
Begin
  Sql_Cur := 'select ' || In_Column || ' from
' || In_Owner || '.' || In_Table_Name || ' where rowid =
' || Chr(39) || In_Rowid || Chr(39);
  Execute Immediate Sql_Cur
    Into Text_C1;

  Text_C1 := Substr(Text_C1, 1, 4000);
  Return Text_C1;
End;

Create Or Replace Function Long_To_Char(
   In_Rowid      Rowid,
--    In_Owner      Varchar,
   In_Table_Name Varchar, -- table name may contain a schema like SCOTT.EMP
   In_Column     Varchar2)
  Return Varchar As
  Text_C1 Varchar2(32767);
  Sql_Cur Varchar2(2000);
  --
Begin
  If regexp_replace(In_Rowid, '[A-Za-z0-9+/]') is not null then
     raise_application_error(-20000, 'Invalid rowid');
  end if;
  Sql_Cur := 
'select ' || dbms_assert.simple_sql_name(In_Column) || ' 
from ' || dbms_assert.sql_object_name(In_Table_Name) || ' 
where rowid = ''' || In_Rowid || '''';
  Execute Immediate Sql_Cur Into Text_C1;
  Text_C1 := Substr(Text_C1, 1, 4000);
  Return Text_C1;
End;