43 lines
1.7 KiB
MySQL
43 lines
1.7 KiB
MySQL
|
col TABLE_OWNER for a25 heading "Owner"
|
||
|
|
col TABLE_NAME for a30 heading "Table"
|
||
|
|
col OWNER for a25 heading "Owner"
|
||
|
|
col INDEX_NAME for a30 heading "Index"
|
||
|
|
col INDEX_TYPE for a10 heading "Type"
|
||
|
|
col PARTITIONED for a3 heading "Par"
|
||
|
|
col PARTITION_POSITION for 999 heading "P#"
|
||
|
|
col PARTITION_NAME for a30 heading "Partition"
|
||
|
|
col SUBPARTITION_NAME for a30 heading "Subpartition"
|
||
|
|
col PART_HIGHVAL for a32 trunc heading "Partition|high value"
|
||
|
|
col PART_INTERVAL for a3 heading "Par|Int"
|
||
|
|
col SUBPART_HIGHVAL for a32 trunc heading "Subpartition|high value"
|
||
|
|
col SUBPART_INTERVAL for a3 heading "Sub|Int"
|
||
|
|
col NUM_ROWS heading "Rows"
|
||
|
|
|
||
|
|
define v_table_owner=&&1
|
||
|
|
define v_table_name=&&2
|
||
|
|
|
||
|
|
select
|
||
|
|
par.TABLE_OWNER
|
||
|
|
,par.TABLE_NAME
|
||
|
|
,par.PARTITION_POSITION
|
||
|
|
,par.PARTITION_NAME
|
||
|
|
,par.NUM_ROWS
|
||
|
|
,par.HIGH_VALUE PART_HIGHVAL
|
||
|
|
,par.INTERVAL PART_INTERVAL
|
||
|
|
,sub.SUBPARTITION_NAME
|
||
|
|
,sub.NUM_ROWS
|
||
|
|
,sub.HIGH_VALUE SUBPART_HIGHVAL
|
||
|
|
,sub.INTERVAL SUBPART_INTERVAL
|
||
|
|
from
|
||
|
|
dba_tab_partitions par
|
||
|
|
left outer join dba_tab_subpartitions sub
|
||
|
|
on (par.TABLE_OWNER=sub.TABLE_OWNER) and (par.TABLE_NAME=sub.TABLE_NAME) and(par.PARTITION_NAME=sub.PARTITION_NAME)
|
||
|
|
where
|
||
|
|
par.table_owner='&&v_table_owner'
|
||
|
|
and par.table_name='&&v_table_name'
|
||
|
|
order by
|
||
|
|
par.PARTITION_POSITION asc
|
||
|
|
,sub.SUBPARTITION_POSITION asc
|
||
|
|
;
|
||
|
|
|