Oracle锁表处理、表空间使用率查询

前言

兜兜转转，记得刚毕业的时候，使用得是oracle数据库，后来转战mysql，postgres等开源数据库。这不现在又用起了国产数据库和oracle。最近遇到了一些小问题，在处理过程中简单的记个笔记，方便日后查询。

死锁处理

1.锁表查询的代码有以下的形式：

select count(*) from v$locked_object;

select * from v$locked_object;

2.查看哪个表被锁

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;

3.查看是哪个session引起的

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;

4.查看是哪个sql引起的

select b.username,b.sid,b.serial#,c.* from v$locked_object a,v$session b,v$sql c where a.session_id = b.sid
and b.SQL_ID = c.sql_id and c.sql_id = ''
order by b.logon_time;

5.杀掉对应进程

# 其中1025为sid,41为serial#.
alter system kill session'1025,41';

表空间使用情况查询

查询数据库所有表空间

select tablespace_name, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name;

表空间占用率查询

SELECT --B.file_name "文件名",
       A.TABLESPACE_NAME "表空间名",
       TOTAL "表空间大小",
       FREE "表空间剩余大小",
       (TOTAL - FREE) "表空间使用大小",
       TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
       FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
       ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM   (SELECT TABLESPACE_NAME,
               SUM(BYTES) FREE
        FROM   DBA_FREE_SPACE
        GROUP  BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME,
             --  file_name,
               SUM(BYTES) TOTAL
        FROM   DBA_DATA_FILES
        GROUP  BY TABLESPACE_NAME/*,file_name*/) B
WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME;

查看具体表单所占空间

Select Segment_Name,Sum(bytes)/1024/1024 mb From User_Extents Group By Segment_Name;

查看用户表、索引、分区表占用空间

select owner,segment_type,segment_name, sum(bytes)/1024/1024/1024 siez_Gb from dba_segments group by owner,segment_type,segment_name order by 4 desc;