ORA-00069： cannot acquire lock -- table locks disable for

     以下做个简单的外键未加索引的测试：

建立表：

hr@ORCL> create table t_father (id number,name varchar2(25),primary key(id));
hr@ORCL> create table t_sun (fid number,name varchar2(25),foreign key(fid) references t_father(id));

hr@ORCL> select table_name,CONSTRAINT_NAME,STATUS,R_CONSTRAINT_NAME from user_constraints where owner=\'HR\' and table_name in (\'T_FATHER\',\'T_SUN\');

TABLE_NAME                     CONSTRAINT_NAME                STATUS   R_CONSTRAINT_NAME
------------------------------ ------------------------------ -------- ------------------------------
T_FATHER                       SYS_C005495                    ENABLED
T_SUN                          SYS_C005497                    ENABLED  SYS_C005495

倒入数据并分析表：

hr@ORCL> insert into t_father select rownum,rownum||\'a\' from dual connect by rownum<1000;
hr@ORCL> insert into t_sun select rownum,rownum||\'b\' from dual connect by rownum<1000;
hr@ORCL> commit;
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>\'HR\',tabname=>\'T_FATHER\');
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>\'HR\',tabname=>\'T_SUN\');



用以下 TOM 给出的脚本检查外键无索引的表：

COLUMN COLUMNS format a30 word_wrapped
COLUMN tablename format a15 word_wrapped
COLUMN constraint_name format a15 word_wrapped
SELECT TABLE_NAME,
       CONSTRAINT_NAME,
       CNAME1 || NVL2(CNAME2, \',\' || CNAME2, NULL) ||
       NVL2(CNAME3, \',\' || CNAME3, NULL) ||
       NVL2(CNAME4, \',\' || CNAME4, NULL) ||
       NVL2(CNAME5, \',\' || CNAME5, NULL) ||
       NVL2(CNAME6, \',\' || CNAME6, NULL) ||
       NVL2(CNAME7, \',\' || CNAME7, NULL) ||
       NVL2(CNAME8, \',\' || CNAME8, NULL) COLUMNS
  FROM (SELECT B.TABLE_NAME,
               B.CONSTRAINT_NAME,
               MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
               MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
               MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
               MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
               MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
               MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
               MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
               MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
               COUNT(*) COL_CNT
          FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
                       SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
                       SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
                       POSITION
                  FROM USER_CONS_COLUMNS) A,
               USER_CONSTRAINTS B
         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
           AND B.CONSTRAINT_TYPE = \'R\'
         GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
 WHERE COL_CNT > ALL
 (SELECT COUNT(*)
          FROM USER_IND_COLUMNS I
         WHERE I.TABLE_NAME = CONS.TABLE_NAME
           AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
                CNAME6, CNAME7, CNAME8)
           AND I.COLUMN_POSITION <= CONS.COL_CNT
         GROUP BY I.INDEX_NAME)
/



hr@ORCL> /

TABLE_NAME                     CONSTRAINT_NAME COLUMNS
------------------------------ --------------- ------------------------------
T_SUN                          SYS_C005497     FID



以下进行测试：

1）Session_A:

hr@ORCL> select sid from v$session where sid in (select sid from v$mystat where rownum=1);

       SID
----------
       159


hr@ORCL> delete t_sun where fid=998;

1 row deleted.




2）Session_B:

hr@ORCL> select sid from v$session where sid in (select sid from v$mystat where rownum=1);

       SID
----------
       142
       
hr@ORCL> delete t_sun where fid=123;

1 row deleted.


3）Session_A：

hr@ORCL> delete t_father where id=555;

----请求子表的表锁却不可得之、被hang住了



用下面脚本查询数据库锁情况：

SELECT a.sid ||
         decode(request,
                0,
                \' :holder\',\' :Waiter\') sess_id,blocking_session blocker,
         lmode,
         request,
         a.type,
         c.object_name,
         decode(row_wait_obj#,
                -1,
                \'Holder of Lock !!!\',
                dbms_rowid.rowid_create(1,
                                        row_wait_obj#,
                                        row_wait_file#,
                                        row_wait_block#,
                                        row_wait_row#)) row_id,
         nvl(SQL_FULLTEXT, \'Holder of Lock !!!\') sqltext
 FROM V$LOCK A, V$LOCKED_OBJECT B, ALL_OBJECTS C, V$SESSION D, V$SQL E
WHERE (id1, id2, a.type) in
         (select id1, id2, type from v$lock where request > 0)
     AND a.sid = b.session_id
     AND b.object_id = c.object_id
     AND d.sid = a.sid
     AND d.sql_hash_value = e.hash_value(+)
     
     

sys@ORCL> /

SESS_ID                                             BLOCKER      LMODE    REQUEST TY OBJECT_NAME                    ROW_ID           SQLTEXT
------------------------------------------------ ---------- ---------- ---------- -- ------------------------------ ------------------ --------------------------------------------------------------------------------
159 :Waiter                                             142          3          5 TM T_SUN                          Holder of Lock !!! delete t_father where id=555
159 :Waiter                                             142          3          5 TM T_FATHER                       Holder of Lock !!! delete t_father where id=555
142 :holder                                                          3          0 TM T_SUN                          Holder of Lock !!! Holder of Lock !!!
142 :holder                                                          3          0 TM T_FATHER                       Holder of Lock !!! Holder of Lock !!!