186 lines
9.0 KiB
SQL
186 lines
9.0 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : To convert statspack tables into partitioned tables (based on DBID).
|
|
* to hold consolidated data from multiple databases.
|
|
* Version : 9.2.x, 10.2.x, 11.1.x, 11.2.x
|
|
* Usage : Login as DBA and pass schema name holding the consolidated statspack repository
|
|
* as argument
|
|
* Syntax : scriptname.sql <schemaname>
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 23-Oct-09 Vishal Gupta First Draft
|
|
*/
|
|
set term on
|
|
|
|
set verify off
|
|
set serveroutput on size 1000000
|
|
DECLARE
|
|
lv_sql VARCHAR2(4000) := null;
|
|
lv_schema_owner VARCHAR2(30) := '&&1';
|
|
lv_temporary_table_name VARCHAR2(30) := 'STATS$_TEMP';
|
|
lv_num_errors PLS_INTEGER;
|
|
BEGIN
|
|
|
|
BEGIN
|
|
execute immediate 'drop table ' || lv_schema_owner || '.' || lv_temporary_table_name;
|
|
EXCEPTION
|
|
WHEN others THEN
|
|
null;
|
|
END;
|
|
|
|
|
|
|
|
DBMS_OUTPUT.put_LINE('Converting non partition tables to partitioned table ...' );
|
|
-- Identify tables LIST paritioned on DBID
|
|
FOR i in (SELECT t.owner, t.table_name , t.tablespace_name
|
|
FROM dba_tables t
|
|
WHERE t.owner = lv_schema_owner
|
|
AND t.table_name like 'STATS$%'
|
|
AND t.table_name <> lv_temporary_table_name
|
|
AND t.PARTITIONED = 'NO'
|
|
AND EXISTS (SELECT 1
|
|
FROM dba_tab_columns c
|
|
WHERE c.owner = t.owner
|
|
AND c.table_name = t.table_name
|
|
AND c.column_name = 'DBID')
|
|
-- AND t.table_name = 'STATS$SNAPSHOT'
|
|
ORDER BY t.table_name
|
|
)
|
|
LOOP
|
|
DBMS_OUTPUT.put_LINE(' ...... ' || i.owner || '.' || i.table_name );
|
|
|
|
|
|
BEGIN
|
|
|
|
-- -- Cleanup the redefinition (if left over from previous failed runs)
|
|
-- sys.DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname => i.owner
|
|
-- , orig_table => i.table_name
|
|
-- , int_table => lv_temporary_table_name
|
|
-- );
|
|
|
|
-- Verify that the table is a candidate for online redefinition.
|
|
sys.dbms_redefinition.can_redef_table(uname => i.owner
|
|
, tname => i.table_name
|
|
, options_flag => sys.dbms_redefinition.cons_use_rowid
|
|
);
|
|
|
|
|
|
-- Create interim partitioned table with same structure as non-partitioned table.
|
|
lv_sql := 'create table ' || i.owner || '.' || lv_temporary_table_name
|
|
|| ' partition by list (DBID) '
|
|
|| ' ( PARTITION others VALUES (DEFAULT) ) '
|
|
|| ' tablespace ' || i.tablespace_name
|
|
|| ' pctfree 5 pctused 95 '
|
|
|| ' as select * from ' || i.owner || '.' || i.table_name || ' where 1=2';
|
|
execute immediate lv_sql;
|
|
|
|
-- exit;
|
|
|
|
-- Start the redefinition process
|
|
sys.DBMS_REDEFINITION.START_REDEF_TABLE(uname => i.owner
|
|
, orig_table => i.table_name
|
|
, int_table => lv_temporary_table_name
|
|
, options_flag => sys.dbms_redefinition.cons_use_rowid
|
|
);
|
|
|
|
-- Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs,
|
|
-- grants, and constraints
|
|
sys.DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => i.owner
|
|
, orig_table => i.table_name
|
|
, int_table => lv_temporary_table_name
|
|
, copy_indexes => sys.DBMS_REDEFINITION.CONS_ORIG_PARAMS
|
|
-- , copy_triggers => TRUE -- Default value
|
|
-- , copy_constraints => TRUE -- Default value
|
|
-- , copy_privileges => TRUE -- Default value
|
|
, ignore_errors => TRUE
|
|
, num_errors => lv_num_errors
|
|
-- , copy_statistics => FALSE -- Default value
|
|
-- , copy_mvlog => FALSE -- Default value
|
|
);
|
|
|
|
-- synchronize the interim table
|
|
sys.DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname => i.owner
|
|
, orig_table => i.table_name
|
|
, int_table => lv_temporary_table_name
|
|
);
|
|
|
|
-- Complete the redefinition
|
|
sys.DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => i.owner
|
|
, orig_table => i.table_name
|
|
, int_table => lv_temporary_table_name
|
|
);
|
|
|
|
EXCEPTION
|
|
WHEN others THEN
|
|
-- Abort the redefinition
|
|
sys.DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname => i.owner
|
|
, orig_table => i.table_name
|
|
, int_table => lv_temporary_table_name
|
|
);
|
|
|
|
-- Disable temporary Foreign keys created by re-definition process
|
|
FOR cons in (SELECT *
|
|
FROM dba_constraints
|
|
WHERE constraint_type = 'R'
|
|
AND constraint_name like 'TMP$$_%')
|
|
LOOP
|
|
lv_sql := 'alter table ' || cons.owner || '.' || cons.table_name || ' drop constraint ' || cons.constraint_name ;
|
|
execute immediate lv_sql;
|
|
END LOOP;
|
|
|
|
-- Drop the interim table
|
|
execute immediate 'drop table ' || lv_schema_owner || '.' || lv_temporary_table_name;
|
|
|
|
-- Raise Exception
|
|
RAISE;
|
|
END;
|
|
|
|
-- Disable temporary Foreign keys created by re-definition process
|
|
FOR cons in (SELECT *
|
|
FROM dba_constraints
|
|
WHERE constraint_type = 'R'
|
|
AND constraint_name like 'TMP$$_%')
|
|
LOOP
|
|
lv_sql := 'alter table ' || cons.owner || '.' || cons.table_name || ' drop constraint ' || cons.constraint_name ;
|
|
execute immediate lv_sql;
|
|
END LOOP;
|
|
|
|
-- Drop the interim table
|
|
execute immediate 'drop table ' || lv_schema_owner || '.' || lv_temporary_table_name;
|
|
|
|
-- -- 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 = i.table_name
|
|
-- AND p.partition_name = 'P'||d.dbid
|
|
-- )
|
|
-- )
|
|
-- LOOP
|
|
-- lv_sql := 'alter table ' || i.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 ' || i.table_name );
|
|
-- execute immediate lv_sql;
|
|
-- 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;
|
|
/
|