Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle LOCK TABLE

In Oracle, LOCK TABLE statement is used to lock tables, table partitions, or table subpartitions.

Oracle LOCK TABLE Syntax

To commits all changes into the database table, use the following syntax:

Syntax

LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];

Parameters:

tables: The comma delimited table name.

lock_mode: The loack mode.

WAIT: To specifies wait to acquire a DML lock.

NOWAIT: To specifies not to wait for a lock to be released.

Oracle List of Lock Modes

Lock Mode Description
ROW SHARE Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access.
ROW EXCLUSIVE Allows concurrent access to the table, but users are prevented from locking the entire table with exclusive access and locking the table in share mode.
SHARE UPDATE Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access.
SHARE Allows concurrent queries but users are prevented from updating the locked table.
SHARE ROW EXCLUSIVE Users can view records in table, but are prevented from updating the table or from locking the table in SHARE mode.
EXCLUSIVE Allows queries on the locked table, but no other activities.

Oracle LOCK TABLE Statement examples

The following Oracle statement to lock the suppliers table in SHARE MODE and not wait for a lock to be released:

Example

LOCK TABLE suppliers IN SHARE MODE NOWAIT;

Note:

In the above example would lock the suppliers table in SHARE MODE and not wait for a lock to be released.