Sql queries to check ACTIVE / INACTIVE Sessions

 --Total Count of sessions

select count(s.status) TOTAL_SESSIONS

from gv$session s;

--Total Count of Inactive sessions

select count(s.status) INACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR

select count(s.status) "INACTIVE SESSIONS > 1HOUR "

from gv$session s, v$process p

where

p.addr=s.paddr and

s.last_call_et > 3600 and

s.status='INACTIVE';

--COUNT OF ACTIVE SESSIONS

select count(s.status) ACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='ACTIVE';

--TOTAL SESSIONS COUNT ORDERED BY PROGRAM

col program for a30

select s.program,count(s.program) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.program;

--TOTAL COUNT OF SESSIONS ORDERED BY MODULE

col module  for a30

prompt TOTAL SESSIONS

select s.module,count(s.sid) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.module;

--TOTAL COUNT OF SESSIONS ORDERED BY ACTION

col action for a30

prompt TOTAL SESSIONS

select s.action,count(s.sid) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.action;

--INACTIVE SESSIONS

prompt INACTIVE SESSIONS

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

--INACTIVE

prompt INACTIVE SESSIONS

select count(s.status) INACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE';

--INACTIVE PROGRAMS

col module for a40             

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, v$process p

where  p.addr=s.paddr and

s.status='INACTIVE';

--INACTIVE PROGRAMS with disk reads

prompt INACTIVE SESSIONS

select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

GROUP BY S.PROGRAM;

--INACTIVE SESSIONS COUNT WITH PROGRAM

col program for a30

prompt TOTAL INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

group by s.program

order by 2 desc;

--TOTAL INACTIVE SESSIONS MORE THAN 1HOUR

col program for a30

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Inactive_Sessions_from_1Hour

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

and s.last_call_et > (3600)

group by s.program

order by 2 desc;

--TOTAL INACTIVE SESSIONS GROUP BY  MODULE

col program for a60

COL MODULE FOR A30

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.module,count(s.module) Total_Inactive_Sessions

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

group by s.module;

--INACTIVE SESSION DETAILS MORE THAN 1 HOUR

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et_Hrs for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.last_call_et > (3600)

order by last_call_et;

--INACTIVE PROGRAM  --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

And s.program='&PROGRAM_NAME'

order by last_call_et;

--INACTIVE MODULES  --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr

And s.module like '%order_cleanup_hazmat_v3.sql'

order by last_call_et;

--INACTIVE JDBC SESSIONS

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,

s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

--COUNT OF INACTIVE SESSIONS MORE THAN ONE HOUR

SELECT COUNT(P.SPID)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

FORMS

--TOTAL FORM SESSIONS

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%');

--FORMS SESSIONS DETAILS

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600

order by spid;                      

col machine for a15

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

S.ACTION,s.process Client_Process,s.machine

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600;         

order by 4;                           

--INACTIVE FORMS SESSIONS DETAILS

col program for a15

col last_call_et for 999.99

select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

order by last_call_et desc;

--UNIQUE SPID

select unique(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

--COUNT FORMS

select COUNT(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

--ZERO HASH VALUE

select COUNT(p.spid)

from gv$session s,gv$process p

where

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

AND S.SQL_HASH_VALUE=0;

--INACTIVE FORM BY NAME

select count(s.sid) from v$session S

where s.action like ('%&ACTION%')

AND S.STATUS='INACTIVE';

GROUP BY ACTION

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%')

group by s.action;

FROM A SPECIFIC USERNAME

SET LINSIZE 152

col spid for a10

col process_spid for a10

col user_name for a20

col form_name for a20

select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,

d.user_form_name "FORM_NAME"

from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,

apps.fnd_form_tl d

where

a.login_id=b.login_id

and c.user_name like 'JROMO'

and a.user_id=c.user_id

and trunc(b.start_time) >trunc(sysdate -11)

and trunc(b.end_time) is null

and b.form_id=d.form_id

and d.language='US';

INACTIVE FORM

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like ('FRM%')

and s.last_call_et > (3600*3)

order by last_call_et;

--INACTIVE FORM SESSIONS 

col cli_proc for a9

COL AUDSID FOR A6

COL PID FOR A6

COL SID FOR A5

COL FORM_NAME FOR A25

COL USER_NAME FOR A15

col last_call_et for 9999.99

SELECT

-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */

( SELECT SUBSTR ( fu.user_name, 1, 20 )

FROM apps.fnd_user fu

WHERE fu.user_id = fl.user_id

) user_name,vs.status,

TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,

TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,

vs.last_call_et/3600 last_call_et,

SUBSTR ( fl.process_spid, 1, 6 ) spid,

SUBSTR ( vs.process, 1, 8 ) cli_proc,

SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,

SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,

SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,

SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,

SUBSTR ( vs.module || ' - ' ||

( SELECT SUBSTR ( ft.user_form_name, 1, 40 )

FROM apps.fnd_form_tl ft

WHERE ft.application_id = rf.form_appl_id

AND ft.form_id        = rf.form_id

AND ft.language       = USERENV('LANG')

), 1, 40 ) form_name

FROM apps.fnd_logins           fl,

gv$process            vp,

apps.fnd_login_resp_forms rf,

gv$session            vs

WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */

AND fl.login_type   = 'FORM'

AND fl.process_spid = vp.spid

AND fl.pid          = vp.pid

AND fl.login_id     = rf.login_id

AND rf.end_time    IS NULL

AND rf.audsid       = vs.audsid

and vs.status='INACTIVE'

ORDER BY

vs.process,

fl.process_spid;

--ACTIVE

prompt ACTIVE SESSIONS

select count(s.status) ACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='ACTIVE';

--MODULE

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr 

and s.MODULE like ('&MODULE_NAME_1HR%')

and s.last_call_et > ('&TIME_HRS' * 3600)

order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

p.addr=s.paddr 

and s.MODULE like ('%TOAD%')

Order by last_call_et;

--TOAD SESSIONS

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$process p

where

p.addr=s.paddr 

and s.MODULE like ('%TOAD%')

Order by last_call_et;

--CLIENT MACHINE SESSIONS COUNT

select count(s.process) TOTAL from v$session S

where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0

AND S.LAST_CALL_ET > 3600;

--Unique Actions

col module for a40             

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

s.machine like ('%&CLIENT_MACHINE%') AND

p.addr=s.paddr and

s.status='INACTIVE';

GROUP BY  program

col program for a60

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr  AND

s.machine like ('%&CLIENT_MACHINE%') AND

s.status='INACTIVE'

group by s.program;

Load data using SQL loader via shell or equivalent python

---------------------------------------------------------------------

Shell script:

==========run_sqlldr.sh==============

#!/bin/bash
# Oracle Environment
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=oradb
# Variables
USERID=ashwani/ashwani@pdb1
CONTROL_FILE=/home/oracle/emp.ctl
LOG_FILE=/home/oracle/emp.log
BAD_FILE=/home/oracle/emp.bad
DATA_FILE=/home/oracle/emp.csv
echo "Starting SQL Loader..."
sqlldr userid=$USERID \
control=$CONTROL_FILE \
data=$DATA_FILE \
log=$LOG_FILE \
bad=$BAD_FILE
if [ $? -eq 0 ]
then
   echo "Data loaded successfully."
else
   echo "SQL Loader failed."
fi

------------------------------------------------------------------------

Python script:

=============run_sqlldr.py============================================

#!/usr/bin/env python3

import argparse

import os

import subprocess

import sys

from datetime import datetime

# ---------------------------------------------------------------------------

# Oracle environment

# ---------------------------------------------------------------------------

ORACLE_HOME = "/u01/app/oracle/product/19.0.0/db_1"

ORACLE_SID  = "oradb"

# ---------------------------------------------------------------------------

# Default SQL*Loader parameters

# ---------------------------------------------------------------------------

DEFAULT_USERID   = "ashwani/ashwani@pdb1"

DEFAULT_CONTROL  = "/home/oracle/emp.ctl"

DEFAULT_LOG      = "/home/oracle/emp.log"

DEFAULT_BAD      = "/home/oracle/emp.bad"

DEFAULT_DATA     = "/home/oracle/emp.csv"

def parse_args():

    p = argparse.ArgumentParser(description="Python wrapper for SQL*Loader (sqlldr).")

    p.add_argument("--oracle-home", default=ORACLE_HOME)

    p.add_argument("--oracle-sid",  default=ORACLE_SID)

    p.add_argument("--userid",      default=DEFAULT_USERID)

    p.add_argument("--control",     default=DEFAULT_CONTROL)

    p.add_argument("--data",        default=DEFAULT_DATA)

    p.add_argument("--log",         default=DEFAULT_LOG)

    p.add_argument("--bad",         default=DEFAULT_BAD)

    return p.parse_args()

def build_env(oracle_home, oracle_sid):

    env = os.environ.copy()

    env["ORACLE_HOME"] = oracle_home

    env["ORACLE_SID"]  = oracle_sid

    env["PATH"]        = os.path.join(oracle_home, "bin") + os.pathsep + env.get("PATH", "")

    lib = os.path.join(oracle_home, "lib")

    env["LD_LIBRARY_PATH"] = lib + os.pathsep + env.get("LD_LIBRARY_PATH", "")

    return env

def main():

    args = parse_args()

    env  = build_env(args.oracle_home, args.oracle_sid)

    sqlldr = os.path.join(args.oracle_home, "bin", "sqlldr")

    cmd = [

        sqlldr,

        "userid="  + args.userid,

        "control=" + args.control,

        "data="    + args.data,

        "log="     + args.log,

        "bad="     + args.bad,

    ]

    print("Starting SQL Loader...")

    print("Command:", " ".join(cmd))

    # universal_newlines=True is the Python 3.6-compatible equivalent of text=True

    result = subprocess.run(cmd, env=env,

                            stdout=subprocess.PIPE,

                            stderr=subprocess.STDOUT,

                            universal_newlines=True)

    print(result.stdout or "")

    # Append run metadata to log

    try:

        with open(args.log, "a") as lf:

            lf.write("\n" + "="*60 + "\n")

            lf.write("Python runner: {}\n".format(datetime.now()))

            lf.write("Return code : {}\n".format(result.returncode))

    except OSError as e:

        print("WARNING: could not write log - {}".format(e))

    if result.returncode == 0:

        print("Data loaded successfully.")

    else:

        print("SQL Loader failed. (exit code: {})".format(result.returncode))

        sys.exit(result.returncode)

if __name__ == "__main__":

    main()

=============

create table:

=============

CREATE TABLE emp (

    emp_id NUMBER GENERATED ALWAYS AS IDENTITY,

    emp_name VARCHAR2(100),

    salary NUMBER,

    created_time TIMESTAMP DEFAULT SYSTIMESTAMP

);

===================

Data file: cat emp.csv

===================

PETER,50000

RICHARD,60000

ASHWANI,75000

================

Control File: emp.ctl

================

LOAD DATA

INFILE 'emp.csv'

INSERT

INTO TABLE emp

FIELDS TERMINATED BY ','

TRAILING NULLCOLS

(

  emp_name,

  salary

)

Analysing alert- Metrics Process Limit% is at 99 / Process limit usage % 99 in oracle database

What this means:

Process Limit % = 99% → almost all DB processes are used
Controlled by parameter: PROCESSES
When limit hits → users get errors like:
ORA-00020: maximum number of processes exceeded

Step 1: Check current usage

SELECT resource_name, current_utilization, max_utilization, limit_value
FROM v$resource_limit
WHERE resource_name IN ('processes','sessions');

Step 2: Identify what is consuming processes

SELECT username, program, COUNT(*)
FROM v$session
GROUP BY username, program
ORDER BY 3 DESC;

Check inactive sessions (big culprit)

SELECT status, COUNT(*)
FROM v$session
GROUP BY status;
Check Long idle sessions:
SELECT sid, serial#, username, status, last_call_et
FROM v$session
WHERE status='INACTIVE'
ORDER BY last_call_et DESC;

Drop the mail to Application Owner. Once got confirmation Kill the session.

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Note: Don’t kill active business sessions blindly.

Step 4: Increase PROCESSES (permanent fix) 

SHOW PARAMETER processes;

ALTER SYSTEM SET processes=500 SCOPE=SPFILE;

Then restart DB

Shell Script to Generate RMAN Backup Monitoring Report for All Databases on a Single Server

 [oracle@srv1 ~]$ cat rman_backup_report_enhanced.sh

#!/bin/bash

# ===== CONFIGURATION =====

REPORT_DIR="/tmp"

DATE_TAG=$(date +%Y%m%d_%H%M%S)

TEXT_REPORT="$REPORT_DIR/rman_report_$DATE_TAG.log"

HTML_REPORT="$REPORT_DIR/rman_report_$DATE_TAG.html"

# Email (optional)

EMAIL_TO="your_email@example.com"

SEND_EMAIL=false   # change to true if mailx configured

# ===== INIT =====

echo "RMAN Backup Report - $(date)" > $TEXT_REPORT

# HTML Header

cat <<EOF > $HTML_REPORT

<html>

<head>

<style>

body { font-family: Arial; }

table { border-collapse: collapse; width: 100%; }

th, td { border: 1px solid black; padding: 6px; text-align: center; }

th { background-color: #333; color: white; }

.success { background-color: #c6efce; }

.failed { background-color: #ffc7ce; }

</style>

</head>

<body>

<h2>RMAN Backup Report</h2>

<p>Generated on: $(date)</p>

<table>

<tr>

<th>DB Name</th>

<th>Input Type</th>

<th>Status</th>

<th>Start Time</th>

<th>End Time</th>

<th>Duration (Min)</th>

<th>Size (MB)</th>

</tr>

EOF

TOTAL_DB=0

FAILED_COUNT=0

# ===== LOOP DATABASES =====

grep -v '^#' /etc/oratab | grep -v '^$' | while IFS=: read ORACLE_SID ORACLE_HOME REST

do

    export ORACLE_SID

    export ORACLE_HOME

    export PATH=$ORACLE_HOME/bin:$PATH

    TOTAL_DB=$((TOTAL_DB+1))

    echo "Checking DB: $ORACLE_SID" >> $TEXT_REPORT

    RESULT=$(sqlplus -s / as sysdba <<EOF

SET HEADING OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 300

SELECT

    '$ORACLE_SID' || '|' ||

    INPUT_TYPE || '|' ||

    STATUS || '|' ||

    TO_CHAR(START_TIME,'DD-MON-YYYY HH24:MI') || '|' ||

    TO_CHAR(END_TIME,'DD-MON-YYYY HH24:MI') || '|' ||

    ROUND((END_TIME - START_TIME)*24*60) || '|' ||

    ROUND(OUTPUT_BYTES/1024/1024)

FROM

    V\$RMAN_BACKUP_JOB_DETAILS

WHERE

    START_TIME > SYSDATE - 1

ORDER BY START_TIME DESC;

EXIT;

EOF

)

    echo "$RESULT" | while IFS="|" read DB INPUT STATUS START END DURATION SIZE

    do

        [[ -z "$DB" ]] && continue

        # Failure detection

        CLASS="success"

        if [[ "$STATUS" != "COMPLETED" ]]; then

            CLASS="failed"

            FAILED_COUNT=$((FAILED_COUNT+1))

        fi

        # Text report

        printf "%-10s %-12s %-10s %-18s %-18s %-8s %-8s\n" \

        "$DB" "$INPUT" "$STATUS" "$START" "$END" "$DURATION" "$SIZE" >> $TEXT_REPORT

        # HTML report

        cat <<ROW >> $HTML_REPORT

<tr class="$CLASS">

<td>$DB</td>

<td>$INPUT</td>

<td>$STATUS</td>

<td>$START</td>

<td>$END</td>

<td>$DURATION</td>

<td>$SIZE</td>

</tr>

ROW

    done

done

# ===== SUMMARY =====

cat <<EOF >> $HTML_REPORT

</table>

<h3>Summary</h3>

<p>Total Databases Checked: $TOTAL_DB</p>

<p>Failed Jobs: $FAILED_COUNT</p>

</body>

</html>

EOF

echo "====================================" >> $TEXT_REPORT

echo "Total DB Checked: $TOTAL_DB" >> $TEXT_REPORT

echo "Failed Jobs: $FAILED_COUNT" >> $TEXT_REPORT

# ===== EMAIL =====

if [ "$SEND_EMAIL" = true ]; then

    mailx -s "RMAN Backup Report" -a "$HTML_REPORT" "$EMAIL_TO" < $TEXT_REPORT

fi

# ===== OUTPUT =====

echo "Text Report  : $TEXT_REPORT"

echo "HTML Report  : $HTML_REPORT"

Script to monitor the RMAN backup

chmod +x rman_backup_report.sh

./rman_backup_report.sh 

[oracle@srv1 ~]$ cat rman_backup_report.sh

#!/bin/bash

# Output file

REPORT_FILE="/tmp/rman_backup_report_$(date +%Y%m%d_%H%M%S).log"

echo "RMAN Backup Consolidated Report" > $REPORT_FILE

echo "Generated on: $(date)" >> $REPORT_FILE

echo "=========================================" >> $REPORT_FILE

# Loop through databases from oratab

grep -v '^#' /etc/oratab | grep -v '^$' | while IFS=: read ORACLE_SID ORACLE_HOME REST

do

    export ORACLE_SID

    export ORACLE_HOME

    export PATH=$ORACLE_HOME/bin:$PATH

    echo "" >> $REPORT_FILE

    echo "-----------------------------------------" >> $REPORT_FILE

    echo "Database: $ORACLE_SID" >> $REPORT_FILE

    echo "-----------------------------------------" >> $REPORT_FILE

    sqlplus -s / as sysdba <<EOF >> $REPORT_FILE

SET LINESIZE 200

SET PAGESIZE 100

SET TRIMOUT ON

SET TRIMSPOOL ON

COLUMN STATUS FORMAT A10

COLUMN INPUT_TYPE FORMAT A15

COLUMN START_TIME FORMAT A20

COLUMN END_TIME FORMAT A20

SELECT

    INPUT_TYPE,

    STATUS,

    TO_CHAR(START_TIME,'DD-MON-YYYY HH24:MI') START_TIME,

    TO_CHAR(END_TIME,'DD-MON-YYYY HH24:MI') END_TIME

FROM

    V\$RMAN_BACKUP_JOB_DETAILS

WHERE

    START_TIME > SYSDATE - 2

ORDER BY

    START_TIME DESC;

EXIT;

EOF

done

echo "" >> $REPORT_FILE

echo "=========================================" >> $REPORT_FILE

echo "End of Report" >> $REPORT_FILE

# Print location

echo "Report generated at: $REPORT_FILE"

SWAP usage

grep VmSwap /proc/[0-9]*/status | sort -nk2

ps p procesid

for FILE in `ls /proc/[0-9]*/status`; do awk '/VmSwap|Name/ {printf $2 " " $3} END {print ""}' "$FILE" ; done

for FILE in `ls /proc/[0-9]*/status`; do awk '/VmSwap|Name/ {printf $2 " " $3} END {print ""}' "$FILE" ; done | sort -nk2 |grep kB >/tmp/swap.log

HOW TO FIND THE SESSION CONSUMING HIGH MEMORY AT DATABASE LEVEL.

 ###########Top 10 highest memory sessions#############

SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       p.spid AS os_pid,
       ROUND(st.value/1024/1024,2) AS pga_memory_mb
FROM v$session s
JOIN v$sesstat st ON s.sid = st.sid
JOIN v$statname sn ON st.statistic# = sn.statistic#
JOIN v$process p ON s.paddr = p.addr
WHERE sn.name = 'session pga memory'
---and s.program not like '%ORACLE%'
ORDER BY st.value DESC;

For Multi Tennant DB

col PDB_NAME  form a15
col USERNAME form a15
col MACHINE form a18
col Module form a15
col OS_PID form a10
SELECT 
       c.name AS pdb_name,
       s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       s.module,
       s.sql_id,
       p.spid AS os_pid,
       ROUND(st.value/1024/1024,2) AS pga_memory_mb,
       s.logon_time
FROM gv$session s
JOIN gv$sesstat st 
     ON s.sid = st.sid 
     AND s.inst_id = st.inst_id
JOIN gv$statname sn 
     ON st.statistic# = sn.statistic# 
     AND st.inst_id = sn.inst_id
JOIN gv$process p 
     ON s.paddr = p.addr 
     AND s.inst_id = p.inst_id
LEFT JOIN v$containers c 
     ON s.con_id = c.con_id
WHERE sn.name = 'session pga memory'
AND s.type = 'USER'
AND s.program NOT LIKE '%ORACLE%'
ORDER BY st.value DESC;

#####This shows sessions active about 1 hour ago with PGA usage###########

SELECT 
    ash.sample_time,
    ash.session_id,
    ash.session_serial#,
    ash.sql_id,
    ash.program,
    ROUND(ash.pga_allocated/1024/1024,2) AS pga_mb
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN SYSDATE - 1/24 AND SYSDATE - 55/1440
ORDER BY ash.pga_allocated DESC;

###Top SQL by PGA Memory Usage

SELECT 
    c.name AS pdb_name,
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    s.program,
    ROUND(st.value/1024/1024,2) AS pga_mb
FROM gv$session s
JOIN gv$sesstat st 
     ON s.sid = st.sid 
     AND s.inst_id = st.inst_id
JOIN gv$statname sn 
     ON st.statistic# = sn.statistic#
     AND st.inst_id = sn.inst_id
LEFT JOIN v$containers c 
     ON s.con_id = c.con_id
WHERE sn.name = 'session pga memory'
AND s.type='USER'
ORDER BY st.value DESC;

##Query: SQL_ID Consuming Highest Memory in a Time Window

SELECT 
    c.name AS pdb_name,
    ash.sql_id,
    COUNT(*) samples,
    ROUND(MAX(ash.pga_allocated)/1024/1024,2) AS max_pga_mb,
    ROUND(AVG(ash.pga_allocated)/1024/1024,2) AS avg_pga_mb
FROM dba_hist_active_sess_history ash
LEFT JOIN v$containers c
       ON ash.con_id = c.con_id
WHERE ash.sample_time BETWEEN 
      TO_DATE('08-MAR-2026 01:00','DD-MON-YYYY HH24:MI')
  AND TO_DATE('08-MAR-2026 02:30','DD-MON-YYYY HH24:MI')
AND ash.sql_id IS NOT NULL
GROUP BY c.name, ash.sql_id
ORDER BY max_pga_mb DESC;

--OR only for PDBS

SELECT 
    c.name AS pdb_name,
    ash.sql_id,
    COUNT(*) samples,
    ROUND(MAX(ash.pga_allocated)/1024/1024,2) AS max_pga_mb,
    ROUND(AVG(ash.pga_allocated)/1024/1024,2) AS avg_pga_mb
FROM dba_hist_active_sess_history ash
LEFT JOIN v$containers c
       ON ash.con_id = c.con_id
WHERE ash.sample_time BETWEEN 
      TO_DATE('08-MAR-2026 01:00','DD-MON-YYYY HH24:MI')
  AND TO_DATE('08-MAR-2026 02:30','DD-MON-YYYY HH24:MI')
AND ash.sql_id IS NOT NULL AND c.name <>'CDB$ROOT'
GROUP BY c.name, ash.sql_id
ORDER BY max_pga_mb DESC;

Performance Tuning Oracle 19c (Finding CPU Consuming Query) - Multitenant DB

### Find the SNAPID

select CON_ID,SNAP_ID,STARTUP_TIME,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot order by SNAP_ID;

####Below Query will find the sqlid which consumes the most CPU during mention time.######

SELECT
   p.name AS pdb_name,
   ash.session_id,
   ash.session_serial#,
   ash.user_id,
   ash.program,
   ash.sql_id,
   COUNT(*) AS cpu_samples
FROM
   dba_hist_active_sess_history ash,
   v$pdbs p
WHERE
   ash.sample_time BETWEEN
      to_date('11-JAN-2026 23:00','DD-MON-YYYY HH24:MI')
      AND
      to_date('12-JAN-2026 00:30','DD-MON-YYYY HH24:MI')
AND ash.session_state = 'ON CPU'
AND ash.con_id = p.con_id
GROUP BY
   p.name,
   ash.session_id,
   ash.session_serial#,
   ash.user_id,
   ash.program,
   ash.sql_id
ORDER BY cpu_samples DESC
FETCH FIRST 10 ROWS ONLY;

######Once we have details of SQLID we can get the details of sql text fron history view as below:#######

SELECT sql_id, sql_text FROM dba_hist_sqltext WHERE sql_id='2yhkvj9bru81z';

###For Current session: 

select con_id,SERVICE_NAME,sid from v$session where sql_id='247cszx5pfbqa';

or

SELECT

   s.sql_id,
   s.executions_total,
   ROUND(s.cpu_time_total/1000000,2) cpu_seconds,
   t.sql_text
FROM
   dba_hist_sqlstat s,
   dba_hist_sqltext t
WHERE
   s.sql_id = t.sql_id
AND s.sql_id = '2yhkvj9bru81z';

#######Search SQL text by keyword: Suppose you remember only part of query#######

SELECT sql_id, sql_text
FROM dba_hist_sqltext
WHERE LOWER(sql_text) LIKE '%material_records%';

###To find which SQL was running at a certain time:###

--This gives the most active SQLs in last 1 hour with full text.

SELECT
    h.sql_id,
    DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text,
    COUNT(*) AS occurrences
FROM
    dba_hist_active_sess_history h,
    dba_hist_sqltext t
WHERE
    h.sql_id = t.sql_id
AND h.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
    h.sql_id,
    DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY occurrences DESC
FETCH FIRST 5 ROWS ONLY;

####Find SQL Text from Specific Snapshot--If you know snapshot range from AWR. Here we have snap_id 58 and 59.##

SELECT
t.sql_id,

   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqltext t
WHERE
   t.sql_id IN (
      SELECT DISTINCT sql_id
      FROM dba_hist_sqlstat
      WHERE snap_id BETWEEN 58 AND 59
   );

###########If you want only SQL from a specific PDB:########

SELECT
   t.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqltext t
WHERE
   t.sql_id IN (
      SELECT DISTINCT sql_id
      FROM dba_hist_sqlstat
      WHERE snap_id BETWEEN 58 AND 59
      AND con_id = 3        -- your PDB con_id
   );

   

####If You Know the name of PDB:####

--Query for PDB = TRAINING

SELECT
   t.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqltext t
WHERE
   t.sql_id IN (
      SELECT DISTINCT s.sql_id
      FROM dba_hist_sqlstat s,
           v$pdbs p
      WHERE s.con_id = p.con_id
      AND p.name = 'TRAINING'
      AND s.snap_id BETWEEN 58 AND 59
   );

   

###If You Want Top CPU SQL in TRAINING PDB###

SELECT
   s.sql_id,
   ROUND(SUM(s.cpu_time_delta)/1000000,2) AS cpu_seconds,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqlstat s,
   dba_hist_sqltext t,
   v$pdbs p
WHERE
   s.sql_id = t.sql_id
AND s.con_id = p.con_id
AND p.name = 'TRAINING'
AND s.snap_id BETWEEN 58 AND 59
GROUP BY
   s.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY cpu_seconds DESC
FETCH FIRST 5 ROWS ONLY;

###If you want ALL SQL executed in last 1 hour in TRAINING###

SELECT
   ash.sql_id,
   COUNT(*) AS samples,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_active_sess_history ash,
   dba_hist_sqltext t,
   v$pdbs p
WHERE
   ash.sql_id = t.sql_id
AND ash.con_id = p.con_id
AND p.name = 'TRAINING'
AND ash.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
   ash.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

###If you want to know who ran those SQLs:###

SELECT
   ash.sql_id,
   u.username,
   ash.program,
   COUNT(*) AS cpu_samples,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_active_sess_history ash,
   dba_hist_sqltext t,
   v$pdbs p,
   dba_users u
WHERE
   ash.sql_id = t.sql_id
AND ash.con_id = p.con_id
AND ash.user_id = u.user_id
AND p.name = 'TRAINING'
AND ash.session_state = 'ON CPU'
AND ash.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
   ash.sql_id,
   u.username,
   ash.program,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY cpu_samples DESC
FETCH FIRST 10 ROWS ONLY;

Important Notes:

DBA_HIST_SQLTEXT keeps SQL as long as AWR retention is set

Daily Scripts

 1) To find I/O order by Tablespces:

select name, phyrds, phyrds * 100 / (select sum(phyrds) from v$filestat)
phywrts,  phywrts * 100 /(select sum(phywrts) from v$filestat)
from  v$datafile df, v$filestat fs
where df.file# = fs.file# order by phyrds desc;

2) Memory Hungry Oracle Process:


select   sid,name,value from   v$statname n,v$sesstat s where   n.STATISTIC# = s.STATISTIC# and   name like 'session%memory%'order by 3 desc;

select sum(bytes)/1024/1024 Mb from (select bytes from v$sgastat union select value bytes from 
v$sesstat s,v$statname n where n.STATISTIC# = s.STATISTIC# and  n.name = 'session pga memory');

3) To see OS Version using PL/SQL

select DBMS_UTILITY.PORT_STRING from dual;

Script to find Top CPU consuming PDBs (last few minutes)

 SET LINES 200

COL pdb_name FORMAT A20

SELECT

    p.name            AS pdb_name,

    ROUND(SUM(s.value)/100,2) AS cpu_seconds,

    COUNT(*)          AS sessions

FROM

    v$sesstat s,

    v$statname n,

    v$session se,

    v$pdbs p

WHERE

    s.statistic# = n.statistic#

AND s.sid = se.sid

AND n.name = 'CPU used by this session'

AND se.con_id = p.con_id

GROUP BY p.name

ORDER BY cpu_seconds DESC;

==============or================

SET LINES 200
COL pdb_name   FORMAT A15
COL username   FORMAT A15
COL program    FORMAT A30
COL service    FORMAT A20
COL cpu_mins   FORMAT 99990.99
SELECT
    ROWNUM AS rank,
    x.*
FROM
(
    SELECT
        p.name              AS pdb_name,
        s.sid,
        s.serial#,
        s.username,
        s.service_name      AS service,
        s.program,
        s.sql_id,
        ROUND(v.value / (100*60), 2) AS cpu_mins
    FROM
        v$statname  n,
        v$sesstat  v,
        v$session  s,
        v$pdbs     p
    WHERE
        n.name = 'CPU used by this session'
    AND v.statistic# = n.statistic#
    AND v.sid = s.sid
    AND s.con_id = p.con_id
    AND v.value > 0
    ORDER BY v.value DESC
) x
WHERE ROWNUM <= 10;

Automation to execute revoke statement

#!/bin/bash

CSV_FILE="revoke_access.csv"
ORACLE_HOME="/u01/app/oracle/product/19c/dbhome_1"
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME PATH
# Skip header
tail -n +2 "$CSV_FILE" | while IFS=',' read -r CDB PDB GRANTEE OWNER OBJECT PRIV
do
  echo "Processing: $CDB | $PDB | $GRANTEE | $OWNER.$OBJECT | $PRIV"
  export ORACLE_SID=$CDB
  sqlplus -s / as sysdba <<EOF
SET FEEDBACK OFF
SET HEADING OFF
SET ECHO OFF
SET SERVEROUTPUT ON
ALTER SESSION SET CONTAINER=$PDB;
DECLARE
  v_status VARCHAR2(10);
  v_error  VARCHAR2(4000);
BEGIN
  BEGIN
    EXECUTE IMMEDIATE
      'REVOKE $PRIV ON $OWNER.$OBJECT FROM $GRANTEE';
    v_status := 'SUCCESS';
    v_error  := NULL;
  EXCEPTION
    WHEN OTHERS THEN
      v_status := 'FAILED';
      v_error  := SUBSTR(SQLERRM,1,4000);
  END;
  INSERT INTO SEC_AUDIT.REVOKE_CONTROL_LOG
  (
    CDB_NAME,
    PDB_NAME,
    GRANTEE,
    OBJECT_OWNER,
    OBJECT_NAME,
    PRIVILEGE,
    STATUS,
    ERROR_MESSAGE
  )
  VALUES
  (
    '$CDB',
    '$PDB',
    '$GRANTEE',
    '$OWNER',
    '$OBJECT',
    '$PRIV',
    v_status,
    v_error
  );
  COMMIT;
x
END;
/
EXIT;
EOF
done