oracle 表锁住了什么原因

在对指定表做append *** 作，其他再做truncate时候，会产生锁表，如下验证步骤，

1、创建测试表，

create table test_lock(id number, value varchar2(200))

2、执行append语句；并且不做提交，insert /*+append*/ into test_lock values(1,1)

3、再次执行清表语句，truncate table test_lock报锁表错误，

4、查看锁表语句，发现被锁表，

select b.object_name, t.*

from v$locked_object t, user_objects b

where t.object_id = b.object_id

oracle数据库分行级锁和表级锁。用select * from table-name for update完成行级锁。用delete或update完成表级锁。你锁定的资源 别人会等待你的提交语句或回退语句完成以后再继续进行。

Session1创建测试表：

SQL>create table test (id number (10) not null , name varchar(20), primary key(id))

Table created.

SQL>desc test

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER(10)

NAME VARCHAR2(20)

SQL>insert into test values(001,'tom')

1 row created.

SQL>insert into test values(002,'lisa')

1 row created.

SQL>insert into test values(003,'joy')

1 row created.

SQL>insert into test values(004,'jia')

1 row created.

查看test表信息

SQL>update test set name='xue' where name='joy'

1 row updated.

SQL>commit

Commit complete.

SQL>select * from test updata

ID NAME

---------- --------------------

1 tom

2 lisa

3 xue

4 jia

重新打开session 2：

SQL>select * from test；

ID NAME

---------- --------------------

1 tom

2 lisa

3 xue

4 jia

update模拟锁表

SQL>update test set name='da' where name='tom'

1 row updated.

注：不提交

Session2查询：

SQL>select * from test；

ID NAME

---------- --------------------

1 tom

2 lisa

3 xue

4 jia

查看哪个表被锁

SQL>select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id

OWNER

------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

SESSION_ID LOCKED_MODE

---------- -----------

SYS

TEST

23 3

查看是哪个session引起的

SQL>select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time

USERNAME SID SERIAL# LOGON_TIM

------------------------------ ---------- ---------- ---------

SYS 23 23 02-JAN-20

杀掉对应进程

SQL>alter system kill session'23,23'

System altered.

其中23为sid,23为serial#.

SQL>select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id

no rows selected

SQL>select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time

no rows selected

session 1查询：

SQL>select * from test

select * from test

*

ERROR at line 1:

ORA-00028: your session has been killed

SQL>select * from test

select * from test

*

ERROR at line 1:

ORA-01012: not logged on

Process ID: 5366

Session ID: 23 Serial number: 23

重新连接SQL

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 2 11:39:53 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>select * from test updata

ID NAME

---------- --------------------

1 tom

2 lisa

3 xue

4 jia

---