--禁用自动收集
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
--启用自动收集
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
--查看job
select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
SQL> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor DISABLED
sql tuning advisor DISABLED
sqlplus / as sysdba<<!
BEGIN
dbms_stats.gather_schema_stats(
ownname=>'VXSPACE',
METHOD_OPT=>'for all columns size repeat',
CASCADE=>TRUE,
ESTIMATE_PERCENT=>100,
DEGREE=>2);
END;
/
exit
!