Showing posts with label monitoring. Show all posts
Showing posts with label monitoring. Show all posts
Thursday, February 14, 2013
Who is using database link
Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
s2.username,
substr(
decode(bitand(ksuseidl,11),
1,'ACTIVE',
0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
2,'SNIPED',
3,'SNIPED',
'KILLED'
),1,1
) "S",
substr(w.event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
And W.Sid=S.Indx
and s2.sid = W.Sid;
Wednesday, December 21, 2011
Change Snapshot Setting
select * from dba_hist_wr_control;
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 66240, -- = 46 Days
interval => 15) -- = 15 Minutes
;
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 66240, -- = 46 Days
interval => 15) -- = 15 Minutes
;
END;
/
Wednesday, September 22, 2010
IDLE Session > Sniped Session > Kill > Release Resource
Making Idle Session SNIPED:
An idle session can be setup to become sniped after x minutes by setting the initialization parameter resource_limit = true in the init.ora and idle_time in the user profile. You can make user session becomes sniped after 1 hours of idle time by running below command:
select * from v$parameter where name like '%resource_limit%';
select * from dba_profiles where resource_name like 'IDLE_TIME';
alter system set resource_limit=true scope=spfile;
alter profile LD_BACKOFFICE limit IDLE_TIME 30;
after 30 minutes status=inactive will become sniped
--------------------Finding Sniped Idle Session--------------------------
SELECT
p.spid "Thread ID",
s.sid "Session ID",
s.serial# "Serial Num",
b.name "Background Process",
s.sql_id "SQL ID",
s.username "Session User",
s.osuser "OS User",
s.status "Status",
s.program "Program"
FROM
v$process p,
v$bgprocess b,
v$session s
WHERE
s.status = 'SNIPED'
AND
s.paddr = p.addr
AND b.paddr(+) = p.addr
AND b.name IS NULL
ORDER BY s.username ;
-----------------release resource from idle inactive / sniped / killed session-------
orakill SID SPID
-------------script---Sniped.sql--------------------------
store set c:\prevsqlsettings.sql replace
set pagesize 0
set feedback off
set trimspool on
set termout off
set verify off
spool c:\killSniped.bat
select 'orakill ' || i.instance_name || ' ' || spid from v$instance i,v$process p, v$session s,v$bgprocess b where p.addr=s.paddr AND b.paddr(+) = p.addr AND b.name IS NULL and s.status='SNIPED' and s.username != 'SYSTEM' and s.username != 'SYS' and s.username != 'DBSNMP' and s.username != 'REPADMIN' and s.username != 'WMSYS' and s.username != 'TSMSYS' and s.username != 'OUTLN' and s.username != 'ORACLE_OCM' and s.username != 'LDBO' and s.username != 'SNSEXPORT' and s.username != 'RENEXPORT' and s.username != 'CLLVL';
spool off
host c:\killSniped.bat
@c:\sqlsettings
host del c:\killSniped.bat
host del c:\prevsqlsettings.sql
exit
------------Job Schedule-----sniped.bat--------------
@echo
set oracle_sid=sns6
sqlplus ldbo/ldbo@sns1011srv @c:\sniped.sql
exit
--------------------------------------------------------Just for Knowledge-------------------------------------------
Then IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.
This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session.
SQLNET.EXPIRE_TIME =10
sqlnet.expire_time
sqlnet.expire_time parameter: The database is to detect dead TCP connections, not idle client applications
if you kill an idle session, you don't have a dead connection. A dead connection occurs if you switch off or disconnect your client such that it cannot send a proper FIN to close the TCP/IP connection.
Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client , and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server. Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client, and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server.
A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server , whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again. A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server, whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user (or the user process) tries to communicate with the server again.
But again,as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates. But again, as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.
------------maual kill session----------------
alter system kill session '526,67';
-------------------------Last activity / work time ----------
select username,to_char(logon_time, 'DD-Mon-YYYY HH24:MI:SS') Logon_time,last_call_et,
to_char(sysdate-(last_call_et/(60*60*24)),'hh24:mi:ss') last_work_time
from v$session
where username is not null;
------------find sessions which are running for more than 2 hours----------------
SELECT
S.STATUS "Status",
S.SERIAL# "Serial#",
S.TYPE "Type",
S.USERNAME "DB User",
S.OSUSER "Client User",
S.SERVER "Server",
S.MACHINE "Machine",
S.MODULE "Module",
S.CLIENT_INFO "Client Info",
S.TERMINAL "Terminal",
S.PROGRAM "Program",
P.PROGRAM "O.S. Program",
s.logon_time "Connect Time",
lockwait "Lock Wait",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, si.sid, s.audsid,
s.sql_address "Address", s.sql_hash_value "Sql Hash", s.Action,
sysdate - (s.LAST_CALL_ET / 86400) "Last Call"
FROM
V$SESSION S,
V$PROCESS P,
sys.V_$SESS_IO si
WHERE
S.paddr = P.addr(+)
and si.sid(+)=s.sid and S.USERNAME like 'APL%'
AND s.LAST_CALL_ET/60 >= 2.1
ORDER BY 5
DESC ;
----------------Kill all user sessions except me----------------
1 declare
2 sql_stmt VARCHAR2(200);
3 cursor c1 is select sid, serial# from v$session where
username is not null
4 begin
5 for sessions in c1 loop
6 sql_stmt := 'alter system kill session ' || '''';
7 sql_stmt := sql_stmt || to_char(sessions.sid)
||', ';
8 sql_stmt := sql_stmt || to_char(sessions.serial#)
|| '''';
9 dbms_output.put_line(sql_stmt);
10 begin -- Subblock in order to continue after
-- exception when it will try
-- to kill my session
11 execute immediate sql_stmt;
12 end;
13 end loop;
14* end;
----------------------------alter kill session --job schedule-------------------
creating a procedure to kill sesssion and schedule it nightly be the best solution?
Create or replace procedure KillSession(idleTime in Number)
as cursor c1 is
select sid,serial#,trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0) TMIN from
v$session
where status='INACTIVE' and username not in ('SYS');
VString varchar2(50);
begin
for c1rec in c1 loop
if c1rec.TMIN > idleTime then
Vstring := 'alter system kill session
'||chr(39)||c1rec.sid||','||c1rec.serial#||chr(39);
execute immediate(Vstring);
end if;
end loop;
end;
------------------------------
An idle session can be setup to become sniped after x minutes by setting the initialization parameter resource_limit = true in the init.ora and idle_time in the user profile. You can make user session becomes sniped after 1 hours of idle time by running below command:
select * from v$parameter where name like '%resource_limit%';
select * from dba_profiles where resource_name like 'IDLE_TIME';
alter system set resource_limit=true scope=spfile;
alter profile LD_BACKOFFICE limit IDLE_TIME 30;
after 30 minutes status=inactive will become sniped
--------------------Finding Sniped Idle Session--------------------------
SELECT
p.spid "Thread ID",
s.sid "Session ID",
s.serial# "Serial Num",
b.name "Background Process",
s.sql_id "SQL ID",
s.username "Session User",
s.osuser "OS User",
s.status "Status",
s.program "Program"
FROM
v$process p,
v$bgprocess b,
v$session s
WHERE
s.status = 'SNIPED'
AND
s.paddr = p.addr
AND b.paddr(+) = p.addr
AND b.name IS NULL
ORDER BY s.username ;
-----------------release resource from idle inactive / sniped / killed session-------
orakill SID SPID
-------------script---Sniped.sql--------------------------
store set c:\prevsqlsettings.sql replace
set pagesize 0
set feedback off
set trimspool on
set termout off
set verify off
spool c:\killSniped.bat
select 'orakill ' || i.instance_name || ' ' || spid from v$instance i,v$process p, v$session s,v$bgprocess b where p.addr=s.paddr AND b.paddr(+) = p.addr AND b.name IS NULL and s.status='SNIPED' and s.username != 'SYSTEM' and s.username != 'SYS' and s.username != 'DBSNMP' and s.username != 'REPADMIN' and s.username != 'WMSYS' and s.username != 'TSMSYS' and s.username != 'OUTLN' and s.username != 'ORACLE_OCM' and s.username != 'LDBO' and s.username != 'SNSEXPORT' and s.username != 'RENEXPORT' and s.username != 'CLLVL';
spool off
host c:\killSniped.bat
@c:\sqlsettings
host del c:\killSniped.bat
host del c:\prevsqlsettings.sql
exit
------------Job Schedule-----sniped.bat--------------
@echo
set oracle_sid=sns6
sqlplus ldbo/ldbo@sns1011srv @c:\sniped.sql
exit
--------------------------------------------------------Just for Knowledge-------------------------------------------
Then IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.
This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session.
SQLNET.EXPIRE_TIME =10
sqlnet.expire_time
sqlnet.expire_time parameter: The database is to detect dead TCP connections, not idle client applications
if you kill an idle session, you don't have a dead connection. A dead connection occurs if you switch off or disconnect your client such that it cannot send a proper FIN to close the TCP/IP connection.
Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client , and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server. Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client, and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server.
A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server , whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again. A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server, whereas the resource_limit will snipe the session when idle_time is exceeded. The 'sniped' session will get disconnected when the user (or the user process) tries to communicate with the server again.
But again,as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates. But again, as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.
------------maual kill session----------------
alter system kill session '526,67';
-------------------------Last activity / work time ----------
select username,to_char(logon_time, 'DD-Mon-YYYY HH24:MI:SS') Logon_time,last_call_et,
to_char(sysdate-(last_call_et/(60*60*24)),'hh24:mi:ss') last_work_time
from v$session
where username is not null;
------------find sessions which are running for more than 2 hours----------------
SELECT
S.STATUS "Status",
S.SERIAL# "Serial#",
S.TYPE "Type",
S.USERNAME "DB User",
S.OSUSER "Client User",
S.SERVER "Server",
S.MACHINE "Machine",
S.MODULE "Module",
S.CLIENT_INFO "Client Info",
S.TERMINAL "Terminal",
S.PROGRAM "Program",
P.PROGRAM "O.S. Program",
s.logon_time "Connect Time",
lockwait "Lock Wait",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, si.sid, s.audsid,
s.sql_address "Address", s.sql_hash_value "Sql Hash", s.Action,
sysdate - (s.LAST_CALL_ET / 86400) "Last Call"
FROM
V$SESSION S,
V$PROCESS P,
sys.V_$SESS_IO si
WHERE
S.paddr = P.addr(+)
and si.sid(+)=s.sid and S.USERNAME like 'APL%'
AND s.LAST_CALL_ET/60 >= 2.1
ORDER BY 5
DESC ;
----------------Kill all user sessions except me----------------
1 declare
2 sql_stmt VARCHAR2(200);
3 cursor c1 is select sid, serial# from v$session where
username is not null
4 begin
5 for sessions in c1 loop
6 sql_stmt := 'alter system kill session ' || '''';
7 sql_stmt := sql_stmt || to_char(sessions.sid)
||', ';
8 sql_stmt := sql_stmt || to_char(sessions.serial#)
|| '''';
9 dbms_output.put_line(sql_stmt);
10 begin -- Subblock in order to continue after
-- exception when it will try
-- to kill my session
11 execute immediate sql_stmt;
12 end;
13 end loop;
14* end;
----------------------------alter kill session --job schedule-------------------
creating a procedure to kill sesssion and schedule it nightly be the best solution?
Create or replace procedure KillSession(idleTime in Number)
as cursor c1 is
select sid,serial#,trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0) TMIN from
v$session
where status='INACTIVE' and username not in ('SYS');
VString varchar2(50);
begin
for c1rec in c1 loop
if c1rec.TMIN > idleTime then
Vstring := 'alter system kill session
'||chr(39)||c1rec.sid||','||c1rec.serial#||chr(39);
execute immediate(Vstring);
end if;
end loop;
end;
------------------------------
Monday, September 20, 2010
User Session Monitoring
-------------------Sniped idle session--------------
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_before, SID, v$session.SERIAL#, v$process.SPID OS_PROCESS, v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE FROM v$session, v$process
WHERE ((v$session.paddr = v$process.addr) AND (status = 'SNIPED'))
ORDER BY logon_time ASC;
-------------------------Last activity / work time ----------
select username,to_char(logon_time, 'DD-Mon-YYYY HH24:MI:SS') Logon_time,last_call_et,
to_char(sysdate-(last_call_et/(60*60*24)),'hh24:mi:ss') last_work_time
from v$session
where username is not null;
--------- Monitor Per Session UGA, PGA UGA Max, PGA Max Usage----------------
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC;
--------- Monitor Per Session UGA (User Global Area), PGA(Process Global Area) Usage----------------
SELECT
e.SID,
e.username,
e.status,
a.UGA_MEMORY,
b.PGA_MEMORY
FROM
(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' UGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session uga memory') a,
(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' PGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session pga memory') b,
v$session e
WHERE
e.sid=a.sid
AND
e.sid=b.sid
ORDER BY
e.status,
a.UGA_MEMORY desc;
--------- Monitor Per Session UGA, PGA and Cursor Usage----------------
set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1
select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name
order by vses.username, vsst.sid, vses.serial#, vstt.name;
--------------------Determine PGA and Process Memory in use by Process------------------------
set lines 110
col unm format a30 hea "USERNAME (SID,SERIAL#)"
col pus format 999,990.9 hea "PROC KB|USED"
col pal format 999,990.9 hea "PROC KB|MAX ALLOC"
col pgu format 99,999,990.9 hea "PGA KB|USED"
col pga format 99,999,990.9 hea "PGA KB|ALLOC"
col pgm format 99,999,990.9 hea "PGA KB|MAX MEM"
select s.username||' ('||s.sid||','||s.serial#||')' unm, round((sum(m.used)/1024),1) pus,
round((sum(m.max_allocated)/1024),1) pal, round((sum(p.pga_used_mem)/1024),1) pgu,
round((sum(p.pga_alloc_mem)/1024),1) pga, round((sum(p.pga_max_mem)/1024),1) pgm
from v$process_memory m, v$session s, v$process p
where m.serial# = p.serial# and p.pid = m.pid and p.addr=s.paddr and
s.username is not null group by s.username, s.sid, s.serial# order by unm;
-------------Session I/O By User---------------------------
select nvl(ses.USERNAME,'ORACLE PROC') username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME;
----------------CPU Usage By Session-------------------------
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
VALUE cpu_usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
order by VALUE desc;
----------------Resource Usage By User-------------------------
select ses.SID,
nvl(ses.USERNAME,'ORACLE PROC') username,
sn.NAME statistic,
sest.VALUE
from v$session ses,
v$statname sn,
v$sesstat sest
where ses.SID = sest.SID
and sn.STATISTIC# = sest.STATISTIC#
and sest.VALUE is not null
and sest.VALUE != 0
order by ses.USERNAME, ses.SID, sn.NAME;
--------------Session Stats By Session-------------
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
sn.NAME stastic,
VALUE usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and se.SID = ss.SID
and se.VALUE > 0
order by sn.NAME, se.SID, se.VALUE desc;
----------Cursor Usage By Session-------------
select user_process username,
"Recursive Calls",
"Opened Cursors",
"Current Cursors"
from (
select nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and (NAME like '%opened cursors current%'
or NAME like '%recursive calls%'
or NAME like '%opened cursors cumulative%')
and se.SID = ss.SID
and ss.USERNAME is not null
group by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order by USER_PROCESS,"Recursive Calls" ;
---------------------------User Hit Ratios---------------------
select USERNAME,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
from v$session, v$sess_io
where v$session.SID = v$sess_io.SID
and (CONSISTENT_GETS+BLOCK_GETS) > 0
and USERNAME is not null
order by ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS));
--------------------------------generate sql to kill a sessoin. See genDisconnect.sql
accept killme prompt 'User to Kill > '
set ver off
select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' as sql_to_run from V$PROCESS p, V$SESSION s
where s.paddr = p.addr and s.username = upper ('&killme');
User to Kill > UIBADUSER
SQL_TO_RUN
---------------------------------------------------------
alter system disconnect session '296,3841' immediate;
--list old sessions to "disconnect", then generate sql to disconnect them
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null and s.status = 'INACTIVE' and s.osuser = 'oracle'
and s.machine like 'banformprod5%' and s.logon_time < sysdate-1;
set echo off feedback off heading off
select '--run sql below to disconnect sessions above' from dual;
select 'alter system disconnect session '||chr(39)||sid||','||s.serial#||chr(39)||' immediate;' as sql_to_run from V$PROCESS p,V$SESSION s
where s.paddr = p.addr and s.username is not null and s.status = 'INACTIVE' and s.osuser = 'oracle'
and s.machine like 'banformprod5%' and s.logon_time < sysdate-1;
select '' from dual;
exit
OSUSER SPID USERNAME SID SERIAL# START_TIME STATUS MACHINE MODULE
------------ -------- ------------------------- ----- -------- -------------------- -------- -------------------- ----------
oracle 11911 AWILS1 1317 54857 Thu 25 Aug 07:18:55 INACTIVE banformprod5.admin.u SHACRSE
----------------------run sql below to disconnect sessions above
alter system disconnect session '1317,54857' immediate;
------------------------show current users. See showUsers.sql
set echo off feedback off heading off
set pagesize 0
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select 'Show users ordered by logon_time, username' from dual;
select 'OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE' from dual;
select '------------ -------- ------------------------ ----- -------- ------------------- --------- -------------------- ----------' from dual;
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null order by logon_time,1;
Show users ordered by logon_time, username
OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE
------------ -------- ------------------------ ----- -------- ------------------- --------- -------------------- ----------
submit 29273 JOBRUNNER 2 19 332 Sat 20 Aug 04:01:07 ACTIVE servername.dns.netw SFRPIPE
oracle 28413 SYS 26 452 Mon 22 Aug 15:31:55 INACTIVE servername.dns.netw sqlplus@se
johnny 14223 TLJOHN 37 710 Tue 23 Aug 09:03:36 INACTIVE TREE\USER1 TOAD 7.6.0
janedoey 22345 USER01 28 1462 Tue 23 Aug 12:44:00 INACTIVE TREE\USER1 designer.e
oracle 29986 OPS$ORACLE 18 219 Tue 23 Aug 13:01:01 ACTIVE servername.dns.netw SQL*Plus
-------------------generate SQL to disconnect blocking users.
SELECT 'alter system disconnect session '''||s.sid||','||s.serial#||'''immediate;' as "run_this_sql" FROM sys.x$kglpn p, sys.v_$session s,
(select kglhdadr, kglnaobj FROM sys.x$kglob WHERE kglhdadr in
(SELECT p1raw FROM sys.v_$session_wait WHERE event = 'library cache pin' AND state = 'WAITING')) k
WHERE p.kglpnuse = s.saddr AND kglpnhdl in
(SELECT p1raw FROM sys.v_$session_wait WHERE event = 'library cache pin' AND state = 'WAITING')
and k.kglhdadr = kglpnhdl and kglpnmod=3 order by kglpnmod;
run_this_sql
--------------------------------------------------------------------------------------
alter system disconnect session '853,44482'immediate;
------------------find the number of current commits occuring.
select command,count(command) from V$SESSION where command = '44' group by command order by 1;
USERNAME COUNT
-------------------- -----
JOBSUMITTER 37680
VISITOR 3488
WORKER 2839
TESTER 1793
DBSNMP 11
SYS 740
-----------------------show "stale" connections. showStale.sql---------------------------
set recsep off
set feedback off
set pages 1000
set wrap off
define l1='Stale Sessions (showStale.sql)'
define l2='==================================='
ttitle left l1 skip 1 left l2 skip 2
col sid_serial format a12 heading 'SID,SERIAL#'
col username format a10 heading 'User'
col osuser format a10 heading 'OS User'
col stats format a8 heading 'Status'
col program format a20 heading 'Program'
col module format a30 heading 'Module'
column last_call format 9999 heading 'Last|Call|(Days)'
select to_char(logon_time,'Dy dd Mon HH24:MI') logon_time,sid || ',' || serial# sid_serial,
username,osuser,trunc(last_call_et/60/60/24) last_call,status,program,module from V$SESSION
where type != 'BACKGROUND' and username is not null and trunc(last_call_et/60/60/24) > 1 order by 5;
exit
Last
Call
LOGON_TIME SID,SERIAL# User OS User (Days) STATUS Program Module
---------------- ------------ ---------- ---------- ------ -------- -------------------- ------------------------------
-----------------------show how users are logged on------------
select program,count(program) from V$SESSION group by program order by program;
PROGRAM COUNT(PROGRAM)
-------------------------------------------------- --------------
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_before, SID, v$session.SERIAL#, v$process.SPID OS_PROCESS, v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE FROM v$session, v$process
WHERE ((v$session.paddr = v$process.addr) AND (status = 'SNIPED'))
ORDER BY logon_time ASC;
-------------------------Last activity / work time ----------
select username,to_char(logon_time, 'DD-Mon-YYYY HH24:MI:SS') Logon_time,last_call_et,
to_char(sysdate-(last_call_et/(60*60*24)),'hh24:mi:ss') last_work_time
from v$session
where username is not null;
--------- Monitor Per Session UGA, PGA UGA Max, PGA Max Usage----------------
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC;
--------- Monitor Per Session UGA (User Global Area), PGA(Process Global Area) Usage----------------
SELECT
e.SID,
e.username,
e.status,
a.UGA_MEMORY,
b.PGA_MEMORY
FROM
(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' UGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session uga memory') a,
(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' PGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session pga memory') b,
v$session e
WHERE
e.sid=a.sid
AND
e.sid=b.sid
ORDER BY
e.status,
a.UGA_MEMORY desc;
--------- Monitor Per Session UGA, PGA and Cursor Usage----------------
set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1
select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name
order by vses.username, vsst.sid, vses.serial#, vstt.name;
--------------------Determine PGA and Process Memory in use by Process------------------------
set lines 110
col unm format a30 hea "USERNAME (SID,SERIAL#)"
col pus format 999,990.9 hea "PROC KB|USED"
col pal format 999,990.9 hea "PROC KB|MAX ALLOC"
col pgu format 99,999,990.9 hea "PGA KB|USED"
col pga format 99,999,990.9 hea "PGA KB|ALLOC"
col pgm format 99,999,990.9 hea "PGA KB|MAX MEM"
select s.username||' ('||s.sid||','||s.serial#||')' unm, round((sum(m.used)/1024),1) pus,
round((sum(m.max_allocated)/1024),1) pal, round((sum(p.pga_used_mem)/1024),1) pgu,
round((sum(p.pga_alloc_mem)/1024),1) pga, round((sum(p.pga_max_mem)/1024),1) pgm
from v$process_memory m, v$session s, v$process p
where m.serial# = p.serial# and p.pid = m.pid and p.addr=s.paddr and
s.username is not null group by s.username, s.sid, s.serial# order by unm;
-------------Session I/O By User---------------------------
select nvl(ses.USERNAME,'ORACLE PROC') username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME;
----------------CPU Usage By Session-------------------------
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
VALUE cpu_usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
order by VALUE desc;
----------------Resource Usage By User-------------------------
select ses.SID,
nvl(ses.USERNAME,'ORACLE PROC') username,
sn.NAME statistic,
sest.VALUE
from v$session ses,
v$statname sn,
v$sesstat sest
where ses.SID = sest.SID
and sn.STATISTIC# = sest.STATISTIC#
and sest.VALUE is not null
and sest.VALUE != 0
order by ses.USERNAME, ses.SID, sn.NAME;
--------------Session Stats By Session-------------
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
sn.NAME stastic,
VALUE usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and se.SID = ss.SID
and se.VALUE > 0
order by sn.NAME, se.SID, se.VALUE desc;
----------Cursor Usage By Session-------------
select user_process username,
"Recursive Calls",
"Opened Cursors",
"Current Cursors"
from (
select nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and (NAME like '%opened cursors current%'
or NAME like '%recursive calls%'
or NAME like '%opened cursors cumulative%')
and se.SID = ss.SID
and ss.USERNAME is not null
group by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order by USER_PROCESS,"Recursive Calls" ;
---------------------------User Hit Ratios---------------------
select USERNAME,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
from v$session, v$sess_io
where v$session.SID = v$sess_io.SID
and (CONSISTENT_GETS+BLOCK_GETS) > 0
and USERNAME is not null
order by ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS));
--------------------------------generate sql to kill a sessoin. See genDisconnect.sql
accept killme prompt 'User to Kill > '
set ver off
select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' as sql_to_run from V$PROCESS p, V$SESSION s
where s.paddr = p.addr and s.username = upper ('&killme');
User to Kill > UIBADUSER
SQL_TO_RUN
---------------------------------------------------------
alter system disconnect session '296,3841' immediate;
--list old sessions to "disconnect", then generate sql to disconnect them
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null and s.status = 'INACTIVE' and s.osuser = 'oracle'
and s.machine like 'banformprod5%' and s.logon_time < sysdate-1;
set echo off feedback off heading off
select '--run sql below to disconnect sessions above' from dual;
select 'alter system disconnect session '||chr(39)||sid||','||s.serial#||chr(39)||' immediate;' as sql_to_run from V$PROCESS p,V$SESSION s
where s.paddr = p.addr and s.username is not null and s.status = 'INACTIVE' and s.osuser = 'oracle'
and s.machine like 'banformprod5%' and s.logon_time < sysdate-1;
select '' from dual;
exit
OSUSER SPID USERNAME SID SERIAL# START_TIME STATUS MACHINE MODULE
------------ -------- ------------------------- ----- -------- -------------------- -------- -------------------- ----------
oracle 11911 AWILS1 1317 54857 Thu 25 Aug 07:18:55 INACTIVE banformprod5.admin.u SHACRSE
----------------------run sql below to disconnect sessions above
alter system disconnect session '1317,54857' immediate;
------------------------show current users. See showUsers.sql
set echo off feedback off heading off
set pagesize 0
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select 'Show users ordered by logon_time, username' from dual;
select 'OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE' from dual;
select '------------ -------- ------------------------ ----- -------- ------------------- --------- -------------------- ----------' from dual;
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null order by logon_time,1;
Show users ordered by logon_time, username
OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE
------------ -------- ------------------------ ----- -------- ------------------- --------- -------------------- ----------
submit 29273 JOBRUNNER 2 19 332 Sat 20 Aug 04:01:07 ACTIVE servername.dns.netw SFRPIPE
oracle 28413 SYS 26 452 Mon 22 Aug 15:31:55 INACTIVE servername.dns.netw sqlplus@se
johnny 14223 TLJOHN 37 710 Tue 23 Aug 09:03:36 INACTIVE TREE\USER1 TOAD 7.6.0
janedoey 22345 USER01 28 1462 Tue 23 Aug 12:44:00 INACTIVE TREE\USER1 designer.e
oracle 29986 OPS$ORACLE 18 219 Tue 23 Aug 13:01:01 ACTIVE servername.dns.netw SQL*Plus
-------------------generate SQL to disconnect blocking users.
SELECT 'alter system disconnect session '''||s.sid||','||s.serial#||'''immediate;' as "run_this_sql" FROM sys.x$kglpn p, sys.v_$session s,
(select kglhdadr, kglnaobj FROM sys.x$kglob WHERE kglhdadr in
(SELECT p1raw FROM sys.v_$session_wait WHERE event = 'library cache pin' AND state = 'WAITING')) k
WHERE p.kglpnuse = s.saddr AND kglpnhdl in
(SELECT p1raw FROM sys.v_$session_wait WHERE event = 'library cache pin' AND state = 'WAITING')
and k.kglhdadr = kglpnhdl and kglpnmod=3 order by kglpnmod;
run_this_sql
--------------------------------------------------------------------------------------
alter system disconnect session '853,44482'immediate;
------------------find the number of current commits occuring.
select command,count(command) from V$SESSION where command = '44' group by command order by 1;
USERNAME COUNT
-------------------- -----
JOBSUMITTER 37680
VISITOR 3488
WORKER 2839
TESTER 1793
DBSNMP 11
SYS 740
-----------------------show "stale" connections. showStale.sql---------------------------
set recsep off
set feedback off
set pages 1000
set wrap off
define l1='Stale Sessions (showStale.sql)'
define l2='==================================='
ttitle left l1 skip 1 left l2 skip 2
col sid_serial format a12 heading 'SID,SERIAL#'
col username format a10 heading 'User'
col osuser format a10 heading 'OS User'
col stats format a8 heading 'Status'
col program format a20 heading 'Program'
col module format a30 heading 'Module'
column last_call format 9999 heading 'Last|Call|(Days)'
select to_char(logon_time,'Dy dd Mon HH24:MI') logon_time,sid || ',' || serial# sid_serial,
username,osuser,trunc(last_call_et/60/60/24) last_call,status,program,module from V$SESSION
where type != 'BACKGROUND' and username is not null and trunc(last_call_et/60/60/24) > 1 order by 5;
exit
Last
Call
LOGON_TIME SID,SERIAL# User OS User (Days) STATUS Program Module
---------------- ------------ ---------- ---------- ------ -------- -------------------- ------------------------------
-----------------------show how users are logged on------------
select program,count(program) from V$SESSION group by program order by program;
PROGRAM COUNT(PROGRAM)
-------------------------------------------------- --------------
Wednesday, August 4, 2010
AutoTrace
Prerequisites
SQL> @ORACLE_HOME\rdbms\admin\utlxplan.sqlThis creates the PLAN_TABLE for the user executing the script.
----------
Setting AUTOTRACE On
There is also an easier method with SQL*Plus for generating an EXPLAIN PLAN and statistics about the performance of a query.
SET AUTOTRACE ON
select * from accounts;
----------------------
set autotrace off
set autotrace on
set autotrace traceonly
set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics
set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics
set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics
----------
set autotrace on: Shows the execution plan as well as statistics of the statement.
set autotrace on explain: Displays the execution plan only.
set autotrace on statistics: Displays the statistics only.
set autotrace traceonly: Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off: Disables all autotrace
If autotrace is enabled with statistics, then the following statistics are displayed:
* recursive calls
* db block gets
* consistent gets
* physical reads
* redo size
* bytes sent via SQL*Net to client
* bytes received via SQL*Net from client
* SQL*Net roundtrips to/from client
* sorts (memory)
* sorts (disk)
------------
Cost Based Optimizer (CBO) and Database Statistics
Cost Based Optimizer (CBO) and Database Statistics
The mechanisms and issues relating to maintenance of internal statistics are explained below:
* Analyze Statement
* DBMS_UTILITY
* DBMS_STATS
* Scheduling Stats
* Transfering Stats
* Issues
1) Analyze Statement
select 'ANALYZE TABLE '||Owner||'.'||table_name||' compute statistics;'
from sys.all_tables where table_name!='_default_auditing_options_'
/
select 'ANALYZE INDEX '||Owner||'.'||index_name||' compute statistics;'
from sys.all_indexes
/
2) DBMS_UTILITY
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('LDBO','COMPUTE');
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('LDBO', 'ESTIMATE')
3) DBMS_STATS
EXEC DBMS_STATS.gather_schema_stats('LDBO');
4) Scheduling Stats
SET SERVEROUTPUT ON
DECLARE
l_job NUMBER;
BEGIN
DBMS_JOB.submit(l_job,
'BEGIN DBMS_STATS.gather_schema_stats(''LDBO''); END;',
SYSDATE,
'SYSDATE + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/
-----EXEC DBMS_JOB.remove(X);
-----COMMIT;
5) Transfering Stats
It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:
EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
6) Issues
* Exclude dataload tables from your regular stats gathering, unless you know they will be full at the time that stats are gathered.
* I've found gathering stats for the SYS schema can make the system run slower, not faster.
* Gathering statistics can be very resource intensive for the server so avoid peak workload times or gather stale stats only.
* Even if scheduled, it may be necessary to gather fresh statistics after database maintenance or large data loads.
7)
select table_name, avg_row_len, chain_cnt, num_rows,last_analyzed from dba_tables where owner ='LDBO' order by last_analyzed desc;
The mechanisms and issues relating to maintenance of internal statistics are explained below:
* Analyze Statement
* DBMS_UTILITY
* DBMS_STATS
* Scheduling Stats
* Transfering Stats
* Issues
1) Analyze Statement
select 'ANALYZE TABLE '||Owner||'.'||table_name||' compute statistics;'
from sys.all_tables where table_name!='_default_auditing_options_'
/
select 'ANALYZE INDEX '||Owner||'.'||index_name||' compute statistics;'
from sys.all_indexes
/
2) DBMS_UTILITY
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('LDBO','COMPUTE');
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('LDBO', 'ESTIMATE')
3) DBMS_STATS
EXEC DBMS_STATS.gather_schema_stats('LDBO');
4) Scheduling Stats
SET SERVEROUTPUT ON
DECLARE
l_job NUMBER;
BEGIN
DBMS_JOB.submit(l_job,
'BEGIN DBMS_STATS.gather_schema_stats(''LDBO''); END;',
SYSDATE,
'SYSDATE + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/
-----EXEC DBMS_JOB.remove(X);
-----COMMIT;
5) Transfering Stats
It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:
EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
6) Issues
* Exclude dataload tables from your regular stats gathering, unless you know they will be full at the time that stats are gathered.
* I've found gathering stats for the SYS schema can make the system run slower, not faster.
* Gathering statistics can be very resource intensive for the server so avoid peak workload times or gather stale stats only.
* Even if scheduled, it may be necessary to gather fresh statistics after database maintenance or large data loads.
7)
select table_name, avg_row_len, chain_cnt, num_rows,last_analyzed from dba_tables where owner ='LDBO' order by last_analyzed desc;
Explain Plan
SQL> @ORACLE_HOME\rdbms\admin\utlxplan.sql
This creates the PLAN_TABLE for the user executing the script.
Run EXPLAIN PLAN for the query to be optimized:
1)explain plan for
select * from accounts;
2)select * from PLAN_TABLE; ---to check output
3)explain plan
SET STATEMENT_ID = 'ACC' FOR
select * from accounts;
4)
SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||
options||' '||object_name "Plan",cost "Cost by CBO",bytes/1024/1024 "MB",time "Time By CBO"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'ACC'
ORDER BY id;
5)
SELECT LPAD(' ', 2 * (level - 1)) ||
DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = '&1'
CONNECT BY PRIOR pt.id = pt.parent_id
AND pt.statement_id = '&1';
This creates the PLAN_TABLE for the user executing the script.
Run EXPLAIN PLAN for the query to be optimized:
1)explain plan for
select * from accounts;
2)select * from PLAN_TABLE; ---to check output
3)explain plan
SET STATEMENT_ID = 'ACC' FOR
select * from accounts;
4)
SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||
options||' '||object_name "Plan",cost "Cost by CBO",bytes/1024/1024 "MB",time "Time By CBO"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'ACC'
ORDER BY id;
5)
SELECT LPAD(' ', 2 * (level - 1)) ||
DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = '&1'
CONNECT BY PRIOR pt.id = pt.parent_id
AND pt.statement_id = '&1';
The following diagram demonstrates the procedures for running TRACE versus EXPLAIN PLAN:
| TRACE It takes four hours to TRACE a query that takes four hours to run.
| EXPLAIN PLAN It takes less than a minute to EXPLAIN PLAN a query that takes four hours to run.
|
Tuesday, August 3, 2010
SQL Trace / TKPROF
To start a SQL trace for the current session, execute:
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET sql_trace = true;
or DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
ALTER SESSION SET tracefile_identifier = mysqltrace;
DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions. Steps:
select sid, serial# from sys.v_$session where .....
SID SERIAL#
---------- ----------
8 13607
--------------------
Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is:
SQL> ALTER SYSTEM SET timed_statistics = true;
--------------------
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
SQL> execute dbms_system.set_sql_trace_in_session(8, 13607, true);
Ask user to run just the necessary to demonstrate his problem.
Disable tracing for your selected process:
execute dbms_system.set_sql_trace_in_session(8,13607, false);
Look for trace file in USER_DUMP_DEST
ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;
Identifying trace files
Trace output is written to the database's UDUMP directory.
The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc where:
* INSTANCE is the name of the Oracle instance,
* PID is the operating system process ID (V$PROCESS.OSPID); and
* TRACEID is a character string of your choosing.
---http://www.ordba.net/Tutorials/OracleUtilities~TKPROF.htm---
Trace output is quite unreadable. However, Oracle provides a utility, called TKProf, that can be used to format trace output.
SET ORACLE_SID=SNS6
tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]
C:\> tkprof C:\oracle\product\10.2.0\admin\sns1011\udump\sns6_ora_976.trc C:\oracle\product\10.2.0\admin\sns1011\udump\sns6_ora_976.prf explain = ldbo/ldbo sys=no sort = (PRSDSK,EXEDSK,FCHDSK,EXECPU,FCHCPU)
Some of the things to look for in the TKPROF output are listed in this table:
----------------
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET sql_trace = true;
or DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
ALTER SESSION SET tracefile_identifier = mysqltrace;
DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions. Steps:
select sid, serial# from sys.v_$session where .....
SID SERIAL#
---------- ----------
8 13607
--------------------
Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is:
SQL> ALTER SYSTEM SET timed_statistics = true;
--------------------
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
SQL> execute dbms_system.set_sql_trace_in_session(8, 13607, true);
Ask user to run just the necessary to demonstrate his problem.
Disable tracing for your selected process:
execute dbms_system.set_sql_trace_in_session(8,13607, false);
Look for trace file in USER_DUMP_DEST
ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;
Identifying trace files
Trace output is written to the database's UDUMP directory.
The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc where:
* INSTANCE is the name of the Oracle instance,
* PID is the operating system process ID (V$PROCESS.OSPID); and
* TRACEID is a character string of your choosing.
---http://www.ordba.net/Tutorials/OracleUtilities~TKPROF.htm---
Trace output is quite unreadable. However, Oracle provides a utility, called TKProf, that can be used to format trace output.
SET ORACLE_SID=SNS6
tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]
C:\> tkprof C:\oracle\product\10.2.0\admin\sns1011\udump\sns6_ora_976.trc C:\oracle\product\10.2.0\admin\sns1011\udump\sns6_ora_976.prf explain = ldbo/ldbo sys=no sort = (PRSDSK,EXEDSK,FCHDSK,EXECPU,FCHCPU)
Some of the things to look for in the TKPROF output are listed in this table:
| Problems | Solutions |
| High numbers for the parsing | The SHARED_POOL_SIZE may need to be increased. |
| The disk reads are very high | Indexes are not used or may not exist. |
| The "query" and/or "current" (memory reads) are very high | Indexes may be on columns with high cardinality (columns where an individual value generally makes up a large percentage of the table). Removing or suppressing the index may increase performance. |
| The parse elapse time is high | There may be a problem with the number of open cursors. |
| The number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows | This could be a sign of an index with a poor distribution distinct keys (unique values for a column). Or this could also be a sign of a poorly written statement. |
| If the number of misses in the library cache during parse is greater than 1 | This is an indication that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora. |
----------------
Monday, August 2, 2010
import job monitoring(How fast import running)
SELECT SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea WHERE sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;
-----------------
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea WHERE sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;
-----------------
Oracle Table Size Growth Monitoring
select
segment_name table_name,
sum(bytes)/(1024*1024) table_size_meg
from
user_extents
where
segment_type='TABLE'
and
segment_name = 'TBLDIGITALSIGNEDREPORTS'
group by segment_name;
-------------------TABLE SIZE GROWTH WITH TIME-------
select
to_char(begin_interval_time,'DD-MM-YYYY hh24:mm') TIME,
object_name TABLE_NAME,
space_used_total/1024/1024 SPACE_USED_MB
from
dba_hist_seg_stat s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot sn
where
o.owner = 'LDBO'
and
s.obj# = o.obj#
and
sn.snap_id = s.snap_id
and
TRIM(object_name) LIKE 'TBLDIGITALSIGNEDREPORTS'
order by
begin_interval_time DESC;
-------------------------------
select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and b.object_name ='TBLDIGITALSIGNEDREPORTS'
and c.owner = 'LDBO'
and space_used_delta > 0;
--------------------
segment_name table_name,
sum(bytes)/(1024*1024) table_size_meg
from
user_extents
where
segment_type='TABLE'
and
segment_name = 'TBLDIGITALSIGNEDREPORTS'
group by segment_name;
-------------------TABLE SIZE GROWTH WITH TIME-------
select
to_char(begin_interval_time,'DD-MM-YYYY hh24:mm') TIME,
object_name TABLE_NAME,
space_used_total/1024/1024 SPACE_USED_MB
from
dba_hist_seg_stat s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot sn
where
o.owner = 'LDBO'
and
s.obj# = o.obj#
and
sn.snap_id = s.snap_id
and
TRIM(object_name) LIKE 'TBLDIGITALSIGNEDREPORTS'
order by
begin_interval_time DESC;
-------------------------------
select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and b.object_name ='TBLDIGITALSIGNEDREPORTS'
and c.owner = 'LDBO'
and space_used_delta > 0;
--------------------
Friday, June 25, 2010
Oracle Database Growth Report
select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner = 'SYS'
and space_used_delta > 0;
----------------------
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY tsname desc,days desc;
-------------------Increase Snap Shot time
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/
----------------------
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner = 'SYS'
and space_used_delta > 0;
----------------------
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY tsname desc,days desc;
-------------------Increase Snap Shot time
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/
----------------------
Database Growth Monitoring Script
Step : 1 Calculate total Size of tablespace
select sum(bytes)/1024/1024 "TOTAL SIZE (MB)" from dba_Data_files
Step : 2 Calculate Free Space in Tablespace
select sum(bytes)/1024/1024 "FREE SPACE (MB)" from dba_free_space
Step : 3 Calculate total size , free space and used space in tablespace
select t2.total "TOTAL SIZE",t1.free "FREE SPACE",(t1.free/t2.total)*100 "FREE (%)" ,(1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2
Step : 4 Create table which is store all free/use space related information of tablespace
create table db_growth
as select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
insert into db_growth
select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
create view v_db_growth
as select *
from
(
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
insert into db_growth select *
from v_db_growth
Step : 8 Check everything goes fine.
select * from db_growth;
Check Result
Step : 9 Execute following SQL for more time stamp information
alter session set nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.
Step : 10 Create a DBMS jobs which execute after 24 hours
declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;', sysdate, 'SYSDATE+ 24', TRUE);
commit;
end;
PL/SQL procedure successfully completed.
Step: 11 View your dbms jobs and it's other information
select * from user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
exec dbms_job.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 13 Finally all data populated in db_growth table
select * from db_growth;
-------------------------
select sum(bytes)/1024/1024 "TOTAL SIZE (MB)" from dba_Data_files
Step : 2 Calculate Free Space in Tablespace
select sum(bytes)/1024/1024 "FREE SPACE (MB)" from dba_free_space
Step : 3 Calculate total size , free space and used space in tablespace
select t2.total "TOTAL SIZE",t1.free "FREE SPACE",(t1.free/t2.total)*100 "FREE (%)" ,(1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2
Step : 4 Create table which is store all free/use space related information of tablespace
create table db_growth
as select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
insert into db_growth
select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
create view v_db_growth
as select *
from
(
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
insert into db_growth select *
from v_db_growth
Step : 8 Check everything goes fine.
select * from db_growth;
Check Result
Step : 9 Execute following SQL for more time stamp information
alter session set nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.
Step : 10 Create a DBMS jobs which execute after 24 hours
declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;', sysdate, 'SYSDATE+ 24', TRUE);
commit;
end;
PL/SQL procedure successfully completed.
Step: 11 View your dbms jobs and it's other information
select * from user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
exec dbms_job.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 13 Finally all data populated in db_growth table
select * from db_growth;
-------------------------
Thursday, June 24, 2010
Oracle User Login Lock Wrong Attempt details
select username,osuser,machine,blocking_session from v$session where username='LDBO';
select username,os_username,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",terminal,
---UTL_INADDR.get_host_address(terminal) "IP",
decode(returncode,0,'Successful',1017,'WrongAttempt',28000,'Locked',28009,'SYS Login',1005,'Fail_NULL',28001,'EXPIRED',28031,'Roles_Exceeded',returncode) Login_Status
from dba_audit_session where trim(Timestamp) > trunc(sysdate-1)
and username='AHE'
-----and os_username like '%UNICON\%'
order by timestamp desc;
select username,password,account_status,to_char(lock_date, 'Dy DD-Mon-YYYY HH24:MI:SS') lock_date,expiry_date
from dba_users where account_status like '%LOCKED%' order by 3 desc,lock_date desc;
select distinct username "USER ID",osuser,machine,UTL_INADDR.get_host_address(terminal)"System IP", decode(username,'USSB3409','ANILKUMAR','USSB0065','GAURAVSINGH','USSB0737','KAVITA', 'USSB0580','MAHINDERSINGH', 'USSB0624','RAJENDERSINGH','USSB0625','RAKESHKUMAR','UIPL6716','RAKESHLAL','USSB0500','RUPINDERKAUR','UIPL6713','SHASHINATH','USSB0502','SURENDERKUMAR','UIPL6715','VIRENDERSINGH','UIPL6714','SANJAYKUMARSISODIA','UIPL7249','BHAVYASINGH','UFIPL00152','GOVINDGUPTA','UIPL7244','INDIRARAWAT','USSB2693','JYOTI','UIPL7258','MANTUKUMARSINGH','USSB0080','PRIYADAS','USSB2720','RAHUL','UIPL7210','ROBINTYAGI','UFIPL00154','ROHITGUPTA','USSB1740','SHAKSHI','UIPL7259','VINODKUMAR','USSB0991','ARJUNSINGH','UFPL00058','DHEERAJTANEJA','UFIPL00151','PAWANKUMARJINDAL','UIPL7759','PRABHAKARSINGH','USSB0948','PRIYANKARANA','USSB6549','RAJESHSHARMA.','USSB6788','RAMKRISHANKUMAR','USSB6790','ROSHANKUMAR','USSB0550','SACHINSUGANDHA','UIPL7261','SHOBHITAGARWAL','USSB1470','SIDDHARTHBHATIA','USSB6996','KANIKAKHURANA','UFIPL00153','DEVESH','UIPL10283','SWATISALUJA','UIPL10287','NITINGOYAL','UIPL9767','GAURAV','UFIPL00156','AMITKUMARSINGHAL','UIPL10305','PRANJEETKAUSHIK','UFPL00173','PUNEETSHARMA','UIPL10672','SUMITRAJORA','UFPL00188','DEEPAKGUPTA','UFPL00195','VINEETSABHLOK','USSB0019','PRASHANTUPADHYAY','USSB0022','NEERAJGROVER','USSB0109','SAURABHAATRE','USSB0626','SHAHABUDDIN','USSB2701','NITESH','UCPL0491','POONAMSINGH','UCPL0116','DURGESHKESHRI','USSB0211','SAURABHCHAUDHARY','USSB0431','VIRENDERSINGHCHANDEL','USSB5498','AKANSHARAWAL','USSB7030','SATYANARAYANA','UIPL6793','JAGDEEPSINGH','USSB3700','JAYAACHAR','UIPL6778','NEERAJSHARMA','USSB6047','NEHAGOEL','UIPL6798','ROHANSHARMA','UIPL6828','SACHINTYAGI','USSB4173','SEEMAGAMBHIR','USSB4766','SHAKSHIARORA','UCPL0117','SHAMPAMUKHERJEE','UIPL6736','SONIABENWAL','USSB6621','TANUSHREESOMANI','USSB1236','ASHIMAARORA','UIPL6771','SHELLY','USSB5708','KULDEEPKAUR','UIPL6801','SHWETA','UIPL6800','ROHITLUTHRA','USSB7428','AMRESHKUMAR','UIPL10160','SONIASABHARWAL','USSB7444','ANILSHARMA','UIPL10515','SAPNAGUPTA','UIPL10402','KHUSHALRAZDAN','UIPL6742','GITTUKATARIA','UIPL6746','MUNNASINGH','USSB0013','AMITTYAGI','UIPL6722','DEEPANSHUMALHOTRA','USSB5695','HARENDRASHARMA','UIPL6727','MANISHVERMA','USSB1112','PRATIMASINGH','USSB5374','PULKIT','USSB1253','RAHULTYAGI','USSB5274','VARUNKANT','UFPL00178','SUBHASHCHANDRAGEHLOT','UFPL00028','SUBHASHSHARMA','USSB6901','KSHITIJR.GUPTA','UCPL0154','RACHITJAIN','USSB6097','SHEKHARSAXENA','UIPL6712','YESHPALTHAKUR','USSB6002','SANDHYASHARMA','UFIPL00130','ANJALIMUKHIJA','UCPL0111','AMITUPADHYAY','USSB6045','RAJAN','UFIPL00136','SANDEEPSIR','USSB0064','AMITJAIN','UIPL6831','ANKITASRIVASTAVA','USSB6805','SHASHIBHUSHAN','USSB6950','SUNIIGABA','USSB7222','NILESHTRIPATHI','USSB0849','OMPRAKASHBANDERWAL','USSB0458','ROHTASAGARWAL','RMS','RMS','USSB1366','NEETARASTOGI','USSB0256','ANANDJOSHI','USSB1032','SATISHSHARMA','USSB1113','SUNILKUMARSINGHAL','UIPL6721','VISHALGUPTA','UIPL6724','YATENDRASINGHBISHT','USSB6241','KULDEEPSHARMA','USSB5495','RAGHIBHUSAIN','USSB1031','TARUNCHHOKRA','UIPL6717','NEERAJKAUSHIK','USSB6508','ACHINANAND','USSB7530','KHYATIADLAKHA','USSB7565','BHUWANSHARMA','USSB7550','NEHASINGH','LDBO','DBA') Name
from v$session where program ='ld.exe' order by 5;
select username,password,account_status,lock_date, to_char(expiry_date, 'Dy DD-Mon-YYYY HH24:MI:SS') expir_date
from dba_users where account_status like '%EXPIRED%' order by expiry_date desc;
select username,os_username,terminal "System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name, decode(ses_actions,'---------S------','SELECT','----------S-----','UPDATE',ses_actions) action,action_name
from dba_audit_trail
where trim(Timestamp)=trunc(sysdate)
order by 4 desc;
select username,os_username,terminal,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name,action_name
,decode(returncode,1917,'Grant Revoke',1918,'ALter',1920,'Creation',returncode) Status
from dba_audit_exists
order by timestamp desc;
select firmnumber,oowncode as username,cclientlevelpassword as password from clemaildetail where oowncode='DP122';
SELECT username, terminal, to_char (timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS'),
decode(returncode,0,'Successful',1017,'WrongAttempt',28000,'Locked',28009,'SYS Login',1005,'Fail_NULL',28001,'EXPIRED',28031,'Roles_Exceeded',returncode) Login_Status
FROM dba_audit_session
WHERE returncode <> 0 AND not exists (SELECT 'x' FROM dba_users WHERE dba_users.username=dba_audit_session.username)
and trim(Timestamp)=trunc(sysdate)
order by timestamp desc;
select username,os_username,terminal "System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name,action_name from dba_audit_object where trim(Timestamp)=trunc(sysdate) order by timestamp desc;
select username,os_username,
UTL_INADDR.get_host_address(terminal)"System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",
obj_name,action_name from dba_audit_statement
--where
----trim(Timestamp)=trunc(sysdate)
--obj_name='UIPL6713'
order by timestamp desc;
select * from dba_stmt_audit_opts;
select * from dba_priv_audit_opts;
select * from dba_audit_object where obj_name='LDBO' order by timestamp desc;
select distinct * from TBLAUDITUSERLOGONDETAILS
where cusername not in ('SYS','CLLVL')
and cusername='LDBO'
and trim(dlogonday)=trunc(SYSDATE)
order by dlogonday desc,clogontime desc;
Select Substr(Upper(User_audit_trail.Os_Username),1,30) as cOS_Username, Rpad(' ',10) as Oowncode, Substr(User_audit_trail.Username,1,30) as cUsername,User_audit_trail.Userhost as cUserhost, Upper(Substr(User_audit_trail.Terminal,1,30)) as cTerminal,User_audit_trail.Timestamp as dTimeStamp, nvl(User_audit_trail.Obj_name,' ') as cObjectName,User_audit_trail.Action_name as cActionname, nvl(User_audit_trail.Sys_Privilege,' ') as cSysPrivilege,nvl(User_audit_trail.Grantee,' ') as cGrantee, nvl(User_audit_trail.Priv_Used,' ') as cPrivUsed From User_audit_trail User_audit_trail
Where User_audit_trail.Os_Username not in ('SYSTEM','SYSMAN','DBSNMP')
and User_audit_trail.Username='LDBO'
and trim(User_audit_trail.Timestamp)=trunc(sysdate)
order by timestamp;
Select Dba_Users.Username as cUsername,Dba_Users.Profile as cProfile, Dba_Users.Account_status as cAccountstatus,Dba_Users.Lock_date as dLockDate,Dba_Users.Expiry_Date as dExpiryDate, Dba_users.Created as dCreationDate
From Dba_Users Dba_Users
Where Dba_users.Username not in ('SYS', 'SYSTEM', 'DBSNMP', 'TSMSYS', 'OUTLN', 'ORACLE_OCM','MGMT_VIEW','SYSMAN', 'WMSYS', 'DIP')
and Dba_Users.Created>='13-MAR-09'
--and trim(dlockdate)!=' '
---and trim(dexpirydate)!=' '
and Dba_Users.Created<=trunc(sysdate) order by 5 desc, 4 desc;
-----------------------------
select username,os_username,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",terminal,
---UTL_INADDR.get_host_address(terminal) "IP",
decode(returncode,0,'Successful',1017,'WrongAttempt',28000,'Locked',28009,'SYS Login',1005,'Fail_NULL',28001,'EXPIRED',28031,'Roles_Exceeded',returncode) Login_Status
from dba_audit_session where trim(Timestamp) > trunc(sysdate-1)
and username='AHE'
-----and os_username like '%UNICON\%'
order by timestamp desc;
select username,password,account_status,to_char(lock_date, 'Dy DD-Mon-YYYY HH24:MI:SS') lock_date,expiry_date
from dba_users where account_status like '%LOCKED%' order by 3 desc,lock_date desc;
select distinct username "USER ID",osuser,machine,UTL_INADDR.get_host_address(terminal)"System IP", decode(username,'USSB3409','ANILKUMAR','USSB0065','GAURAVSINGH','USSB0737','KAVITA', 'USSB0580','MAHINDERSINGH', 'USSB0624','RAJENDERSINGH','USSB0625','RAKESHKUMAR','UIPL6716','RAKESHLAL','USSB0500','RUPINDERKAUR','UIPL6713','SHASHINATH','USSB0502','SURENDERKUMAR','UIPL6715','VIRENDERSINGH','UIPL6714','SANJAYKUMARSISODIA','UIPL7249','BHAVYASINGH','UFIPL00152','GOVINDGUPTA','UIPL7244','INDIRARAWAT','USSB2693','JYOTI','UIPL7258','MANTUKUMARSINGH','USSB0080','PRIYADAS','USSB2720','RAHUL','UIPL7210','ROBINTYAGI','UFIPL00154','ROHITGUPTA','USSB1740','SHAKSHI','UIPL7259','VINODKUMAR','USSB0991','ARJUNSINGH','UFPL00058','DHEERAJTANEJA','UFIPL00151','PAWANKUMARJINDAL','UIPL7759','PRABHAKARSINGH','USSB0948','PRIYANKARANA','USSB6549','RAJESHSHARMA.','USSB6788','RAMKRISHANKUMAR','USSB6790','ROSHANKUMAR','USSB0550','SACHINSUGANDHA','UIPL7261','SHOBHITAGARWAL','USSB1470','SIDDHARTHBHATIA','USSB6996','KANIKAKHURANA','UFIPL00153','DEVESH','UIPL10283','SWATISALUJA','UIPL10287','NITINGOYAL','UIPL9767','GAURAV','UFIPL00156','AMITKUMARSINGHAL','UIPL10305','PRANJEETKAUSHIK','UFPL00173','PUNEETSHARMA','UIPL10672','SUMITRAJORA','UFPL00188','DEEPAKGUPTA','UFPL00195','VINEETSABHLOK','USSB0019','PRASHANTUPADHYAY','USSB0022','NEERAJGROVER','USSB0109','SAURABHAATRE','USSB0626','SHAHABUDDIN','USSB2701','NITESH','UCPL0491','POONAMSINGH','UCPL0116','DURGESHKESHRI','USSB0211','SAURABHCHAUDHARY','USSB0431','VIRENDERSINGHCHANDEL','USSB5498','AKANSHARAWAL','USSB7030','SATYANARAYANA','UIPL6793','JAGDEEPSINGH','USSB3700','JAYAACHAR','UIPL6778','NEERAJSHARMA','USSB6047','NEHAGOEL','UIPL6798','ROHANSHARMA','UIPL6828','SACHINTYAGI','USSB4173','SEEMAGAMBHIR','USSB4766','SHAKSHIARORA','UCPL0117','SHAMPAMUKHERJEE','UIPL6736','SONIABENWAL','USSB6621','TANUSHREESOMANI','USSB1236','ASHIMAARORA','UIPL6771','SHELLY','USSB5708','KULDEEPKAUR','UIPL6801','SHWETA','UIPL6800','ROHITLUTHRA','USSB7428','AMRESHKUMAR','UIPL10160','SONIASABHARWAL','USSB7444','ANILSHARMA','UIPL10515','SAPNAGUPTA','UIPL10402','KHUSHALRAZDAN','UIPL6742','GITTUKATARIA','UIPL6746','MUNNASINGH','USSB0013','AMITTYAGI','UIPL6722','DEEPANSHUMALHOTRA','USSB5695','HARENDRASHARMA','UIPL6727','MANISHVERMA','USSB1112','PRATIMASINGH','USSB5374','PULKIT','USSB1253','RAHULTYAGI','USSB5274','VARUNKANT','UFPL00178','SUBHASHCHANDRAGEHLOT','UFPL00028','SUBHASHSHARMA','USSB6901','KSHITIJR.GUPTA','UCPL0154','RACHITJAIN','USSB6097','SHEKHARSAXENA','UIPL6712','YESHPALTHAKUR','USSB6002','SANDHYASHARMA','UFIPL00130','ANJALIMUKHIJA','UCPL0111','AMITUPADHYAY','USSB6045','RAJAN','UFIPL00136','SANDEEPSIR','USSB0064','AMITJAIN','UIPL6831','ANKITASRIVASTAVA','USSB6805','SHASHIBHUSHAN','USSB6950','SUNIIGABA','USSB7222','NILESHTRIPATHI','USSB0849','OMPRAKASHBANDERWAL','USSB0458','ROHTASAGARWAL','RMS','RMS','USSB1366','NEETARASTOGI','USSB0256','ANANDJOSHI','USSB1032','SATISHSHARMA','USSB1113','SUNILKUMARSINGHAL','UIPL6721','VISHALGUPTA','UIPL6724','YATENDRASINGHBISHT','USSB6241','KULDEEPSHARMA','USSB5495','RAGHIBHUSAIN','USSB1031','TARUNCHHOKRA','UIPL6717','NEERAJKAUSHIK','USSB6508','ACHINANAND','USSB7530','KHYATIADLAKHA','USSB7565','BHUWANSHARMA','USSB7550','NEHASINGH','LDBO','DBA') Name
from v$session where program ='ld.exe' order by 5;
select username,password,account_status,lock_date, to_char(expiry_date, 'Dy DD-Mon-YYYY HH24:MI:SS') expir_date
from dba_users where account_status like '%EXPIRED%' order by expiry_date desc;
select username,os_username,terminal "System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name, decode(ses_actions,'---------S------','SELECT','----------S-----','UPDATE',ses_actions) action,action_name
from dba_audit_trail
where trim(Timestamp)=trunc(sysdate)
order by 4 desc;
select username,os_username,terminal,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name,action_name
,decode(returncode,1917,'Grant Revoke',1918,'ALter',1920,'Creation',returncode) Status
from dba_audit_exists
order by timestamp desc;
select firmnumber,oowncode as username,cclientlevelpassword as password from clemaildetail where oowncode='DP122';
SELECT username, terminal, to_char (timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS'),
decode(returncode,0,'Successful',1017,'WrongAttempt',28000,'Locked',28009,'SYS Login',1005,'Fail_NULL',28001,'EXPIRED',28031,'Roles_Exceeded',returncode) Login_Status
FROM dba_audit_session
WHERE returncode <> 0 AND not exists (SELECT 'x' FROM dba_users WHERE dba_users.username=dba_audit_session.username)
and trim(Timestamp)=trunc(sysdate)
order by timestamp desc;
select username,os_username,terminal "System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name,action_name from dba_audit_object where trim(Timestamp)=trunc(sysdate) order by timestamp desc;
select username,os_username,
UTL_INADDR.get_host_address(terminal)"System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",
obj_name,action_name from dba_audit_statement
--where
----trim(Timestamp)=trunc(sysdate)
--obj_name='UIPL6713'
order by timestamp desc;
select * from dba_stmt_audit_opts;
select * from dba_priv_audit_opts;
select * from dba_audit_object where obj_name='LDBO' order by timestamp desc;
select distinct * from TBLAUDITUSERLOGONDETAILS
where cusername not in ('SYS','CLLVL')
and cusername='LDBO'
and trim(dlogonday)=trunc(SYSDATE)
order by dlogonday desc,clogontime desc;
Select Substr(Upper(User_audit_trail.Os_Username),1,30) as cOS_Username, Rpad(' ',10) as Oowncode, Substr(User_audit_trail.Username,1,30) as cUsername,User_audit_trail.Userhost as cUserhost, Upper(Substr(User_audit_trail.Terminal,1,30)) as cTerminal,User_audit_trail.Timestamp as dTimeStamp, nvl(User_audit_trail.Obj_name,' ') as cObjectName,User_audit_trail.Action_name as cActionname, nvl(User_audit_trail.Sys_Privilege,' ') as cSysPrivilege,nvl(User_audit_trail.Grantee,' ') as cGrantee, nvl(User_audit_trail.Priv_Used,' ') as cPrivUsed From User_audit_trail User_audit_trail
Where User_audit_trail.Os_Username not in ('SYSTEM','SYSMAN','DBSNMP')
and User_audit_trail.Username='LDBO'
and trim(User_audit_trail.Timestamp)=trunc(sysdate)
order by timestamp;
Select Dba_Users.Username as cUsername,Dba_Users.Profile as cProfile, Dba_Users.Account_status as cAccountstatus,Dba_Users.Lock_date as dLockDate,Dba_Users.Expiry_Date as dExpiryDate, Dba_users.Created as dCreationDate
From Dba_Users Dba_Users
Where Dba_users.Username not in ('SYS', 'SYSTEM', 'DBSNMP', 'TSMSYS', 'OUTLN', 'ORACLE_OCM','MGMT_VIEW','SYSMAN', 'WMSYS', 'DIP')
and Dba_Users.Created>='13-MAR-09'
--and trim(dlockdate)!=' '
---and trim(dexpirydate)!=' '
and Dba_Users.Created<=trunc(sysdate) order by 5 desc, 4 desc;
-----------------------------
Subscribe to:
Posts (Atom)