MATRIX: ORACLE DATABASE UPGRADE

ORACLE CSSCAN: HOW TO INSTALL AND RUN

CSSCAN is a oracle utility to check the database character set conversion.

TO INSTALL:

The character set migration utility schema is installed by running the "$ORACLE_HOME/rdbms/admin/csminst.sql" script in SQL*Plus as the SYS user

TO RUN

csscan \"sys/password@sid AS SYSDBA\" full=y tochar=<new database character set> for example AL32UTF8

if you run with sintax csscan "sys/password@sid AS SYSDBA" it's appears the message

LRM-00108: invalid positional parameter value 'as'

failed to process command line parameters ç

Scanner terminated unsuccessfully

ORACLE: COMMAND HOW TO KNOW OPENED CURSORS CURRENT

This is a command to know opened cursors current order by user and sid

select b.sid, a.username, b.value Cursores_Abiertos from v$session a, v$sesstat b, v$statname c where c.name in ('opened cursors current') and b.statistic# = c.statistic# and a.sid = b.sid and a.username is not null and b.value >0 order by a.username,b.sid;

ORACLE HOW TO MANIPULATE LISTENER.LOG WITHOUT STOP THE LISTENER

Sometimes, you need manipulate the listener.log because it's too big or your disk device is full and you can't stop the service for your business then this is the solution, stop the generation of  entries into the logfile but the listener continue executing.

this is a script into crontab running each 2 weeks. In this script I stop the inserts into the listener.log, compress the file and start the inserts into a new  listener.log

lsnrctl << EOF
set current_listener LISTENER
set log_status off
exit
EOF
go to place of your listener.log and then, delete, move or compress....
gzip listener.log
lsnrctl << EOF
set current_listener LISTENER
set log_status on
exit
EOF

after this operation you can see in the directory

listener.log.gz -----> old file compressed

listener.log -----> new file generated

ORACLE UTL_SMTP: INSTALL AND EXAMPLE

---- To install UTL_SMTP ----

host@oracle]$ cd $ORACLE_HOME/rdbms/admin
[host@oracle]$sqlplus / as sysdba
SQL> @utlsmtp
SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
SQL> alter system set smtp_out_server='mailhost ip address:25' scope=both;


---- Example UTL_SMTP ----



DECLARE
v_From VARCHAR2 := 'aaaaa@gmail.com';
v_Recipient VARCHAR2 := 'bbbb@gmail.com';
v_Recipient2 VARCHAR2 := 'ccccc@gmail.com';
v_Subject VARCHAR2 := 'this is a test of utl_smtp';
v_Mail_Host VARCHAR2 := 'mailhost name or mailhost IP address';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2 := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient2);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || ';' || v_Recipient2 || crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/

ORACLE UTL_MAIL: INSTALL AND EXAMPLE

----- To install utl_mail -----

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
grant execute on UTL_MAIL to public;


----- Example of utl_mail -----

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => 'xxxxx@gmail.com',
            recipients => 'bbbbbbb@gmail.com,ccccccc@gmail.com',
               subject => 'This is the subject of message',
               message => 'this is the message',
             mime_type => 'text; charset=us-ascii');
END;
/

LINUX HOW TO CONFIGURE SENDMAIL. EXAMPLE OF SEND MESSAGE USING MAILX

This summary is not available. Please click here to view the post.