-- space used schema wise
select owner, sum(bytes/(1024*1024*1024)) from dba_segments group by rollup(owner)
-- find partitios, subpartition high
level info along with # of partition
with
temp_low as (
select table_name, PARTITION_NAME from user_tab_partitions a where PARTITION_POSITION in (
select min(PARTITION_POSITION) from
user_tab_partitions b where a.table_name=b.table_name)
)
,temp_high as
(
select table_name, PARTITION_NAME from user_tab_partitions a where PARTITION_POSITION in (
select max(PARTITION_POSITION)-1 from
user_tab_partitions b where a.table_name=b.table_name)
)
select a.table_name, a.PARTITION_NAME,
b.PARTITION_NAME, c.PARTITION_COUNT,
decode(c.SUBPARTITIONING_KEY_COUNT,1,'YES','NO') as SUBPARTITION
from temp_low a, temp_high b, user_part_tables c
where a.table_name=b.table_name
and a.table_name=c.table_name
/
sample report
TABLE_NAME starting_partition end_partition #
SUBP
------------------------------ ------------------
------------------ ---------------- ------
BOS_MESSAGE_LOG P_20130307 P_20140802 514 NO
