Tuesday, July 17, 2012

Disable or Enable all Oracle jobs

There  are two  kinds  of  jobs  dbms_job and  dbms_scheduler  jobs
  1. Disable
    • dbms_job
      alter system set  job_queue_processes=0;
    • dbms_scheduler
      exec dbms_scheduler.set_scheduler_attribute('scheduler_disabled','true');
  2. Enable
    • dbms_job
      alter system set job_queue_processes=10;
    • dbms_scheduler
      exec dbms_scheduler.set_scheduler_attribute('scheduler_disabled','true');

Tuesday, January 10, 2012

ORA-08104: this index object 136450 is being online built or rebuilt

Problem:While running an online index rebuild your session was killed  so  second attemp will give  following error.

alter index MARDATA.COR_VERGI_NO_HAR_X2  rebuild online
tablespace MARBASINDEX_1M storage(initial 1m next 1m) compress 2;

ERROR at line 1:

ORA-08104: this index object 136450 is being online built or rebuilt
Cause:
you have not installed the patch for Bug 3805539 or are not running on a release that includes this fix.So smon is   very slow   in cleaning up 

Solution:Use  dbms_repair.online_index_clean to  clean  up .

declare
  result boolean ;
begin
result:=DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
end ;
/

After  then you can   rebuild index.