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

)