Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Oracle » Monitoring » Tools »

List session information

     






These handy SQL scripts will display user session information in Oracle.
You may need Dictionary privileges in order to execute these.





SELECT * FROM V$SESSION ;





set heading on
set pagesize 200
set newpage 0



SELECT T1.USERNAME, T2.TABLESPACE, T2.CONTENTS, T2.EXTENTS, T2.BLOCKS
FROM V$SESSION T1, V$SORT_USAGE T2
WHERE T1.SADDR = T2.SESSION_ADDR ;



SELECT i.instance_name, s.sid, s.serial#
FROM v$session s, v$instance i
WHERE s.saddr in
(SELECT t.ses_addr
FROM V$transaction t, dba_rollback_segs r
WHERE t.xidusn=r.segment_id
AND r.tablespace_name='RBS') ;



column action format a12

SELECT
instance_name,
SID, -- NUMBER
SERIAL#, -- NUMBER
USER#, -- NUMBER
substr(USERNAME,1,10) as "username", -- VARCHAR2(30)
substr(SCHEMANAME,1,10) as "schema", -- VARCHAR2(30)
substr(machine,1,18) as "machine",
terminal,
substr(PROGRAM,1,10) as "program", -- VARCHAR2(48)
COMMAND, -- NUMBER
upper(decode(nvl(COMMAND, 0),
0, '---------------',
1, 'Create Table',
2, 'Insert ...',
3, 'Select. ..',
4, 'Create Cluster',
5, 'Alter Cluster',
6, 'Update. ..',
7, 'Delete. ..',
8, 'Drop. ..',
9, 'Create Index',
10, 'Drop Index',
11, 'Alter Index',
12, 'Drop Table',
13, '--',
14, '--',
15, 'Alter Table',
16, '--',
17, 'Grant',
18, 'Revoke',
19, 'Create Synonym',
20, 'Drop Synonym',
21, 'Create View',
22, 'Drop View',
23, '--',
24, '--',
25, '--',
26, 'Lock Table',
27, 'No Operation',
28, 'Rename',
29, 'Comment',
30, 'Audit',
31, 'NoAudit',
32, 'Create Ext DB',
33, 'Drop Ext. DB',
34, 'Create Database',
35, 'Alter Database',
36, 'Create RBS',
37, 'Alter RBS',
38, 'Drop RBS',
39, 'Create Tablespace',
40, 'Alter Tablespace',
41, 'Drop tablespace',
42, 'Alter Session',
43, 'Alter User',
44, 'Commit',
45, 'Rollback',
46, 'Savepoint')) job,
LOCKWAIT, -- VARCHAR2(8)
t1.STATUS, -- VARCHAR2(8)
PROCESS, -- VARCHAR2(9)
TYPE, -- VARCHAR2(10)
LOGON_TIME, -- DATE
ACTION, seconds_in_wait
FROM v$session t1, v$instance
order by USERNAME, SERIAL#
/


select username,count(1) from v$session group by username
/












Oracle : Related Topics

Oracle : Monitoring : What is going on in the database?
Oracle : Monitoring : Version information
Oracle : Monitoring : List free and used space in database
Oracle : Monitoring : List names and default storage parameters for all tablespaces
Oracle : Monitoring : List information about tablespace to which datafiles belong
Oracle : Monitoring : List data file information
Oracle : Monitoring : List tablespace fragmentation information
Oracle : Monitoring : Check the current number of extents and blocks allocated to a seg
Oracle : Monitoring : Extent information
Oracle : Monitoring : Extent information for a table
Oracle : Monitoring : List segments with fewer than 5 extents remaining
Oracle : Monitoring : List segments reaching extent limits
Oracle : Monitoring : List table blocks, empty blocks, extent count, and chain block count
Oracle : Monitoring : Information about all rollback segments in the database
Oracle : Monitoring : Statistics of the rollback segments currently used by instance
Oracle : Monitoring : Active sorts in instance
Oracle : Monitoring : Index & constraint information
Oracle : Monitoring : List tables and synonyms
Oracle : Monitoring : Constraint columns
Oracle : Monitoring : Constraint listing
Oracle : Monitoring : Indexed column listing
Oracle : Monitoring : Trigger listing
Oracle : Monitoring : Tuning: library cache
Oracle : Monitoring : Tuning: data dictionary cache
Oracle : Monitoring : Tuning: buffer cache
Oracle : Monitoring : Tuning: sorts
Oracle : Monitoring : Tuning: rollback segments
Oracle : Monitoring : Tuning: physical file placement
Oracle : Monitoring : Archive Log Mode Status
Oracle : Monitoring : List log file information
Oracle : Monitoring : A Simple Monitoring Tool
Oracle : Monitoring : Connection Errors
Oracle : Monitoring : List Space Allocated by Table
Oracle : Monitoring : Tablespace types, and availability of data files
Oracle : Monitoring : List sessions with active transactions
Oracle : Monitoring : Tuning: dynamic extension
Oracle : Monitoring : Check the extents for a given segment
Oracle : Monitoring : Explain Plan: syntax

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.