DML_LOCKS設定為0導致SHUTDOWN IMMEDIATE失敗

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方式關閉資料庫了。