Memory usage for AMM RAC database and changing RAC database to ASMM

SQL> select sum(bytes/1024/1024) Current_SGA_SIZE_in_MB from v$sgastat;
CURRENT_SGA_SIZE_IN_MB
----------------------
        1804.448437
SQL> select sum(bytes/1024/1024) MAX_SGA_SIZE_in_MB from  v$sgainfo    where name = 'Maximum SGA Size';
MAX_SGA_SIZE_IN_MB
------------------
    2592.84766
SQL> show parameter memory_max_target;
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target             big integer 1600M
SQL> select (value/1024/1024) Current_PGA_IN_USE_in_MB from v$pgastat where name = 'total PGA inuse';
CURRENT_PGA_IN_USE_IN_MB
------------------------
          788.078938
SQL> select (value/1024/1024) MAX_PGA_ALLOCATED_in_MB from v$pgastat where name = 'maximum PGA allocated';
MAX_PGA_ALLOCATED_IN_MB
-----------------------
         1567.658203
SQL> select (value/1024/1024) PGA_TARGET_in_MB    from v$pgastat where name = 'aggregate PGA target parameter';
PGA_TARGET_IN_MB
----------------
         480





Memory usage of exisitng AMM: 
 - memory reserved  for PGA/SGA: 8 GB 
 - current PGA size 790 MB
 - current SGA size 1804 MB
 - free memory for future PGA/SGA usage: ~ 2.5 GB

For switching ASMM this can be translated into 
  SGA_MAX_SIZE             : 3 GB
  SGA_TARGET               : 3 GB
  PGA_AGGREGATE_TARGET     :  2 GB

For further tuning check : V$PGA_TARGET_ADVICE

Execute the  following commands.
Disable AMM
  SQL> alter system reset memory_max_target scope=spfile  sid='*';
  SQL> alter system reset memory_target  scope=spfile  sid='*';

Enable ASMM
  SQL> alter system set SGA_MAX_SIZE=3G scope=spfile  sid='*';
  SQL> alter system set SGA_TARGET=3G scope=spfile  sid='*'; 
  SQL> alter system set PGA_AGGREGATE_TARGET=2G scope=spfile  sid='*';  

Reboot database and verify that we have switched from AMM to ASMM
SQL> show parameter memory
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target             big integer 0
memory_target                 big integer 0
--> AMM disabled 

SQL> show parameter sga
NAME                     TYPE          VALUE
------------------------ ----------- ------------------------------
sga_max_size             big integer 3G
sga_target               big integer 3G

SQL> show parameter pga
NAME                     TYPE     VALUE
------------------------ ----------- ------------------------------
pga_aggregate_target     big integer 2G

--> ASMM enabled !

OPatch, Mainting patches in Oracle Database

OPatch is an Oracle utility that assists us to apply interim patches to Oracle’s rdbms software amd clusterware. We can find opatch utility in $ORACLE_HOME/Opatch directory. 

For example, let we apply patch number 11114547  to our 11.2.0.4 database.

Applying Patch:

1- Backup Oracle Home directory.

       $ tar -cf ora11g.tar ora11g

2- Download patch file p11114547_10205_Linux-x86-64.zip via metalink. And copy it to database server.

3- Unzip the patch file.

       $ unzip p11114547_10205_Linux-x86-64.zip

4- Apply patch with opatch utility.

       $ cd 11114547

       $ ORACLE_HOME/OPatch/opatch apply

To see list of applied patches :

        $ORACLE_HOME/OPatch/opatch lsinventory

For example:

        $ORACLE_HOME/OPatch/opatch lsinventory

   Invoking OPatch 11.2.0.4.9

Oracle Interim Patch Installer version 11.2.0.4.9

Copyright (c) 2011, Oracle Corporation. All rights reserved.

Oracle Home : /oracle/ora10g

Central Inventory : /oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.4.9

OUI version : 11.2.0.5.0

OUI location : /oracle/ora10g/oui

Log file location : /oracle/ora10g/cfgtoollogs/opatch/opatch2017-09-14_12-13-12PM.log

Patch history file: /oracle/ora10g/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle/ora10g/cfgtoollogs/opatch/lsinv/lsinventory2017-09-14_12-13-12PM.log

Installed Top-level Products (3):

Oracle Database 10g 10.2.0.1.0

Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0

Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0

There are 3 products installed in this Oracle Home.

Interim patches (2) :

Patch 8943287 : applied on Fri Oct 21 20:39:46 EEST 2011

Unique Patch ID: 12722995

Created on 23 Aug 2010, 11:45:16 hrs PST8PDT

Bugs fixed:

8943287

Rollback applied patch:

Sometimes a patch is applied to the system may need to take back because of its effect. In this case, rollback is performed as follows.

$ORACLE_HOME/OPatch/opatch rollback -id 11114547

Gather diagnostic Information using TFA in Oracle Database

Oracle Trace File Analyzer (TFA) provides a number of diagnostic tools in a single bundle, making it easy to gather diagnostic information about the Oracle database and clusterware, which in turn helps with problem resolution when dealing with Oracle Support.

If possible we should install Oracle Trace File Analyzer as root. This will give you the highest capabilities. If Oracle Trace File Analyzer is already installed, reinstalling will perform an upgrade to the existing location. If it is not already installed, the recommended location is /opt/oracle.tfa

To install as root:

Download the appropriate Oracle Trace File Analyzer zip, copy to required machine and unzip.

Run the installTFA command: $ ./installTFA

To install as an ORACLE_HOME owner use the –extractto option. This tells Oracle Trace File Analyzer where to install to. The installer includes a JVM, but if you want to use one already installed use the –javahome option to point to it.

./installTFA -extractto -javahome

If we do not want to use ssh, we can install on each host using a local install. Then we use tfactl syncnodes to generate and deploy the relevant SSL certificates.

Now the time to collect TFA.

Please run TFA which collects all clusterware logs from all nodes (needs to be done as root from node1's gi_home): 

TFA from each node: 

==> TFA Collector- The Preferred Tool for Automatic or ADHOC Diagnostic Gathering Across All Cluster Nodes ( Doc ID 1513912.1 ) 

Examples: 

/bin/tfactl diagcollect -all -from "" -to "" 

/bin/tfactl diagcollect -all -since [2d|8h] 

/bin/tfactl diagcollect -for "" <--- 12hrs="" after="" and="" before="" collect="" font="" given="" nbsp="" the="" time="" will="">

Generate Incident Report in Oracle Database

The Automatic Diagnostics Repository (ADR) is a hierarchical file-based repository for handling diagnostic information. 

Directory structure is as:

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/alert
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/cdump
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/hm
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/incident
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/trace
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/{others}

To generate incident report quickly, we can follow the below steps:

adrci> show problem
adrci> show incident

adrci> show incident -mode detail -p "incident_id=incident_no" 
adrci> ips create package problem <problem_id>correlate all
adrci> ips generate package  in "/tmp"

SQLplus user profile; showing connection name in the sqlplus prompt

SQLplus user profile is maintained in the file glogin.sql which is executed at the time of login in to a database connection. The default location of this file is

 $ORACLE_HOME/sqlplus/admin/

Usually when a query is executed through sqlplus terminal, at first it tries to fetch profile form the current directory, if no there in that case from the default location.

Suppose, by defualt if we connect to a database using  sqlplus, it dones not show the connected database information in the screen.  It only prompts with: 

sqlplus>

Now if we want to  show the connected database information in the sqlplus command line as:

username@db_connect_name>

In that case, we cas easily do that by modification in the glogin file as below:

set sqlprompt "_user '@' _connect_identifier >"



There are some other keywords for using in glogin file as:

SET LINESIZE	Followed by a number, sets the number of characters as page width of the query results.
SET NUMFORMAT	Followed by a number format (such as $99,999), sets the default format for displaying numbers in query results.
SET PAGESIZE	Followed by a number, sets the number of lines per page.
SET PAUSE	Followed by ON, causes SQL*Plus to pause at the beginning of each page of output (SQL*Plus continues scrolling after you enter Return). Followed by text, sets the text to be displayed each time SQL*Plus pauses (you must also set PAUSE to ON).
SET SQLPROMPT	Followed by the connect information variable in the form: SET SQLPROMPT '&_CONNECT_IDENTIFIER > ' changes the SQL*Plus command-line prompt to display the SID of the database you are connected to.
SET TIME	Followed by ON, displays the current time before each command prompt.

TNS Listener supports no services

For the case Oracle 11g database in linux, I encountered an issue where the database listener was unable to connect to any DB service although the associated database server was started, up and running. No matter For each time I issued lsnrctl start commands, the listener was unable to make tie to the running database service. The listener reported the following message at startup;

.
.
.
.
The listener supports no services
The command completed successfully

To overcome this issue, we are in need to leave the listener started and reboot the 11g database. After 11g DB restart, while issuing the lsnrctl status command and we saw that the services registered successfully with the listener.

CDB & PDB Operation

List of PLugabel Database in the Container Database:

select CON_ID, NAME, OPEN_MODE from V$PDBS;

Connecting PLuggable Database:

alter session set container = pdb6;

to connect with the container database:

ALTER SESSION SET CONTAINER = CDB$ROOT;

To get the name of connected database:

show con_name

Starting Plugable Database:

From the current PDB:
alter pluggable database open;
From Container Database:
alter pluggable database pdb6 open;

Shutting Down Plugable Database:

From the current PDB:
alter pluggable database close;
From Container Database
alter pluggable database pdb6 close;

List of Common users in CDB:

select distinct USERNAME from CDB_USERS where common = 'YES';

List of Modifiable parameters in PDB level:

set lines 200
col name for a35
select NAME, ISPDB_MODIFIABLE from V$PARAMETER;




Values of CON_ID and definition:
0 = The data pertains to the entire CDB
1=  The data pertains to the root
2= The data pertains to the seed
3 - 254 = The data pertains to a PDB, Each PDB has its own container ID.

Container(CDB) & Plug-able(PDB) Database

Plug-able Database & Container Database:

Background Processes	Shared by root CDB and All PDBs
Control File	Single Control file for entire CDB
Redo Log	Single Redo Log for entire CDB
SYSTEM Tablespace	Separate SYSAUX tablespace for the root and for each PDB.
SYSAUX Tablespace	Separate SYSAUX tablespace for the root and for each PDB.
Temporary Tablespace	one default temporary tablespace for the entire CDB; but we can can create additional temporary tablespaces in individual PDBs
Undo Tablespace	One active undo tablespace is needed for a single-instance CDB, or one active undo tablespace is needed for each instance of an Oracle RAC CDB.
Default Tablespace	We can specify a separate default tablespace for the root and for each PDB
Physical Datafiles	There are separate datafiles for the root, the seed, and each PDB.
Database Character-set	A CDB uses a single character set. All of the PDBs in the CDB use this character set.
listener.ora, tnsnames.ora, and sqlnet.ora	Single copy of listener.ora, tnsnames.ora, and sqlnet.ora file for an entire CDB. All of the PDBs in the CDB use these files.

Get elapsed time for an individual command

In sqlplus, to see the elapsed time for an individual query, we can use the "set timing on" command.

SQL> set timing on;

SQL> select surname from personal_details where firstname='udvas';

Elapsed: 00:00:02.52

Again, the "set timing on" command is a SQL*Plus command, but we can measure run time for Oracle SQL with a variety of Oracle tools.

Sometimes when working on SQL command optimizations, all that is desired is a rough timing estimate; namely, the SQL*Plus client elapsed execution time, or simple clock time. Often that simple metric is sufficient for some very basic tuning needs. SQL*Plus has a built-in capability to do exactly this - it is the SET TIMING  command. It essentially records the clock time before and after the SQL command execution, then displays the run time difference.

This commands works for the single command.

ORA-02297: cannot disable constraint -dependencies exist

ORA-02297: cannot disable constraint -dependencies exist

Whenever you try to disable a constraint of a table it fails with error message ORA-02297: cannot disable constraint -dependencies exist as below.

SQL> alter table transaction disable constraint TRANSACTION_PK;
alter table transaction disable constraint TRANSACTION_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) - dependencies exist

Reason behind this problem is as:
Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.

This problem can be solved in different ways.

Two solutions exist for this problem.
1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.

SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
     p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
     FROM user_constraints p
     JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
     WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
     AND c.constraint_type = 'R'
     AND p.table_name = UPPER('&table_name');
Enter value for table_name: transaction
old   7:      AND p.table_name = UPPER('&table_name')
new   7:      AND p.table_name = UPPER('transaction')

Parent Table                   Child Table                    Parent Constraint              Child Constraint
------------------------------ ------------------------------ ------------------------------ ------------------------------
TRANSACTION                    USER_SALARY_RECORD             TRANSACTION_PK                 SYS_C005564
TRANSACTION                    TRANSACTION_DETAIL             TRANSACTION_PK                 TRNSCTN_DTL_TRNSCTN_FK

SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564;
Table altered.

SQL> alter table TRANSACTION_DETAIL  disable constraint TRNSCTN_DTL_TRNSCTN_FK;
Table altered.

SQL> alter table transaction disable constraint TRANSACTION_PK;
Table altered.

2)Disable the constraint with cascade option.

SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
Table altered.

Oracle Bug: kewastUnPackStats(): bad magic 1 (0x2b2b8f4652ad, 0)

Someitmes we may get the error in rdbms alert log...?

It generates thousands of lines each day which will expand the alert log size.

We can override this error by.

ALTER SYSTEM SET control_management_pack_access='NONE' SCOPE=MEMORY;

To get the solution permanantly, we need to deploy the patch.

Starting a Rac Instance from the spfile of another instance for Oracle Database

At a Glance:

Create pfile. Modify pfile with the correct location Start instance with the new pfile.

Step 1: Create pfile from spfile:

create pfile='/tmp/pfile_n1_01062017' from spfile;

Step 2: Transfer the file to target node:

scp /tmp/pfile_n1_01062017 oracle@sdbabut2:/tmp/pfile_n1_01062017.ora

Step 3: Modify the file according to target node:

Fields: thread, instance_number,dispatchers

Replace source node service name by target host service name

:%s/source_host_service_name/target_host_service_name/g

Step 4: Start instance from modified pfile:

sqlplus / as sysdba > startup mount pfile=’/tmp/modifiedpfile.ora’

If it shows error, in that case modify accordingly.

If case of successfully start up,  need to alter the mode of the database.

sqlplus> alter database open;

If everything seems fine, create the spfile from the memory.

sqlplus> create spfile from memory;

Session Trace to find the reason in Oracle DB

Sometimes the same query that ran earlier with less time may take much more time now. To find out the reason, we can follow the below steps.

To find out what is happening you need to do a session trace of a session running this SQL.

• Open up SQL*Plus and connect to your database.
• Enable timed statistics if it not already enabled.

alter session set time_statistics=true;

• Turn on a level 8 SQL Trace of your session. A level 8 trace will capture your wait events. The alter session set events statement below is what enables the SQL Trace of your session.

alter session set tracefile_identifier='Target_Trace';
alter session set events '10046 trace name context forever, level 8';

• Run your Select and then exit SQL*Plus.

• On your database server, go to the $UDUMP directory and find your trace file. It will have 'EMP_Select' as part of the name.

• Using your trace file as input use the tkprof utility to format your trace file into a more readable format. If you invoke tkprof with no arguments, it will give you a help screen with the valid options to use. Make sure you specify the EXPLAIN_PLAN and WAITS=YES options.

Using the output of the TKProf utility you should be able to identify the problem. You would need to especially review the Wait events and Explain plan output.

DMLTransaction Info in oracle

For each DML operation, a transaction id is initiated. We can get the transaction id by below query:

select dbms_transaction.local_transaction_id from dual;

PAUL @ pauldb-uat > select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
8.10.4224

the transaction ID is a series of numbers denoting undo segment number, slot# and record# (also known as sequence#) respectively, separated by periods.

To get the details about a transaction we can use the below query:

select

    owner               object_owner,

    object_name         object_name,

    session_id          oracle_sid,

    oracle_username     db_user,

    decode(LOCKED_MODE,

        0, 'None',

        1, 'Null',

        2, 'Row Share',

        3, 'Row Exclusive',

        4, 'Share',

        5, 'Sub Share Exclusive',

        6, 'Exclusive',

        locked_mode

    )                   locked_mode

    from v$locked_object lo,

        dba_objects do

    where

        (xidusn||'.'||xidslot||'.'||xidsqn)

            = ('&transid')

    and

        do.object_id = lo.object_id;

ITL- Interested Transaction List

Oracle keeps note of which rows are locked by which transaction in an area at the top of each data block known as the 'interested transaction list'. The number of ITL slots in any block in an object is controlled by the INITRANS and MAXTRANS attributes. INITRANS is the number of slots initially created in a block when it is first used, while MAXTRANS places an upper bound on the number of entries allowed. Each transaction which wants to modify a block requires a slot in this 'ITL' list in the block.

If multiple transactions attempt to modify the same block, they can block each other if the following conditions are fulfilled:

- There is no free ITL ("Interested Transaction List") slot available. Oracle records the lock information right in the block and each transactions allocates an ITL entry. 

- Insufficient space in the block left to add a new ITL slot. Since each ITL entry requires a couple of bytes a new one cannot be created if the block doesn't have sufficient free space.

The INITRANS and MAXTRANS settings of a segment control the initial and maximum number of ITL slots per block. The default of INITRANS in recent Oracle releases is 1 resp. 2 for indexes and the default value for MAXTRANS is 255 since the 10g release.

The following example demonstrates the issue. A block is almost full and several transactions attempt to manipulate different rows that all reside in this block.


The ITL in the "enq: TX - allocate ITL entry" indicates error is for "Interested Transaction List", and there are several approaches to fixing this error:
1 - Increasing the value of INITRANS and/or MAXTRANS for the table and indexes.
2 - Move the table to a smaller blocksize.
3 - In some cases, you can remove the enq: TX - allocate ITL entry error for UPDATE/DELETE DML issues by reorganizing the table to increase PCTFREE for the table, thereby leaving less rows per data block.
4 - Reduce the degree of parallel DML on this table

Parallel Execution Wait Events in Oracle

Oracle Parallel Execution can help utilize the power of your hardware and yet remains under-used. 

It is an interesting technology that is particularly suited to data warehousing, in that it allows a single user (or small set of users) to effectively soak up all of the server resources to satisfy a particular query. According to AskTom article by Tom Kyte on parallel query that said that this isn't always what you want - you wouldn't for example want individual OTLP users soaking up all resource for regular transactions or queries - but parallel query is an excellent way to effectively use up all the available CPUs and disk units when you've got a particularly big warehouse query.

Oracle documentation states that these are main wait/idle events because they indicate the normal behavior of a process waiting for another process to do its work:

• PX Deq: Table Q Normal 
• PX Deq: Execute Reply 
• PX Deq Credit: send blkd 

PX Deq: Table Q Normal

Indicates that the slave wait for data to arrive on its input table queue. In a parallel execution environment we have a producer-consumer model. One slave set works on the data ( e.g. read data from disk , do a join ) called the producer slave set and the other slave set waits to get the data so can start the work. The slaves in this slave set are called consumer. The wait event "PX Deq: Table Q Normal" means that the slaves in the consumer slave have to wait for rows (data) from the other slave set so they can start their work. 

PX Deq: Execute Reply 

The QC is expecting a response (acknowledgment) to a control message from the slaves or is expecting to dequeue data from the producer slave set. This means he waits that the slaves finished to execute the SQL statement and that they send the result of the query back to the QC. 

PX Deq Credit: send blkd 

The wait events "PX Deq Credit: need buffer" and "PX Deq Credit: send blkd" are occur when data or messages are exchanged between process that are part of a px query. 

There is an another event: The PX qref latch event can often mean that the Producers are producing data quicker than the Consumers can consume it. On this particular system, very high degrees of parallelism were being used during an overnight batch run so a great deal of messaging was going on. Maybe we could increase parallel_execution_message_size to try to eliminate some of these waits or we might decrease the DOP(Degree of Parallelism).

Sending mail by PL Sql in oracle

A stored procedure, to send mail can be as:

We are using a new type in the SP for containing the CC mail list. For this purpose, we can do as:


CREATE OR REPLACE TYPE mail_ccs AS TABLE OF VARCHAR2;

SP Definition:

CREATE OR REPLACE PROCEDURE sendMail(
    username       IN VARCHAR2,
    password       IN VARCHAR2,
    smtpHost       IN VARCHAR2,
    smtpPort       IN PLS_INTEGER DEFAULT 25,
    mailFrom       IN VARCHAR2,
    rcptTo         IN VARCHAR2,
    ccs            IN mail_ccs,
    messageSubject IN VARCHAR2,
    messageBody    IN VARCHAR2)
IS
  l_conn UTL_SMTP.connection;
  l_ccs              VARCHAR2(2000);
  l_encoded_username VARCHAR2(200);
  l_encoded_password VARCHAR2(200);
BEGIN
  --open connection
  /*
  l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);
  UTL_SMTP.helo(l_conn, smtpHost);
  */
  --In case of authentication needed
  l_encoded_username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username)));
  l_encoded_password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(password)));
  l_conn             := UTL_SMTP.open_connection(smtpHost, smtpPort);
  UTL_SMTP.ehlo(l_conn, smtpHost);--DO NOT USE HELO
  UTL_SMTP.command(l_conn, 'AUTH', 'LOGIN');
  UTL_SMTP.command(l_conn, l_encoded_username);
  UTL_SMTP.command(l_conn, l_encoded_password);
  --prepare headers
  UTL_SMTP.mail(l_conn, mailFrom);
  UTL_SMTP.rcpt(l_conn, rcptTo);
  /*if we have multiple recipients or CCs, we must call UTL_SMTP.rcpt once for each one
  however, we shall specify that there are CCs in the mail header in order for them to appear as such*/
  IF ccs IS NOT NULL THEN
    FOR i IN ccs.FIRST..ccs.LAST
    LOOP
      UTL_SMTP.rcpt(l_conn, ccs(i));--add recipient
      l_ccs:=l_ccs||ccs(i)||',';    --mark as CC
    END LOOP;
    --now remove the trailing comma at the end of l_ccs
    l_ccs:=SUBSTR(l_ccs,0,LENGTH(l_ccs)-1 );
  END IF;
  --start multi line message
  UTL_SMTP.open_data(l_conn);
  --prepare mail header
  /*DO NOT USE MON instead of MM in the date pattern if you run the script on machines with different locales as it will be misunderstood
  and the mail date will appear as 01/01/1970*/
  UTL_SMTP.write_data(l_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'To: ' || rcptTo || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'Cc: ' || l_ccs || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'From: ' || mailFrom || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'Subject: ' || messageSubject || UTL_TCP.crlf || UTL_TCP.crlf);
  --include the message body
  UTL_SMTP.write_data(l_conn, messageBody || UTL_TCP.crlf || UTL_TCP.crlf);
  --send the email
  UTL_SMTP.close_data(l_conn);
  UTL_SMTP.quit(l_conn);
END; 



To use the above SP, we can execute that by calling below code:

DECLARE
  USERNAME VARCHAR2(200);
  PASSWORD VARCHAR2(200);
  SMTPHOST VARCHAR2(200);
  SMTPPORT BINARY_INTEGER;
  MAILFROM VARCHAR2(200);
  RCPTTO VARCHAR2(200);
  CCS PAUL.MAIL_CCS;
  MESSAGESUBJECT VARCHAR2(200);
  MESSAGEBODY VARCHAR2(200);
BEGIN
  USERNAME := 'paul.pronabananda';
  PASSWORD := 'paulpassword';
  SMTPHOST := '172.20.1.1';
  SMTPPORT := 50;
  MAILFROM := 'paul.pronabananda';
  RCPTTO := 'paul.pronabananda@gmail.com';
  -- Modify the code to initialize the variable
  -- CCS := NULL;
  MESSAGESUBJECT := 'Test';
  MESSAGEBODY := 'Test Body';

  SENDMAIL(
    USERNAME => USERNAME,
    PASSWORD => PASSWORD,
    SMTPHOST => SMTPHOST,
    SMTPPORT => SMTPPORT,
    MAILFROM => MAILFROM,
    RCPTTO => RCPTTO,
    CCS => CCS,
    MESSAGESUBJECT => MESSAGESUBJECT,
    MESSAGEBODY => MESSAGEBODY
  );
--rollback; 

END;

If mail server address is not already added in the acl list. in that case we will have to add that info in the acl:

If ACL not exists, at first we need to create ACL by below code:

BEGIN 

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( 

acl => 'utlpkg.xml', 

description => 'created by paul', 

principal => 'PAUL', 

is_grant => TRUE, 

privilege => 'connect', 

start_date => null, 

end_date => null); 

END;

After creation, we are in need to add the host IP and port in that ACL using below:

BEGIN

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => 'utlpkg.xml',

    host        => '172.20.1.1', 

    lower_port  => 1,

    upper_port  => 100);   

  COMMIT;

END;

--