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