This script will display the SQL text of locked transactions. |
| |
| Oracle Server, 7.3 to 9.2.0 |
| Platform Independent |
| 25-Jul-1996 |
| |
Execution Environment:
SQL, SQL*Plus
Access Privileges:
Requires DBA access privileges to be executed -or- SELECT on the V$SQLTEXT,
V$SESSION, and V$ACCESS views.
Usage:
sqlplus sys/<password>
Instructions:
Copy the script to a file and execute it from SQL*Plus.
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.
|
| |
This script will report the SQL text of some of the locks currently being
held in the database.
|
| |
|
| |
SET ECHO off
REM NAME: TFSLKSQL.SQL
REM USAGE:"@path/tfslksql"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on V$SQLTEXT, V$SESSION, and V$ACCESS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM This script will report the SQL text of some of the locks
REM currently being held in the database.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM USERNAME SID
REM ------------------------------ ----------
REM OBJECT
REM -------------------------------------------------------------------
REM LOCKWAIT SQL
REM -------- ----------------------------------------------------------
REM SYSTEM 11
REM SCOTT.TABLE_CONFIG
REM E0034A5C update scott.table_config set tabno=99 where tabno=9
REM
REM SYS 6
REM SCOTT.TABLE_CONFIG
REM E0034C98 update scott.table_config set capacity=28 where capacity=4
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set pagesize 60
set linesize 132
select s.username username,
a.sid sid,
a.owner||'.'||a.object object,
s.lockwait,
t.sql_text SQL
from v$sqltext t,
v$session s,
v$access a
where t.address=s.sql_address
and t.hash_value=s.sql_hash_value
and s.sid = a.sid
and a.owner != 'SYS'
and upper(substr(a.object,1,2)) != 'V$'
/
==============
Sample Output:
==============
USERNAME SID
------------------------------ ----------
OBJECT
------------------------------------------------------------------------
LOCKWAIT SQL
-------- ---------------------------------------------------------------
SYSTEM 11
SCOTT.TABLE_CONFIG
E0034A5C update scott.table_config set tabno=99 where tabno=9
SYS 6
SCOTT.TABLE_CONFIG
E0034C98 update scott.table_config set capacity=28 where capacity=4
|
| |
| |
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.
|
| |
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.
SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.
|
|
