Oracle view the lock held by the object

Oracle view the lock held by the object

My colleague added a field to a table in the test database, prompting ORA-00054, resource busy. The table object lock is not released.

Use the following SQL statement to check the locking status of related objects in the system:

1. /* FormattedOn14:45:46 (QP5 v5.163.1008.3004 )*/
2. SELECTS. SIDSESSION_ID,
3. S. USERNAME,
4. DECODE (LMODE,
5. 0,'None',
6. 1,'Null',
7. 2,'Row-S (SS )',
8. 3,'Row-X (SX )',
9. 4,'Share',
10. 5,'S/Row-X (SSX )',
11. 6,'Exclusion',
12. TO_CHAR (LMODE ))
13. MODE_HELD,
14. DECODE (REQUEST,
15. 0,'None',
16. 1,'Null',
17. 2,'Row-S (SS )',
18. 3,'Row-X (SX )',
19. 4,'Share',
20. 5,'S/Row-X (SSX )',
21. 6,'Exclusion',
22. TO_CHAR (REQUEST ))
23. MODE_REQUESTED,
24. O. OWNER |'.'| O. OBJECT_NAME |'('| O. OBJECT_TYPE |')' AsOBJECT_NAME,
25. S. TYPELOCK_TYPE,
26. L. ID1LOCK_ID1,
27. L. ID2LOCK_ID2
28. FROMV $ lock l, SYS. DBA_OBJECTSO, V $ SESSION S
29.  WHEREL. SID = S. SIDANDL. ID1 = O. OBJECT_ID

This SQL statement displays the locks on all objects. If you want to query a specific object, you can perform Filtering Based on the OBJECT_NAME field. Find the corresponding SID and check the V $ SESSION view.

This view displays the session information, including terminal information. If a terminal is found, it can be submitted or rolled back. I am in the test environment and kill the session directly. Then modify the table.

1. SQL>AlterSystem kill session'Sid, SERIAL #'