ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DML_LOCKS设置为0导致SHUTDOWN IMMEDIATE失败

DML_LOCKS设置为0导致SHUTDOWN IMMEDIATE失败

原创 Linux操作系统 作者:yangtingkun 时间:2012-04-11 23:39:46 0 删除 编辑

DML_LOCKS参数设置系统中允许DML锁的数量,如果将这个参数设置为0,会禁止ENQUEUE的产生,有可能轻微的提升系统的性能。

 

 

不过如果将这个参数设置为0,带来的后果会严重很多:

SQL> show parameter dml_locks

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dml_locks integer 264
SQL> alter system set dml_locks = 0 scope = spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 2097560 bytes
Variable Size 150998632 bytes
Database Buffers 369098752 bytes
Redo Buffers 2093056 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> create table t_dml (id number);

Table created.

SQL> insert into t_dml values (1);

1 row created.

SQL> update t_dml set id = 2;

1 row updated.

SQL> delete t_dml;

1 row deleted.

SQL> commit;

Commit complete.

SQL> create index i_t_dml_id on t_dml(id);
create index i_t_dml_id on t_dml(id)
*
ERROR at line 1:
ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS is 0


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


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

可以看到,并非是DML_LOCKS设置为0,就不能执行DML操作了,事实上DML仍然可以执行。相反由于大部分的DDL在执行过程中需要对表加锁,因此大部分DDL语句在DML_LOCKS设置为0后不能在运行了。

收影响的操作不只是DDL而已,连SHUTDOWN IMMEDIATE都不能幸免:

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS is 0

SQL> shutdown abort
ORACLE instance shut down.

也就是说设置DML_LOCKS后,只能使用ABORT方式关闭数据库了。

 

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10488742