dml_locks

dml_locks这个参数呢，主要是用来规定TM锁的总数，实际上这个参数可以设置为0，表示我们要取消数据库获得TM表级锁的能力，这时候数据库的数据封锁粒度实际只有一级，就是行级(TX)。这样设置之后，其实数据行的updte修改仍能进行，但是会话只能是获得了TX锁，而不能获得任何的TM锁，由于在更新数据库的时候没有任何TM锁的保护，所以数据库中任何的DDL语句都是不能执行的(ORA-00069: cannot acquire lock -- table locks disabled for my_table)
当然这样做的虽然可以提高数据库的性能，但是也会带来一些小小的麻烦。
为了不在全局使这样的改变生效，我们还可以针对table来让他生效
alter table my_table disable table lock;
alter table my_table enable table lock;

from:http://space.itpub.net/?uid-12361284-action-viewspace-itemid-601

---------------------------------------------------------------------------------------------------------

Tips--设定dml_locks来禁止在表上的DDL
===========================================================
作者: vongates(http://vongates.itpub.net)
发表于: 2007.11.07 16:16
分类: Oracle
出处: http://vongates.itpub.net/post/2553/411762
---------------------------------------------------------------

在ORACLE中我们可以建立trigger来对DDL进行管理，常见的有不充许删除表，修改表等。但是仍然可以建立新表的哟，我们只要设定初始化参数dml_locks就可以实现这一需求，不需要建立trigger来实现，仅需设定dml_locks=0就可以了。
下面是实际的例子：

[oracle@sx501:/opt/oracle]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 7 14:45:14 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> show parameter dml_lock

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dml_locks integer 360
SQL> alter system set dml_locks=0;
alter system set dml_locks=0
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> show parameter spf

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/10g/dbs/sp
fileORASTM.ora
SQL> conn scott/tiger
Connected.
SQL> select tname from tab;

TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

SQL> create table deptt as select * from dept;

Table created.

SQL> conn / as sysdba
Connected.
SQL> alter system set dml_locks=0 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 2008304 bytes
Variable Size 192940816 bytes
Database Buffers 411041792 bytes
Redo Buffers 6377472 bytes
Database mounted.
Database opened.
SQL> show parameter dml_locks

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dml_locks integer 0
SQL> conn scott/tiger
Connected.
SQL> select tname from tab;

TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
DEPTT

SQL> create table depttt as select * from dept;

Table created.

SQL> drop table deptt;
drop table deptt
*
ERROR at line 1:
ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS is 0

SQL> select tname from tab;

TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
DEPTTT
DEPTT

6 rows selected.

SQL> alter table deptt disable table lock;
alter table deptt disable table lock
*
ERROR at line 1:
ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS is 0

SQL> rename deptt to deptttt;
rename deptt to deptttt
*
ERROR at line 1:
ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS is 0

SQL> desc deptt;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL> alter table deptt rename column loc to location;
alter table deptt rename column loc to location
*
ERROR at line 1:
ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS is 0

SQL> conn / as sysdba
Connected.
SQL> alter system set dml_locks=360 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 2008304 bytes
Variable Size 197135120 bytes
Database Buffers 406847488 bytes
Redo Buffers 6377472 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger
Connected.
SQL> select tname from tab;

TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
DEPTTT
DEPTT

6 rows selected.

SQL> drop table deptt purge;

Table dropped.

SQL> select tname from tab;

TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
DEPTTT

SQL>

vongates 发表于:2007.11.07 16:16 ::分类: ( Oracle ) ::阅读:(757次) :: 评论 (2)
re: Tips--设定dml_locks来禁止在表上的DDL [回复]
所有表都不能建立？好像不实用

ora110 评论于: 2007.11.22 20:35
re: Tips--设定dml_locks来禁止在表上的DDL [回复]
用 ddl 触发器实现更灵活

from:http://vongates.itpub.net/post/2553/411762