# oracle

**Repository Path**: qzgyyds/oracle

## Basic Information

- **Project Name**: oracle
- **Description**: oracle相关问题积累
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2023-11-21
- **Last Updated**: 2023-11-21

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# oracle
#### 锁表查询
#### 1.sysdba身份登录，sql窗口查询锁表情况。
```
select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name, ao.object_name,lo.locked_mode  
from v$locked_object lo,dba_objects ao,v$session sess where ao.object_id=lo.object_id and lo.session_id=sess.sid
```
![查询结果](https://images.gitee.com/uploads/images/2021/0525/115906_6ddd622f_5735118.png "屏幕截图.png")

#### 2.打开命令窗口执行，杀死锁表回话
```
alter system kill session '5668,14325';
```
![解锁结果](https://images.gitee.com/uploads/images/2021/0525/120046_06b9bbdb_5735118.png "屏幕截图.png")

#### 3.查看锁表语句

```
SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,
A.STATUS,C.PIECE,C.SQL_TEXT FROM V$SESSION A,V$SQLTEXT C
WHERE A.SID IN (
SELECT DISTINCT T2.SID FROM V$LOCKED_OBJECT T1, V$SESSION T2 WHERE T1.SESSION_ID = T2.SID
) AND A.SQL_ADDRESS = C.ADDRESS(+)
ORDER BY C.PIECE
```