FROM http://blog.csdn.net/jgmydsai/article/details/10523969
- Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
- Connected as test@192.168.1.118:1521/orcl
- SQL>
- SQL> INSERT INTO TEST SELECT 1 FROM dual WHERE NOT EXISTS(SELECT * FROM TEST);
- 1 row inserted
- SQL> COMMIT;
- Commit complete
- SQL> BEGIN
- 2 DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'TEST_JOB',
- 3 JOB_TYPE => 'PLSQL_BLOCK',
- 4 JOB_ACTION => 'declare
- 5 v_date date := sysdate + 10 / 24 / 60 / 60;
- 6 begin
- 7 loop
- 8 dbms_lock.sleep(1);
- 9 update test set t1 = t1 + 1;
- 10 exit when sysdate >= v_date;
- 11 end loop;
- 12 commit;
- 13 end;',
- 14 START_DATE => SYSDATE,
- 15 ENABLED => TRUE,
- 16 AUTO_DROP => TRUE);
- 17 DBMS_LOCK.SLEEP(5);
- 18 END;
- 19 /
- PL/SQL procedure successfully completed
- SQL> TRUNCATE TABLE TEST;
- TRUNCATE TABLE TEST
- ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
- SQL>
- SQL> show parameter ddl_lock;
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- ddl_lock_timeout integer 0
- SQL> alter session set ddl_lock_timeout=10;
- Session altered
- SQL>
- SQL> INSERT INTO TEST SELECT 1 FROM dual WHERE NOT EXISTS(SELECT * FROM TEST);
- 0 rows inserted
- SQL> COMMIT;
- Commit complete
- SQL> BEGIN
- 2 DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'TEST_JOB',
- 3 JOB_TYPE => 'PLSQL_BLOCK',
- 4 JOB_ACTION => 'declare
- 5 v_date date := sysdate + 10 / 24 / 60 / 60;
- 6 begin
- 7 loop
- 8 dbms_lock.sleep(1);
- 9 update test set t1 = t1 + 1;
- 10 exit when sysdate >= v_date;
- 11 end loop;
- 12 commit;
- 13 end;',
- 14 START_DATE => SYSDATE,
- 15 ENABLED => TRUE,
- 16 AUTO_DROP => TRUE);
- 17 DBMS_LOCK.SLEEP(5);
- 18 END;
- 19 /
- PL/SQL procedure successfully completed
- SQL> TRUNCATE TABLE TEST;
- Table truncated
- SQL>