ckcur.sql

REM *************************************************************************** 
REM 
REM        TITLE:
REM        AUTHOR:
REM        DESCRIPTION:
REM
REM
REM
REM        USAGE W/ PARAMETERS:
REM        
REM        TABLES USED:
REM
REM  MODIFICATION ALLOWED (Y/N):	
REM  MODIFICATION HISTORY:
REM  WHO          WHEN         WHAT
REM  --------------------------------------------------------------------------
REM               MM/DD/YY     INITIAL CREATION
REM
REM ***************************************************************************

set echo off
set feedback off
set verify off
set linesize 400 
set pagesize 200



BREAK ON uname
BREAK ON ROW SKIP 2
COLUMN sqltxt WORD_WRAP 
COLUMN user_name FORMAT A7 	ALIAS uname
COLUMN sql_text  FORMAT A80    ALIAS sqltxt	HEADING "SQL TEXT" 
COLUMN users_executing  FORMAT 999 HEADING "USERS EXEC"
COLUMN sorts FORMAT 9999 
COLUMN loads FORMAT 9999

rem PROMPT
rem PROMPT **** CHECK OPEN CURSORS ****
rem PROMPT

rem ACCEPT  user_nm  PROMPT "Enter the desired user name: "

SELECT   b.user_name uname, 
	 b.address,
	 a.runtime_mem,
	 a.persistent_mem,
	 a.users_executing,
	 a.executions,
	 a.parse_calls,
	 a.loads,
	 a.sorts,
	 a.disk_reads,
	 a.buffer_gets,
	 RTRIM(a.sql_text) sqltxt  
  FROM   V$SQLAREA a, V$OPEN_CURSOR b
  WHERE  b.address = a. address  
    AND  b.user_name LIKE UPPER('&user_nm%')
ORDER BY user_name;

undefine user_nm