Friday, April 27, 2012
Analyze Process and Lock
Analyze table estimate or compute statistics will acquire an exclusive lock on the library cache object, preventing any ddl changes, however dml on the table should be able to proceed. Analyze table validate structure, however, acquires an exclusive lock on the table, preventing any inserts/updates/deletes. In general, ANALYZE ... VALIDATE STRUCTURE requires an exclusive lock on the object being analyzed. Other permutations of ANALYZE should allow concurrent DML access.An exclusive lock doesn't prevent other people from reading the data, users should still be able to select from the table.
Issuing an analyze on an index puts a shared lock on the table. This means that you cannot do DML on the table that is locked. The DML
operation will wait for the analyze to release the lock. This lock can be viewed in v$lock. The lock Type will be TM and the Object id of the table is v$lock.id1. If there are transactions already against the table, then trying to do an analyze will give ora-54 resource busy.
select * from v$lock where type='TM';
CURSOR_SHARING
CURSOR_SHARING
The default value of parameter Cursor_sharing is Exact. Who changed it?
CURSOR_SHARING is a parameter which decides whether a SQL send from user is a candidate for fresh parsing or will use an existing plan.
CURSOR_SHARING (Default value: Exact): Share the plan only if text of SQL matches exactly with the text of SQL lying in shared pool.
Note: When cursor_sharing =EXACT it uses the index and is 10times faster than cursor_sharing= SIMILAR
trigger to fire under certain conditions
Issue
I do not want a trigger to fire under certain conditions like running on some stored procedure.
I cannot disable the trigger. For ex. There is a table on which if records are deleted then the same is written to audit table. However if on the same table if records are deleted when bill is processed which is normal behaviour I do not want the trigger to fire. Please note I cannot disable the trigger because at that time there could be someone else deleting the record manually.
I do not want a trigger to fire under certain conditions like running on some stored procedure.
I cannot disable the trigger. For ex. There is a table on which if records are deleted then the same is written to audit table. However if on the same table if records are deleted when bill is processed which is normal behaviour I do not want the trigger to fire. Please note I cannot disable the trigger because at that time there could be someone else deleting the record manually.
Solution
You may
add flag column in table.
In bill
or any process flag your data created or modified.
and use
"IF" statement or "WHEN" clause of the trigger to only fire
for certain pre-known value of a column.
CREATE
TRIGGER TRG_XYZ
after
insert on TBL_XYZ
for
each row
WHEN(NEW.BILL_FLG
!= 'Y')
begin
-------------
fine grained auditing oracle
SELECT policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail;
The following policy audits any queries of salaries greater than £50,000.
CONN sys/password AS sysdba
BEGIN
DBMS_FGA.add_policy(
object_schema => 'AUDIT_TEST',
object_name => 'EMP',
policy_name => 'SALARY_CHK_AUDIT',
audit_condition => 'SAL > 50000',
audit_column => 'SAL');
END;
/
Tuesday, April 24, 2012
Access Network Services (UTL_INADDR,UTL_TCP,UTL_HTTP, UTL_SMTP, UTL_MAIL) in Oracle 11g ( ora 24247 network access denied)
From 11g the built-in packages which access the network resources e.g. UTL_HTTP, UTL_SMTP, UTL_MAIL etc. now requires an access control list to be used. If you see this warning that means there are some objects in your database which are using one of these packages. Once the upgrade is complete you need to configure an Access Control List for the users who are using the packages otherwise your applications will fail.
/*
To see if there are any objects depending upon network packages like UTL_TCP ,
UTL_SMTP etc.
*/
SELECT owner , name , type , referenced_name FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
Kindly run the below sqls in SYS. It is only for 11g.
Exec dbms_network_acl_admin.create_acl ('utl_http_access.xml','Normal Access','DPCDSL',TRUE,'connect',NULL,NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_http_access.xml', principal => 'DPCDSL',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','DPCDSL',TRUE,'resolve',NULL, NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_inaddr_access.xml', principal => 'DPCDSL',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','DPCDSL',TRUE,'connect' );
Exec dbms_network_acl_admin.add_privilege ('utl_mail.xml','DPCDSL',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','DPCDSL',TRUE,'connect',null,null);
Exec dbms_network_acl_admin.add_privilege ('utl_http.xml','DPCDSL',TRUE,'resolve',null,null);
Exec dbms_network_acl_admin.assign_acl ('utl_http.xml','*',NULL,NULL);
commit;
---------------------------
Exec dbms_network_acl_admin.create_acl ('utl_smtp.xml','SMTP Access','DPCDSL',TRUE,'connect',null,null);
Exec dbms_network_acl_admin.add_privilege ('utl_smtp.xml','DPCDSL',TRUE,'resolve',null,null);
Exec dbms_network_acl_admin.assign_acl ('utl_smtp.xml','*',NULL,NULL);
commit;
In addition to the above, install the oracle mail scripts to instal UTL_MAIL packages to enable sending emails form inside of database
SQL>@$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL>@$ORACLE_HOME/rdbms/admin/prvtmail.plb
SQL>ALTER SYSTEM SET smtp_out_server='smtp.oracle.com' scope=BOTH;
Grant Execute on utl_inaddr to DPCDSL ;
Grant Execute on utl_http to DPCDSL ;
SELECT global_name,utl_inaddr.get_host_address FROM global_name;
SELECT UTL_INADDR.get_host_address ('www.oracle.com') FROM DUAL;
SELECT UTL_HTTP.request ('http://www.oracle.com') FROM DUAL;
---------------------------------Removing ACL and priviliges
Unassign ACL
begin
dbms_network_acl_admin.unassign_acl(
acl => 'utl_http.xml',
host => '*',
lower_port => 80,
upper_port => 80
);
end;
Delete Privilege
begin
dbms_network_acl_admin.delete_privilege(
'utl_http.xml', 'DPCDSL', NULL, 'connect'
);
end;
Drop ACL
begin
dbms_network_acl_admin.drop_acl(
'utl_http.xml'
);
end;
------------------------------testing----------------------
create or replace procedure getTitle(pUrl VARCHAR2)
is
vResult CLOB;
begin
vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
vResult := regexp_replace(vResult,'.*<title> ?(.+) ?</title>.*','\1',1,1,'i');
dbms_output.put_line(vResult);
end;
/
/*
This is just a dummy procedure and will only display
the title if the title tag is defined in the first 2000
characters in web page.
*/
set serveroutput on
execute getTitle('http://www.oracle.com');
create or replace procedure getTitle(pUrl VARCHAR2)
is
vResult CLOB;
begin
vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
vResult := regexp_replace(vResult,'.*.*','\1',1,1,'i');
dbms_output.put_line(vResult);
end;
/
set serveroutput on
execute getTitle('http://www.oracle.com');
/*
To see if there are any objects depending upon network packages like UTL_TCP ,
UTL_SMTP etc.
*/
SELECT owner , name , type , referenced_name FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
Kindly run the below sqls in SYS. It is only for 11g.
Exec dbms_network_acl_admin.create_acl ('utl_http_access.xml','Normal Access','DPCDSL',TRUE,'connect',NULL,NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_http_access.xml', principal => 'DPCDSL',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','DPCDSL',TRUE,'resolve',NULL, NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_inaddr_access.xml', principal => 'DPCDSL',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','DPCDSL',TRUE,'connect' );
Exec dbms_network_acl_admin.add_privilege ('utl_mail.xml','DPCDSL',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','DPCDSL',TRUE,'connect',null,null);
Exec dbms_network_acl_admin.add_privilege ('utl_http.xml','DPCDSL',TRUE,'resolve',null,null);
Exec dbms_network_acl_admin.assign_acl ('utl_http.xml','*',NULL,NULL);
commit;
---------------------------
Exec dbms_network_acl_admin.create_acl ('utl_smtp.xml','SMTP Access','DPCDSL',TRUE,'connect',null,null);
Exec dbms_network_acl_admin.add_privilege ('utl_smtp.xml','DPCDSL',TRUE,'resolve',null,null);
Exec dbms_network_acl_admin.assign_acl ('utl_smtp.xml','*',NULL,NULL);
commit;
In addition to the above, install the oracle mail scripts to instal UTL_MAIL packages to enable sending emails form inside of database
SQL>@$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL>@$ORACLE_HOME/rdbms/admin/prvtmail.plb
SQL>ALTER SYSTEM SET smtp_out_server='smtp.oracle.com' scope=BOTH;
Grant Execute on utl_inaddr to DPCDSL ;
Grant Execute on utl_http to DPCDSL ;
SELECT global_name,utl_inaddr.get_host_address FROM global_name;
SELECT UTL_INADDR.get_host_address ('www.oracle.com') FROM DUAL;
SELECT UTL_HTTP.request ('http://www.oracle.com') FROM DUAL;
---------------------------------Removing ACL and priviliges
Unassign ACL
begin
dbms_network_acl_admin.unassign_acl(
acl => 'utl_http.xml',
host => '*',
lower_port => 80,
upper_port => 80
);
end;
Delete Privilege
begin
dbms_network_acl_admin.delete_privilege(
'utl_http.xml', 'DPCDSL', NULL, 'connect'
);
end;
Drop ACL
begin
dbms_network_acl_admin.drop_acl(
'utl_http.xml'
);
end;
------------------------------testing----------------------
create or replace procedure getTitle(pUrl VARCHAR2)
is
vResult CLOB;
begin
vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
vResult := regexp_replace(vResult,'.*<title> ?(.+) ?</title>.*','\1',1,1,'i');
dbms_output.put_line(vResult);
end;
/
/*
This is just a dummy procedure and will only display
the title if the title tag is defined in the first 2000
characters in web page.
*/
set serveroutput on
execute getTitle('http://www.oracle.com');
create or replace procedure getTitle(pUrl VARCHAR2)
is
vResult CLOB;
begin
vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
vResult := regexp_replace(vResult,'.*.*','\1',1,1,'i');
dbms_output.put_line(vResult);
end;
/
set serveroutput on
execute getTitle('http://www.oracle.com');
Monday, April 23, 2012
Auto-Changing SQL Prompt
You have to insert the following line of code in glogin.sql which is usually found in $ORACLE_HOME/sqlplus/admin
set termout off
set echo off
define X=NotConnected
define Y=DBNAME
Column Usr New_Value X
Column DBName New_Value Y
Select SYS_CONTEXT('USERENV','SESSION_USER' ) Usr From Dual;
Select Global_Name DBNAME from Global_Name;
set termout on
set sqlprompt '&X@&Y> '
set termout off
set echo off
define X=NotConnected
define Y=DBNAME
Column Usr New_Value X
Column DBName New_Value Y
Select SYS_CONTEXT('USERENV','SESSION_USER' ) Usr From Dual;
Select Global_Name DBNAME from Global_Name;
set termout on
set sqlprompt '&X@&Y> '
Connect as an Oracle User Without Knowing the Password
SQL> alter user ldbo grant connect through ksh;
User altered.
SQL> connect n/ksh$1#@apx1112srv;
Connected.
SQL> show user
USER is "LDBO"
2. If we can allow ourselves to change the password, but don’t want to change it permanently, there is a way to change the password back.
The DBA_USERS table contains the PASSWORD column. This column contains the encrypted password of the user.
Follow these steps to change the user’s password:
Get the encrypted password of the user from DBA_USERS table and save it.
Change the password by using the “alter user <user> identified by <pwd>” command.
Perform the following step to change the password back:
Use the “alter user <user> identified by values <encrypted_password>” command.
Use the encrypted password you have and pay attention to the “values” keyword in the command, it specifies that the password given in the command is already encrypted.
User altered.
SQL> connect n/ksh$1#@apx1112srv;
Connected.
SQL> show user
USER is "LDBO"
2. If we can allow ourselves to change the password, but don’t want to change it permanently, there is a way to change the password back.
The DBA_USERS table contains the PASSWORD column. This column contains the encrypted password of the user.
Follow these steps to change the user’s password:
Get the encrypted password of the user from DBA_USERS table and save it.
Change the password by using the “alter user <user> identified by <pwd>” command.
Perform the following step to change the password back:
Use the “alter user <user> identified by values <encrypted_password>” command.
Use the encrypted password you have and pay attention to the “values” keyword in the command, it specifies that the password given in the command is already encrypted.
ORA-01092 ORACLE instance terminated. Disconnection forced ORA-00704 bootstrap process failure
shut immediate
startup upgrade
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catupgrd.sql
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlirp.sql
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
shut immediate
startup
SELECT * FROM dba_registry;
startup upgrade
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catupgrd.sql
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlirp.sql
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
shut immediate
startup
SELECT * FROM dba_registry;
ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862]
Fri Apr 20 12:24:30 2012
Errors in file d:\oracle\product\10.2.0\admin\tss1112\bdump\tss5_mmon_2416.trc:
ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862], [], [], [], [], [], [], []
BEGIN :success := dbms_ha_alerts_prvt.check_ha_resources; END;
To implement the workaround, please execute the following steps as the SYS user:
1. Collect the following information and spool it to a file for your records.
a. output of select * from v$instance
b. show parameter instance_name
c. set pages 1000
d. select * from recent_resource_incarnations$
2. Create a backup table of recent_resource_incarnations$.
SQL> create table recent_resource_inc$bk as select * from recent_resource_incarnations$;
3. Truncate recent_resource_incarnations$. Be sure to do this while the instance is up and running. Do not issue this statement if a shutdown is pending.
SQL> truncate table recent_resource_incarnations$;
4. Perform a clean shutdown, followed by a startup.
Errors in file d:\oracle\product\10.2.0\admin\tss1112\bdump\tss5_mmon_2416.trc:
ORA-00600: internal error code, arguments: [kjhn_post_ha_alert0-862], [], [], [], [], [], [], []
BEGIN :success := dbms_ha_alerts_prvt.check_ha_resources; END;
To implement the workaround, please execute the following steps as the SYS user:
1. Collect the following information and spool it to a file for your records.
a. output of select * from v$instance
b. show parameter instance_name
c. set pages 1000
d. select * from recent_resource_incarnations$
2. Create a backup table of recent_resource_incarnations$.
SQL> create table recent_resource_inc$bk as select * from recent_resource_incarnations$;
3. Truncate recent_resource_incarnations$. Be sure to do this while the instance is up and running. Do not issue this statement if a shutdown is pending.
SQL> truncate table recent_resource_incarnations$;
4. Perform a clean shutdown, followed by a startup.
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');
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');
Friday, April 20, 2012
Oracle 10g Scheduler Job Email Notification Wrapped
------------------------job_email_notification.sql------------------------------
prompt Enter an outgoing e-mail SMTP server host
define email_server_host = &1
begin
if sys_context('USERENV','SESSION_USER') != 'SYS' then
raise_application_error(-20000, 'This script must be run AS SYS');
end if;
end;
/
create or replace procedure sys.email_notification_procedure wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
8d1 40e
VeYguoLO7klQMOD091RnOY7N6yYwg2NcuiCDfI4Zxz8C6i6ZWT7mVZJ8aSwlXabiX5Ax6B9/
q8zsDVn2XkSDnC21vLdn5nYOHyoGN11uu06/4ssapd0sWsqzVTCSSTTQwesP6/owIvC7agcU
QaKx0KGRPVosicCYZzwoQ6JMTUCbI1rdZz+e7o5Tyz6758ZmoqyAHew/UhAPZnrpbkUlHr6b
yjKpvAyTTWdBrHQS5inCHQhrhst1GzPBY8KTiXrpoTD9coufGCvdENtDaAw1ASMqVyL7rgc3
RssAmgZwbiznWJAsfALzEX2sVhR2djz0Cti8/ECr/4F7RK26zboDMdjEO19Qtm4fHB3wSdIy
UFqdUavFMAXAiNky1oPmoqOA25+KleDvAnkMzBkBacuY3RzJq0prDL4C7u5kaW7TQdvqYsIH
2AoJ39tlJAZf1nFWiu3Ub++/+MiDUOD3l7fU6AB0LsLLMUsFpY5Vcro290JOIaeyUZbhZ7Wc
pfkwHGLu0wpSHEbT4YsjaQpLzM+xSCS4ik/wOMe1+H276sFPTEmWFkiwrVGQ5v22VciSRsUV
AhvP3QRSvg8qqnpmLlES3YK5wD9ttRa0yQiyaz/oGRRUVlmytAIw51VpHQme9DtdatzpbJzq
JsKbRRShbsihz5DJnIkKXi+5Vv+ezx/XyYYTdg9SO6VrrPJ13gtv1qyrbYjz+6W4ABYN8ETu
fl9+AEPR5yPdojuPn994XylBo9m5i/ImF+DVTkDA6lXjHunek4LJMS2WkO5jgpLgF1tYx7xA
i/A0/W2PAxwBaQC8JZmTXFkAG92oM7i0RaHUKMQ7s+cHUaKpcBuicIj28iM30bAUnKNrlc3D
cubPD6wZt1EBDGPzkSllE4W/DZjLatiSWBqrxRx1sNzTmjurRcANj5yXuTCUa0rq9RMf7vEI
zYn8CPy/IVjBZdeZSOknt3NjWH4FTjGfJwQLD4PS9svHf3rEYZp26ULMudQjWRYwYUokOcP9
39ij+GqqlpHqWQ==
/
show errors
grant execute on sys.email_notification_procedure to public;
begin
dbms_scheduler.drop_program (
program_name => 'sys.email_notification_program',
force => true);
exception when others then null;
end;
/
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;
create or replace procedure sys.add_job_email_notification wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
221f c06
LJgrI9/yuQ8GWaZAPQ3Rnz8kmYQwg82ruscT1JJ6Hup+gq1Eo1H3kcahHpdeNtVYNjfCqZYQ
OI4TGrlXK8X6eYKGLJognUxwH1Bs9AVOwCEc/c0//vjRQS2FY4eXFlgPDxedYhnJOgc613mf
xFSaNJi+7GQgb7+kA4SFChGJAv+J0wYHXUc4uSZqOd9+fPySgk9AoYswHIxScVsOF0uItkOM
pi/QSpAhWntTOLjRUvxFiSN3A/cptAgdMIvQ72/t6Kgi1Lneb87rX8zGAI0FZ1AoA1dS4Wd8
Vx51QBwBY3DN2PRSxFSLLCAkc3q1dbUNozS6D0PExFUKlVs176TtUUjx1wSk+B/93qLZOj15
3AYKD04e+AXPoYdiGfFpLWbTjF5QG20+2h0t3IxX3kkzO7gVxQ/595YDDx8KMrYSb3/bQURS
iwe9kGk8LeO2v0btAxZWzaTo6/MBXcaoKaS/U2Nmwya00c4TW3Jivqoc4c/4vB5GzHVc7g65
/qBzrPIQLGhqd7hp/r7ICAFuvvNN9fd1YJ78JaVHS/AEcX0d3rzgpmt88uQtKKcWAbOIAtJ6
C0os69jjZ9fLSJcnBWySDsYIF0ckHa0lfLPNMFZWR+nS5NuA2pxNBwDbMowpju3djVCNiv5p
DLiw7mhkH3IZwb86wLz/ZL2RLJOWhcWdmBsEppHLN6CH979tbEirxpBdwGDpYGuXyB16QODx
V4yp8RFxAzeeShV48/VjV7NTr6ZWKw+C6eIbywyRwin69rTgNRogCC6RuAiy2CGZTUpPogOT
RA8MTuAxTgvRvKzq2RC+qwiiWHsfiMNXHGXlbAZvmA66q9nJmhy0BzMMmKqRLHSKjISYJDDe
KEEsMXL41vH8BGaVtzdRXRpf3/vru+16q35GiLP0LJ9n4Ycy0YbieOzhWkeUu+ZzGKyCGDN4
bakfRAkUA8+LPhe29wvK7MpARiXbKORdwm06KR1GtW0Ow48e9Hm2dA6QhUSEYikMkQAoZy4Q
7u6vMOB6encMOQ+5/P1xFEYb5j5rdSTixxuEJnr0VAghFDl168r6PrItcllRFGZ67aghSY7g
B0VkA1i2xneG5oTrppcCCEvwNjRx4GJ8nQMO/PLPkxzUVuyskG5f6tG8HLdOXha6kRV2pXyk
8jVuUIT5bB5r7uaRSY6f0b61Xwtrieb4RtJppTvmCma702Hxp+n7VYBxLYqo5FDdMFkdfgpe
JOR4otFsQFEN0N0EvWojkd3iTSMEWYQAYRw1bXKZH7ZsjQ4JKJh8KqzKqaaEwrqPsyqrwNev
e40hRuINN+AmEbdxiFXjJ1IuQjpJwT/Aoy9NmyiDC4U+0NGNAGcHNFYWx9Pl7wBB5fBfMFZy
is1vvc5MVZlFleVpZre8YuBKWTmfJmJ3XEPZQk2Db5LCGjxfzRixYCShB++Gg/JsqZsv6DVp
weqZSkXa+H2heXeGvSGIER5NKjw6lzFzJXJwN56h0ZAHjbE4D+i570KIEpQoXpdU20XgNNa7
vZAYbE4xj48O/sJjOD2YLM/TpwkZhKqSJpD4+vSR1au/Ke955dsW9kfsXNyNPY5U2cLobmFA
YtERZEi0U9Fxe2r00m790V9lcqF2rViJVbsIXxSE8oP05+LzfKzunkwHoAEMYs/DLEE4Fnw8
TsRVElfNkbFOgTSYMPi6Pw3/b6oVcEfVrLN28xwce/y94WdQITv1uYssmxuIf2mlPcLbzjhu
fIICOrdYFa6n8AsURk31bjiQ0sUMBpYykh1Mkk1IL14BXl5hvDZeLm2KOiw6VXu6qpJ1txPa
4J6IqBKoAs8//F7ogbm0F1IlzeUMNx3rfi/p6Y0xDpCs4JKn6AUZJYy9LBz1CQ6J+4Bhhqs+
VfJT0U7EMItlDEzEQkZja8AjzmWv2qPkfQcfyEwA7Y4Pbev6HOU+AqGZji4WOWxD4DmwE5OP
U3fYNd04xj8nVMaQcqUCASFJVHwaxYbI0iJkBHYzP6ngyoooZfpZS9mgILj2idpXIJtvwy15
Z6l77BP0p2CYr6iPfuPmLVRbnHfFf51W8Jep2OuaQYDwEucvvC0z7Z+KXcPSfOUBxsnMn2PG
hyV6t1HQ5Dbyt5OaxNnr3jG8RbJhbzq8BEwzqeKtEQc/E56bJCZxlcewyZQRggAnj38uZUmh
dSTytvMeG97atiAA04QJ0cF4Cnpq4i2sArJrFylblAHgrM+7FY/N+w70xI5l7pWNAjLYp/NS
bWuN8yM1TGmpzrhF/s+A5RVkU6w2QLZCMluqtwZ7tHw2WEY1Q+9XaIXaJEGQpjaGYKaq4SXG
b0zEC3/HoZRueLirzXtBW+nPjphwrm4MvgCaGiGN1pKlNmThRWNzT55qkecLjskKWrh0So80
Quyttqkh/YLzln4GxFyPgsrwNLxw4u5VdyeIi4kt8ik+Bc8lBo15+WnaUS2V2rMwR8Y7p9Fb
Fdsg4XibzqHQxPDWhH85wIZaME19PcNU1SDwPw2uDs2wJUGeDRrl51arBt5y1mhA86hK/ThL
Phfwo5cI/7ZEh0CcsZx2p3AEE5Xu+70pPg/ctWYFCFKx++aHrCEo9lf2p44dtSqVuMFjKyy5
+J+dF+5Z9+4SW5f59so43A5T1O+B/QC8rQvzbnqwQwJ2GbyNTZXiWloESaVjQFNHv6QcpVg2
7lfkS1Tfj6PN0lQhhpvsRTWYbsI9EVjy16lPi9kd+QY4roTjcN5sr6Q2Dtf9Lqz/XSguNAbg
A2trU5k1pP1UYWWd3yRFjWuOOyI7RdfqEjHGQwZzwPmvtNw7It7hr5LYRZQPiY8wYExn6cwr
lRLCsFHeAZ2awf6+c9u08gegtG/wxJAQlDtJI1O5xaCjLvhAHw0fB5aHTRe6Y6x55/To6mvQ
aRtzxdHS6TwgvSRpwue8SVEAej/ScyTk+XjuSf0P2XDjAxQTGnbv2sesiWHOOHC7yL6SIYc9
ug+VOnCq//D+GQ/GOriq/5Kdps+kBcFPryv4ZDdkCxKgJAUUBiDm7lRxta77Clw2fzwhk8Bw
HJy1Ov1YNKd4
/
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;
create or replace procedure sys.remove_job_email_notification wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
ad9 4d4
qpXz6AXyFGIr0eqn4Y84+3I044Uwg+0rBUiDZy/Ngg9/5cMxojb7zGPlRlo5lkv07W2fsWmS
4LNAw8M+WkYz9NC7is5IwAqOKpdqwoLHi8DJn3pewNvkWn+0ZKrN+diGT5ggCJKxJ8csIL8H
11WL+PxLMXn9SB2Po8sL7gJ0qZl1EichgcNkvUFIVPR2hwP3N9KWtpgfLBNzorwvWa+Gm/dJ
GuOZ5MI0XvcXFjJCL4nd9sDOeqbtdwhanmq8BLH0IV5ZR5CnjFg8dmvN8NZn5qsii1mULcOk
145P79wUm6LpjstYFS+RBGU49X5Mw4R9nxY52A6GcyKK/6rYxB5z/Eu8RFbfdp+lSxkZ4ZSk
K/4ec+77uZ8S03tz1MguUxEOf/kLb3mqBPx1HrOJOnmVoHSLX2YyA8i6aVD3QXmzo8jDvQNa
3ecWZXqPyTHonsd3jw+2Gke88i6ZTXSWqxfL1aBD1D2jqNo9bYazQrt20DIlzTKYxUuo998U
oWz0CFJVgnQlXTXliiGmW9kpqVhls0vPLhr3cL2L+xNLBJl6RfQXQTxHxDWC7u/AK3oMOZl+
al54Uxcudhh3InnILfGoPPptebsxErJtPt8cWYkH8IU53spBpfqquSOPX45OdIT6JZvB0rca
8rTqUGvdT7JFdpsMr2+zxYhLsUYTcfgaJLhNLO+JxkEswAgk5v/v3xmlKvteSXT8kDcJZ6nK
iFE1DcQZym2q9h+cZi5c63qjkyahAo7TKqwQYE9NJgSx5+JRouEPFUkvTqKu7pu5G+vo13cw
l92sE945aUcsvK8VZiicbIjshnHJhW9RWRoggIGRZhkYWOrr8cVBPZNNK9gRbHX1uqFyrxqx
lwOVvw3SC6iXDSrZkxifgwUCZElC8r1Jxg0TZqklNaI1af7q9kp7kEzgwsLYzaYuI40Sl+wZ
OqyKSP+CKw588mRvUXCkQyl9vPPZoAp+E2VjGifV0PbJqv1vG+q5QrsGUU4p9wJcBT/Dtoxm
yZx9Q0OGuWTiCZ0LE5OPxtLMLaAWtEwwu8mgs+zvrjpI2dns2Cp7b4ynJMJNp7yfleuf8vuN
rYB0zrIdpLINzEhiZEgKbU8Wxu9Xf5kAWaEQuOe04ZHZiPwSg54X8uAkAjrsY5DsE8kJm9Dm
4a78N0+Z1D9CY8U81GFnygFlHb3ekxV5D/pY8LE7b6B59xMxI2HUWnQc+Lv54eNwFT4=
/
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;
prompt Enter an outgoing e-mail SMTP server host
define email_server_host = &1
begin
if sys_context('USERENV','SESSION_USER') != 'SYS' then
raise_application_error(-20000, 'This script must be run AS SYS');
end if;
end;
/
create or replace procedure sys.email_notification_procedure wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
8d1 40e
VeYguoLO7klQMOD091RnOY7N6yYwg2NcuiCDfI4Zxz8C6i6ZWT7mVZJ8aSwlXabiX5Ax6B9/
q8zsDVn2XkSDnC21vLdn5nYOHyoGN11uu06/4ssapd0sWsqzVTCSSTTQwesP6/owIvC7agcU
QaKx0KGRPVosicCYZzwoQ6JMTUCbI1rdZz+e7o5Tyz6758ZmoqyAHew/UhAPZnrpbkUlHr6b
yjKpvAyTTWdBrHQS5inCHQhrhst1GzPBY8KTiXrpoTD9coufGCvdENtDaAw1ASMqVyL7rgc3
RssAmgZwbiznWJAsfALzEX2sVhR2djz0Cti8/ECr/4F7RK26zboDMdjEO19Qtm4fHB3wSdIy
UFqdUavFMAXAiNky1oPmoqOA25+KleDvAnkMzBkBacuY3RzJq0prDL4C7u5kaW7TQdvqYsIH
2AoJ39tlJAZf1nFWiu3Ub++/+MiDUOD3l7fU6AB0LsLLMUsFpY5Vcro290JOIaeyUZbhZ7Wc
pfkwHGLu0wpSHEbT4YsjaQpLzM+xSCS4ik/wOMe1+H276sFPTEmWFkiwrVGQ5v22VciSRsUV
AhvP3QRSvg8qqnpmLlES3YK5wD9ttRa0yQiyaz/oGRRUVlmytAIw51VpHQme9DtdatzpbJzq
JsKbRRShbsihz5DJnIkKXi+5Vv+ezx/XyYYTdg9SO6VrrPJ13gtv1qyrbYjz+6W4ABYN8ETu
fl9+AEPR5yPdojuPn994XylBo9m5i/ImF+DVTkDA6lXjHunek4LJMS2WkO5jgpLgF1tYx7xA
i/A0/W2PAxwBaQC8JZmTXFkAG92oM7i0RaHUKMQ7s+cHUaKpcBuicIj28iM30bAUnKNrlc3D
cubPD6wZt1EBDGPzkSllE4W/DZjLatiSWBqrxRx1sNzTmjurRcANj5yXuTCUa0rq9RMf7vEI
zYn8CPy/IVjBZdeZSOknt3NjWH4FTjGfJwQLD4PS9svHf3rEYZp26ULMudQjWRYwYUokOcP9
39ij+GqqlpHqWQ==
/
show errors
grant execute on sys.email_notification_procedure to public;
begin
dbms_scheduler.drop_program (
program_name => 'sys.email_notification_program',
force => true);
exception when others then null;
end;
/
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;
create or replace procedure sys.add_job_email_notification wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
221f c06
LJgrI9/yuQ8GWaZAPQ3Rnz8kmYQwg82ruscT1JJ6Hup+gq1Eo1H3kcahHpdeNtVYNjfCqZYQ
OI4TGrlXK8X6eYKGLJognUxwH1Bs9AVOwCEc/c0//vjRQS2FY4eXFlgPDxedYhnJOgc613mf
xFSaNJi+7GQgb7+kA4SFChGJAv+J0wYHXUc4uSZqOd9+fPySgk9AoYswHIxScVsOF0uItkOM
pi/QSpAhWntTOLjRUvxFiSN3A/cptAgdMIvQ72/t6Kgi1Lneb87rX8zGAI0FZ1AoA1dS4Wd8
Vx51QBwBY3DN2PRSxFSLLCAkc3q1dbUNozS6D0PExFUKlVs176TtUUjx1wSk+B/93qLZOj15
3AYKD04e+AXPoYdiGfFpLWbTjF5QG20+2h0t3IxX3kkzO7gVxQ/595YDDx8KMrYSb3/bQURS
iwe9kGk8LeO2v0btAxZWzaTo6/MBXcaoKaS/U2Nmwya00c4TW3Jivqoc4c/4vB5GzHVc7g65
/qBzrPIQLGhqd7hp/r7ICAFuvvNN9fd1YJ78JaVHS/AEcX0d3rzgpmt88uQtKKcWAbOIAtJ6
C0os69jjZ9fLSJcnBWySDsYIF0ckHa0lfLPNMFZWR+nS5NuA2pxNBwDbMowpju3djVCNiv5p
DLiw7mhkH3IZwb86wLz/ZL2RLJOWhcWdmBsEppHLN6CH979tbEirxpBdwGDpYGuXyB16QODx
V4yp8RFxAzeeShV48/VjV7NTr6ZWKw+C6eIbywyRwin69rTgNRogCC6RuAiy2CGZTUpPogOT
RA8MTuAxTgvRvKzq2RC+qwiiWHsfiMNXHGXlbAZvmA66q9nJmhy0BzMMmKqRLHSKjISYJDDe
KEEsMXL41vH8BGaVtzdRXRpf3/vru+16q35GiLP0LJ9n4Ycy0YbieOzhWkeUu+ZzGKyCGDN4
bakfRAkUA8+LPhe29wvK7MpARiXbKORdwm06KR1GtW0Ow48e9Hm2dA6QhUSEYikMkQAoZy4Q
7u6vMOB6encMOQ+5/P1xFEYb5j5rdSTixxuEJnr0VAghFDl168r6PrItcllRFGZ67aghSY7g
B0VkA1i2xneG5oTrppcCCEvwNjRx4GJ8nQMO/PLPkxzUVuyskG5f6tG8HLdOXha6kRV2pXyk
8jVuUIT5bB5r7uaRSY6f0b61Xwtrieb4RtJppTvmCma702Hxp+n7VYBxLYqo5FDdMFkdfgpe
JOR4otFsQFEN0N0EvWojkd3iTSMEWYQAYRw1bXKZH7ZsjQ4JKJh8KqzKqaaEwrqPsyqrwNev
e40hRuINN+AmEbdxiFXjJ1IuQjpJwT/Aoy9NmyiDC4U+0NGNAGcHNFYWx9Pl7wBB5fBfMFZy
is1vvc5MVZlFleVpZre8YuBKWTmfJmJ3XEPZQk2Db5LCGjxfzRixYCShB++Gg/JsqZsv6DVp
weqZSkXa+H2heXeGvSGIER5NKjw6lzFzJXJwN56h0ZAHjbE4D+i570KIEpQoXpdU20XgNNa7
vZAYbE4xj48O/sJjOD2YLM/TpwkZhKqSJpD4+vSR1au/Ke955dsW9kfsXNyNPY5U2cLobmFA
YtERZEi0U9Fxe2r00m790V9lcqF2rViJVbsIXxSE8oP05+LzfKzunkwHoAEMYs/DLEE4Fnw8
TsRVElfNkbFOgTSYMPi6Pw3/b6oVcEfVrLN28xwce/y94WdQITv1uYssmxuIf2mlPcLbzjhu
fIICOrdYFa6n8AsURk31bjiQ0sUMBpYykh1Mkk1IL14BXl5hvDZeLm2KOiw6VXu6qpJ1txPa
4J6IqBKoAs8//F7ogbm0F1IlzeUMNx3rfi/p6Y0xDpCs4JKn6AUZJYy9LBz1CQ6J+4Bhhqs+
VfJT0U7EMItlDEzEQkZja8AjzmWv2qPkfQcfyEwA7Y4Pbev6HOU+AqGZji4WOWxD4DmwE5OP
U3fYNd04xj8nVMaQcqUCASFJVHwaxYbI0iJkBHYzP6ngyoooZfpZS9mgILj2idpXIJtvwy15
Z6l77BP0p2CYr6iPfuPmLVRbnHfFf51W8Jep2OuaQYDwEucvvC0z7Z+KXcPSfOUBxsnMn2PG
hyV6t1HQ5Dbyt5OaxNnr3jG8RbJhbzq8BEwzqeKtEQc/E56bJCZxlcewyZQRggAnj38uZUmh
dSTytvMeG97atiAA04QJ0cF4Cnpq4i2sArJrFylblAHgrM+7FY/N+w70xI5l7pWNAjLYp/NS
bWuN8yM1TGmpzrhF/s+A5RVkU6w2QLZCMluqtwZ7tHw2WEY1Q+9XaIXaJEGQpjaGYKaq4SXG
b0zEC3/HoZRueLirzXtBW+nPjphwrm4MvgCaGiGN1pKlNmThRWNzT55qkecLjskKWrh0So80
Quyttqkh/YLzln4GxFyPgsrwNLxw4u5VdyeIi4kt8ik+Bc8lBo15+WnaUS2V2rMwR8Y7p9Fb
Fdsg4XibzqHQxPDWhH85wIZaME19PcNU1SDwPw2uDs2wJUGeDRrl51arBt5y1mhA86hK/ThL
Phfwo5cI/7ZEh0CcsZx2p3AEE5Xu+70pPg/ctWYFCFKx++aHrCEo9lf2p44dtSqVuMFjKyy5
+J+dF+5Z9+4SW5f59so43A5T1O+B/QC8rQvzbnqwQwJ2GbyNTZXiWloESaVjQFNHv6QcpVg2
7lfkS1Tfj6PN0lQhhpvsRTWYbsI9EVjy16lPi9kd+QY4roTjcN5sr6Q2Dtf9Lqz/XSguNAbg
A2trU5k1pP1UYWWd3yRFjWuOOyI7RdfqEjHGQwZzwPmvtNw7It7hr5LYRZQPiY8wYExn6cwr
lRLCsFHeAZ2awf6+c9u08gegtG/wxJAQlDtJI1O5xaCjLvhAHw0fB5aHTRe6Y6x55/To6mvQ
aRtzxdHS6TwgvSRpwue8SVEAej/ScyTk+XjuSf0P2XDjAxQTGnbv2sesiWHOOHC7yL6SIYc9
ug+VOnCq//D+GQ/GOriq/5Kdps+kBcFPryv4ZDdkCxKgJAUUBiDm7lRxta77Clw2fzwhk8Bw
HJy1Ov1YNKd4
/
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;
create or replace procedure sys.remove_job_email_notification wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
ad9 4d4
qpXz6AXyFGIr0eqn4Y84+3I044Uwg+0rBUiDZy/Ngg9/5cMxojb7zGPlRlo5lkv07W2fsWmS
4LNAw8M+WkYz9NC7is5IwAqOKpdqwoLHi8DJn3pewNvkWn+0ZKrN+diGT5ggCJKxJ8csIL8H
11WL+PxLMXn9SB2Po8sL7gJ0qZl1EichgcNkvUFIVPR2hwP3N9KWtpgfLBNzorwvWa+Gm/dJ
GuOZ5MI0XvcXFjJCL4nd9sDOeqbtdwhanmq8BLH0IV5ZR5CnjFg8dmvN8NZn5qsii1mULcOk
145P79wUm6LpjstYFS+RBGU49X5Mw4R9nxY52A6GcyKK/6rYxB5z/Eu8RFbfdp+lSxkZ4ZSk
K/4ec+77uZ8S03tz1MguUxEOf/kLb3mqBPx1HrOJOnmVoHSLX2YyA8i6aVD3QXmzo8jDvQNa
3ecWZXqPyTHonsd3jw+2Gke88i6ZTXSWqxfL1aBD1D2jqNo9bYazQrt20DIlzTKYxUuo998U
oWz0CFJVgnQlXTXliiGmW9kpqVhls0vPLhr3cL2L+xNLBJl6RfQXQTxHxDWC7u/AK3oMOZl+
al54Uxcudhh3InnILfGoPPptebsxErJtPt8cWYkH8IU53spBpfqquSOPX45OdIT6JZvB0rca
8rTqUGvdT7JFdpsMr2+zxYhLsUYTcfgaJLhNLO+JxkEswAgk5v/v3xmlKvteSXT8kDcJZ6nK
iFE1DcQZym2q9h+cZi5c63qjkyahAo7TKqwQYE9NJgSx5+JRouEPFUkvTqKu7pu5G+vo13cw
l92sE945aUcsvK8VZiicbIjshnHJhW9RWRoggIGRZhkYWOrr8cVBPZNNK9gRbHX1uqFyrxqx
lwOVvw3SC6iXDSrZkxifgwUCZElC8r1Jxg0TZqklNaI1af7q9kp7kEzgwsLYzaYuI40Sl+wZ
OqyKSP+CKw588mRvUXCkQyl9vPPZoAp+E2VjGifV0PbJqv1vG+q5QrsGUU4p9wJcBT/Dtoxm
yZx9Q0OGuWTiCZ0LE5OPxtLMLaAWtEwwu8mgs+zvrjpI2dns2Cp7b4ynJMJNp7yfleuf8vuN
rYB0zrIdpLINzEhiZEgKbU8Wxu9Xf5kAWaEQuOe04ZHZiPwSg54X8uAkAjrsY5DsE8kJm9Dm
4a78N0+Z1D9CY8U81GFnygFlHb3ekxV5D/pY8LE7b6B59xMxI2HUWnQc+Lv54eNwFT4=
/
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_email_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 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;
-- 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;
/
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');
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');
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;
/
Tuesday, April 17, 2012
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.
Monday, April 16, 2012
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');
Subscribe to:
Posts (Atom)