Question: How do I lock-down my
production tables such that I am the only person who can run
DDL against a production table? How can I use the
"alter table xxx disable table lock syntax?
Answer: To prevent users from acquiring
individual table locks, the following statement can be used:
ALTER TABLE
table_name DISABLE TABLE LOCK
When users attempt to lock
tables with disabled locks, they will receive an error. To re-enable
table locking after a transaction, the following statement can be
used:
ALTER TABLE
table_name ENABLE TABLE LOCK
Using this syntax forces all
currently executing transactions to commit before enabling the table
lock. The statement does not wait for new transactions to start
after issuing the ENABLE statement. The disadvantage to this
statement is that it must be executed for all tables that may
experience improper locking.
To determine whether a table in
the schema has its table lock enabled or disabled, the table_lock
column in the dba_tables data dictionary table should be queried.
If SELECT privilege is on dba_tables, the table lock state of other
user's tables can be queried as well. The dba_tables views can be
used to see the locking state of tables for which a user has been
granted SELECT privileges.
The disable table lock command will prevent all
DDL (including "truncate" commands against a production
table:
alter table xxx disable table lock;
Then, all DDL with throw an ORA-00069 error:
SQL> truncate table mytab;
truncate
table mytab
ORA-00069: cannot acquire lock — table locks
disabled for mytab
The "alter table xxx disable table lock" command is
used with the "revoke DBA" privilege command to ensure that
only the DBA can change a production table.
The disable table lock command is an excellent
safeguard for production tables. The following script
will disable all production tables for the SCOTT schema
connect scott/tiger;
spool /tmp/disable_table_lock.sql
select
alter table scott.||table_name||'
disable table lock;'
from
user_tables;
spool off;
@/tmp/disable_table_lock
