ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Tips--设定dml_locks来禁止在表上的DDL

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

原创 Linux操作系统 作者:vongates 时间:2018-12-08 21:39:02 0 删除 编辑
在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-11

  • 博文量
    46
  • 访问量
    23177