@@header

set term off
/*
*
*  Author  : Vishal Gupta
*  Purpose : To split default parition of statspack tables based on DBID
*            hold consolidated data from multiple databases.
*  Version : 9.2.x, 10.2.x
*  Usage   : Login as schema holding the consolidated statspack repository
*
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  05-Aug-07  Vishal Gupta  First Draft
*/
set term on

set verify off
set serveroutput on
DECLARE
  lv_sql VARCHAR2(4000) := null;
BEGIN
   -- Identify tables LIST paritioned on DBID
   FOR t in (SELECT pt.table_name 
                   FROM user_part_key_columns pc, user_part_tables pt
                  WHERE object_type = 'TABLE' 
                    AND column_name = 'DBID'
                    AND pt.table_name = pc.name
                    AND pt.PARTITIONING_TYPE = 'LIST')
   LOOP
       -- For each DBID value, create partition. 
       FOR dbid in (SELECT DISTINCT dbid
                      FROM stats$database_instance d
                     WHERE  NOT exists (SELECT 1 
                                         FROM user_tab_partitions p
                                        WHERE p.table_name = t.table_name
                                          AND p.partition_name = 'P'||d.dbid
                                       )
                )
       LOOP
            lv_sql := 'alter table ' || t.table_name || ' split partition others values (' || dbid.dbid || ') '
                                 || ' into (partition p'|| dbid.dbid || ' , partition others )';
            -- DBMS_OUTPUT.put_LINE(lv_sql);                                  
            -- add partitions
            DBMS_OUTPUT.put_LINE('Adding parition P' || dbid.dbid || ' FOR ' || t.table_name );
            execute immediate lv_sql;
            null;            
       END LOOP;
   END LOOP;

   FOR i in ( SELECT 'alter index ' || index_name || ' rebuild partition ' || PARTITION_NAME  lv_sql 
                FROM user_ind_partitions 
              WHERE status = 'UNUSABLE')
   LOOP
      execute immediate i.lv_sql;
   END LOOP;

END;
/