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

来源：互联网发布：oa网络地板编辑：程序博客网时间：2024/06/06 17:54

--创建使用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/bashexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1export PATH=$ORACLE_HOME/bin:$PATHexport ORACLE_SID=PROD2sqlplus / as sysdba <<EOFspool /tmp/i.sql repselect 'alter index '||owner||'."'||index_name||'" rebuild;' from dba_indexes where OWNER='SCOTT' and status='UNUSABLE';spool offho sed '/^alter index /p' -n /tmp/i.sql > /tmp/i1.sqlstart /tmp/i1.sqlEOF[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 2017Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@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 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@ocm1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 6 15:58:30 2017Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith 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.编写匿名sqlSYS@PROD2> alter table scott.dept move;alTable 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.sqlCreated file index_plsql.sqlSYS@PROD2> conn scott/tigerConnected.SCOTT@PROD2> start index_plsql.sqlPL/SQL procedure successfully completed.SCOTT@PROD2> select index_name,status from user_indexes;INDEX_NAME       STATUS------------------------------ --------PK_EMP       VALIDPK_DEPT        VALID

3.创建procedureSCOTT@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.sqlCreated file index_procudure.sqlSCOTT@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       UNUSABLEPK_DEPT        UNUSABLESCOTT@PROD2> exec index_rebuild;PL/SQL procedure successfully completed.SCOTT@PROD2> select index_name,status from user_indexes;INDEX_NAME       STATUS------------------------------ --------PK_EMP       VALIDPK_DEPT        VALID

0 0