Showing posts from February, 2014

automated statistics gathering failed in Oracle

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_name, repeat_interval,duration, windo…

Unable to recompile invalid package bodies after data pump import

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>;