Sessions and Process related Views of oracle

Sessions and Process related Views of oracle:

Sessions and Processes related important Queries of DBA are as follows:

We can know the active sessions in my Database as follows:
There are times that you may need to issue a “SHUTDOWN IMMEDIATE” command to an Oracle Database. It is critical that you should consider checking who are the users that are currently running sessions on that Database. You don’t want to shutdown on your Boss or Important person’s session. Here’s a simple SQL to find all Active sessions in your Oracle Database:
1. SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
2. FROM V$Session
3. WHERE
4. Status=‘ACTIVE’ AND
5. UserName IS NOT NULL;
How to kill all sessions connected to my Database:

Before killing sessions, if possible do
ALTER SYSTEM ENABLE RESTRICTED SESSION;
to stop new sessions from connecting.
If you want to stop new users from connecting, but allow current sessions to continue until they are inactive, you can put the database in QUIESCE mode:
ALTER SYSTEM QUIESCE RESTRICTED;

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';


KILL ALL SESSION BY GETTING ALTER SYSTEM SCRIPTS:

Step 1:Generate all the sessions to be killed as follows:
--------
SQL> select 'Alter system kill session '''||sid||','||serial#||''';'
from v$session; 2

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
Alter system kill session '1839,3172';
Alter system kill session '1842,295';
Alter system kill session '1844,553';
Alter system kill session '1845,2707';
Alter system kill session '1846,219';
Alter system kill session '1847,1555';
(Or)
Note:Use immediate option with alter system to rollback transactions
Eg: Alter system kill session '1847,1555' immediate;




Killing inactive session as follows:


SQL> SELECT 'ALTER SYSTEM KILL SESSION'''||SID||','||SERIAL#||''';'
2 FROM V$SESSION WHERE STATUS='INACTIVE';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION'1835,1618';
ALTER SYSTEM KILL SESSION'1842,295';
ALTER SYSTEM KILL SESSION'1845,2707';
ALTER SYSTEM KILL SESSION'1850,114';
ALTER SYSTEM KILL SESSION'1855,11910';
KILL ALL SESSIONS OF PARTICULAR USER:
-----------------------------------------------------------------------
begin
for sessions in ( select sid
, serial#
from v$session
where username = 'user_to_kill_sessions')
loop
execute immediate 'alter system kill session '''||sessions.sid||','|sessions.serial#||'''';
end loop;
end;

Some toad work import views:

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

select *from v$session;
select *from v$process;
select pid,username,program from v$process where username='20C1248';

Check while migration work in Sony <em>which user is using which program and status:------------------------------------------------------------------------------

SELECT s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,s.module,s.status,s.action FROM v$process p,v$session s WHERE p.addr = s.paddr
and machine like '%BHASKAR%';

SELECT s.sid,s.serial#,p.spid,s.osuser,s.program,s.module,s.status,s.action FROM v$process p,v$session s WHERE p.addr = s.paddr
and osuser='20C1248';

SELECT a.username, a.osuser, b.spid
FROM v$session a, v$process b
WHERE a.paddr = b.addr
AND a.username IS NOT null;

select spid,serial# from v$process where spid='29467';

select 'Alter system kill session '''||sid||','||serial#||''';'
from v$session;

TO FIND THE BLOCKING SESSION ALONG WITH SID AND KILLING IN THE
DATABASE:
select process,sid, blocking_session from v$session where blocking_session is not null;


Kill oracle process in Database:
--------------------------------------------------------
select process,sid, blocking_session from v$session where blocking_session is not null;
select SERIAL# from v$session where SID=1568;
conn /as sysdba
alter system kill session '732,11231';


How to see current transaction in Database?


SQL>select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
from v$session a, v$transaction b
where a.saddr = b.ses_addr;


How to find the job related details scheduled from Oracle Database?

SQL> SELECT
JOB||' '||SCHEMA_USER||' '||LAST_DATE||' '||NEXT_DATE||' '||WHAT||' '||FAILURES
FROM DBA_JOBS;

How To check server reboot time on windows server:


C:\Documents and Settings\Tc62MYNODE.APAC>net statistics server

Server Statistics for \\MYNODEETD106244


Statistics since 10/19/2011 2:22 PM


Sessions accepted 1
Sessions timed-out 7
Sessions errored-out 12

Kilobytes sent 62885
Kilobytes received 15769

Mean response time (msec) 0

System errors 0
Permission violations 0
Password violations 0

Files accessed 7275
Communication devices accessed 0
Print jobs spooled 0

Times buffers exhausted

Big buffers 0
Request buffers 0

The command completed successfully.


How to know the logon time in minutes(secs) and OS user details login time to Oracle Database?

select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine from v$session where type !='BACKGROUND';



Happy SQL learning...


Best regards,

Rafi.