Disable and enable daily nightly automatic statistics gathering job in oracle 10g:
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
Disable:
BEGIN
DBMS_SCHEDULER.disable('GATHER_STATS_JOB');
END;
Enable:
BEGIN
DBMS_SCHEDULER.enable('GATHER_STATS_JOB');
END;
Query how much table data has changed daily:
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
select * from DBA_TAB_MODIFICATIONS where table_owner='user1'
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
Disable:
BEGIN
DBMS_SCHEDULER.disable('GATHER_STATS_JOB');
END;
Enable:
BEGIN
DBMS_SCHEDULER.enable('GATHER_STATS_JOB');
END;
Query how much table data has changed daily:
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
select * from DBA_TAB_MODIFICATIONS where table_owner='user1'