вторник, 18 февраля 2014 г.

How to connect SQLPlus without tnsnames.ora

Connect using one of service_name:
sqlplus username/password@//host[:port][/service_name]

where service_name can be one of simple name or domain name specified in service_names parameter:
show parameter service_names
ALTER SYSTEM SET service_names = 'ORCL', 'us.acme.com' COMMENT='Add new services' SCOPE=BOTH;

понедельник, 17 февраля 2014 г.

Procedure to write to alert.log

Link to original article Stored Proc to write to database alert logs author Charles Kim.

From the application or from database triggers, if I want to record anything to the alert log, I can simply invoke this stored procedure and write error messages to the alert log.

Most of the DBAs have implemented alert log scanners and will send alert notifications if new ORA- error messages pop up in the alert log file.
 
create or replace procedure write_msg_to_alert_log (
  p_message varchar2,
  p_oranum number default 20001
)
is
/*
  create procedure under system user
*/
begin
  sys.dbms_system.ksdddt;
  sys.dbms_system.ksdwrt(2, 'ORA-' || to_char(p_oranum) || ': ' || p_message);
end;
/
  
--grant execute on write_msg_to_alert_log to public;
grant execute on write_msg_to_alert_log to scott;

create public synonym write_msg_to_alert_log for system.write_msg_to_alert_log;