How to send email from 10g Oracle Database (UTL_MAIL)?

To enable mailing from database using below steps.

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;

Use default SMTP Port as 25.

If database instance had been started with spfile

alter system set smtp_out_server = ’10.10.10.10:25′ scope=both;

If database instance had been started with pfile


alter system set smtp_out_server = ’10.10.10.10:25′;


Also make below entry in your initSID.ora 

smtp_out_server = ’10.10.10.10:25′

To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.

grant execute on utl_mail to USERS;

Now,database configuration finished.

To send email, use below code in your procedure/package,

exec utl_mail.send((sender => ‘abc@abc.com’, recipients => ‘xyz@xyz.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘TESTMAIL USING  UTL_MAIL PACKAGE.’);
 

Check the inbox of the email id, to verify the email received or not.

How I can find a running Oracle database is started using PFILE or SPFILE?

You can check through following ways

Check in v$parameter view

Select * from v$parameter where  upper(name)  like ‘SPFILE’;                              

or

SHOW PARAMETER SPFILE. 

If you find any data in above query then it started using SPFILE else with PFILE.

What is Oracle Data Pump (impdp and expdp)?

• Oracle Data Pump is a new feature of Oracle Database 10 g that provides high speed, parallel, bulk data and metadata movement of Oracle database contents.
• DBMS_DATAPUMP package  provides a server-side infrastructure for fast data and metadata movement. expdp and impdp is used to import/export activites.
• It vastly improved performance and greatly enhanced functionality, such as restartability, flexible object selection, and better monitoring and control of export and import jobs.
• Data Pump is server-based, rather than client-based. Dump files, log files, and SQL files are accessed relative to server-based directory paths, so that appropriate file security can be enforced.
• Data Pump requires you to specify directory paths as directory objects. A directory object maps a name to a directory name on the file system.
• Before you can run Data Pump Export or Data Pump Import, a directory object must be created by a DBA or by any user with CREATE ANY DIRECTORY privilege. 

What is autonomous transaction in Oracle?

• An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction).
• An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction. 
• The autonomous transaction must commit or roll back before the autonomous transaction is ended and the parent transaction continues. 
• An autonomous transactions is available from Oracle 8i. 
• An autonomous transaction is defined in the declaration of a pl/sql block. This can be an anonymous block, function, procedure, object method or trigger.
• This is done by adding the statement 'PRAGMA AUTONOMOUS_TRANSACTION;' anywhere in the declaration block. 
• Autonomous transactions can be used for logging in the database independent of the rollback/commit of the parent transaction.

      CREATE OR REPLACE PROCEDURE test

      IS

             PRAGMA AUTONOMOUS_TRANSACTION;

      BEGIN

             insert ....

             commit;

      END;

What are ORACLE PRE-COMPILERS?

• Using ORACLE PRE-COMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. 
• The Precompilers are known as Pro*C,Pro*Cobol,This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. 
• The pre-compiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to create an executable.

How you can coalesce free space in oracle?

• SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then, only for a short period of time. 
• SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. 
• With Oracle 7.3 one can manually coalesce a tablespace using the ALTER TABLESPACE tblspace_temp COALESCE; command, until then use 

          SQL> alter session set events 'immediate trace name coalesce level n'

          Where 'n' is the tablespace number.

          SELECT TS#, NAME FROM SYS.TS$;

• You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.

What is the need of rebuilding of an index?

          ANALYZE INDEX  INDEX_NAME VALIDATE STRUCTURE;

• Using above statement we can rebuild a Index.
• Each command creates a single row in the V$INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. 
• The 'badness' of the index can then be identified DEL_LF_ROWS/LF_ROWS.