Tips--设定dml_locks来禁止在表上的DDL

在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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987/viewspace-52034/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29987/viewspace-52034/