shivenracdba

Sometimes automated statistics job failed on large table or table partition segments. In that case , you have to find out the automatic stats job scheduling , its running time and failed times and how to resolve them . By default , automatic stats job runs every weekday night between 22:00 hours and 06:00 hours.You can find out these information  by below queries :- SELECT * FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection'; SELECT window_group_name, window_name FROM dba_scheduler_wingroup_members; SELECT window_name, start_time, duration FROM dba_autotask_schedule; select * from dba_autotask_client; SELECT client_name, job_status, job_start_time, job_duration, job_info FROM dba_autotask_job_history WHERE client_name like '%stats%' ORDER BY job_start_time; select Owner, job_name, program_name, schedule_name, schedule_type, failure_count, last_start_date , comments from dba_scheduler_jobs; select window_...

Sometimes after IMPORT , the user is unable to recompile invalid package bodies after data pump import. This issue arises when sys objects has been also imported . To solve this issue , Please see the below view :- select * from user_errors ; You may  found some privileges are missing . Then grant them :-  grant execute on SYS.DBMS_LOCK to <usr_name>; grant execute on SYS.DBMS_FLASHBACK to <usr_name>; grant execute on SYS.DBMS_SYS_SQL to <usr_name>;