当前位置:Gxlcms > 数据库问题 > Script to Monitor Current User Activity in the Database

Script to Monitor Current User Activity in the Database

时间:2021-07-01 10:21:17 帮助过:28人阅读

Execution Environment:
     SQL, SQL*Plus, iSQL*Plus

Access Privileges:
     Requires select privileges on view V$SQLTEXT, V$SESSION and V$PROCESS.

Usage:
     sqlplus /nolog 
     SQL> connect sys;/<password>
     SQL> @sqltext

Instructions:

Copy the script into the file sqltext.sql and execute it from SQL*Plus connected 
as sys.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.
 
Description
The script displays the text of the SQL statements of the shared cursors in the
SGA and information of the sessions executing them. 

SQL> @sqltext

Unix PID  OS User   Oracle Us SID   SERIAL# SQL_TEXT
--------- --------- --------- ----- ------- -----------------------------------
16995     lsupport  SYS          24       7 /* OracleOEM */ select value from
                                            v$sysstat order by statistic#

16999     lsupport  SYS          22    6769 /* OracleOEM */ select value from
                                            v$sesstat t where sid = :1

 
References
 
 
Script
set pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
  from v$sqltext a, v$session b, v$process c
   where a.address    = b.sql_address
--   and b.status     = ‘ACTIVE‘ /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
--                                  ACTVE TRANSACTION ON THAT MOMENT */
   and b.paddr      = c.addr
   and a.hash_value = b.sql_hash_value
 order by c.spid,a.hash_value,a.piece
/      		  
 
 
Disclaimer
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.
 

Script to Monitor Current User Activity in the Database

标签:fread   set   edit   tran   error   des   user   basis   asi   

人气教程排行