send email with attached file from oracle 11g database

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

How to send email from 11g Oracle Database (UTL_MAIL)

Heres a simple solution to send out emails from 10g Database sql prompt.
This solution will be really helpful if the OS utility (mailx, sendmail) is restricted for end users.
Steps to enable Mailing from Database
1. sqlplus ‘/ as sysdba’
2. @$ORACLE_HOME/rdbms/admin/utlmail.sql
3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb
4. Set smtp_server information in init.ora or spfile.ora
alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;
25 = Default SMTP Port If instance had been started with spfile
eg: alter system set smtp_out_server = ’172.25.90.165:25? scope=both;
restart database ;
If instance had been started with pfile
alter system set smtp_out_server = ’172.25.90.165:25?;
Also make below entry in your initSID.ora smtp_out_server = ’172.25.90.165:25?
Thats It, your database is configured to send emails ….
How to send an email
1. sqlplus '/ as sysdba'
2. exec utl_mail.send((sender => 'oraclepitstop@wordpress.com', recipients => 'oraclepitstop@wordpress.com', subject => 'Testing UTL_MAIL Option', message => 'blah blah blah');
3. Check the inbox of the email id, to verify the email receipt.
To enable other DB users to use this functionality,
grant execute permission on UTL_MAIL package.
eg: grant execute on utl_mail to apps;
Happy Mailing !!!
cheers,
OraclePitStop.

Delete old file in AIX (older than 3 days)

find /opt/oracle/grid/diag/asm/+asm/+ASM2/trace/ -type f -name '*.trc' -mtime +3 -exec rm -f {} \; find /opt/oracle/grid/diag/asm/+asm/+ASM2/trace/ -type f -name '*.trm' -mtime +3 -exec rm -f {} \; cp /opt/oracle/grid/diag/asm/+asm/+ASM2/trace/*.trc /opt/oracle/arch2/backup_tracefile20110917 cp /opt/oracle/grid/diag/asm/+asm/+ASM2/trace/*.trm /opt/oracle/arch2/backup_tracefile20110917


delete trace file older than 3 days ...
su - grid
find /opt/oracle/grid/diag/asm/+asm/+ASM1/trace -name "*.trc"  -mtime +3 -exec rm -f {} \;
find /opt/oracle/grid/diag/asm/+asm/+ASM1/trace -name "*.trm"  -mtime +3 -exec rm -f {} \;
su - oracle
find /opt/oracle/app/oracle/diag/rdbms/boss/boss1/trace -name "*.trc"  -mtime +3 -exec rm -f {} \;
find /opt/oracle/app/oracle/diag/rdbms/boss/boss1/trace -name "*.trm"  -mtime +3 -exec rm -f {} \;

تحية واعتزاز الى كل الاباء

تلبس حذاءه فتتعثر لصغر قدمك وكبر الحذاء, تلبس نظارته، سترته، قبعته فتشعر بالوقار. أقصى أمنياتك قيادة سيارته. واليوم أنت لا تلبس حذاءه لذوقه القديم، تحتقر أغراضه لعتاقتها، وسيارته لا تروق لك الآن. كلامه لا يلائمك، وحركاته تزعجك. تتأخر فيتصل بك قلقاً عليك فتضيق ذرعاً باتصاله لأنك لم تعد طفلاً. ترجع متأخراً فيوبخك فترفع صوتك عليه، فيسكت. تحمل سفهك وجهلك وحماقتك لأنه يحبك. أنشرها تقديراً لوالدك، لإنسان أنت بدونه لاشيء تحية واعتزاز الى كل الاباء احبك يا والدي الطيب يا غالي

Details: ORA-28001: the password has expired (DBD ERROR: OCISessionBegin)

Ensure that ORACLE_HOME is set to the correct home 1. Stop the standalone dbconsole on Unix $ emctl stop dbconsole on Windows Stop the Windows Service OracleDBConsole Or Open a DOS Command Window and type C> emctl stop dbconsole 2. Check that the standalone dbconsole is stopped on Unix $ emctl status dbconsole on Windows Check the status of the Windows Service OracleDBConsole Or Open a DOS Command Window and type C> emctl status dbconsole 3. Connect to the database as a user with DBA privilege with SQL*Plus and execute SQL> alter user sysman identified by ; 4. Check the new password SQL> connect sysman/[@database_alias] 5. Go to $ORACLE_HOME/host_sid/sysman/config 5.1 Save the file emoms.properties to emoms.properties.orig 5.2 Edit the file emoms.properties a. Search for the line beginning with: oracle.sysman.eml.mntr.emdRepPwd= Replace the encrypted value by the new password value b. Search for the line: oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE Replace TRUE by FALSE 6. Restart the standalone dbconsole on Unix $ emctl start dbconsole on Windows Start the Windows Service OracleDBConsole Or Open a DOS Command Window and type C> emctl start dbconsole 7. Check that the password has been encrypted Edit the file emoms.properties 7.1 Search for the line beginning with: oracle.sysman.eml.mntr.emdRepPwd= Check that the password is encrypted 7.2 Search for the line beginning with: oracle.sysman.eml.mntr.emdRepPwdEncrypted= Check that the value is TRUE BY DBMS Direct

Loop In Command line DOS

FOR /F "tokens=*" %%G IN ('dir /b ^"D:\HLRNEW\HLR_HISTORY\OUT\*.csv^"') DO ECHO %%D