创建调度任务自动完成索引重建

--创建顺序
programms-schedule-job

其中创建3个programms分别可执行脚本，匿名pl/sql，procedure。

--创建P1,P2,P3

--创建Schedule

--创建job

--p1
--使索引无效
SCOTT@PROD1> alter table emp move;

Table altered.

SCOTT@PROD1> alter table dept move;

Table altered.

SCOTT@PROD1> select index_name,status from user_indexes;

INDEX_NAME		       STATUS
------------------------------ --------
E_I			       VALID
BIN$RMuU96dgD73gU0edqMCaWg==$0 UNUSABLE
BIN$RMuU96dcD73gU0edqMCaWg==$0 UNUSABLE
BIN$RMuU96ddD73gU0edqMCaWg==$0 UNUSABLE
BIN$RMuU96deD73gU0edqMCaWg==$0 UNUSABLE
BIN$RMuU96dfD73gU0edqMCaWg==$0 UNUSABLE
PK_DEPT 		       UNUSABLE
SYS_C0018329		       VALID

8 rows selected.

SYS@PROD1> exec dbms_scheduler.run_job('scott_index_job1');  --手动执行job

PL/SQL procedure successfully completed.

SCOTT@PROD1> select index_name,status from user_indexes;

INDEX_NAME		       STATUS
------------------------------ --------
E_I			       VALID
BIN$RMuU96dgD73gU0edqMCaWg==$0 VALID
BIN$RMuU96dcD73gU0edqMCaWg==$0 VALID
BIN$RMuU96ddD73gU0edqMCaWg==$0 VALID
BIN$RMuU96deD73gU0edqMCaWg==$0 VALID
BIN$RMuU96dfD73gU0edqMCaWg==$0 VALID
PK_DEPT 		       VALID
SYS_C0018329		       VALID

8 rows selected.

--更改job中的p1为p2

--调用p2
SCOTT@PROD1> alter table emp move;

Table altered.

SCOTT@PROD1> alter table dept move;

Table altered.

SYS@PROD1> exec dbms_scheduler.run_job('scott_index_job1');  --手动执行job

PL/SQL procedure successfully completed.

SCOTT@PROD1> select index_name,status from user_indexes;  --无效果

INDEX_NAME		       STATUS
------------------------------ --------
E_I			       VALID
BIN$RMuU96dgD73gU0edqMCaWg==$0 UNUSABLE
BIN$RMuU96dcD73gU0edqMCaWg==$0 UNUSABLE
BIN$RMuU96ddD73gU0edqMCaWg==$0 UNUSABLE
BIN$RMuU96deD73gU0edqMCaWg==$0 UNUSABLE
BIN$RMuU96dfD73gU0edqMCaWg==$0 UNUSABLE
PK_DEPT 		       UNUSABLE
SYS_C0018329		       VALID

8 rows selected.

--重建调用p2的文件在SYS用户下

--并重建job

SYS@PROD1> exec dbms_scheduler.run_job('scott_index_job1');  --手动执行job

PL/SQL procedure successfully completed.

SCOTT@PROD1> select index_name,status from user_indexes;  --成功

INDEX_NAME		       STATUS
------------------------------ --------
E_I			       VALID
BIN$RMuU96dgD73gU0edqMCaWg==$0 VALID
BIN$RMuU96dcD73gU0edqMCaWg==$0 VALID
BIN$RMuU96ddD73gU0edqMCaWg==$0 VALID
BIN$RMuU96deD73gU0edqMCaWg==$0 VALID
BIN$RMuU96dfD73gU0edqMCaWg==$0 VALID
PK_DEPT 		       VALID
SYS_C0018329		       VALID

8 rows selected.