创建使用program对无效索引进行重建

--创建使用program对无效索引进行重建

• shell脚本
• 匿名pl/sql
• 创建procedure

--模拟将索引不可用
SYS@PROD2> alter table scott.emp move;

Table altered.

SYS@PROD2> alter table scott.dept move;

Table altered.

SYS@PROD2> select 'alter index '||owner||'."'||index_name||'" rebuild;' from dba_indexes where OWNER='SCOTT' and status='UNUSABLE';

'ALTERINDEX'||OWNER||'."'||INDEX_NAME||'"REBUILD;'
------------------------------------------------------------------------------------
alter index SCOTT."PK_DEPT" rebuild;
alter index SCOTT."PK_EMP" rebuild;

1.通过shell脚本实现

[oracle@ocm1 ~]$ vi index_re.sh  --编写脚本
[oracle@ocm1 ~]$ cat index_re.sh 
#!/bin/bash

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=PROD2
sqlplus / as sysdba <<EOF
spool /tmp/i.sql rep
select 'alter index '||owner||'."'||index_name||'" rebuild;' from dba_indexes where OWNER='SCOTT' and status='UNUSABLE';
spool off
ho sed '/^alter index /p' -n /tmp/i.sql > /tmp/i1.sql
start /tmp/i1.sql
EOF
[oracle@ocm1 ~]$ chmod +x index_re.sh --给予执行权限
[oracle@ocm1 ~]$ ./index_re.sh 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 6 15:58:20 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD2> SYS@PROD2> 
'ALTERINDEX'||OWNER||'."'||INDEX_NAME||'"REBUILD;'
------------------------------------------------------------------------------------
alter index SCOTT."PK_DEPT" rebuild;
alter index SCOTT."PK_EMP" rebuild;

SYS@PROD2> SYS@PROD2> 
SYS@PROD2> 
Index altered.


Index altered.

SYS@PROD2> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 6 15:58:30 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--验证索引确实被重建
SYS@PROD2> select 'alter index '||owner||'."'||index_name||'" rebuild;' from dba_indexes where OWNER='SCOTT' and status='UNUSABLE';

no rows selected

2.编写匿名sql

SYS@PROD2> alter table scott.dept move;
al
Table altered.

SYS@PROD2> alter table scott.emp move;

Table altered.

SYS@PROD2> begin
  2  for i in (select index_name from user_indexes where status='UNUSABLE') loop
  3  execute immediate 'alter index "'||i.index_name||'" rebuild';
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SYS@PROD2> save index_plsql.sql
Created file index_plsql.sql
SYS@PROD2> conn scott/tiger
Connected.

SCOTT@PROD2> start index_plsql.sql

PL/SQL procedure successfully completed.

SCOTT@PROD2> select index_name,status from user_indexes;

INDEX_NAME		       STATUS
------------------------------ --------
PK_EMP			       VALID
PK_DEPT 		       VALID

3.创建procedure

SCOTT@PROD2> create or replace procedure index_rebuild
  2  is
  3  begin
  4  for i in (select index_name from user_indexes where status='UNUSABLE') loop
  5  execute immediate 'alter index "'||i.index_name||'" rebuild';
  6  end loop;
  7  end;
  8  /

Procedure created.

SCOTT@PROD2> save index_procudure.sql
Created file index_procudure.sql
SCOTT@PROD2> alter table emp move;

Table altered.

SCOTT@PROD2> alter table dept move;

Table altered.

SCOTT@PROD2> select index_name,status from user_indexes;

INDEX_NAME		       STATUS
------------------------------ --------
PK_EMP			       UNUSABLE
PK_DEPT 		       UNUSABLE

SCOTT@PROD2> exec index_rebuild;

PL/SQL procedure successfully completed.

SCOTT@PROD2> select index_name,status from user_indexes;

INDEX_NAME		       STATUS
------------------------------ --------
PK_EMP			       VALID
PK_DEPT 		       VALID