前言

兜兜转转，记得刚毕业的时候，使用得是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) FREEFROM   DBA_FREE_SPACEGROUP  BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,--  file_name,SUM(BYTES) TOTALFROM   DBA_DATA_FILESGROUP  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;