How to Identify SID Based on OS PID in Oracle

How to Identify SID Based on OS PID in Oracle

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

Query to check SID from PID (get from top command )

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

set lines 200 pages 500

col sid format 99999
col username format a30
col osuser format a15
select p.spid,s.sid, s.serial#,s.username, s.osuser
from gv$session s, gv$process p
where s.paddr= p.addr
and p.spid='&spid'

order by p.spid;

Purge AWR Snapshots in oracle

Purge AWR Snapshots

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

• Get the snapshot details  : 

SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot;

SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;

• purge snapshot by given range :

First Method:

exec dbms_workload_repository.drop_snapshot_range(881,991);



Second Method:

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id  => 881,
high_snap_id => 991);
END;
/  

Get the client IP address from a session in oracle

Get the client IP address from a session in oracle .  There are two way to get IP

•  SYS_CONTEXT('USERENV','IP_ADDRESS')
•  UTL_INADDR.GET_HOST_ADDRESS

Query1 : using :  SYS_CONTEXT('USERENV','IP_ADDRESS')
=============================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP,
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;


Query2: using  UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP
=========================================================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE,
UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP,
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;

Extract Index DDL from database export dump

PAR file for index ddl extract from dump using traditional import utility
===================================================================

userid= '/ as sysdba '
statistics=none
buffer=9999999
log=/oracle/export/IND/dump/imp_index_full_10122019.log
constraints=n
grants=n
rows=n
FROMUSER=HR
indexfile=/oracle/export/IND/dump/index3.sql
Tables=(EMPLOYEES)
file=(/oracle/export/IND//exp_full_IND.dmp)


To execute the command in background 
===========================================

nohup imp parfile=file_name.par &