LOCKS

Prerequisites

You have generated the demo data for the SQL Tutorial.

Log on to the database instance DEMODB as user MONA.

Examples

You can use the system table LOCKS to determine the following database information, among other things:

?     All locks that are currently held on table ROOM

SELECT lockmode, lockstate, rowidlength, rowidhex, rowid
  FROM DOMAIN.LOCKS
    WHERE tablename = 'ROOM'

?     All locks that the current user holds in that database session on table ROOM

SELECT lockmode, lockstate, rowidlength, rowidhex, rowid
  FROM DOMAIN.LOCKS, SYSINFO.
SESSIONS
    WHERE tablename = 'ROOM'
      AND locks.session = sessions.sessionid

?     All locks that are currently held on the table with the hexadecimal internal identification 000000000000035D

SELECT lockmode, lockstate, rowidlength, rowidhex, rowid
  FROM DOMAIN.LOCKS
    WHERE tableid = X'000000000000035D'

If the current user is the database administrator (DBA user) or the database system administrator (SYSDBA user), then all currently held locks are shown.

Users that belong to other user classes only see the locks held by that one user.