|Oracle® Database SQL Language Reference
11g Release 2 (11.2)
Part Number E17118-03
TABLE statement to lock one or more tables, table partitions, or table subpartitions in a specified mode. This lock manually overrides automatic locking and permits or denies access to a table or view by other users for the duration of your operation.
Some forms of locks can be placed on the same table at the same time. Other locks allow only one lock for a table.
The table or view must be in your own schema or you must have the
TABLE system privilege, or you must have any object privilege on the table or view.
Specify the schema containing the table or view. If you omit
schema, then Oracle Database assumes the table or view is in your own schema.
Specify the name of the table or view to be locked.
If you specify
view, then Oracle Database locks the base tables of the view.
If you specify the
partition_extension_clause, then Oracle Database first acquires an implicit lock on the table. The table lock is the same as the lock you specify for the partition or subpartition, with two exceptions:
If you specify a
SHARE lock for the subpartition, then the database acquires an implicit
SHARE lock on the table.
If you specify an
EXCLUSIVE lock for the subpartition, then the database acquires an implicit
EXCLUSIVE lock on the table.
If you specify
table is composite-partitioned, then the database acquires locks on all the subpartitions of the partition.
Restriction on Locking Tables If
view is part of a hierarchy, then it must be the root of the hierarchy.
Specify a database link to a remote Oracle Database where the table or view is located. You can lock tables and views on a remote database only if you are using Oracle distributed functionality. All tables locked by a
TABLE statement must be on the same database.
If you omit
dblink, then Oracle Database assumes the table or view is on the local database.
See Also:"References to Objects in Remote Databases" for information on specifying database links
Specify one of the following modes:
SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access.
SHARE is synonymous with
UPDATE, which is included for compatibility with earlier versions of Oracle Database.
SHARE UPDATE See ROW SHARE.
SHARE permits concurrent queries but prohibits updates to the locked table.
SHARE ROW EXCLUSIVE
EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in
SHARE mode or from updating rows.
NOWAIT if you want the database to return control to you immediately if the specified table, partition, or table subpartition is already locked by another user. In this case, the database returns a message indicating that the table, partition, or subpartition is already locked by another user.
WAIT clause to indicate that the
TABLE statement should wait up to the specified number of seconds to acquire a DML lock. There is no limit on the value of
If you specify neither
WAIT, then the database waits indefinitely until the table is available, locks it, and returns control to you. When the database is executing DDL statements concurrently with DML statements, a timeout or deadlock can sometimes result. The database detects such timeouts and deadlocks and returns an error.
See Also:Oracle Database Administrator's Guide for more information about locking tables
Locking a Table: Example The following statement locks the
employees table in exclusive mode but does not wait if another user already has locked the table:
LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT;
The following statement locks the remote
employees table that is accessible through the database link
LOCK TABLE employees@remote IN SHARE MODE;