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 &

Enable Trace in oracle for SID

Get SID :
================

column SID for a20
SELECT sys_context('USERENV', 'SID') SID FROM DUAL;


Enable Trace for SID  in oracle 
==================================


SQL> select username,sid,serial# from v$session where sid=22;

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
SYS                                    22       2895

SQL>




alter session set tracefile_identifier='anurag_sql_trace';

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>22, serial#=>2895, sql_trace=>TRUE)

---To disable

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

--- Get the tracefile name:

SELECT p.tracefile FROM   v$session s  JOIN v$process p ON s.paddr = p.addr WHERE  s.sid = 22;

TRACEFILE
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trc