linux中间服务器重建索引,Oracle 重建索引脚本

2、重建索引调用的SQL脚本

robin@SZDB:~/dba_scripts/custom/sql> more rebuild_unbalanced_indices.sql

conn / as sysdba

set serveroutput on;

DECLARE

resource_busy              EXCEPTION;

PRAGMA EXCEPTION_INIT (resource_busy, -54);

c_max_trial        CONSTANT PLS_INTEGER := 10;

c_trial_interval  CONSTANT PLS_INTEGER := 1;

pmaxheight        CONSTANT INTEGER := 3;

pmaxleafsdeleted  CONSTANT INTEGER := 20;

CURSOR csrindexstats

IS

SELECT NAME,

height,

lf_rows AS leafrows,

del_lf_rows AS leafrowsdeleted

FROM index_stats;

vindexstats                csrindexstats%ROWTYPE;

CURSOR csrglobalindexes

IS

SELECT owner,index_name, tablespace_name

FROM dba_indexes

WHERE partitioned = 'NO'

AND owner IN ('GX_ADMIN');

CURSOR csrlocalindexes

IS

SELECT index_owner,index_name, partition_name, tablespace_name

FROM dba_ind_partitions

WHERE status = 'USABLE'

AND index_owner IN ('GX_ADMIN');

trial                      PLS_INTEGER;

vcount                      INTEGER := 0;

BEGIN

trial := 0;

/* Global indexes */

FOR vindexrec IN csrglobalindexes

LOOP

EXECUTE IMMEDIATE

'analyze index ' || vindexrec.owner ||'.'|| vindexrec.index_name || ' validate structure';

OPEN csrindexstats;

FETCH csrindexstats INTO vindexstats;

IF csrindexstats%FOUND

THEN

IF    (vindexstats.height > pmaxheight)

OR (    vindexstats.leafrows > 0

AND vindexstats.leafrowsdeleted > 0

AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >

pmaxleafsdeleted)

THEN

vcount := vcount + 1;

DBMS_OUTPUT.PUT_LINE (

'Rebuilding index ' || vindexrec.owner ||'.'|| vindexrec.index_name || '...');

<>

BEGIN

EXECUTE IMMEDIATE

'alter index '

|| vindexrec.owner ||'.'

|| vindexrec.index_name

|| ' rebuild'

|| ' parallel nologging compute statistics'

|| ' tablespace '

|| vindexrec.tablespace_name;

EXCEPTION

WHEN resource_busy OR TIMEOUT_ON_RESOURCE

THEN

DBMS_OUTPUT.PUT_LINE (

'alter index - busy and wait for 1 sec');

DBMS_LOCK.sleep (c_trial_interval);

IF trial <= c_max_trial

THEN

GOTO alter_index;

ELSE

DBMS_OUTPUT.PUT_LINE (

'alter index busy and waited - quit after '

|| TO_CHAR (c_max_trial)

|| ' trials');

RAISE;

END IF;

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE ('alter index err ' || SQLERRM);

RAISE;

END;

END IF;

END IF;

CLOSE csrindexstats;

END LOOP;

DBMS_OUTPUT.PUT_LINE ('Global indices rebuilt: ' || TO_CHAR (vcount));

vcount := 0;

trial := 0;

/* Local indexes */

FOR vindexrec IN csrlocalindexes

LOOP

EXECUTE IMMEDIATE

'analyze index '

|| vindexrec.index_owner||'.'

|| vindexrec.index_name

|| ' partition ('

|| vindexrec.partition_name

|| ') validate structure';

OPEN csrindexstats;

FETCH csrindexstats INTO vindexstats;

IF csrindexstats%FOUND

THEN

IF    (vindexstats.height > pmaxheight)

OR (    vindexstats.leafrows > 0

AND vindexstats.leafrowsdeleted > 0

AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >

pmaxleafsdeleted)

THEN

vcount := vcount + 1;

DBMS_OUTPUT.PUT_LINE (

'Rebuilding index ' || vindexrec.index_owner||'.'|| vindexrec.index_name || '...');

<>

BEGIN

EXECUTE IMMEDIATE

'alter index '

|| vindexrec.index_owner||'.'

|| vindexrec.index_name

|| ' rebuild'

|| ' partition '

|| vindexrec.partition_name

|| ' parallel nologging compute statistics'

|| ' tablespace '

|| vindexrec.tablespace_name;

EXCEPTION

WHEN resource_busy OR TIMEOUT_ON_RESOURCE

THEN

DBMS_OUTPUT.PUT_LINE (

'alter partitioned index - busy and wait for 1 sec');

DBMS_LOCK.sleep (c_trial_interval);

IF trial <= c_max_trial

THEN

GOTO alter_partitioned_index;

ELSE

DBMS_OUTPUT.PUT_LINE (

'alter partitioned index busy and waited - quit after '

|| TO_CHAR (c_max_trial)

|| ' trials');

RAISE;

END IF;

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE (

'alter partitioned index err ' || SQLERRM);

RAISE;

END;

END IF;

END IF;

CLOSE csrindexstats;

END LOOP;

DBMS_OUTPUT.PUT_LINE ('Local indices rebuilt: ' || TO_CHAR (vcount));

END;

/

exit;

4、后记

a、如果同一台服务器上有多个实例，且每个实例有相同的schema，此脚本会轮巡所有实例并根据analyze结果来rebuild。

a、大家应根据需要作相应调整，如脚本的路径信息等。

b、需要修改相应的schema name。

d、可根据系统环境调整相应的并行度。