Oracle Lock Monitoring
SQL*Plus
SQL*Plus
Example Output
Example Output
SQL> @oralocks
Lock report for the MYDB database
---------O/S---------- -----------ORACLE------------ LockUsername PID Username SID Ser TY Type Object Name Lock Held Requested---------------------- ----------------------------- -- -------------------------- --------------------------------- --------- ---------oracle 2752525 SYS 249 23 HW Segment High Water Mark Exclusiveoracle 2752525 SYS 249 23 SK Shrink Segment Exclusiveoracle 2752525 SYS 249 23 OD Online DDLs Row Excloracle 3355450 SYS 201 29 TO Temp Object Row Excloracle 1107569 PUBLIC 494 3 WL Being Written Redo Log Sh Row Exoms 5898282 SYS 543 3 AE Edition Lock Shareoracle 1107569 PUBLIC 494 3 AE Edition Lock Shareoracle 1946443 PUBLIC 151 5 AE Edition Lock Shareoracle 2752525 SYS 249 23 AE Edition Lock Shareoracle 3355450 SYS 201 29 AE Edition Lock Shareoracle 4082908 PUBLIC 102 7 AE Edition Lock Shareoracle 4286062 PUBLIC 396 7 AE Edition Lock Shareoracle 2752525 SYS 249 23 TX Transaction (Rollback=_SYSSMU22_2770487794$) Exclusiveoracle 3355450 SYS 201 29 TX Transaction (Rollback=_SYSSMU8_3424954036$) Exclusiveoracle 2752525 SYS 249 23 TM DML MYTABLE Row Excloracle 3355450 SYS 201 29 TM DML LOCKTYPELOOKUP Row Excl
17 rows selected.
Lock report for the MYDB database
---------O/S---------- -----------ORACLE------------ LockUsername PID Username SID Ser TY Type Object Name Lock Held Requested---------------------- ----------------------------- -- -------------------------- --------------------------------- --------- ---------oracle 2752525 SYS 249 23 HW Segment High Water Mark Exclusiveoracle 2752525 SYS 249 23 SK Shrink Segment Exclusiveoracle 2752525 SYS 249 23 OD Online DDLs Row Excloracle 3355450 SYS 201 29 TO Temp Object Row Excloracle 1107569 PUBLIC 494 3 WL Being Written Redo Log Sh Row Exoms 5898282 SYS 543 3 AE Edition Lock Shareoracle 1107569 PUBLIC 494 3 AE Edition Lock Shareoracle 1946443 PUBLIC 151 5 AE Edition Lock Shareoracle 2752525 SYS 249 23 AE Edition Lock Shareoracle 3355450 SYS 201 29 AE Edition Lock Shareoracle 4082908 PUBLIC 102 7 AE Edition Lock Shareoracle 4286062 PUBLIC 396 7 AE Edition Lock Shareoracle 2752525 SYS 249 23 TX Transaction (Rollback=_SYSSMU22_2770487794$) Exclusiveoracle 3355450 SYS 201 29 TX Transaction (Rollback=_SYSSMU8_3424954036$) Exclusiveoracle 2752525 SYS 249 23 TM DML MYTABLE Row Excloracle 3355450 SYS 201 29 TM DML LOCKTYPELOOKUP Row Excl
17 rows selected.
The output above was generated on a quiesced database (in this case a Snapshot Standby of a Primary database) whilst running...ALTER TABLE mytable MODIFY LOB(mylob) (SHRINK SPACE);
OEM
OEM
Targets - Databases - select instance - Performance - Search Sessions - Instance Locks (link at bottom of page)
