解決Oracle 11g安裝導致資料庫無法自動搜集統計資訊
近期發現個別Oracle 11g資料庫無法自動收集統計資訊,部分視圖查詢結果如下:
SQL> select client_name,status from dba_autotask_client where client_name = 'auto optimizer stats collection';
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
SQL>
SQL> select client_name,status from dba_autotask_task;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
SQL>
SQL> select window_name,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME AUTOTASK_STATUS
------------------------------ ---------------
WEDNESDAY_WINDOW DISABLED
FRIDAY_WINDOW DISABLED
SATURDAY_WINDOW DISABLED
THURSDAY_WINDOW DISABLED
TUESDAY_WINDOW DISABLED
SUNDAY_WINDOW DISABLED
MONDAY_WINDOW DISABLED
7 rows selected
發現作業全部DISABLED,經測試發現和資料庫安裝時未勾選“啟用自動維護任務”的現象一致(預設勾選),可能是之前DBA安裝時取消勾選,如:
由於未勾選“啟用自動維護任務“,資料安裝會執行如下命令,導致作業disable:
execute DBMS_AUTO_TASK_ADMIN.disable();
以後資料庫如果需要啟動自動維護任務,只需要執行如下命令:
SQL> execute DBMS_AUTO_TASK_ADMIN.enable();
PL/SQL procedure successfully completed
SQL> select window_name,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME AUTOTASK_STATUS
------------------------------ ---------------
WEDNESDAY_WINDOW ENABLED
FRIDAY_WINDOW ENABLED
SATURDAY_WINDOW ENABLED
THURSDAY_WINDOW ENABLED
TUESDAY_WINDOW ENABLED
SUNDAY_WINDOW ENABLED
MONDAY_WINDOW ENABLED
7 rows selected