• Category Archives Oracle Performance Tuning
  • Database » Oracle Database » Oracle Performance Tuning
  • Command Line Partitions and Subpartitions

    Just the partitions:

    set wrap off linesize 132 pages 5000
    col tbs format a10
    column tab format a25
    col owner format a10
    column part format a25
    column owner format a10
    select t.table_owner "Owner"	
            ,t.tablespace_name tbs
    	,t.table_name as "Tab"
    	,t.PARTITION_NAME "Part"
    	,t.num_rows
    	,t.last_analyzed
    from dba_TAB_PARTITIONS t
    --where t.table_owner='...'
    order by t.table_owner,t.table_name,t.PARTITION_NAME;
    

    Add partition keys:

    set wrap off linesize 132 pages 5000
    column tab format a25
    column partkey format a25
    column pos format 99990
    column part format a25
    column subparts format 99990
    column stats format a10
    col tbs format a10
    column owner format a10
    select	 t.table_owner "Owner"
            ,t.table_name "Tab"
    	,k.column_name "PartKey"
    	,t.tablespace_name "Tbs"
    	,t.num_rows||'@'||t.last_analyzed "Stats"
            ,DECODE(t.subpartition_count,0,NULL,t.subpartition_count) "SubParts"
    from dba_TAB_PARTITIONS t join dba_PART_KEY_COLUMNS k on(k.name=t.table_name)
    where k.object_type='TABLE'
    --and t.table_owner='...'
    group by t.table_owner,t.tablespace_name,t.table_name,k.column_name,t.num_rows,t.last_analyzed,t.subpartition_count
    order by t.table_owner,t.table_name;
    

    Subpartitions? In one of my books somewhere – be careful with subpartitioning – can get a little too involved and does not really fit in with KISS (Keep It Simple Stupid) approach, but if you have the manpower to deal with convoluted complexity then why not. All of this stuff on my blog is in one of my books somewhere so if you want more detail ask me or go and read a book. You’ll probably also find scripts like these online by searching Google, but maybe not quite the same but this stuff’s all been in the public domain for 10-20 years now.

    Reading Partitions and Subpartitions Metadata

    --List all partitions
    set wrap off linesize 132 pages 5000
    col tbs format a10
    column tab format a25
    col owner format a10
    column part format a25
    select t.tablespace_name tbs
    ,t.table_name as "Tab"
    ,t.PARTITION_NAME "Part"
    ,t.num_rows
    ,t.last_analyzed
    from dba_TAB_PARTITIONS t
    --where t.owner='...'
    order by t.table_name,t.PARTITION_NAME;
    
    --Partition keys
    set wrap off linesize 132 pages 5000
    column tab format a25
    column partkey format a25
    column pos format 99990
    column part format a25
    column subparts format 99990
    column stats format a10
    col tbs format a10
    select t.table_name "Tab"
    ,k.column_name "PartKey"
    ,t.tablespace_name "Tbs"
    ,t.num_rows||'@'||t.last_analyzed "Stats"
    from dba_TAB_PARTITIONS t join dba_PART_KEY_COLUMNS k on(k.name=t.table_name)
    where k.object_type='TABLE'
    and t.table_owner='...'
    group by t.tablespace_name,t.table_name,k.column_name,t.num_rows,t.last_analyzed
    order by t.table_name;
    
    --partition type
    column table_name format a10
    column partitioning_type format a10
    set wrap on lines 1024 pages 5000;
    select table_name, partitioning_type, interval from user_part_tables;
    
    --count subpartitions
    set wrap off linesize 132 pages 5000
    column Tab format a25
    column Tbs format a25
    column Part format a25
    BREAK ON tab NODUPLICATES SKIP -1
    select t.table_name "Tab"
    ,t.partition_name "Part"
    ,count(s.partition_name)
    from user_TAB_PARTITIONS t join user_TAB_SUBPARTITIONS s on(s.table_name=t.table_name and s.partition_name=t.partition_name)
    where t.subpartition_count!=0
    group by t.table_name,t.partition_name
    order by t.table_name;
    
    --lists partition keys when subpartitions present
    set wrap off linesize 132 pages 5000
    column tab format a25
    column partkey format a25
    column pos format 99990
    column part format a25
    column subparts format 99990
    col tbs format a10
    col rws format a15
    BREAK ON tab NODUPLICATES SKIP -1
    select t.table_name "Tab"
    ,k.column_name "PartKey"
    ,t.tablespace_name "Tbs"
    ,max(t.partition_position) "Pos"
    ,max(t.subpartition_count) "SubParts"
    ,round(tab.num_rows/1000000,0)||' mill @ '||tab.last_analyzed "Rows"
    from user_TAB_PARTITIONS t join user_PART_KEY_COLUMNS k on(k.name=t.table_name) join user_tables tab on(tab.table_name=t.table_name)
    where k.object_type='TABLE'
    and t.subpartition_count!=0
    group by t.tablespace_name,t.table_name,k.column_name,tab.num_rows,tab.last_analyzed
    order by t.table_name;
    
    --lists subpartitions
    set wrap off linesize 132 pages 5000
    column Tab format a25
    column partkey format a25
    column pos format 99990
    column part format a25
    column SubPartKey format a16
    col tbs format a10
    col SubPartName format a40
    BREAK ON tab NODUPLICATES SKIP -1
    select t.table_name "Tab"
    ,k.column_name "SubPartKey"
    ,t.tablespace_name "Tbs"
    ,t.SUBPARTITION_NAME "SubPartName"
    ,t.subpartition_position "Pos"
    from user_TAB_SUBPARTITIONS t join user_SUBPART_KEY_COLUMNS k on(k.name=t.table_name)
    where k.object_type='TABLE'
    order by t.table_name,t.SUBPARTITION_NAME,t.subpartition_position;
    
    --first and last
    select /*+ PARALLEL(16) */ min(PARTITION_NAME),max(PARTITION_NAME)
    from user_tab_partitions;
    
    --find user_ind_partitions where they are in tab_partitions
    set wrap off linesize 132 pages 5000
    column tab format a25
    column ind format a25
    column part format a25
    col tbs format a10
    BREAK ON tab NODUPLICATES SKIP -1
    select t.table_name "Tab"
    ,i.index_name "Ind"
    ,t.tablespace_name "Tbs"
    ,t.num_rows||'@'||t.last_analyzed
    from user_TAB_PARTITIONS t join user_ind_partitions i on(i.partition_name=t.partition_name)
    where t.subpartition_count=0
    order by t.table_name,i.index_name;
    
    --partitions, subpartitions and subpartition keys
    set wrap off linesize 132 pages 5000
    column owner format a10
    column tab format a25
    column partkey format a10
    column partpos format 99990
    column subparts format 99990
    column subpartkey format a10
    column subpartpos format 99990
    BREAK ON tab NODUPLICATES SKIP -1
    select p.table_owner "Owner"
    ,p.table_name "Tab"
    ,pk.column_name "PartKey"
    ,max(p.partition_position) "PartPos"
    ,max(p.subpartition_count) "SubParts"
    ,spk.column_name "SubPartKey"
    ,max(sp.subpartition_position) "SubPartPos"
    from DBA_TAB_PARTITIONS p join DBA_PART_KEY_COLUMNS pk on(pk.owner=p.table_owner)
    join DBA_TAB_SUBPARTITIONS sp on(sp.table_owner=p.table_owner)
    join DBA_SUBPART_KEY_COLUMNS spk on(spk.owner=sp.table_owner)
    where pk.object_type='TABLE'
    group by p.table_owner,p.table_name,pk.column_name,spk.column_name
    order by p.table_owner,p.table_name;