63 lines
2.2 KiB
SQL
63 lines
2.2 KiB
SQL
@@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;
|
|
/
|