--创建顺序
programms-schedule-job
--更改job中的p1为p2
--重建调用p2的文件在SYS用户下
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.