IT运维管理体系和规范,it运维管理系统介绍
751
2022-10-04
Oracle DBA 脚本: FULLY DECODED LOCKING
Abstract | |
---|---|
This script provides a complex fully decoded locking information which is much more detailed than the other two scripts in the series. Note 1020012.6 SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO Note 1020011.6 SCRIPT: LOW COMPLEXITY LOCKING INFO | |
Product Name, Product Version | Rdbms:07.03.03 Rdbms:09.02.00.02 |
Platform | Platform Independent |
Date Created | 07-NOV-2002 |
Instructions | |
Execution Environment: <SQL, SQL*Plus>Access Privileges: SELECT on V_$LOCK, V_$SESSION, SYS.USER$, SYS.OBJ$ Usage: sqlplus <user>/<pw> @[SCRIPTFILE]Instructions: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 statewhen you first receive it. Check over the script to ensure that errors ofthis type are corrected.The script will produce an output file named [outputfile].This file can be viewed in a browser or uploaded for support analysis.NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. | |
Description | |
The following locking information script provides fully DECODED information regarding the locks currently held in the database. The report generated is fairly complex and difficult to read, but has considerable detail. | |
References | |
Note:15476.1 FAQ about Detecting and Resolving Locking ConflictsNote:1020012.6 SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO Note:1020011.6 SCRIPT: LOW COMPLEXITY LOCKING INFO | |
Script | |
Script: ----------- cut ---------------------- cut -------------- cut -------------- SET ECHO off REM NAME: TFSCLOCK.SQL REM USAGE:"@path/tfsclock" REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM SELECT on V_$LOCK, V_$SESSION, SYS.USER$, SYS.OBJ$ REM ------------------------------------------------------------------------ REM PURPOSE: REM The following locking information script provides fully DECODED REM information regarding the locks currently held in the database. REM The report generated is fairly complex and difficult to read, REM but has considerable detail. REM REM The TFTS series contains scripts to provide (less detailed) lock REM information in a formats which are somewhat less difficult to read: REM TFSMLOCK.SQL and TFSLLOCK.SQL. REM ------------------------------------------------------------------------ REM EXAMPLE: REM Too complex to show a representative sample here REM REM ------------------------------------------------------------------------ REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM ------------------------------------------------------------------------ REM set lines 200 set pagesize 66 break on Kill on sid on username on terminal column Kill heading 'Kill String' format a13 column res heading 'Resource Type' format 999 column id1 format 9999990 column id2 format 9999990 column locking heading 'Lock Held/Lock Requested' format a40 column lmode heading 'Lock Held' format a20 column request heading 'Lock Requested' format a20 column serial# format 99999 column username format a10 heading "Username" column terminal heading Term format a6 column tab format a30 heading "Table Name" column owner format a9 column LAddr heading "ID1 - ID2" format a18 column Lockt heading "Lock Type" format a40 column command format a25 column sid format 990 select nvl(S.USERNAME,'Internal') username,s.SERIAL#, L.SID, nvl(S.TERMINAL,'None') terminal, decode(command, 0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab, decode(command, 0,'BACKGROUND', 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,'CREATE SEQUENCE', 14,'ALTER SEQUENCE', 15,'ALTER TABLE', 16,'DROP SEQUENCE', 17,'GRANT', 18,'REVOKE', 19,'CREATE SYNONYM', 20,'DROP SYNONYM', 21,'CREATE VIEW', 22,'DROP VIEW', 23,'VALIDATE INDEX', 24,'CREATE PROCEDURE', 25,'ALTER PROCEDURE', 26,'LOCK TABLE', 27,'NO OPERATION', 28,'RENAME', 29,'COMMENT', 30,'AUDIT', 31,'NOAUDIT', 32,'CREATE EXTERNAL DATABASE', 33,'DROP EXTERNAL DATABASE', 34,'CREATE DATABASE', 35,'ALTER DATABASE', 36,'CREATE ROLLBACK SEGMENT', 37,'ALTER ROLLBACK SEGMENT', 38,'DROP ROLLBACK SEGMENT', 39,'CREATE TABLESPACE', 40,'ALTER TABLESPACE', 41,'DROP TABLESPACE', 42,'ALTER SESSION', 43,'ALTER USER', 44,'COMMIT', 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL/SQL EXECUTE', 48,'SET TRANSACTION', 49,'ALTER SYSTEM SWITCH LOG', 50,'EXPLAIN', 51,'CREATE USER', 52,'CREATE ROLE', 53,'DROP USER', 54,'DROP ROLE', 55,'SET ROLE', 56,'CREATE SCHEMA', 57,'CREATE CONTROL FILE', 58,'ALTER TRACING', 59,'CREATE TRIGGER', 60,'ALTER TRIGGER', 61,'DROP TRIGGER', 62,'ANALYZE TABLE', 63,'ANALYZE INDEX', 64,'ANALYZE CLUSTER', 65,'CREATE PROFILE', 66,'DROP PROFILE', 67,'ALTER PROFILE', 68,'DROP PROCEDURE', 69,'DROP PROCEDURE',70,'ALTER RESOURCE COST', 71,'CREATE SNAPSHOT LOG', 72,'ALTER SNAPSHOT LOG', 73,'DROP SNAPSHOT LOG', 74,'CREATE SNAPSHOT', 75,'ALTER SNAPSHOT', 76,'DROP SNAPSHOT', 79,'ALTER ROLE',85,'TRUNCATE TABLE', 86,'TRUNCATE CLUSTER', 87,'-', 88,'ALTER VIEW', 89,'-', 90,'-', 91,'CREATE FUNCTION', 92,'ALTER FUNCTION', 93,'DROP FUNCTION', 94,'CREATE PACKAGE', 95,'ALTER PACKAGE', 96,'DROP PACKAGE', 97,'CREATE PACKAGE BODY', 98,'ALTER PACKAGE BODY', 99,'DROP PACKAGE BODY', command||' - ???') COMMAND, decode(L.LMODE,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive','NONE') lmode, decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive','NONE') request, l.id1||'-'||l.id2 Laddr, l.type||' - '|| decode(l.type, 'BL','Buffer hash table instance lock', 'CF',' Control file schema global enqueue lock', 'CI','Cross-instance function invocation instance lock','CS','Control file schema global enqueue lock', 'CU','Cursor bind lock','DF','Data file instance lock', 'DL','Direct loader parallel index create','DM','Mount/startup db primary/secondary instance lock', 'DR','Distributed recovery process lock', 'DX','Distributed transaction entry lock', 'FI','SGA open-file information lock', 'FS','File set lock', 'HW','Space management operations on a specific segment lock','IN','Instance number lock','IR','Instance recovery serialization global enqueue lock', 'IS','Instance state lock','IV','Library cache invalidation instance lock', 'JQ','Job queue lock','KK','Thread kick lock','MB','Master buffer hash table instance lock', 'MM','Mount definition gloabal enqueue lock', 'MR','Media recovery lock', 'PF','Password file lock','PI','Parallel operation lock','PR','Process startup lock','PS','Parallel operation lock','RE','USE_ROW_ENQUEUE enforcement lock', 'RT','Redo thread global enqueue lock', 'RW','Row wait enqueue lock', 'SC','System commit number instance lock', 'SH','System commit number high water mark enqueue lock', 'SM','SMON lock','SN','Sequence number instance lock', 'SQ','Sequence number enqueue lock', 'SS','Sort segment lock','ST','Space transaction enqueue lock', 'SV','Sequence number value lock', 'TA','Generic enqueue lock', 'TD','DDL enqueue lock', 'TE','Extend-segment enqueue lock', 'TM','DML enqueue lock', 'TO','Temporary Table Object Enqueue', 'TT','Temporary table enqueue lock', 'TX','Transaction enqueue lock', 'UL','User supplied lock', 'UN','User name lock', 'US','Undo segment DDL lock','WL','Being-written redo log instance lock', 'WS','Write-atomic-log-switch global enqueue lock', 'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)', 'New block allocation enqueue lock (ID2=1)'), 'LA','Library cache lock instance lock (A=namespace)', 'LB','Library cache lock instance lock (B=namespace)', 'LC','Library cache lock instance lock (C=namespace)', 'LD','Library cache lock instance lock (D=namespace)', 'LE','Library cache lock instance lock (E=namespace)', 'LF','Library cache lock instance lock (F=namespace)', 'LG','Library cache lock instance lock (G=namespace)', 'LH','Library cache lock instance lock (H=namespace)', 'LI','Library cache lock instance lock (I=namespace)', 'LJ','Library cache lock instance lock (J=namespace)', 'LK','Library cache lock instance lock (K=namespace)', 'LL','Library cache lock instance lock (L=namespace)', 'LM','Library cache lock instance lock (M=namespace)', 'LN','Library cache lock instance lock (N=namespace)', 'LO','Library cache lock instance lock (O=namespace)', 'LP','Library cache lock instance lock (P=namespace)', 'LS','Log start/log switch enqueue lock', 'PA','Library cache pin instance lock (A=namespace)', 'PB','Library cache pin instance lock (B=namespace)', 'PC','Library cache pin instance lock (C=namespace)', 'PD','Library cache pin instance lock (D=namespace)', 'PE','Library cache pin instance lock (E=namespace)', 'PF','Library cache pin instance lock (F=namespace)', 'PG','Library cache pin instance lock (G=namespace)', 'PH','Library cache pin instance lock (H=namespace)', 'PI','Library cache pin instance lock (I=namespace)', 'PJ','Library cache pin instance lock (J=namespace)', 'PL','Library cache pin instance lock (K=namespace)', 'PK','Library cache pin instance lock (L=namespace)', 'PM','Library cache pin instance lock (M=namespace)', 'PN','Library cache pin instance lock (N=namespace)', 'PO','Library cache pin instance lock (O=namespace)', 'PP','Library cache pin instance lock (P=namespace)', 'PQ','Library cache pin instance lock (Q=namespace)', 'PR','Library cache pin instance lock (R=namespace)', 'PS','Library cache pin instance lock (S=namespace)', 'PT','Library cache pin instance lock (T=namespace)', 'PU','Library cache pin instance lock (U=namespace)', 'PV','Library cache pin instance lock (V=namespace)', 'PW','Library cache pin instance lock (W=namespace)', 'PX','Library cache pin instance lock (X=namespace)', 'PY','Library cache pin instance lock (Y=namespace)', 'PZ','Library cache pin instance lock (Z=namespace)', 'QA','Row cache instance lock (A=cache)', 'QB','Row cache instance lock (B=cache)', 'QC','Row cache instance lock (C=cache)', 'QD','Row cache instance lock (D=cache)', 'QE','Row cache instance lock (E=cache)', 'QF','Row cache instance lock (F=cache)', 'QG','Row cache instance lock (G=cache)', 'QH','Row cache instance lock (H=cache)', 'QI','Row cache instance lock (I=cache)', 'QJ','Row cache instance lock (J=cache)', 'QL','Row cache instance lock (K=cache)', 'QK','Row cache instance lock (L=cache)', 'QM','Row cache instance lock (M=cache)', 'QN','Row cache instance lock (N=cache)', 'QO','Row cache instance lock (O=cache)', 'QP','Row cache instance lock (P=cache)', 'QQ','Row cache instance lock (Q=cache)', 'QR','Row cache instance lock (R=cache)', 'QS','Row cache instance lock (S=cache)', 'QT','Row cache instance lock (T=cache)', 'QU','Row cache instance lock (U=cache)', 'QV','Row cache instance lock (V=cache)', 'QW','Row cache instance lock (W=cache)', 'QX','Row cache instance lock (X=cache)', 'QY','Row cache instance lock (Y=cache)', 'QZ','Row cache instance lock (Z=cache)','????') Lockt from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1 where L.SID = S.SID and T1.OBJ# = decode(L.ID2,0,L.ID1,1) and U1.USER# = T1.OWNER# and S.TYPE != 'BACKGROUND' order by 1,2,5 / ----------- cut ---------------------- cut -------------- cut -------------- Examples: ========= NOTE: The following report is fairly complex and difficult to format here, as it is over eighty columns wide. Username SID Term Table Name COMMAND Lock Held Lock Requested ID1 - ID2 Lock Type ---------- ---- ------ ------------------------------ ------------------------- -------------------- -------------------- ------------------ ---------------------------------------- USERNAME 7 ttyp9 None UPDATE Exclusive NONE 196615-215922 TX - Transaction enqueue lock USERNAME.TABLE_CONFIG UPDATE Row Exclusive NONE 3364-0 TM - DML enqueue lock USERNAME 8 ttyr5 None INSERT Exclusive NONE 196632-215907 TX - Transaction enqueue lock USERNAME.TT INSERT Row Exclusive NONE 3384-0 TM - DML enqueue lock SYS 6 ttyq3 None UPDATE NONE Exclusive 196615-215922 TX - Transaction enqueue lock None UPDATE Exclusive NONE 131093-2324 TX - Transaction enqueue lock USERNAME.TABLE_CONFIG UPDATE Row Exclusive NONE 3364-0 TM - DML enqueue lock SYSTEM 11 ttyq4 None UPDATE NONE Exclusive 196615-215922 TX - Transaction enqueue lock USERNAME.TABLE_CONFIG UPDATE Row Exclusive NONE 3364-0 TM - DML enqueue lock SYSTEM 12 ttyr6 None DELETE Exclusive NONE 131100-2321 TX - Transaction enqueue lock USERNAME.TT DELETE Row Exclusive NONE 3384-0 TM - DML enqueue lock USUPPORT 9 ttyp1 None SELECT Exclusive NONE 131083-2316 TX - Transaction enqueue lock | |
Disclaimer | |
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMSALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOTLIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULARPURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTSTHAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE ORRELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUREXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OROTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVENO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THATRESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANYTIME WITHOUT NOTICE. | |
Limitation of Liability | |
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 INCONTRACT 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. |
发表评论
暂时没有评论,来抢沙发吧~