fixini.sql

--###################################################################
--#
--# Title:         fixini.sql
--# Description:   Alters table and index INITRANS values to value
--#                retrieved from the CPU_COUNT paramater.
--#
--# Usage Note:    Should be run as '/ as sysdba'
--#                Should be run from a directory that is writable.
--#
--# Copyright:     ABCdba.com
--#
--###################################################################
 
SET PAGES 0
SET LINES 2000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET TERMOUT OFF
 
VARIABLE cpucnt NUMBER;
 
BEGIN
   SELECT value INTO :cpucnt FROM v$parameter WHERE name = 'cpu_count';
 
   IF :cpucnt < 8 THEN :cpucnt := 8;
   END IF;
 
END;
/
 
SPOOL initrans.sql
 
SELECT
   'ALTER TABLE '||
    owner||
   '.'||
    table_name||
   ' INITRANS '||
    :cpucnt||
   ';'
FROM
   dba_tables
WHERE
   owner NOT IN
   ('SYS','SYSTEM','TSMSYS','OUTLN','DIP','DBSNMP')
AND
   temporary = 'N'
ORDER BY
   owner, table_name
/
 
SELECT
   'ALTER INDEX '||
    owner||
   '.'||
    index_name||
   ' INITRANS '||
    :cpucnt||
   ';'
FROM
   dba_indexes
WHERE
   owner NOT IN
   ('SYS','SYSTEM','TSMSYS','OUTLN','DIP','DBSNMP')
AND
   index_type != 'LOB'
ORDER BY
   owner, index_name
/
 
SPOOL OFF
 
SET PAGES 20
SET LINES 80
SET TRIMSPOOL OFF
SET FEEDBACK ON
SET TERMOUT ON
 
SET ECHO ON
 
@initrans.sql
 
SET ECHO OFF
© copyright 2001-2014 ABCdba.com | all rights reserved