Oracle 10g Scheduler Job Email Notification

----------------------------------------------------start job_notification.sql---------------------

-- Run this script as SYS with 2 parameters, an outgoing e-mail SMTP server
-- and port (normally 25) for example:
--   SQL> connect sys/sys_password as sysdba
--   SQL> @job_notification smtp.example.com 25
--
-- This script creates 2 procedures to let users setup e-mail notification for
-- jobs they have access to and grants execute on these to public. It also
-- creates public synonyms for these procedures.
-- add_job_email_notification
-- remove_job_email_notification
--
-- It also creates a utility program and procedure
-- sys.email_notification_program
-- sys.email_notification_procedure
--
-- WARNING If any of these objects exist, they will be replaced.
--
-- To remove the objects created by this script, run the accompanying
-- remove_job_notification.sql script. This script also removes any job
-- e-mail notifications that have been added.

-- DBA either define the below variables or pass them into the script
prompt Enter an outgoing e-mail SMTP server host
define email_server_host    = &1
prompt Enter an outgoing e-mail SMTP server port
define email_server_port    = &2

-- this must be run as SYS (to do privilege checking) so check this
begin
  if sys_context('USERENV','SESSION_USER') != 'SYS' then
          raise_application_error(-20000, 'This script must be run AS SYS');
  end if;
end;
/

-- create a utility procedure to send e-mail for a scheduler job event
create or replace procedure sys.email_notification_procedure
( message           IN sys.scheduler$_event_info,
  recipient_address IN VARCHAR2,
  email_server_host IN VARCHAR2,
  sender_address    IN VARCHAR2,
  subject_prefix    IN VARCHAR2,
  email_server_port IN PLS_INTEGER) AS
msg_text          VARCHAR2(4000);
mail_conn         utl_smtp.connection;
all_addresses     VARCHAR2(4000) := ','||recipient_address;
single_address    VARCHAR2(500);
BEGIN

  -- compose the message
  msg_text := 'Job: "'||message.object_owner||'"."'||message.object_name||'"'||
                UTL_TCP.CRLF ||
              'Event: '|| message.event_type || UTL_TCP.CRLF ||
              'Date: '|| regexp_replace(message.event_timestamp,'\.[0-9]{3,}') ||
                UTL_TCP.CRLF;

  IF message.log_id IS NOT NULL AND message.log_id != 0 THEN
    msg_text := msg_text || 'Log ID: '||message.log_id||UTL_TCP.CRLF;
  END IF;

  IF message.error_code IS NOT NULL AND message.error_code != 0 THEN
    msg_text := msg_text ||
      'Error code: ' || message.error_code|| UTL_TCP.CRLF ||
      'Error message: ' || UTL_TCP.CRLF || message.error_msg;
  END IF;


  -- now send the e-mail
  mail_conn := utl_smtp.open_connection(email_server_host,email_server_port);
  utl_smtp.helo(mail_conn, email_server_host);
  utl_smtp.mail(mail_conn, sender_address);

  single_address := regexp_substr(all_addresses,'^,[^,]*');
  all_addresses := regexp_replace(all_addresses,'^,[^,]*');

  WHILE single_address IS NOT NULL AND
    regexp_substr(single_address, '[[:graph:]]') IS NOT NULL
  LOOP
    utl_smtp.rcpt(mail_conn, replace(single_address,','));
    single_address := regexp_substr(all_addresses,'^,[^,]*');
    all_addresses := regexp_replace(all_addresses,'^,[^,]*');
  END LOOP;

  utl_smtp.open_data(mail_conn);
  utl_smtp.write_data(mail_conn, 'From: ' || sender_address ||    UTL_TCP.CRLF);
  utl_smtp.write_data(mail_conn, 'To: ' || recipient_address ||     UTL_TCP.CRLF);
  utl_smtp.write_data(mail_conn, 'Subject: ' || subject_prefix ||     ' - ' ||  message.object_owner || '.' || message.object_name ||     ' ' || message.event_type || UTL_TCP.CRLF || UTL_TCP.CRLF);
  utl_smtp.write_data(mail_conn, msg_text || UTL_TCP.CRLF);
  utl_smtp.close_data(mail_conn);
  utl_smtp.quit(mail_conn);
END;
/

show errors

grant execute on sys.email_notification_procedure to public;

-- drop any existing email_notification_program, suppressing all errors
begin
  dbms_scheduler.drop_program (
      program_name => 'sys.email_notification_program',
      force => true);
exception when others then null;
end;
/

-- create a utility program to send e-mail for a scheduler job event
begin
  dbms_scheduler.create_program (
      program_name => 'sys.email_notification_program',
      program_action=> 'sys.email_notification_procedure ',
      program_type => 'STORED_PROCEDURE',
      number_of_arguments => 6,
      enabled => FALSE) ;

  dbms_scheduler.define_metadata_argument (
      program_name => 'email_notification_program',
      argument_position => 1 ,
      argument_name => 'message',
      metadata_attribute => 'EVENT_MESSAGE') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 2,
      argument_name => 'recipient_address',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 3,
      argument_name => 'email_server_host',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 4,
      argument_name => 'sender_address',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 5,
      argument_name => 'subject_prefix',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 6,
      argument_name => 'email_server_port',
      argument_type => 'number') ;

  dbms_scheduler.enable ('email_notification_program');
end ;
/

grant execute on sys.email_notification_program to public;

-- Sets up e-mail notification for selected job events
-- for a given job.
-- This creates an e-mailer job in the job owner's schema
-- The default events chosen for e-mail notification are:
-- JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED
-- The caller must be the job owner or have alter privileges on the job.
create or replace procedure sys.add_job_email_notification
(
  job_name  IN VARCHAR2,
  recipient_address IN VARCHAR2,
  events            IN VARCHAR2 DEFAULT
     'JOB_SUCCEEDED, JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED',
  sender_address    IN VARCHAR2 DEFAULT 'noreply@apexsoftcell.com',
  subject_prefix    IN VARCHAR2 DEFAULT 'Oracle Scheduler Job Notification',
  email_server_host IN VARCHAR2 DEFAULT '&email_server_host',
  email_server_port IN PLS_INTEGER DEFAULT &email_server_port
) AS
events_on         NUMBER := 0;
job_object        VARCHAR2(35);
job_owner         VARCHAR2(35);
canon_job_name    VARCHAR2(30);
canon_job_owner   VARCHAR2(30);
caller            VARCHAR2(30) := sys_context('USERENV','SESSION_USER');
notifier_job_name VARCHAR2(30);
new_raise_events  NUMBER := 0;
cur_raise_events  VARCHAR2(200);
priv_count        NUMBER;
event_condition   VARCHAR2(4000);
comments_text     VARCHAR2(100) :=
  'Auto-generated job to send email alerts for job ';
type event_name_list is table of varchar2(30);
type event_number_list is table of number;
event_names event_name_list :=  event_name_list('JOB_STARTED',
  'JOB_SUCCEEDED', 'JOB_FAILED', 'JOB_BROKEN', 'JOB_COMPLETED',
  'JOB_STOPPED', 'JOB_SCH_LIM_REACHED', 'JOB_DISABLED',
  'JOB_CHAIN_STALLED',  'JOB_OVER_MAX_DUR');
event_numbers event_number_list :=
  event_number_list(1,2,4,8,16,32,64,128,256,512);
event_found       pls_integer := 0;
event_10862       pls_integer := 0;
begin

  -- get job name and owner
  sys.dbms_isched.resolve_name(job_name, job_object, job_owner, caller);

  -- canonicalize job name and owner
  dbms_utility.canonicalize(job_object, canon_job_name, 30);
  dbms_utility.canonicalize(job_owner, canon_job_owner, 30);
  comments_text := comments_text ||'"'||canon_job_owner ||'"."'||canon_job_name||'"' ;

  -- check if the caller has privileges on the job

  -- check if the caller is the job owner or 'SYS'
  IF canon_job_owner = caller or caller = 'SYS' THEN
    goto privilege_check_passed;
  END IF;

  -- check whether the caller has been granted ALTER on the job or
  -- CREATE ANY JOB or SCHEDULER_ADMIN or DBA directly
  select count(*) into priv_count from dba_sys_privs where grantee=caller and
    privilege='CREATE ANY JOB';
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  select count(*) into priv_count  from dba_role_privs where grantee=caller and
    granted_role='SCHEDULER_ADMIN';
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  select count(*) into priv_count from dba_tab_privs where grantee=caller and
    owner=canon_job_owner and table_name=canon_job_name;
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- recursive privileges check for CREATE ANY JOB system priv
  -- includes a recursive roles check so SCHEDULER_ADMIN will also work
  -- this is slow but all simple privilege checks have failed
  select count(*) into priv_count from (
    select grantee, granted from
    (
      /* roles granted */
      select grantee, granted_role granted from dba_role_privs
      /* system privileges granted */
      union
      select grantee, privilege granted from dba_sys_privs
    )
  start with grantee = caller connect by grantee = prior granted )
  where granted = 'CREATE ANY JOB';

  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- recursive privileges check whether the caller has object privileges on the job
  -- this is slow but all simple privilege checks have failed
  select count(*) into priv_count from (
  select * from
    (
    /* object privileges granted */
      select table_name g1, owner g2, grantee obj, grantee own, privilege typ
      from dba_tab_privs
    /* role privileges granted */
    union
      select granted_role  g1, granted_role  g2, grantee, grantee, null
      from dba_role_privs
    )
  start with g1 = canon_job_name and g2 = canon_Job_owner
  connect by g1 = prior obj and g2 = prior own)
  where obj=caller;

  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- no privileges, throw job_does_exist error
  dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name,    TRUE);

<<privilege_check_passed>>

  -- retrieve current events turned on. cast NO_DATA_FOUND to job not found
  begin
  select raise_events into cur_raise_events from dba_scheduler_jobs where
  job_name=canon_job_name and owner=canon_job_owner ;
  exception when no_data_found then
    dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name,
      TRUE);
  when others then raise;
  end;

  -- generate event_condition
  event_condition := 'tab.user_data.object_owner = '''||canon_job_owner||
            ''' AND tab.user_data.object_name = '''||canon_job_name|| '''';

  if instr(UPPER(events),'JOB_ALL_EVENTS')>0 then
    -- by default we have no events clause so all events will trigger an e-mail
    event_found := 1;
  else
    event_condition := event_condition ||' AND tab.user_data.event_type in (';

    for i in event_names.first..event_names.last loop
      if instr(UPPER(events),event_names(i))>0 then
        event_condition := event_condition || ''''||event_names(i)||''',';
        event_found := 1;
      end if;
    end loop;

    if instr(UPPER(events),'JOB_RUN_COMPLETED')>0 then
        event_condition := event_condition ||
          '''JOB_SUCCEEDED'',''JOB_FAILED'',''JOB_STOPPED'',';
        event_found := 1;
    end if;

    -- strip last comma and add close brace
    event_condition := regexp_replace(event_condition, ',$');
    event_condition := event_condition || ')';
  end if;

  -- if no events have been specified, throw an error
  if event_found = 0 then
    dbms_sys_error.raise_system_error(-24098, events, 'EVENTS',TRUE);
  end if;

  -- collect all events to turn on
  if cur_raise_events is null then
    cur_raise_events := UPPER(events) ;
  else
    cur_raise_events := UPPER(events) ||','||UPPER(cur_raise_events) ;
  end if;

  for i in event_names.first..event_names.last loop
    if instr(cur_raise_events,event_names(i))>0 then
      new_raise_events := new_raise_events + event_numbers(i);
    end if;
  end loop;

  if instr(cur_raise_events,'JOB_RUN_COMPLETED')>0 then
    new_raise_events := new_raise_events -
      bitand(new_raise_events,sys.dbms_scheduler.job_run_completed) +
      sys.dbms_scheduler.job_run_completed;
  end if;

  if instr(cur_raise_events,'JOB_ALL_EVENTS')>0 then
    new_raise_events := new_raise_events -
      bitand(new_raise_events,sys.dbms_scheduler.job_all_events) +
      sys.dbms_scheduler.job_all_events;
  end if;

  -- turn on events the user is interested in
  dbms_scheduler.set_attribute
    ( '"'||canon_job_owner||'"."'||canon_job_name||'"' , 'raise_events' ,
      new_raise_events);

  -- set event 10862 if not set so that we can add a subscriber
  -- this is necessary because if event 10862 is not set then AQ is in backward
  -- compatibility mode which checks the login user instead of the current user
  -- for privileges.
  dbms_system.read_ev(10862, event_10862);
  IF event_10862 = 0 THEN
    EXECUTE IMMEDIATE
      'ALTER SESSION SET EVENTS ''10862 TRACE NAME CONTEXT FOREVER, LEVEL 1''';
  END IF;

  -- add a new subscriber for this notification
  BEGIN
    dbms_aqadm.add_subscriber
     (queue_name => 'SYS.SCHEDULER$_EVENT_QUEUE',
      subscriber => sys.aq$_agent(canon_job_owner, NULL, NULL),
      rule => 'tab.user_data.object_owner = '''||canon_job_owner||'''');
  EXCEPTION WHEN others then

    -- unset event 10862, if we set it above
    IF event_10862 = 0 THEN
      EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ' ||
        '''10862 TRACE NAME CONTEXT OFF''' ;
    END IF;

    if sqlcode = -24034 then NULL;
    else raise;
    end if;
  end;

  -- unset event 10862, if we set it above
  IF event_10862 = 0 THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ' ||
      '''10862 TRACE NAME CONTEXT OFF''' ;
  END IF;

  -- allow the job owner to access our events queue via this subscriber
  dbms_aqadm.enable_db_access(canon_job_owner,'"'||canon_job_owner||'"');

  -- if this procedure has been run already for this job, drop previously
  -- created email notification jobs. One of the parameters might have changed
  -- and existing email notification jobs might have been altered or broken.
  FOR old_notify_job IN
  ( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
    owner = canon_job_owner and job_name like substr(canon_job_name,1,10) || '_EMAILER%'
    and comments = comments_text and program_owner = 'SYS'
    and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
  )
  LOOP
    dbms_scheduler.drop_job(old_notify_job.name);
  END LOOP;

  -- now create a notifier job which waits on job events
  BEGIN
  -- try using a simple name without an added number suffix
  notifier_job_name := substr(canon_job_name,1,10) || '_EMAILER' ;
  notifier_job_name := '"' || canon_job_owner || '"."' || notifier_job_name ||'"' ;
  dbms_scheduler.create_job(notifier_job_name,
                            program_name => 'sys.email_notification_program',
                            event_condition => event_condition,
                            queue_spec =>'sys.scheduler$_event_queue,"'
                              || canon_job_owner||'"',
                            comments => comments_text);
  EXCEPTION WHEN OTHERS THEN
  IF sqlcode != -27477 THEN RAISE; END IF;
  -- a job already exists using our simple name, add a numerical suffix
  notifier_job_name :=
  dbms_scheduler.generate_job_name( '"'||substr(canon_job_name,1,10)||'_EMAILER"');
  notifier_job_name := '"' || canon_job_owner || '"."' || notifier_job_name ||'"' ;

  dbms_scheduler.create_job(notifier_job_name,
                            program_name => 'sys.email_notification_program',
                            event_condition => event_condition,
                            queue_spec =>'sys.scheduler$_event_queue,"'
                              || canon_job_owner||'"',
                            comments => comments_text);
  END;

  dbms_scheduler.set_job_argument_value(notifier_job_name,2,
                                        recipient_address);

  dbms_scheduler.set_job_argument_value(notifier_job_name,3,
                                        email_server_host);

  dbms_scheduler.set_job_argument_value(notifier_job_name,4,
                                        sender_address);

  dbms_scheduler.set_job_argument_value(notifier_job_name,5,
                                        subject_prefix);

  dbms_scheduler.set_job_argument_value(notifier_job_name,6,
                                        to_char(email_server_port));

  dbms_scheduler.enable(notifier_job_name);

end;
/

show errors

create or replace public synonym add_job_email_notification for
  sys.add_job_email_notification;

grant execute on sys.add_job_email_notification to public;

-- Removes e-mail notification for a given job.
-- This removes the e-mailer job that was created for the job in the job
-- owner's schema.
-- It does not remove the agent created for the job owner or reset raise_events
-- for the given job.
-- The caller must be the job owner or have alter privileges on the given job.
create or replace procedure sys.remove_job_email_notification
(
  job_name  IN VARCHAR2) AS
job_object        VARCHAR2(35);
job_owner         VARCHAR2(35);
canon_job_name    VARCHAR2(30);
canon_job_owner   VARCHAR2(30);
caller            VARCHAR2(30) := sys_context('USERENV','SESSION_USER');
notifier_job_name VARCHAR2(30);
priv_count        NUMBER;
comments_text     VARCHAR2(100) :=
  'Auto-generated job to send email alerts for job ';
begin

  -- get job name and owner
  sys.dbms_isched.resolve_name(job_name, job_object, job_owner, caller);

  -- canonicalize job name and owner
  dbms_utility.canonicalize(job_object, canon_job_name, 30);
  dbms_utility.canonicalize(job_owner, canon_job_owner, 30);
  comments_text := comments_text ||'"'||canon_job_owner ||'"."'||canon_job_name||'"' ;

  -- check if the caller has privileges on the job

  -- check if the caller is the job owner or 'SYS'
  IF canon_job_owner = caller or caller = 'SYS' THEN
    goto privilege_check_passed;
  END IF;

  -- check whether the caller has been granted ALTER on the job or
  -- CREATE ANY JOB or SCHEDULER_ADMIN or DBA directly
  select count(*) into priv_count from dba_sys_privs where grantee=caller and
    privilege='CREATE ANY JOB';
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  select count(*) into priv_count  from dba_role_privs where grantee=caller and
    granted_role='SCHEDULER_ADMIN';
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  select count(*) into priv_count from dba_tab_privs where grantee=caller and
    owner=canon_job_owner and table_name=canon_job_name;
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- recursive privileges check for CREATE ANY JOB system priv
  -- includes a recursive roles check so SCHEDULER_ADMIN will also work
  -- this is slow but all simple privilege checks have failed
  select count(*) into priv_count from (
    select grantee, granted from
    (
      /* roles granted */
      select grantee, granted_role granted from dba_role_privs
      /* system privileges granted */
      union
      select grantee, privilege granted from dba_sys_privs
    )
  start with grantee = caller connect by grantee = prior granted )
  where granted = 'CREATE ANY JOB';

  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- recursive privileges check whether the caller has object privileges on the job
  -- this is slow but all simple privilege checks have failed
  select count(*) into priv_count from (
  select * from
    (
    /* object privileges granted */
      select table_name g1, owner g2, grantee obj, grantee own, privilege typ
      from dba_tab_privs
    /* role privileges granted */
    union
      select granted_role  g1, granted_role  g2, grantee, grantee, null
      from dba_role_privs
    )
  start with g1 = canon_job_name and g2 = canon_Job_owner
  connect by g1 = prior obj and g2 = prior own)
  where obj=caller;

  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- no privileges, throw job_does_exist error
  dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name,
    TRUE);

<<privilege_check_passed>>

  -- drop created email notification jobs.
  FOR old_notify_job IN
  ( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
    owner = canon_job_owner and job_name like substr(canon_job_name,1,10) || '_EMAILER%'
    and comments = comments_text and program_owner = 'SYS'
    and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
  )
  LOOP
    dbms_scheduler.drop_job(old_notify_job.name);
  END LOOP;

end;
/

show errors

create or replace public synonym remove_job_email_notification for
  sys.remove_job_email_notification;

grant execute on sys.remove_job_email_notification to public;

----------------------------------------------------end job_notification.sql---------------------

exec add_job_email_notification(job_name => 'EXPORT_JOB', subject_prefix => 'Apex Job Notification', recipient_address => 'kshitij@apexsoftcell.com',sender_address =>'info@apexsoftcell.com', email_server_host => 'mail.apexsoftcell.com', email_server_port => 25);

exec  DBMS_SCHEDULER.run_job ('EXPORT_JOB');

----------------------------------------------------start 

remove_job_notification.sql

--------------------

-- Run this script as SYS for example:

--   SQL> connect sys/sys_password as sysdba

--   SQL> @remove_job_notification

-- This script removes all objects created by the accompanying

-- job_notification.sql script. It also removes any job e-mail

-- notifications that have been added.

-- this must be run as SYS (to do privilege checking) so check this

begin

  if sys_context('USERENV','SESSION_USER') != 'SYS' then

          raise_application_error(-20000, 'This script must be run AS SYS');

  end if;

end;

/

drop procedure sys.email_notification_procedure;

begin

  dbms_scheduler.drop_program (

      program_name => 'sys.email_notification_program',

      force => true);

end;

/

drop public synonym add_job_email_notification;

drop procedure sys.add_job_email_notification;

drop public synonym remove_job_email_notification;

drop procedure sys.remove_job_email_notification;

-- now remove any e-mailer jobs that have been created

-- They won't run anyway since the program they point to has been removed.

BEGIN

  FOR old_notify_job IN

  ( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE

    job_name like '%_EMAILER%'

    and comments like 'Auto-generated job to send email alerts for job%'

    and program_owner = 'SYS'

    and program_name = 'EMAIL_NOTIFICATION_PROGRAM'

  )

  LOOP

    dbms_scheduler.drop_job(old_notify_job.name, TRUE);

  END LOOP;

END;

/

----------------------------------------------------end 

remove_job_notification.sql

--------------------

Oracle Job Scheduler for Old Analyze Method / Full Analyze

create or replace procedure AnalyzeFull as
BEGIN
 FOR cur_rec IN (SELECT distinct table_name
                  FROM   dba_tables) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ANALYZE TABLE '|| cur_rec.table_name ||' COMPUTE STATISTICS' ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
 FOR cur_rec IN (SELECT distinct index_name
                  FROM   dba_indexes) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ANALYZE INDEX '|| cur_rec.index_name ||' COMPUTE STATISTICS' ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'analyze_full',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN analyzefull; END;',
   start_date      => '01-APR-12 02:00.00.00 AM ASIA/CALCUTTA',
  repeat_interval => 'FREQ=WEEKLY; BYDAY=FRI,SAT,SUN; BYHOUR=02; BYMINUTE=01;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Analyze all tables indexes');
END;
/

Oracle Job Scheduler for Rename Export dump file / OS files

UTL_FILE.FRENAME ( location  IN VARCHAR2,
   filename  IN VARCHAR2,
   dest_dir  IN VARCHAR2,
   dest_file IN VARCHAR2,
   overwrite IN BOOLEAN DEFAULT FALSE);


select * from dba_directories;

EXEC UTL_FILE.FRENAME ('EXPORT_AUTO','exp1213.DMP','EXPORT_AUTO','exp1213'||'_'||TO_CHAR(SYSDATE,'DDMMYYYY')||'.DMP');

------------------------

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'RENAMEexp',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN UTL_FILE.FRENAME (''EXPORT_AUTO'',''exp1213.DMP'',''EXPORT_AUTO'',''exp1213''||''_''||TO_CHAR(SYSDATE,''DDMMYYYY'')||''.DMP''); END;',
    start_date      => '01-APR-12 01.00.00 PM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB to rename dump file');
END;
/

exec dbms_scheduler.run_job('renameexp');

Oracle Job Scheduler for Remove export files / OS files

select * from dba_directories;

exec utl_file.fremove('MYDIRECTORY', 'test.txt');

EXEC UTL_FILE.FREMOVE ('EXPORT_AUTO','exp1213'||'_'||TO_CHAR(SYSDATE-2,'DDMMYYYY')||'.DMP');

------------------------

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'REMOVEexp',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN UTL_FILE.FREMOVE (''EXPORT_AUTO'',''exp1213''||''_''||TO_CHAR(SYSDATE-2,''DDMMYYYY'')||''.DMP''); END;',
    start_date      => '01-APR-12 01.00.00 PM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB to remove old dump file');
END;
/

exec dbms_scheduler.run_job('removeexp');

Oracle Job Scheduler Archivelog Deletion

CREATE OR REPLACE PROCEDURE archive_dir_setup AS

archive_dir VARCHAR2(40);

BEGIN

EXECUTE IMMEDIATE

'SELECT DESTINATION '||

' FROM v$archive_dest '||

' WHERE dest_name = ''LOG_ARCHIVE_DEST_1'''

INTO archive_dir;

EXECUTE IMMEDIATE

'CREATE DIRECTORY ARCHIVE_DIR '||

' AS '''||archive_dir||'''';

END archive_dir_setup;

/

EXEC archive_dir_setup;

CREATE OR REPLACE PROCEDURE archive_del as

BEGIN

FOR cur_rec IN (select name from v$archived_log where trunc(first_time)< trim(sysdate-7) ORDER BY first_time) LOOP

BEGIN

DBMS_OUTPUT.PUT_LINE ('begin utl_file.fremove(''ARCHIVE_DIR'','''||cur_rec.name||''') end;');

utl_file.fremove('ARCHIVE_DIR',''|| cur_rec.name ||'');

END;

END LOOP;

END archive_del;

/

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'archive_deletion',

job_type => 'STORED_PROCEDURE',

job_action => 'archive_del',

start_date => '01-APR-12 02:00.00.00 AM ASIA/CALCUTTA',

repeat_interval => 'freq=DAILY',

end_date => NULL,

enabled => TRUE,

comments => 'Delete 7 days old Archive logs');

END;

/

Difference Oracle on Windows & Unix

Advantages of Oracle UNIX:

Significant performance improvement

Provides High Availability

Contains in-depth system utilities and open-source code

Highly respected by Oracle personnel

Advantages of Oracle Windows:

Very easy to deploy and support

Requires far less IT training

Simple interface to Microsoft tools such as ODBC and .NET.

In my personal opinion, here are some specific disadvantages to Linux and Windows:

Disadvantages of Oracle UNIX:

Required specialized skills (vi editor, shell scripting, etc.)

Required highly-technical Systems Administrators and DBA

Contains in-depth system utilities and open-source code

Security Holes (if mis-configured)

Susceptible to root kit attacks

Disadvantages of Oracle Windows:

Slower than Linux

Less glamorous for the SA and DBA

History of poor reliability (bad reputation)

Security Holes (if mis-configured)

Susceptible to Internet viruses

The main disadvantage is regarding the requirements for a technical staff that is proficient in shell scripting, the vi editor and the cryptic UNIX command syntax.

Unlike the easy-to-use Windows GUI, Linux and proprietary UNIX often require cryptic shell scripts to perform basic Oracle functions. Given the vast differences in administration, begin with looking at porting from UNIX to Windows.

The core difference is that in UNIX the OS controls the operations, while in Windows the Oracle database controls the operations.

There is also the issue of the expense of licensing the proprietary UNIX software such as Solaris, AIX, and HP UNIX, which can be tens of thousands of dollars. This has led many companies to consider the public-domain Linux option. To understand the benefits and shortcomings of Linux, you must take a closer look at Linux technology.

With the increasing popularity of Intel-based database servers, Oracle shops are struggling to make the choice between Linux and Windows for their Oracle databases. As we may know, Windows has suffered from a history of unreliability and Linux suffers because of it’s nascent technology and lack of support.

Find here major differences of Oracle on Unix, Linux and Windows.

Windows is multitasking but doesn't multi user operating system. Unix/Linux is multitasking, multiuser and Multithreading operating system.

For installation of Oracle on windows doesn't require any other user creation, we can perform oracle installation using "administrator" superuser of windows. For installation of Oracle on Unix/linux required to creating separate operating system user account. Using super user "root" we doesn't require to perform Oracle installation.

For installation of Oracle on windows, if we create separate operating system then it should be group of super user administrator. For installation of Oracle on Unix/Linux, when we create operating system user then it should be not part of super user group.

Default location of password file and parameter file for Windows is ORACLE_HOME\database folder.Default location of password file and parameter file for Unix/Linux is ORACLE_HOME/dbs folder.

ORACLE_BASE,ORACLE_HOME,ORACLE_SID are defined in registry of Windows as HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0. ORACLE_BASE,ORACLE_HOME,ORACLE_SID are defined as user's environment variables in Unix/Linux.

Symbolic links are NOT supported for user's environment variables or registry parameter in Windows. Symbolic links are supported for user's environment variables in Unix/Linux.

In windows we should need to set environment variable using "set" command and it doesn't save in user profile. In Unix and Linux we should need to set environment variable using "export" command and it can save using .profile (in Unix) and .bash_profile (in Linux).

Oracle's shared libraries are called as shared DLL in windows. Oracle's shared libraries are available in Unix/Linux.

Relinking of Oracle executable is not available in Windows. Relinking of Oracle executable is available in Unix/Linux.

Shared memory , shared segments and semaphores are NOT adjustable in Windows. Shared memory segment(SHMMAX), shared segments (SHMMNI) and semaphores (SEMMNS) are adjustable using kernel parameters in Unix/Linux.

Oracle's SGA locking in real memory doesn't possible in Windows. Oracle's SGA locking in real memory is possible in Unix/Linux.

Each background process of Oracle is implementing as Thread inside single process in Windows. Each background process of Oracle is a process in Unix/Linux.

Windows called as GUI because it provides Graphical User Interface. Unix and Linux called as CLUI called Command Line User Interface. Due to this reason Unix and Linux provides more performance than Windows due to resource utilization.

Oracle on Unix,Linux & Windows: Comparison of Oracle on operating system level with memory,disk I/O, security, Installation etc . View large

Windows is flat file system. Unix and Linux is hierarchical model file system. Windows kernel stores in couple of files like Registry. Unix and Linux kernel stores in many files which are hierarchy. It is very easy to understand Unix and Linux file systems in any version.

Earlier FAT and FAT32 file system has no security in Windows. Using NTFS file system windows use file permission based security. In Unix and Linux has traditional file permission security with owner,group and other users.Unix has greater built-in security and permissions features than Windows. Linux contains also same type of security and permissions logic like Unix.

There are very few utilities available in Windows for performance monitoring and administration. There are lot of command line utilities are available in Unix/Linux for performance monitoring and administration.

Source code of Operating system doesn't available in Windows. Source code of Operating system is available in some of Linux flavors, means we can modify source code of operating system.

Oracle on Windows magnetize because easy to understand, easy to maintain, easy to develop, resource availability and with good support. Oracle on Unix/Linux is not easy to understand,easy to maintain or easy to develop because it requires high skill set and depth knowledge.

Oracle deployment is very easy in Windows because not need to more knowledge or special skill sets. Oracle deployment is not easy in Unix/Linux because it requires special skill sets.

Windows is user friendly operating system. Unix and Linux doesn't user friendly operating system.

There is high risk of virus attacks on Windows. Because majority of windows users run as Administrator and virus can be affecting on any of files of kernel due to super user account. There is minimum risk for virus attacks on Unix and Linux. Because most of Unix box or Linux box is being run by user interface not using "root" super user. Due to this reason virus attacker cannot able to modify kernel of operating system.

Oracle on Windows magnetize because easy to understand, easy to maintain, easy to develop, resource availability and with good support.Oracle on Unix/Linux is not easy to understand,easy to install, maintain or easy to develop because it requires high skill set and depth knowledge.

Above reasons explain that, Oracle database administration on Unix and Linux requires extra knowledge and special expertise. Oracle on Linux and Unix provides best security and performance. Large and critical databases are running on Linux, Unix and Windows. Remote DBA Services provider has expertise to manage all type operating systems and all versions of Oracle.

Compile Invalid Objects

SET HEADING OFF

spool c:\temp\invalid.sql ;

select OBJECT_NAME from dba_objects where STATUS='INVALID';

select

'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '."' || OBJECT_NAME || '" COMPILE;' from dba_objects where status = 'INVALID'

and object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','TRIGGER');

Select decode( object_type, 'PACKAGE BODY', 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;')

from dba_objects

where status = 'INVALID' and object_type in ('PACKAGE BODY') order by object_type;

select 'ALTER ' || OWNER || ' ' || OBJECT_TYPE || ' "' || OBJECT_NAME || '" COMPILE;' from dba_objects where status = 'INVALID'

and object_type in ('SYNONYM');

Select decode( object_type, 'TYPE', 'ALTER TYPE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;')

from dba_objects where status = 'INVALID' and object_type in ('TYPE') order by object_type;

Select decode( object_type, 'TYPE BODY', 'ALTER TYPE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;')

from dba_objects

where status = 'INVALID' and object_type in ('TYPE BODY') order by object_type;

spool out ;

@ c:\temp\invalid.sql ;

Job Scheduler for EXPDP Dump

CREATE DIRECTORY DPUMP_DIR1 AS 'f:\expdp1213';

GRANT read, write ON DIRECTORY EXPORT_AUTO TO ldbo;

begin

dbms_scheduler.create_job

(job_name => 'expdp',

job_type => 'EXECUTABLE',

job_action => 'expdp ldbo/ldbo@ksh1112srv full=Y directory=EXPORT_AUTO dumpfile=ksh1112.DMP LOGFILE=ksh1112.LOG',

start_date => '01-APR-12 09:00.00.00 PM ASIA/CALCUTTA',

repeat_interval => 'freq=DAILY',

enabled => TRUE,

comments=>'expdp');

end;

/

exec dbms_scheduler.run_job ('expdp');

Job Scheduler for EXP Dump

begin

dbms_scheduler.create_job

(job_name => 'exp1213',

job_type => 'EXECUTABLE',

job_action => 'exp ldbo/ldbo@nbs1112srv full=Y STATISTICS= NONE file=d:\expdp1213\exp1213.DMP log=d:\expdp1213\exp1213.log ',

start_date => '01-APR-12 09:00.00.00 PM ASIA/CALCUTTA',

repeat_interval => 'freq=DAILY',

enabled => TRUE,

comments=>'exp');

end;

/

exec dbms_scheduler.run_job ('exp1213');

Oracle 11g Job Email Notification

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','mail.kshitijdomain.com:25');

----------------exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','info@kshitijdomain.com');

BEGIN

DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (

job_name => 'COMPILE',

recipients => 'kshitij@kshitijdomain.com',

sender => 'info@kshitijdomain.com',

subject => 'Job Notification-%job_owner%.%job_name%-%event_type%',

body => '%event_type% at %event_timestamp%. %error_message%',

events => 'JOB_SUCCEEDED,JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR');

END;

/

--------------------------------------

SET serveroutput ON

declare

v_att VARCHAR2(64);

v_att2 varchar2(64);

BEGIN

DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', v_att);

DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', v_att2);

dbms_output.put_line('server: ' || v_att);

dbms_output.put_line('sender: ' || v_att2);

END;

/

------------------------------------

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'compile',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN UTL_RECOMP.recomp_serial(''DPCDSL''); END;',

start_date => '01-APR-12 11:00.00.00 PM ASIA/CALCUTTA',

repeat_interval => 'freq=DAILY',

end_date => NULL,

enabled => TRUE,

comments => 'JOB to compile invalid objects');

END;

/

exec DBMS_SCHEDULER.run_job ('compile');

------------------------------------------------------------------------------------------------------------------------

BEGIN

DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION ('COMPILE');

END;

/

foxrun.pif cannot start or run due to incompatibity with 64 bit version of windows. unsupported 16-bit application

---------------------------

Unsupported 16-Bit Application

---------------------------

The program or feature "\??\c:\program files (x86)\microsoft visual foxpro 7\foxrun.pif" cannot start or run due to incompatibity with 64-bit versions of Windows. Please contact the software vendor to ask if a 64-bit Windows compatible version is available.

Solution:

FOXRUN.PIF is just shortcut to MS-DOS program (COMMAND.COM)

Command.com is not exist in 64 bit machine.

CMD.EXE is working in 64 bit.

So make shortcut of C:\Windows\System32\cmd.exe at D:\LD\ with name of FOXRUN.PIF

Now your application will work fine.

-----alternate solution

16 bit applications do not run under 64 bits. foxrun.pif is not required in 64 bit machines.

\ld\foxrun.pif delete or rename it.

Find Duplicate records (Unique Index columns) in all Tables

SELECT 'SELECT COUNT(*),' || wm_concat(column_name) || ' from ' ||table_name || ' group by ' || wm_concat(column_name) || ' having count(*)>1;'

FROM user_ind_columns where index_name in (select index_name from user_indexes where uniqueness='UNIQUE')

GROUP BY index_name,table_name;

ORA-00997: Illegal Use of LONG Datatype

SQL>copy from ldbo/ldbo@APX1213srv -

create CLIENTSCANNEDIMAGEbackup using select * from CLIENTSCANNEDIMAGE;

Oracle Parallel Execution

1. What is Oracle Parallel Execution?

Parallel Execution allows Oracle to perform specific database actions in parallel.

2. Which sub types of Parallel Execution exist?

The following types of Parallel Execution are most important:

Parallel Query

Parallel Query allows to parallelize certain components of selections, e.g.:

Full table scans

Index fast full scans

Access to inner tables of nested loop joins

Parallel DML

Parallel DML allows to parallelize DML operations:

UPDATE

DELETE

INSERT

Parallel DDL

Parallel DDL allows to parallelize DDL operations, e.g. :

CREATE INDEX

ALTER INDEX REBUILD

CREATE TABLE AS SELECT

Parallel Recovery

Parallel Recovery can be used to parallelize recovery activities:

RECOVER DATABASE

The most important Parallel Execution type is Parallel Query. Therefore this note focuses on the Parallel Query type. Nevertheless the technical details apply in the same way also to the other Parallel Execution types.

3. What are the advantages of Parallel Execution?

Based on Parallel Execution you are able to use more system resources like CPU or I/O at the same time for a specific operation. As a consequence the total runtime can significantly decrease.

4. What are restrictions and disadvantages of Parallel Execution?

The following disadvantages are possible:

Resource bottlenecks

Executing an operation in parallel involves increased resource usage (e.g. CPU or I/O). In the worst case a bottleneck situation can be the consequence and the whole system performance is impacted.

High parallelism in case of DEFAULT degree and many CPUs

If a Parallel Execution is performed with DEFAULT degree, a very high parallelism is possible. In order to avoid this it is usually recommended not to use the DEFAULT degree.

Wrong CBO decisions

Activated Parallel Query can significantly impact the calculations of the Cost Based Optimizer (see note 750631). In the worst case a good (sequential) index access can turn into a long running (parallel) full table

scan.

No RBO support

Parallel Query requires the CBO because the Rule Based Optimizer isn't able to handle it. If the CBO is used for an access on tables without statistics, this can cause performance problems.

Parallel DDL activates segment parallelism

If a parallel DDL operation like ALTER INDEX REBUILD PARALLEL is performed, the parallelism degree for the index remains even after the DDL operation is finished. As a consequence unintentionally parallel query

might be used. In order to avoid problems you have to make sure that you reset the parallelism degree of the concerned segments to 1 after the DDL operation. The BR*TOOLS perform this activity automatically after

parallelized DDL operations.

Wrong result sets with 10g

Due to the Oracle bug described in note 985118 it is possible that Parallel Executions return a wrong result set.

Insufficient explain information

With older SAP releases parallel query is not taken into account in the explain functionality in transaction ST04 or ST05 (see note 723879). As a consequence misconceptions and irritations are possible.

Shared pool allocation

Parallel query can consume significant amounts of shared pool memory for communication purposes. Particularly in the case of high values for PARALLEL_MAX_SERVERS and

PARALLEL_EXECUTION_MESSAGE_SIZE up to several GB of shared pool memory can be allocated. A consequence can be ORA-04031 errors (see note 869006).

No parallel DML with IOTs

It is not possible to use parallel DML in combination with (unpartitioned) Index Organized Tables (IOTs).

5. How is Parallel Execution performed on a technical layer?

The following processes are used to perform a Parallel Execution:

Parallel Execution Coordinator

The Parallel Execution Coordinator (later on referred to as "coordinator") is the control process that distributes the work across the Parallel Execution Slaves.

Up to Oracle 9i the coordinator splits SQL statements into smaller pieces (in terms of the working set) and passes the "smaller" SQL commands to the Parallel Execution Slaves. Typical SQL statements that

are transformed by the coordinator look like:

SELECT /*+ CIV_GB */ A1.C0,AVG(SYS_OP_CSR(A1.C1,0)), MAX(SYS_OP_CSR(A1.C1,1)) FROM :Q490001 A1 GROUP BY A1.C0

SELECT /*+ PIV_GB */ A1.C1 C0,SYS_OP_MSR(AVG(A1.C0),MAX(A1.C0)) C1 ...

... /*+ ORDERED NO_EXPAND USE_HASH(A3) */ ...

... /*+ Q354359000 NO_EXPAND ROWID(A4) */ ...

... PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) ...

SELECT /*+ PIV_SSF */ SYS_OP_MSR(MAX(A1.C0)) FROM ...

As of 10g the original SQL statement is passed to the Parallel Execution Slave without any transformation.

Parallel Execution Slaves (in the following named "slaves")

There are two types of Parallel Execution Slaves:

Producers

The producers read the source data and pass it to the consumers or the coordinators.

Consumers

The consumers take the data from the producers and process it. Consumers are only needed if the data from the producers needs to be post-processed (e.g. sorted).

The communication and data transfer between the processes is based on queues. The communication structures are part of the shared pool:

PX msg pool

Communication area for the parallel query processes

Size depends mainly on PARALLEL_EXECUTION_MESSAGE_SIZE, PARALLEL_MAX_SERVERS and the actually used parallel query slaves

PX subheap

Memory structure with additional parallel query related information

Usually small compared to "PX msg pool"

Blocks that are read via Parallel Execution are always read directly from disk bypassing the Oracle buffer pool. For more information about this "direct path" operation see the "direct path read" section in note

619188.

In order to make sure that the most recent data is read from disk a segment specific checkpoint is performed before the "direct path read" operations are started.

6. Which parameters exist in relation to Parallel Execution?

The following Oracle parameters are most important with regards to Parallel Execution:

CPU_COUNT

CPU_COUNT is a parameter that reflects the number of CPUs on the database server. It is used for several Oracle internal purposes and also influences the DEFAULT parallelism used by Oracle (see below).

As CPU_COUNT influences Oracle at a lot of locations, the default value should not be changed.

PARALLEL_EXECUTION_MESSAGE_SIZE

This parameter determines the size of the communication buffer between the differen Parallel Execution processes.

In order to avoid a bottleneck it's recommended to set this parameter to 16384.

PARALLEL_INSTANCE_GROUP

This parameter can be used in RAC environments in order to restrict Parallel Executions to a sub set of the existing RAC instances.

PARALLEL_MAX_SERVERS

With this parameter the maximum number of simultaneously active slave processes can be specified. If more slaves are requested at a certain time the requests are downgraded and a smaller parallelism is

used.

SAP recommends setting this parameter to 10 times the number of CPUs available for the Oracle database in OLAP and Oracle 10g environments.

PARALLEL_MIN_SERVERS

This parameter specifies how many slave processes are created during database startup. If more slaves are needed, they are created on demand.

It's recommended to keep this parameter on the default of 0 in order to avoid unnecessarily started slave processes.

PARALLEL_THREADS_PER_CPU

This parameter influences the DEFAULT parallelism degree (see below).

In order to avoid overload situations with DEFAULT parallelism it's recommended to set this parameter to 1.

PARALLEL_ADAPTIVE_MULTI_USER

When this parameter is set to TRUE (what is default for Oracle 10g) parallel executions may be downgraded by Oracle even before PARALLEL_MAX_SERVERS is reached in order to limit the system load. If

you need to guarantee maximum parallelization (like during reorganizations or system copies) it is advisable to set this parameter to FALSE. During normal operation the value TRUE should be okay because resource

bottlenecks can be avoided.

7. How are activation and degree of parallelism determined?

The following points have to be taken into account in order to determine if and to what extent Parallel Query is used:

The operation must be parallelizable (e.g. full table scan, scan of partitioned index). An index range scan on a non-partitioned index e.g. can't be performed in parallel.

The parallelism must be activated on segment or on statement level.

Segment level

Parallelism on segment level can be activated with the following command:

ALTER TABLE PARALLEL ;

ALTER INDEX PARALLEL ;

In order to check the parallelism for a certain segment, you can use the following selection:

SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME = '';

SELECT DEGREE FROM DBA_INDEXES WHERE INDEX_NAME = '';

Statement level

On SQL statement level the following hint can be used to activate parallelism (see note 772497):

PARALLEL(, )

If is a positive number, up to producers and consumers can be used for a parallel query (-> 2 * slaves).

If DEFAULT is specified for , CPU_COUNT * PARALLEL_THREADS_PER_CPU producers and CPU_COUNT * PARALLEL_THREADS_PER_CPU consumers can be used for a parallel query.

If the PARALLEL_MAX_SERVERS limit is exceeded taking into account all simultaneously active Parallel Executions, the degree of parallelism is reduced.

8. To what extent does SAP use parallelism on segment and statement level?

Per default no segments with activated parallelism are delivered by SAP.

Parallelism on statement level (-> PARALLEL hint) is frequently used in BW environments (e.g. in case of aggregate rollup). In OLTP environments the PARALLEL hint is used only in exceptional situations.

9. How can segments with activated parallelism be determined?

With the following selection all tables and indexes can be determined that have a parallelism degree > 1 or DEFAULT:

SELECT

TABLE_NAME SEGMENT_NAME,

DEGREE,

INSTANCES

FROM DBA_TABLES

WHERE

OWNER LIKE 'SAP%' AND

(DEGREE != ' 1' OR INSTANCES != ' 1')

UNION

SELECT

INDEX_NAME SEGMENT_NAME,

SUBSTR(DEGREE, 1, 10) DEGREE,

SUBSTR(INSTANCES, 1, 10) INSTANCES

FROM DBA_INDEXES

WHERE

OWNER LIKE 'SAP%' AND

INDEX_TYPE != 'LOB' AND

(DEGREE != '1' OR INSTANCES NOT IN ('0', '1'));

10. How can the parallelism degree of segments be modified?

With the following commands the parallelism for a table or index can be deactivated:

ALTER TABLE PARALLEL (DEGREE 1 INSTANCES 1);

ALTER INDEX PARALLEL (DEGREE 1 INSTANCES 1);

In order to set the parallel degree to a certain value, you can use:

ALTER TABLE PARALLEL ;

ALTER INDEX PARALLEL ;

11. How can you determine to what extent the parallelism is downgraded?

The following selection returns how many Parallel Executions are downgraded to what extent:

SELECT NAME, VALUE FROM V$SYSSTAT

WHERE NAME LIKE 'Parallel operations%';

12. Which wait events exist with regards to Parallel Execution?

The following are the main wait events in the area of Parallel Execution (see note 619188 for more information on Oracle wait events). As it is difficult to distinguish between idle events and busy events in the area of

parallel execution, these two types are not distinguished.

PX Idle Wait

A slave waits for acquiration by a coordinator. This is a real idle event.

PX Deq: Execute Reply

The coordinator waits for slaves to finish their work.

PX Deq: Table Q Normal

A consumer waits for data from its producer.

PX Deq: Execution Msg

A slave waits for further instructions from the coordinator.

PX Deq Credit: send blkd

A producer wants to send something to its consumer, but the consumer has not finished the previous work, yet. The same is valid for a slave that want to send something to its coordinator.

In order to tune this wait event you have to check why the consumer / coordinator is not able to process the producer data fast enough. For example deactivated parallelism for DML operations can result in high "PX

Deq Credit: send blkd" waits for parallelized activities containing DML operations (e.g. INSERT ... SELECT). In this case "ALTER SESSION ENABLE PARALLEL DML" can optimize the parallelization.

If an SAP process fetches record by record from the database using parallel query and performs some processing on ABAP side in between, parallel execution slaves wait for "PX Deq Credit: send blkd" during the

ABAP processing. In this case huge wait times for this wait event and huge elapsed times for the SQL statement can be seen although the real execution time on database side is very small. In this scenario "PX Deq

Credit: send blkd" can be treated as an idle event.

PX qref latch

A consumer has to wait for its communication queue to be available. Make sure that PARALLEL_EXECUTION_MESSAGE_SIZE is set to 16384 in order to avoid many small communications and reduce this kind of

contention.

Another possible reason for "PX qref latch" waits is the fact that the consumer (or coordinator) processes the data slower than the producer generates it. In this case further optimization is hardly possible.

PX Deq: Signal ACK

PX Deq: Join ACK

PX Deq Credit: need buffer

These wait events are related to process communication: The coordinator waits for an acknowledgement of a control message ("PX Deq: Signal ACK") or join request ("PX Deq: Join ACK") from a slave or processes

need a queue buffer in order to send data ("PX Deq Credit: need buffer"). It is normal that these events occur (and so Oracle treats them partially as idle events), but if you have doubts that too much time is spent for

communicating, you should check for overall resource bottlenecks (e.g. CPU) and unnecessary parallelization of small tasks.

PX Deq: Parse Reply

The coordinator waits until the slaves have parsed their SQL statements. In case of increased waits for "PX Deq: Parse Reply" you should check if the shared pool is sized sufficiently (note 789011) and if there are

some parallel query SQL statements with a high parse time (note 712624 (24)).

reliable message

The coordinator waits for a reply from another instance in RAC environments. This can e.g. happen if Oracle parameters are set system wide using ALTER SYSTEM.

To a minor extent waits for "reliable message" can also show up in non RAC systems.

latch free (for "query server process" latch)

The "query server process" latch is allocated if an additional slaves are created. This is necessary if more slaves then defined with PARALLEL_MIN_SERVERS are needed. In order to optimize this kind of latch wait

you should check on the one hand side if Parallel Execution is activated by accident (e.g. on segment level) and switch it off in this case. Alternatively you can consider setting PARALLEL_MIN_SERVERS to a higher

value.

13. Where can I find more information about Parallel Execution activites on the system?

The following selection from V$PX_SESSION can be used to monitor the current activities of Parallel Execution processes:

SELECT

PS.SID,

DECODE(SERVER_SET, NULL, 'COORDINATOR', 1, ' CONSUMER',

' PRODUCER') ROLE,

DECODE(SW.WAIT_TIME, 0, SW.EVENT, 'CPU') ACTION,

SQ.SQL_TEXT

FROM

V$PX_SESSION PS,

V$SESSION_WAIT SW,

V$SQL SQ,

V$SESSION S,

AUDIT_ACTIONS AA

WHERE

PS.SID = SW.SID AND

S.SID = PS.SID AND

S.SQL_ADDRESS = SQ.ADDRESS (+) AND

AA.ACTION = S.COMMAND

ORDER BY PS.QCSID, NVL(PS.SERVER#, 0), PS.SERVER_SET;

In addition the following Parallel Execution related views exist:

V$PQ_SYSSTAT

The view V$PQ_SYSSTAT contains general information about Parallel Execution activities like number of Parallel Queries, Parallel DML and Parallel DDL or currently active sessions.

V$PQ_SLAVE

This view contains information about the existing parallel query slaves.

V$PQ_TQSTAT

Statistics for different parallel queries (separated by DFO_NUMBER)

14. How can the progress of a parallelized full table scan be monitored?

Create the following fuction in the schema of a user who has the the the privileges

grant analyze any to ;

grant select any table to ;

directly (not via role) granted. Usually not even the user 'SYS' has these privileges granted directly.

create or replace function sap_fts_progress_parallel

(owner varchar2, tab_name varchar2, sess number, blocks number)

RETURN char IS

stat_nr number;

tot_blk number;

unu_blk number;

dummy number;

query_act number;

perc number;

hwm number;

begin

if blocks=-1 then

dbms_space.unused_space(owner, tab_name, 'TABLE', tot_blk,

dummy, unu_blk, dummy, dummy, dummy, dummy, dummy);

hwm := tot_blk-unu_blk;

else

hwm := blocks;

end if;

select statistic# into stat_nr from v$statname

where name='physical reads direct';

select count(1) into query_act from v$px_sesstat

where statistic#=stat_nr

and qcsid=decode(sign(sess),1,sess,qcsid);

if query_act=0 then

return 'Query not active';

else

select sum(value)/decode(hwm,0,1,hwm)*100 into perc

from v$px_sesstat where statistic#=stat_nr

and sid <>qcsid

and qcsid=decode(sign(sess),1,sess,qcsid);

return to_char(perc,'999.9')||'% of '||to_char(hwm)||' Blocks';

end if;

end;

/

Doublecheck if the character '|' is copied when you cut and paste the function definition.

Execute the function with

select sap_fts_progress_parallel('','',,-1) from

dual;

when your parallel query is running to get the percentual progress and the total number of blocks to be read (example output: '43.7% of 6973 Blocks'). .

is the object on which the query runs.

specifies the session id which initiated the query. For larger tables you can increase performance if you specify the total number of blocks given back from the first query at the second and all further executions:

select sap_fts_progress_parallel('','

',,)

from dual;

If you are sure that there is no other parallel query running concurrently then you can also set equal '-1'. If - against the prerequisite - another query runs then the percentual value is undefined.

15. Which error can show up in relation to Parallel Execution?

The following errors are important in the area of Parallel Execution:

ORA-12801: error signaled in parallel query server

This error indicates that a parallel query process had to terminate due to an error situation. As described in note 636475, the ORA-12801 is a secondary Oracle error code. You have to check for the real error message

that accompanies the ORA-12801 error.

ORA-12805: parallel query server died unexpectedly

This error indicates that a parallel query process terminated in a hard way. In this case you have to analyze trace files and the alert log for more information.

16. How does the INSTANCES setting influence the parallelism?

The INSTANCES storage parameter is mainly intended for RAC environments and has a similar effect like the PARALLEL storage parameter. In order to avoid confusion it is recommended not to use the INSTANCES

parameter setting.

17. How can parallelism be activated on session level?

You can enable the different types of parallelism on session level using: ALTER SESSION ENABLE PARALLEL QUERY;

ALTER SESSION ENABLE PARALLEL DDL;

ALTER SESSION ENABLE PARALLEL DML;

While PARALLEL QUERY and PARALLEL DDL are activated per default, PARALLEL DML has to be activated explicitly if required. This can e.g. be useful in case of parallelized BRSPACE online reorganizations. Per

default only the reading of the source table is parallelized while the writing into the target table is done sequentially. By activating PARALLEL DML the writing can be parallelized.

Additionally it is also possible to force parallelism on session level even if no PARALLEL hint or segment parallelism is used:

ALTER SESSION FORCE PARALLEL QUERY PARALLEL ;

ALTER SESSION FORCE PARALLEL DDL PARALLEL ;

ALTER SESSION FORCE PARALLEL DML PARALLEL ;

Role Creation Using Network Link

BEGIN

FOR cur_rec IN (SELECT role

FROM dba_roles@lnk_previousyearbalance) LOOP

BEGIN

EXECUTE IMMEDIATE 'CREATE ROLE ' || cur_rec.role ;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

BEGIN

FOR cur_rec IN (SELECT grantee,privilege

FROM DBA_SYS_PRIVS@lnk_previousyearbalance where grantee not in ('SYS','SYSTEM','SYSMAN','TSMSYS','WMSYS','RECOVERY_CATALOG_OWNER','RESOURCE','OUTLN','ORACLE_OCM','OEM_MONITOR','OEM_ADVISOR','MGMT_USER','IMP_FULL_DATABASE','EXP_FULL_DATABASE','DBA','CONNECT','AQ_ADMINISTRATOR_ROLE','DBSNMP','SCHEDULER_ADMIN')) LOOP

BEGIN

EXECUTE IMMEDIATE ('Grant ' || cur_rec.privilege || ' to ' || cur_rec.grantee );

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

BEGIN

FOR cur_rec IN (SELECT grantee,privilege,table_name

FROM dba_tab_privs@lnk_previousyearbalance Where Grantor='LDBO') LOOP

BEGIN

EXECUTE IMMEDIATE 'Grant ' || cur_rec.privilege || ' on ' || cur_rec.table_name || ' to ' || cur_rec.grantee ;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

BEGIN

FOR cur_rec IN (SELECT grantee,privilege,table_name,column_name

FROM dba_col_privs@lnk_previousyearbalance Where Grantor='LDBO') LOOP

BEGIN

EXECUTE IMMEDIATE 'Grant '|| cur_rec.PRIVILEGE || '('|| cur_rec.COLUMN_NAME ||') on '|| cur_rec.TABLE_NAME || ' to ' || cur_rec.GRANTEE ;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

BEGIN

FOR cur_rec IN (SELECT grantee,granted_role

FROM dba_role_privs@lnk_previousyearbalance Where Grantee!='SYSTEM' and Grantee!='SYS' and Grantee!='DBSNML' and Grantee!='REPADMIN') LOOP

BEGIN

EXECUTE IMMEDIATE 'Grant '|| cur_rec.granted_role || ' to ' || cur_rec.GRANTEE ;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

User Creation using Network Link

------------Create profile

BEGIN

FOR cur_rec IN (SELECT distinct profile,Resource_name,limit

FROM dba_profiles@lnk_previousyearbalance where profile!='DEFAULT') LOOP

BEGIN

EXECUTE IMMEDIATE 'Create profile '|| cur_rec.profile ||' Limit '|| cur_rec.Resource_name ||' '|| cur_rec.Limit ;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

------------Alter profile

BEGIN

FOR cur_rec IN (SELECT distinct profile,Resource_name,limit

FROM dba_profiles@lnk_previousyearbalance where profile!='DEFAULT' and Limit!='DEFAULT') LOOP

BEGIN

EXECUTE IMMEDIATE 'Alter profile '|| cur_rec.profile ||' Limit '|| cur_rec.Resource_name ||' '|| cur_rec.Limit ;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

------------Create User

BEGIN

FOR cur_rec IN (SELECT username,password,default_tablespace,temporary_tablespace,profile

FROM dba_users@lnk_previousyearbalance where username!='SYSTEM' and Username!='SYS' and Username!='DBSNMP' and Username!='REPADMIN') LOOP

BEGIN

EXECUTE IMMEDIATE 'create user ' || cur_rec.username || ' identified by values ' || '''' || cur_rec.password || '''' || ' default tablespace ' || cur_rec.default_tablespace || ' temporary tablespace ' || cur_rec.temporary_tablespace || ' profile ' || cur_rec.profile;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

Common Error And Solutions

1. Oracle Memory Sizing

RAM = 4GB

Databases =4

-----------For Old Year

Connect to old year database SQLPLUSW sys/oracle@apx0910srv AS SYSDBA

Create pfile from spfile;

show parameter sga

show parameter pga

alter system set sga_target=500M;

alter system set sga_max_size=500M scope=spfile;

alter system set pga_aggregate_target=100M;

shutdown immediate

startup

----------------For Current and Last Year

Connect to old year database SQLPLUSW sys/oracle@apx1213srv AS SYSDBA

SQLPLUSW sys/oracle@apx1112srv AS SYSDBA

Create pfile from spfile;

show parameter sga

show parameter pga

alter system set sga_target=800M;

alter system set sga_max_size=800M scope=spfile;

alter system set pga_aggregate_target=300M;

shutdown immediate

startup

Note1: For 32 bit server, Max Oracle Memory Limit 2*32bits (1.7GB)

Don't give more that 1.5 gb to sga otherwise oracle will not started

Note2: Please disable all OracleJobScheduler, OracleDBConsole services from services.msc

Note3: Stop Archiving for old year databases

2. Change Utl_file_dir location

create pfile from spfile;

Alter system set utl_file_dir='D:\ldoutput' Scope=spfile ;

shut immediate

startup

3. How to set default tablespace as USR ?

select * from database_properties where property_name like '%TABLESPACE';

Alter Database default tablespace "USR" ;

4. At the time of LD Login it shows error message "Oracle Not Available" or Shared Memory Realm does not exist ?

Manually start database

sqlplusw sys@apx1213srv as sysdba

startup

5.for ld digital

sqlplusw sys@apx1213srv as sysdba

CREATE DIRECTORY LDDGITAL AS 'd:\ldoutput\Lddigital';

grant read, write on directory LDDIGITAL to ldbo;

6. If any entry of LISTENER_APX1213 in tnsnames.ora then remove it.

show parameter listener

create pfile from spfile;

alter system set local_listener='' scope=spfile;

shut immediate

start

7. if listener shows error

run listener services from windows command prompt to view right status

lsnrctl status

lsnrctl stop

lsnrctl start

8. Master Transmission Performance

1) delete from CLIENTSCANNEDIMAGE; before master transmission for better performance

2) create table from sql

3) later imp clientscannedimage table

exp ldbo@ldbo@apx1112srv file='d:\expdp1112\tblclientscan.dmp' log='d:\expdp1112\tblclientscan.log' TABLES= CLIENTSCANNEDIMAGE

imp ldbo@ldbo@apx1213srv file='d:\expdp1112\tblclientscan.dmp' log='d:\expdp1112\tblclientscan1.log'

9. if Space Issue, Stop Archiving at time of import or other long insert / update / transmission process. Later enable

Disable Archiving

sqlplusw sys@apx1213srv as sysdba

shut immediate

startup mount

alter database noarchivelog;

alter database open;

Enable Archiving

shut immediate

startup mount

alter database archivelog;

alter database open;

10. unable to extend <> segment by <> in tablespace USR

ALTER TABLESPACE USR

ADD DATAFILE 'D:\APXD1213\USERS2.ORA' SIZE 2048M

AUTOEXTEND ON

NEXT 1024M

MAXSIZE unlimited;

11. ora-01536 space quota exceed

ALTER USER LDBO QUOTA UNLIMITED on USR;

ALTER USER LDBO QUOTA UNLIMITED on INDX;

12. ORA-38029 object statistics are locked

select 'exec DBMS_STATS.UNLOCK_TABLE_STATS('''||owner||''','''||table_name||''');' from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

RUN THE OUTPUT RESULT

13. ORA-25153 Temporary Tablespace is Empty

ALTER TABLESPACE temporary ADD tempfile 'D:\APX1213D\TEMP01.ORA' SIZE 1000M REUSE AUTOEXTEND ON NEXT 500M MAXSIZE unlimited;

14. ORA-20014: %s Brokerage cannot be Run. ~-27477~ORA-27477: "LDBO.C_BSENM1112177_J" already exists

BEGIN

DBMS_SCHEDULER.DROP_JOB (

job_name => 'C_BSENM1112177_J');

END;

/

15. Tablespace, Datafiles Details

select tablespace_name,block_size,INITIAL_EXTENT/1024,MAX_EXTENTS/1024/1024/1024,segment_space_management,bigfile from dba_tablespaces;

select file_name,tablespace_name,bytes/1024/1024,autoextensible,increment_by from dba_temp_files;

select file_name,tablespace_name,bytes/1024/1024,MAXbytes/1024/1024,autoextensible,increment_by from dba_data_files;

16. Partitioned Table Details

select table_name from user_part_tables;

select name,column_name from user_part_key_columns;

select * from user_tab_partitions;

17.

If following ODBC error (Mostly occur at Large DB / Partitioned Table Clients)

Most Critical Error In Oracle ORA-00600

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00600: internal error code, arguments: [15735], [2168], [2152], [], [], [], [], []

solution:

sqlplusw sys@apx1213srv as sysdba

create pfile from spfile;

show parameter parallel_automatic_tuning

alter system set parallel_automatic_tuning=TRUE scope=spfile;

alter system set parallel_execution_message_size=4096 scope=spfile;

shut immediate

startup

18. Enable / Disable Trigger

Alter Trigger Enable ;

Alter Trigger Disable ;

19. Enable / Disable Constraint

Alter Table Enable constraint ;

Alter Table Disable constraint ;

20. ORA-14402 updating partition key column would cause a partition change

ALTER TABLE enable row movement;

21. COMPILE OBJECT ERROR

ORA-02019: connection description for remote database not found

TABLE VIEW DOES NOT EXIST

ADD DATABASE LINK

CREATE PUBLIC DATABASE LINK "LNK_RAKSHAK"

CONNECT TO "LDBO" IDENTIFIED BY password USING 'apx1213srv';

CREATE PUBLIC DATABASE LINK "LNK_CCM"

CONNECT TO "LDBO" IDENTIFIED BY password USING 'apx1213srv';

CREATE DATABASE LINK "LNK_DIGITAL"

CONNECT TO "LDBO" IDENTIFIED BY password USING 'apx1213srv';