I also wrote about it beforeORACLE11g new features article, nowORACLE11g has become mainstreamORACLEdatabase version, understand and learnORACLEThe new features of 11G are crucial, and so are IORACLEFor beginners of 11G's new features, I would like to share my learning process and experience here.
This article mainly records that:ORACLEA new feature of 11g that allows DDL locks to wait for DML locks, which is also a theme shared by Mr. Zhang Leyi (kamus) at the ACOUG event on June 30th.
In versions before 11g, by default, DDL locks do not wait for DML locks, and if you perform DDL operations on a table with DML locks, it will immediately return a failure (except for the same SESSION), let's do a simple experiment, insert data into the table STREAM in SESSION1, do not submit, at this time the table STREAM will have a DML lock, and execute the TRUNCATE table STREAM operation in SESSION2, and it will immediately return a failure:
SESSION1 >insert into stream select * from dbdream; 10 rows created. SESSION2 >truncate table stream; truncate table stream * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired in version 11g,ORACLEIntroduced DDL_LOCK_TIMEOUT parameter, allowing DDL locks to wait for DML locks, which control the time for DDL locks to wait for DML locks, in seconds, the default value is 0, that is, DDL locks do not wait for DML locks, the maximum value is 1 million, that is, 11.5 days, this parameter can be set globally or at the SESSION level.
SESSION2 >show parameter ddl_lock_timeout NAME TYPE VALUE ----------------------- ----------- ------ ddl_lock_timeout integer 0 SESSION2 >alter session set ddl_lock_timeout=2000000; ERROR: ORA-00068: invalid value 2000000 for parameter ddl_lock_timeout, must be between 0 and 1000000 The following demonstrates the effect of the following DDL_LOCK_TIMEOUT parameter, and also takes the above STREAM table as an example, the current STREAM table still has a DML lock, in the SESSION2 will be DDL_LOCK_ TIMEOUT is set to 60 seconds, and then the TRUNCATE operation is executed, and the failure will not be returned immediately, but will wait for 60 seconds, and if the DL lock of the STREAM table is not released within 60 seconds, it will return a failure.
SESSION2 >alter session set ddl_lock_timeout=60; Session altered. SESSION2 >set timing on SESSION2 >truncate table stream; --At this point, SESSION2 suspends, waits for SESSION1 to release the DML lock within 60 seconds, returns failed after 60 seconds truncate table stream * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Elapsed: 00:01:00.01 The emergence of DDL_LOCK_TIMEOUT parameters greatly simplifies the operation of DBAs, for example, we want to add a field to a table, but this table has a lot of DML operations, and we can't use the ALTER SYSTEM KILL SESSION method to end these operations, so if we want to add fields to this table, we need to perform the operation of adding fields countless times, or write scripts to do this job, and now we have DDL_ LOCK_TIMEOUT parameter, we only need to set the value of this parameter slightly larger, and we don't need to worry about executing SQL, let's demonstrate this process below.
SESSION2 add fields:
SESSION2 >alter table stream add jpg_path varchar2(255) default '/home/oracle/'; 此时挂起，等待STREAM表的DML锁释放，SESSION1提交释放DML锁后，SESSION2操作便成功了。
SESSION1 >commit; Commit complete. SESSION2 >alter table stream add jpg_path varchar2(255) default '/home/oracle/'; Table altered. SESSION2 >select * from stream; ID IDENTIFIER PIC_NO JPG_PATH ---------- --------------- ------ --------------- 1 18-0220-003 1 /home/oracle/ 2 18-0221-003 1 /home/oracle/ 3 18-0221-003 2 /home/oracle/ 4 18-0221-003 3 /home/oracle/ 5 18-0223-005 1 /home/oracle/ 6 18-0223-005 2 /home/oracle/ 7 18-0223-005 3 /home/oracle/ 8 18-0223-005 4 /home/oracle/ 9 18-0223-005 5 /home/oracle/ 10 18-0223-005 6 /home/oracle/ 10 rows selected.
|
Posted on 11/26/2014 3:54:15 PM
|
|
|
