Purge Queue

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'purge_boqueue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE  po_t dbms_aqadm.aq$_purge_options_t;
    BEGIN dbms_aqadm.purge_queue_table(''LDBO.TBLDIGITALBOQUEUE'', NULL, po_t); END;',
        start_date      => '28-APR-13 04.20.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'purge queue table');
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'purge_mainboqueue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE  po_t dbms_aqadm.aq$_purge_options_t;
    BEGIN dbms_aqadm.purge_queue_table(''LDBO.TBLMAINBOQUEUE'', NULL, po_t); END;',
        start_date      => '28-APR-13 04.30.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'purge queue table');
END;
/

Job Analyze Temp tables

CREATE OR REPLACE PROCEDURE Analyzetemp AS
BEGIN
 FOR CUR_REC IN (SELECT DISTINCT OWNER,TABLE_NAME
                  FROM   DBA_TABLES where table_name like '%TEMP%') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ANALYZE TABLE '  || CUR_REC.OWNER || '.' || CUR_REC.TABLE_NAME ||' COMPUTE STATISTICS' ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
 FOR CUR_REC IN (SELECT DISTINCT OWNER,INDEX_NAME
                  FROM   DBA_INDEXES where table_name like '%TEMP%') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ANALYZE INDEX ' || CUR_REC.OWNER || '.' || CUR_REC.INDEX_NAME ||' COMPUTE STATISTICS' ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

Analyze Full

CREATE OR REPLACE PROCEDURE AnalyzeFull AS
BEGIN
 FOR CUR_REC IN (SELECT DISTINCT OWNER,TABLE_NAME
                  FROM   DBA_TABLES) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ANALYZE TABLE '  || CUR_REC.OWNER || '.' || CUR_REC.TABLE_NAME ||' COMPUTE STATISTICS' ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
 FOR CUR_REC IN (SELECT DISTINCT OWNER, INDEX_NAME
                  FROM   DBA_INDEXES) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ANALYZE INDEX ' || CUR_REC.OWNER || '.' || CUR_REC.INDEX_NAME ||' COMPUTE STATISTICS' ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

User Creation With job

host mkdir c:\yep1314

spool c:\yep1314\yep1314.log

--------------User Creation

CREATE USER LDBO PROFILE "DEFAULT" IDENTIFIED BY ldbo DEFAULT TABLESPACE "USR" TEMPORARY TABLESPACE "TEMPORARY" ACCOUNT UNLOCK ;


BEGIN
DBMS_WM.GrantSystemPriv('ACCESS_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/

GRANT ADMINISTER ANY SQL TUNING SET TO "LDBO" ;
GRANT ADMINISTER DATABASE TRIGGER TO "LDBO" ;

BEGIN
dbms_resource_manager_privs.grant_system_privilege(privilege_name=>'ADMINISTER_RESOURCE_MANAGER', grantee_name=>'LDBO', admin_option=>FALSE);
END;
/
GRANT ADMINISTER SQL TUNING SET TO "LDBO" ;
GRANT ADVISOR TO "LDBO" ;
GRANT ALTER ANY CLUSTER TO "LDBO" ;
GRANT ALTER ANY DIMENSION TO "LDBO" ;
GRANT ALTER ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT ALTER ANY INDEX TO "LDBO" ;
GRANT ALTER ANY INDEXTYPE TO "LDBO" ;
GRANT ALTER ANY LIBRARY TO "LDBO" ;
GRANT ALTER ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT ALTER ANY OUTLINE TO "LDBO" ;
GRANT ALTER ANY PROCEDURE TO "LDBO" ;
GRANT ALTER ANY ROLE TO "LDBO" ;
GRANT ALTER ANY RULE TO "LDBO" ;
GRANT ALTER ANY RULE SET TO "LDBO" ;
GRANT ALTER ANY SEQUENCE TO "LDBO" ;
GRANT ALTER ANY SQL PROFILE TO "LDBO" ;
GRANT ALTER ANY TABLE TO "LDBO" ;
GRANT ALTER ANY TRIGGER TO "LDBO" ;
GRANT ALTER ANY TYPE TO "LDBO" ;
GRANT ALTER DATABASE TO "LDBO" ;
GRANT ALTER PROFILE TO "LDBO" ;
GRANT ALTER RESOURCE COST TO "LDBO" ;
GRANT ALTER ROLLBACK SEGMENT TO "LDBO" ;
GRANT ALTER SESSION TO "LDBO" ;
GRANT ALTER SYSTEM TO "LDBO" ;
GRANT ALTER TABLESPACE TO "LDBO" ;
GRANT ALTER USER TO "LDBO" ;
GRANT ANALYZE ANY TO "LDBO" ;
GRANT ANALYZE ANY DICTIONARY TO "LDBO" ;
GRANT AUDIT ANY TO "LDBO" ;
GRANT AUDIT SYSTEM TO "LDBO" ;
GRANT BACKUP ANY TABLE TO "LDBO" ;
GRANT BECOME USER TO "LDBO" ;
GRANT CHANGE NOTIFICATION TO "LDBO" ;
GRANT COMMENT ANY TABLE TO "LDBO" ;
GRANT CREATE ANY CLUSTER TO "LDBO" ;
GRANT CREATE ANY CONTEXT TO "LDBO" ;
GRANT CREATE ANY DIMENSION TO "LDBO" ;
GRANT CREATE ANY DIRECTORY TO "LDBO" ;
GRANT CREATE ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT CREATE ANY INDEX TO "LDBO" ;
GRANT CREATE ANY INDEXTYPE TO "LDBO" ;
GRANT CREATE ANY JOB TO "LDBO" ;
GRANT CREATE ANY LIBRARY TO "LDBO" ;
GRANT CREATE ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT CREATE ANY OPERATOR TO "LDBO" ;
GRANT CREATE ANY OUTLINE TO "LDBO" ;
GRANT CREATE ANY PROCEDURE TO "LDBO" ;
GRANT CREATE ANY RULE TO "LDBO" ;
GRANT CREATE ANY RULE SET TO "LDBO" ;
GRANT CREATE ANY SEQUENCE TO "LDBO" ;
GRANT CREATE ANY SQL PROFILE TO "LDBO" ;
GRANT CREATE ANY SYNONYM TO "LDBO" ;
GRANT CREATE ANY TABLE TO "LDBO" ;
GRANT CREATE ANY TRIGGER TO "LDBO" ;
GRANT CREATE ANY TYPE TO "LDBO" ;
GRANT CREATE ANY VIEW TO "LDBO" ;
GRANT CREATE CLUSTER TO "LDBO" ;
GRANT CREATE DATABASE LINK TO "LDBO" ;
GRANT CREATE DIMENSION TO "LDBO" ;
GRANT CREATE EVALUATION CONTEXT TO "LDBO" ;
GRANT CREATE EXTERNAL JOB TO "LDBO" ;
GRANT CREATE INDEXTYPE TO "LDBO" ;
GRANT CREATE JOB TO "LDBO" ;
GRANT CREATE LIBRARY TO "LDBO" ;
GRANT CREATE MATERIALIZED VIEW TO "LDBO" ;
GRANT CREATE OPERATOR TO "LDBO" ;
GRANT CREATE PROCEDURE TO "LDBO" ;
GRANT CREATE PROFILE TO "LDBO" ;
GRANT CREATE PUBLIC DATABASE LINK TO "LDBO" ;
GRANT CREATE PUBLIC SYNONYM TO "LDBO" ;
GRANT CREATE ROLE TO "LDBO" ;
GRANT CREATE ROLLBACK SEGMENT TO "LDBO" ;
GRANT CREATE RULE TO "LDBO" ;
GRANT CREATE RULE SET TO "LDBO" ;
GRANT CREATE SEQUENCE TO "LDBO" ;
GRANT CREATE SESSION TO "LDBO" ;
GRANT CREATE SYNONYM TO "LDBO" ;
GRANT CREATE TABLE TO "LDBO" ;
GRANT CREATE TABLESPACE TO "LDBO" ;
GRANT CREATE TRIGGER TO "LDBO" ;
GRANT CREATE TYPE TO "LDBO" ;
GRANT CREATE USER TO "LDBO" ;
GRANT CREATE VIEW TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('CREATE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT DEBUG ANY PROCEDURE TO "LDBO" ;
GRANT DEBUG CONNECT SESSION TO "LDBO" ;
GRANT DELETE ANY TABLE TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'DEQUEUE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT DROP ANY CLUSTER TO "LDBO" ;
GRANT DROP ANY CONTEXT TO "LDBO" ;
GRANT DROP ANY DIMENSION TO "LDBO" ;
GRANT DROP ANY DIRECTORY TO "LDBO" ;
GRANT DROP ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT DROP ANY INDEX TO "LDBO" ;
GRANT DROP ANY INDEXTYPE TO "LDBO" ;
GRANT DROP ANY LIBRARY TO "LDBO" ;
GRANT DROP ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT DROP ANY OPERATOR TO "LDBO" ;
GRANT DROP ANY OUTLINE TO "LDBO" ;
GRANT DROP ANY PROCEDURE TO "LDBO" ;
GRANT DROP ANY ROLE TO "LDBO" ;
GRANT DROP ANY RULE TO "LDBO" ;
GRANT DROP ANY RULE SET TO "LDBO" ;
GRANT DROP ANY SEQUENCE TO "LDBO" ;
GRANT DROP ANY SQL PROFILE TO "LDBO" ;
GRANT DROP ANY SYNONYM TO "LDBO" ;
GRANT DROP ANY TABLE TO "LDBO" ;
GRANT DROP ANY TRIGGER TO "LDBO" ;
GRANT DROP ANY TYPE TO "LDBO" ;
GRANT DROP ANY VIEW TO "LDBO" ;
GRANT DROP PROFILE TO "LDBO" ;
GRANT DROP PUBLIC DATABASE LINK TO "LDBO" ;
GRANT DROP PUBLIC SYNONYM TO "LDBO" ;
GRANT DROP ROLLBACK SEGMENT TO "LDBO" ;
GRANT DROP TABLESPACE TO "LDBO" ;
GRANT DROP USER TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'ENQUEUE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT EXECUTE ANY CLASS TO "LDBO" ;
GRANT EXECUTE ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT EXECUTE ANY INDEXTYPE TO "LDBO" ;
GRANT EXECUTE ANY LIBRARY TO "LDBO" ;
GRANT EXECUTE ANY OPERATOR TO "LDBO" ;
GRANT EXECUTE ANY PROCEDURE TO "LDBO" ;
GRANT EXECUTE ANY PROGRAM TO "LDBO" ;
GRANT EXECUTE ANY RULE TO "LDBO" ;
GRANT EXECUTE ANY RULE SET TO "LDBO" ;
GRANT EXECUTE ANY TYPE TO "LDBO" ;
GRANT EXPORT FULL DATABASE TO "LDBO" ;
GRANT FLASHBACK ANY TABLE TO "LDBO" ;
GRANT FORCE ANY TRANSACTION TO "LDBO" ;
GRANT FORCE TRANSACTION TO "LDBO" ;
GRANT GRANT ANY OBJECT PRIVILEGE TO "LDBO" ;
GRANT GRANT ANY PRIVILEGE TO "LDBO" ;
GRANT GRANT ANY ROLE TO "LDBO" ;
GRANT IMPORT FULL DATABASE TO "LDBO" ;
GRANT INSERT ANY TABLE TO "LDBO" ;
GRANT LOCK ANY TABLE TO "LDBO" ;
GRANT EXECUTE ON DBMS_LOCK TO "LDBO" ;
GRANT MANAGE ANY FILE GROUP TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'MANAGE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT MANAGE FILE GROUP TO "LDBO" ;
GRANT MANAGE SCHEDULER TO "LDBO" ;
GRANT MANAGE TABLESPACE TO "LDBO" ;
GRANT MERGE ANY VIEW TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('MERGE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT ON COMMIT REFRESH TO "LDBO" ;
GRANT QUERY REWRITE TO "LDBO" ;
GRANT READ ANY FILE GROUP TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('REMOVE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT RESTRICTED SESSION TO "LDBO" ;
GRANT RESUMABLE TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('ROLLBACK_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT SELECT ANY DICTIONARY TO "LDBO" ;
GRANT SELECT ANY SEQUENCE TO "LDBO" ;
GRANT SELECT ANY TABLE TO "LDBO" ;
GRANT SELECT ANY TRANSACTION TO "LDBO" ;
GRANT SYSDBA TO "LDBO" ;
GRANT SYSOPER TO "LDBO" ;
GRANT UNDER ANY TABLE TO "LDBO" ;
GRANT UNDER ANY TYPE TO "LDBO" ;
GRANT UNDER ANY VIEW TO "LDBO" ;
GRANT UNLIMITED TABLESPACE TO "LDBO" ;
GRANT UPDATE ANY TABLE TO "LDBO" ;

GRANT "AQ_ADMINISTRATOR_ROLE" TO "LDBO" ;
GRANT "AQ_USER_ROLE" TO "LDBO" ;
GRANT EXECUTE ON DBMS_AQ TO "LDBO";
GRANT EXECUTE ON DBMS_AQADM TO "LDBO";


GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO "LDBO";
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO "LDBO";


BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => 'ENQUEUE_ANY',grantee => 'LDBO',admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => 'DEQUEUE_ANY',grantee => 'LDBO',admin_option => FALSE);
END;
/

GRANT "CONNECT" TO "LDBO" ;
GRANT "DBA" TO "LDBO" ;
GRANT "EXP_FULL_DATABASE" TO "LDBO" ;
GRANT "IMP_FULL_DATABASE" TO "LDBO" ;

ALTER USER LDBO QUOTA UNLIMITED on USR;
ALTER USER LDBO QUOTA UNLIMITED on INDX;



GRANT execute ON utl_recomp TO "LDBO";

alter system set job_queue_processes=1000;
alter system set sec_case_sensitive_logon=False;
alter system set open_cursors=1000 scope=spfile;
alter system set session_cached_cursors=500 scope=spfile;


CREATE USER CMUSER PROFILE "DEFAULT" IDENTIFIED BY cmuser DEFAULT TABLESPACE "USR" TEMPORARY TABLESPACE "TEMPORARY" ACCOUNT UNLOCK ;
GRANT CREATE SESSION TO cmuser;
GRANT CONNECT TO cmuser;

create or replace directory LDDIGITAL as 'd:\ldoutput\lddigital';

grant all on directory LDDIGITAL to public;



-----------------Auditing

Audit user,system grant,role,alter system,profile whenever successful ;
audit create session by access;
audit audit system by access;
audit grant any privilege by access;
audit grant any object privilege by access;
audit grant any role by access;
audit system grant by access;
audit create user by access;
audit create any table by access;
audit create public database link by access;
audit create any procedure by access;
audit alter user by access;
audit alter any table by access;
audit alter any procedure by access;
audit alter database by access;
audit alter system by access;
audit alter profile by access;
audit drop user by access;
audit drop any procedure by access;
audit drop any table by access;
audit drop profile by access;
audit drop any index by access;

-----------------Profile Setting

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;

-----------------Network Access

Exec dbms_network_acl_admin.create_acl ('utl_http_access.xml','Normal Access','LDBO',TRUE,'connect',NULL,NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_http_access.xml', principal =>  'LDBO',is_grant => TRUE, privilege => 'resolve');
Exec dbms_network_acl_admin.assign_acl ('utl_http_access.xml', '*',NULL,NULL);
commit ;
               
Exec dbms_network_acl_admin.create_acl ('utl_inaddr_access.xml','Normal Access','LDBO',TRUE,'resolve',NULL, NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_inaddr_access.xml', principal =>  'LDBO',is_grant => TRUE, privilege => 'resolve');
Exec dbms_network_acl_admin.assign_acl ('utl_inaddr_access.xml', '*',NULL,NULL);
commit;

Exec dbms_network_acl_admin.create_acl ('utl_mail.xml','Allow mail to be send','LDBO',TRUE,'connect' );
Exec dbms_network_acl_admin.add_privilege ('utl_mail.xml','LDBO',TRUE,'resolve');
Exec dbms_network_acl_admin.assign_acl('utl_mail.xml','*',NULL,NULL);
commit ;


Exec dbms_network_acl_admin.create_acl ('utl_http.xml','HTTP Access','LDBO',TRUE,'connect',null,null);
Exec dbms_network_acl_admin.add_privilege ('utl_http.xml','LDBO',TRUE,'resolve',null,null);
Exec dbms_network_acl_admin.assign_acl ('utl_http.xml','*',NULL,NULL);
commit;

Grant Execute on utl_inaddr to ldbo ;
Grant Execute on utl_http to ldbo ;


--------------Jobs


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'compile',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN UTL_RECOMP.recomp_serial; END;',
    start_date      => '01-APR-12 06:31.00.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB to compile invalid objects');
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'analyze',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''LDBO'',CASCADE=>TRUE); END;',
    start_date      => '01-APR-12 04.00.00 AM ASIA/CALCUTTA',
    repeat_interval=> 'FREQ=DAILY',
    enabled         => TRUE,
    comments        => 'JOB to gather LDBO statistics every DAY');
END;
/


CREATE OR REPLACE PROCEDURE Analyzetemp wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
2a2 168
oUpO82c0m3kRqNYu+ieu9+lzKuMwg+1pLdwdf3QBvGQwDJTIaaoOXtFSqGZSURSwYVZJQEip
BFRn/ggU7vLVLWjhHH7ZwpoSP6gj6SPjNjqS0uQIQs8kvDQ16OKw2mqkYSLmslJfGnAEsfRW
JnR5Cd9xq50LGMSTM6dgp0p75Bh50uKOVdktzWyKuSYvQdBw1x012GW+S9N4SxkHOSFtETUS
CYQbBMFa/ZN7qJODaoZfNvtZ66GKRb0KHS1vnE6ZPN7TaRkCGOzCR4FJjTbfVXhPUcjwJAgf
s3RPbdJG8TrBXhzYkK5UseiJMxXJJhBAKs0ftLEz+d2dQJkt27wDd6pLZMvivKZk5hqg

/

show errors

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'ANALYZE_TEMP',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN Analyzetemp; END;',
    start_date      => '01-APR-12 08.01.00 AM ASIA/CALCUTTA',
    repeat_interval=> 'FREQ=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Analyze TEMP tables indexes');
END;
/

CREATE OR REPLACE PROCEDURE AnalyzeFull wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
265 154
+WnW35aMpBSqlXDoysgRZr/L8jowg+1pLdwdf3QBvGQwumfmczfcvtuA72wzPvomAkPb3Z/g
Hml30Em9YungrX+PqGGHfp3BeGB/kbBaULtk2SmGeVfRmfX8IyPAaDjI54KVrfE3C2hJm5Fl
2AwxUj6Rco4sA7kA19gdMs6UhIlp8c2BaJIlHTSFtHuWVmJ9Z8mXNh/hk7AMYyH2teTmEcdp
3TrPXAPeR5Rp/YG2nd4+Taz3fDw7Ph5x6RMaS828znTwMfqNqp3vQF2klUabm/4+ekKqiDi6
bfGDvQUN+YmWDAeUOQ28nVd3OWs8BPXrmyg5XI+5tpcsjUQ=

/

show errors

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


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'purge_rkqueue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE  po_t dbms_aqadm.aq$_purge_options_t;
    BEGIN dbms_aqadm.purge_queue_table(''LDBO.TBLRKQUEUE'', NULL, po_t); END;',
        start_date      => '28-APR-13 04.10.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'purge queue table');
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'purge_boqueue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE  po_t dbms_aqadm.aq$_purge_options_t;
    BEGIN dbms_aqadm.purge_queue_table(''LDBO.TBLDIGITALBOQUEUE'', NULL, po_t); END;',
        start_date      => '28-APR-13 04.20.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'purge queue table');
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'purge_mainboqueue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE  po_t dbms_aqadm.aq$_purge_options_t;
    BEGIN dbms_aqadm.purge_queue_table(''LDBO.TBLMAINBOQUEUE'', NULL, po_t); END;',
        start_date      => '28-APR-13 04.30.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'purge queue table');
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'analyze_rkqueue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN dbms_stats.gather_table_stats(''LDBO'',''TBLRKQUEUE'' ,force=>TRUE); END;',
    start_date      => '28-APR-13 07.00.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB to gather Queue Table statistics');
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'analyze_boqueue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN dbms_stats.gather_table_stats(''LDBO'',''TBLDIGITALBOQUEUE'' ,force=>TRUE); END;',
    start_date      => '28-APR-13 07.11.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB to gather Queue Table statistics');
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'analyze_mainboqueue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN dbms_stats.gather_table_stats(''LDBO'',''TBLMAINBOQUEUE'' ,force=>TRUE); END;',
    start_date      => '28-APR-13 07.20.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB to gather Queue Table statistics');
END;
/


---------------Backup job

host mkdir d:\expdp1314

create directory export_auto as 'd:\expdp1314';


CREATE USER dba_export_user PROFILE "DEFAULT" IDENTIFIED BY dba_export_user DEFAULT TABLESPACE "USR" TEMPORARY TABLESPACE "TEMPORARY" ACCOUNT UNLOCK ;

grant connect, create database link, resource, create view to dba_export_user;
grant unlimited tablespace to dba_export_user;
grant exp_full_database to dba_export_user;
grant read,write on directory export_auto to dba_export_user;
grant execute on dbms_flashback to dba_export_user;
grant create table to dba_export_user;
grant FLASHBACK ANY TABLE to dba_export_user;

ALTER USER dba_export_user QUOTA UNLIMITED on USR;
ALTER USER dba_export_user QUOTA UNLIMITED on INDX;


CREATE OR REPLACE PROCEDURE dba_export_user.start_export
IS
   hdl_job        NUMBER;
   l_cur_scn      NUMBER;
   l_job_state    VARCHAR2 (20);
   l_status       SYS.ku$_status1010;
   l_job_status   SYS.ku$_jobstatus1010;
BEGIN
 begin
    execute immediate 'drop table  dba_export_user.AUTO_EXPORT';
    exception when others then null;
   end;
   hdl_job := DBMS_DATAPUMP.OPEN ( operation => 'EXPORT', job_mode => 'FULL', job_name => 'AUTO_EXPORT' );
   DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'EXPDP1314.dmp',directory => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_dump_file,reusefile => 1);
   DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'EXPDP1314.log',DIRECTORY => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_log_file,reusefile => 1);
   DBMS_DATAPUMP.start_job (handle => hdl_job);
   DBMS_DATAPUMP.wait_for_job (handle => hdl_job, job_state => l_job_state);
   DBMS_OUTPUT.put_line ('Job exited with status:' || l_job_state);
    DBMS_DATAPUMP.detach(handle => hdl_job);
END;
/

show errors


begin
 dbms_scheduler.create_job(
      job_name => 'EXPORT_JOB'
     ,job_type => 'STORED_PROCEDURE'
     ,job_action => 'dba_export_user.start_export'
     ,start_date => '01-MAR-13 10.00.00.00 PM ASIA/CALCUTTA'
       ,repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;'
     ,enabled => TRUE
     ,comments => 'EXPORT_DATABASE_JOB');
end;
/


spool off

job rebuild index 11G

create or replace procedure sp_rebuildindex as
  vOwner   dba_indexes.owner%TYPE;            /* Index Owner            */
  vIdxName dba_indexes.index_name%TYPE;       /* Index Name             */
  vTbName dba_indexes.table_name%TYPE;        /* Table Name             */
  vAnalyze VARCHAR2(100);                     /* String of Analyze Stmt */
  vCursor  NUMBER;                            /* DBMS_SQL cursor        */
  vNumRows INTEGER;                           /* DBMS_SQL return rows   */
  vHeight  index_stats.height%TYPE;           /* Height of index tree   */
  vLfRows  index_stats.lf_rows%TYPE;          /* Index Leaf Rows        */
  vDLfRows index_stats.del_lf_rows%TYPE;      /* Deleted Leaf Rows      */
  vDLfPerc   NUMBER;                          /* Del lf Percentage      */
  vMaxHeight NUMBER;                          /* Max tree height        */
  vMaxDel    NUMBER;                          /* Max del lf percentage  */
  CURSOR cGetIdx IS SELECT owner,index_name,table_name
     FROM dba_indexes WHERE OWNER NOT in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB','SYSMAN');
BEGIN
  /* Define maximums. This section can be customized. */
  vMaxHeight := 3;
  vMaxDel    := 20;

  /* For every index, validate structure */
  OPEN cGetIdx;
  LOOP
     FETCH cGetIdx INTO vOwner,vIdxName,VTbName;
     EXIT WHEN cGetIdx%NOTFOUND;
     /* Open DBMS_SQL cursor */
     vCursor := DBMS_SQL.OPEN_CURSOR;
     /* Set up dynamic string to validate structure */
     vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
     DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
     vNumRows := DBMS_SQL.EXECUTE(vCursor);
     /* Close DBMS_SQL cursor */
     DBMS_SQL.CLOSE_CURSOR(vCursor);
     /* Does index need rebuilding?  */
     /* If so, then generate command */
     SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
        FROM INDEX_STATS;
     IF vDLfRows = 0 THEN         /* handle case where div by zero */
        vDLfPerc := 0;
     ELSE
        vDLfPerc := (vDLfRows / vLfRows) * 100;
     END IF;
     IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
EXECUTE IMMEDIATE 'ALTER INDEX ' || vIdxName || ' REBUILD ONLINE';
EXECUTE IMMEDIATE 'ANALYZE TABLE ' || vTbName || ' compute statistics';
EXECUTE IMMEDIATE 'ANALYZE INDEX '  || vIdxName || ' compute statistics';
     END IF;
  END LOOP;
  CLOSE cGetIdx;
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'REBUILDINDEX',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN sp_rebuildindex; END;',
   start_date      => '01-APR-12 09:00.00.00 AM ASIA/CALCUTTA',
  repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=09; BYMINUTE=01;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'rebuild index');
END;
/

EXEC DBMS_SCHEDULER.RUN_JOB('REBUILDINDEX');

Temporary Tablespace Cleanup 11g

alter tablespace temporary shrink space keep 1G;

exec dbms_scheduler.drop_job('tempshrink');

 begin
dbms_scheduler.create_job
(job_name => 'tempshrink',
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ''alter tablespace temporary shrink space keep 1G''; end;',
start_date => '01-APR-12 01:00.00.00 AM ASIA/CALCUTTA',
  repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN;',
comments=>'Shrink Temporary Tablespace');
end;
/

exec dbms_scheduler.run_job('tempshrink');

========================

select distinct t1.tablespace_name TB,t2.file_name TempFile_name,t1.tablespace_size/1024/1024 Used_Allocated_size,t1.allocated_space/1024/1024 Allocated_size,t1.free_space/1024/1024 Free_space,t2.Autoextensible,t2.bytes/1024/1024 Used_space, t2.maxbytes/1024/1024 Max_TB_size from dba_temp_free_space t1,dba_temp_files t2 where t1.tablespace_name=t2.tablespace_name ;


select tablespace_size/1024/1024,allocated_space/1024/1024,free_space/1024/1024 from dba_temp_free_space;

select tablespace_name,Autoextensible,bytes/1024/1024,maxbytes/1024/1024 from dba_temp_files;


=================================

create or replace trigger tg_clear_tempTB
    after startup
     on database
  declare
     j   integer;
    pragma autonomous_transaction;
  begin
    dbms_job.submit (j,  'begin execute immediate ''alter tablespace temporary shrink space keep 5G''; end;');
   commit;
 end tg_clear_logindetails;
 /

job OS audit files maintaince

For Oracle on unix .aud files are created whether you have auditing enabled or not.  They record sys operations.  If you've got a lot of shell scripts that connect / as sysdba you are going to get a lot of .aud files. (In windows they are written to the event viewer)

They will go to where you set audit_file_dest (aka adump) to be.  If you don't set adump the first default value is ORACLE_BASE/admin/ORACLE_SID/adump. If this doesn't exist then they will go in ORACLE_HOME/rdbms/audit

If you didn't know about this they tend to raise their heads when $ORACLE_HOME fills up and you wonder why.

Please be clear, I am talking about the OS .aud files.  This process will not touch $aud in your database.


Oracle at 11.2  provide a way to manage these .aud OS files using the audit management package DBMS_AUDIT_MGMT

Here's how:

Initialize DBMS_AUDIT_MGMT

Call just once the initialization procedure INIT_CLEANUP to set up the audit management infrastructure.

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
  DEFAULT_CLEANUP_INTERVAL    => 24 );
END;
/



Create the Procedure to delete files (over a year old) for a single instance

CREATE OR REPLACE procedure SYS.delete_OSaud_files
is
ThisProc             VARCHAR2(30) := 'delete_OSaud_files';
ThisAppErr            NUMBER := -20000;
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-366);
 dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE
  );
EXCEPTION
WHEN OTHERS THEN
   dbms_output.put_line(ThisProc||' - '||SQLERRM);
   ROLLBACK;
   RAISE_APPLICATION_ERROR(ThisAppErr, ThisProc);
END;
/

Create the Procedure to delete files (over a year old) for RAC

CREATE OR REPLACE procedure SYS.delete_OSaud_files
is
ThisProc             VARCHAR2(30) := 'delete_OSaud_files';
ThisAppErr            NUMBER := -20000;
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-366,
    rac_instance_number => 1);
   DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-366,
    rac_instance_number => 2);
 dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
EXCEPTION
WHEN OTHERS THEN
   dbms_output.put_line(ThisProc||' - '||SQLERRM);
   ROLLBACK;
   RAISE_APPLICATION_ERROR(ThisAppErr, ThisProc);
END;
/


If you don't need to keep a years worth, just change SYSTIMESTAMP-366


Feel free to moan at me about 'when others then' and post a better procedure in the comments - I'll readily admit my plsql is not what it should be and I'm happy to be corrected.


Create a Schedule
(I like this type of thing to run when I'm actually working so I don't get called out of hours if something goes wrong)

Begin
DBMS_SCHEDULER.CREATE_SCHEDULE (
   schedule_name   => 'DELETE_OSAUD_FILES_SCHED',
      repeat_interval =>'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=10;',
      comments        => 'Delete adump files');
END;
/

Schedule the Job

BEGIN
dbms_scheduler.create_job (
job_name =>'DELETE_OSAUD_FILES_JOB',
job_type =>'STORED_PROCEDURE',
job_action => 'SYS.delete_OSaud_files',
enabled    =>  TRUE,
auto_drop => false,
schedule_name =>  'DELETE_OSAUD_FILES_SCHED',
comments => 'Remove aud files from adump');
END;
/


Set up mail notifications
(I like to know when my jobs error)

BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'smtp.mycompany.com:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'do_not_reply@mydatabase');
END;
/

Create the events that I want mailing about

to test notifications work first set up events for everything

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'DELETE_OSAUD_FILES_JOB',
  recipients =>  'me@mycompany.com',
  events     =>  'job_all_events');
  END;
  /

run your job

BEGIN
dbms_scheduler.run_job (
job_name =>'DELETE_OSAUD_FILES_JOB');
END;
/

You should get an email saying it ran.
But you don't want an 'I ran' mail every day, I only want to know if it's failed so:

BEGIN
 DBMS_SCHEDULER.remove_job_email_notification (
  job_name   =>  'DELETE_OSAUD_DATA_JOB',
  recipients =>  'me@mycompany.com',
  events     =>  'job_succeeded');
END;
/
BEGIN
 DBMS_SCHEDULER.remove_job_email_notification (
  job_name   =>  'DELETE_OSAUD_DATA_JOB',
  recipients =>  'me@mycompany.com',
  events     =>  'job_started');
END;
/
 BEGIN
 DBMS_SCHEDULER.remove_job_email_notification (
  job_name   =>  'DELETE_OSAUD_DATA_JOB',
  recipients =>  'me@mycompany.com',
  events     =>  'job_completed');
END;
/


Which is the equivalent of :

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'DELETE_OSAUD_DATA_JOB',
  recipients =>  'me@mycompany.com',
  events     =>  'job_broken,job_chain_stalled,job_completed,job_disabled,job_failed,
job_over_max_dur,job_sch_lim_reached,job_stopped');
  END;
  /



So now your .aud files will be kept in check for you.

Analyze Stale Job

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'analyze_stale',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''LDBO'',CASCADE=>TRUE,options=>''GATHER STALE''); END;',
    start_date      => '01-APR-12 09.00.00 AM ASIA/CALCUTTA',
     repeat_interval=> 'freq=minutely;bysecond=0;byminute=00,20,40;byhour=9,10,11,12,13,14,15,16,17;byday=MON,TUE,WED,THU,FRI',
    enabled         => TRUE,
    comments        => 'JOB to gather LDBO stale statistics every 20 min');
END;
/

Schedule Job for specific time interval

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'Test',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN sp_rkexecutejob(13,'' ''); END;',
    start_date      => '01-APR-12 09.00.00 AM ASIA/CALCUTTA',
    repeat_interval=> 'freq=minutely;bysecond=0;byminute=00,15,30,45;byhour=9,10,11,12,13,14,15,16;byday=MON,TUE,WED,THU,FRI',
    enabled         => TRUE,
    comments        => 'JOB for rk test');
END;
/

The repaet_interval paramter of the scheduler configuration should have a freq value of minutely interval value of 5 and byhourly value of 9-17.

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name        => 'analyze_queue',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN dbms_stats.gather_table_stats(''LDBO'',''TBLRKQUEUE'' ,force=>TRUE); END;',

    start_date      => '01-APR-12 10.00.00 AM ASIA/CALCUTTA',

    repeat_interval=> 'freq=minutely;interval=5;byhour=9,10,11,12,13,14,15,16,17;byday=MON,TUE,WED,THU,FRI',

    enabled         => TRUE,

    comments        => 'JOB to gather Queue Table statistics');

END;

/

Job Scheduler for RMAN backup

1)
RMAN TARGET SYS/.....@KSH1213SRV
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'F:\RMANBACKUP\%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:\RMANBACKUP\%U';

or

-----------------------------Advance setting with RMAN maintenace command----------------------------------------------------

# BACKUP.rcv
# Configure RMAN settings
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE  DISK TO '%n_cf_%T_%s_%F.bck';
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DEFAULT DEVICE TYPE TO disk;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '%n_df_%T_%s.bck';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '%n_df_%T_%s.bck';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '%n_df_%T_%s.bck';
CONFIGURE CHANNEL 4 DEVICE TYPE DISK FORMAT '%n_df_%T_%s.bck';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '%n_sn_%T_%s.bck';

# Perform backup of database and archivelogs, deleting backed up archivelogs
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

# Maintenance commands for crosschecks and deleting expired backups
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
# Cleaning up to save space.
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
exit

------------------------------------------------------------------------------------------------------------------------------------------------
2)
create user rman identified by .........
Temporary tablespace temporary
Default tablespace usr
Quota unlimited on usr;

Grant recovery_catalog_owner,connect, resource to rman;
Grant Create type to rman;

3)

 RMAN TARGET SYS/.....@KSH1213SRV CATALOG RMAN/....@KSH1213SRV
CREATE CATALOG;
REGISTER DATABASE;

CREATE SCRIPT RMANBACKUP
{
BACKUP DATABASE;
}


4)
BEGIN
  dbms_scheduler.create_job(
  job_name   => 'RMAN_BACKUP',
  job_type   => 'EXECUTABLE',
  job_action => 'rman target sys/.......@KSH1213SRV CATALOG RMAN/..........@KSH1213SRV SCRIPT RMANBACKUP',
  start_date      => '01-APR-12 07:00.00.00 PM ASIA/CALCUTTA',
  repeat_interval => 'freq=DAILY',
  enabled         => TRUE,
  comments   => 'BACKUP RMAN');
END;
/

EXEC dbms_scheduler.RUN_JOB('RMAN_BACKUP');

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;

/

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 10g Scheduler Job Email Notification

1) connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@apx1213 as sysdba
Enter password: ******
Connected.
SQL> @d:\oracle\product\10.2.0\db_1\rdbms\admin\utlmail.sql

Package created.


Synonym created.

SQL> @d:\oracle\product\10.2.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

2) Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'mail.apexsoftcell.com' scope=spfile;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

3) Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to ldbo;

Grant succeeded.

) Create procedure for Email Notification

create or replace procedure PRC_EMAIL (pSubject IN VARCHAR2, pMessage IN VARCHAR2) is
BEGIN
utl_mail.send(sender => 'info@apexsoftcell.com', recipients => 'kshitij@apexsoftcell.com', subject => pSubject, message => pMessage);
END;
/

4) Create Scheduler Job

BEGIN
DBMS_SCHEDULER.drop_JOB (job_name => 'compile');
END;
/


BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'compile',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE lnResult VARCHAR2(150);
BEGIN UTL_RECOMP.recomp_serial(''LDBO''); lnResult:=''SUCCESS'';
PRC_EMAIL(''Compile Notification'',lnResult);
EXCEPTION WHEN OTHERS THEN lnResult:=SUBSTR(SQLERRM,1,150);
PRC_EMAIL(''Compile Notification'',lnResult);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Compile job');
END;
/

5) Execute Job Manually
exec DBMS_SCHEDULER.run_job ('compile');

Schedule Job for Exe file

BEGIN

dbms_scheduler.create_job(

job_name => 'del_archive',

job_type => 'EXECUTABLE',

job_action => 'd:\ld\oracle\del.bat',

start_date => '14-MAR-12 4:52.00.00 PM ASIA/CALCUTTA',

repeat_interval => 'freq=DAILY',

enabled => TRUE,

comments => 'delete old archivelogs');

END;

/

exec DBMS_SCHEDULER.run_job ('del_archive');

BEGIN

DBMS_SCHEDULER.drop_JOB (job_name => 'del_archive');

END;

/

Job schedule compile invalid objects

REQUIRE SYS PRIVILIGES TO EXECUTE UTL_RECOMP

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'compile_invalid',

job_type => 'PLSQL_BLOCK',

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

start_date => '08-FEB-12 11:35.00.00 PM ASIA/CALCUTTA',

repeat_interval => 'freq=DAILY',

end_date => NULL,

enabled => TRUE,

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

END;

/

BEGIN

DBMS_SCHEDULER.drop_JOB (job_name => 'compile_invalid');

END;

/

exec DBMS_SCHEDULER.run_job ('compile_invalid');

select * from dba_scheduler_jobs;

select job_name,job_action,start_date,repeat_interval,end_date,run_count,failure_count from dba_scheduler_jobs where job_name='ANALYZE';

SELECT * FROM dba_scheduler_running_jobs;

Analyze Scheduling using oracle

---------------------------------------------------------------------frequency 1 day-----------------------------------------

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'analyze',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''LDBO'',CASCADE=>TRUE); END;',

start_date => '01-APR-12 11.00.00 PM ASIA/CALCUTTA',

repeat_interval => 'freq=DAILY',

end_date => '02-APR-13 11.00.00 PM ASIA/CALCUTTA',

enabled => TRUE,

comments => 'JOB to gather LDBO statistics');

END;

/

----------------- frequency 2 hours---------------------------------------

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'analyze1',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''LDBO'',CASCADE=>TRUE); END;',

start_date => '16-FEB-12 06.00.00 PM ASIA/CALCUTTA',

repeat_interval=> 'FREQ=HOURLY;INTERVAL=2',

end_date => '02-APR-13 11.00.00 PM ASIA/CALCUTTA',

enabled => TRUE,

comments => 'JOB to gather LDBO statistics every 2 hours');

END;

/

------------------------------------------frequency syntax

FREQ=[YEARLY | MONTHLY | WEEKLY | DAILY | HOURLY | MINUTELY | SECONDLY] ;

-------------------To run a job every Tuesday at 11:25

FREQ=DAILY; BYDAY=TUE; BYHOUR=11; BYMINUTE=25;

FREQ=WEEKLY; BYDAY=TUE; BYHOUR=11; BYMINUTE=25;

FREQ=YEARLY; BYDAY=TUE; BYHOUR=11; BYMINUTE=25;

------------------ To run a job Tuesday and Thursday at 11, 14 and 22 o'clock

FREQ=WEEKLY; BYDAY=TUE,THUR; BYHOUR=11,14,22;