use the followings sql for displaying current running processes, userids:
SET TERMOUT ON
SET HEADING ON
SET PAGESIZE 40
SET LINESIZE 180
SET NEWPAGE 0
SET VERIFY OFF
SET ECHO OFF
SET UNDERLINE =
SET FEEDBACK ON
SET LONG 1000
SET EMBED ON
COLUMN sid_ser FORMAT a10 HEADING ' SID/Ser'
COLUMN sid FORMAT 99999 HEADING ' SID'
COLUMN username FORMAT a6 HEADING 'Oracle|User'
COLUMN osuser FORMAT a10 TRUNC HEADING 'O/S User'
COLUMN machine FORMAT a10 HEADING 'Machine'
COLUMN program FORMAT a20 HEADING 'Program'
COLUMN F_Ground FORMAT 99999 HEADING 'F''Ground|Process'
COLUMN B_Ground FORMAT 99999 HEADING 'B''Ground|Process'
COLUMN sql_text FORMAT a45 word_wrap HEADING 'SQL Text'
COLUMN disk_reads FORMAT 99,999 HEADING 'Disk|Reads|(*1000)'
COLUMN buffer_gets FORMAT 9,999,999 HEADING 'Buffer|Gets|(*1000)'
COLUMN rows_processed FORMAT 9,999,999 HEADING 'Rows|Processed|(*1000)'
COLUMN sorts FORMAT 99,999 HEADING 'Sorts'
COLUMN executions FORMAT 9,999,999,999 HEADING 'Exctn'
TTITLE Center 'SQL Currently Executing'
SELECT /*+ ORDERED */
s.sid || ',' || s.serial# as sid_ser,
s.username,
s.osuser,
x.sql_text,
x.disk_reads / 1000 AS disk_reads,
x.buffer_gets / 1000 AS buffer_gets,
x.rows_processed / 1000 AS rows_processed,
x.sorts
,x.executions
,x.address
FROM v$session S,
v$process P,
v$sql X
WHERE LOWER(s.osuser) LIKE LOWER(NVL('&os_user%', '%'))
AND s.username LIKE UPPER(NVL('&oracle_user%', '%'))
AND s.sid LIKE NVL('&sid', '%')
AND s.type != 'BACKGROUND'
AND s.sql_address = x.address
AND s.sql_hash_value = x.hash_value
-- AND s.username NOT IN ('SYS','SYSTEM')
AND s.paddr = p.addr
ORDER
BY S.sid
;
Repository of Database Related Information, Assets and Knowledge Base covering Oracle, SQLs, Scripts, Performance Tuning etc
Subscribe to:
Post Comments (Atom)
Labels
oracle SQL
oracle database
oracle 10g
SQL
Optimizer
user
v$session
parameter
sid
sqlplus;database name
userid
username
SET
SPOOL
SQL Faq
SQL Tutorial
column
imp
import
initialization
optimization
oracle DBA
oracle hints
process
rename
rename table
sizing
sql hints
statistics
status
system tables
table
table locks
tablespace
temp tablespace
user name
v$lock
v$parameter
v$process
v$sessmetric
v$sql
No comments:
Post a Comment