oracle查询语句

查看表空间使用情况

SELECT 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, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name

查看锁表情况

-- 查看哪些表被锁
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

-- 查看哪个进程锁表
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

-- 杀掉进程
alter system kill session 'sid,serial#';

查看正在执行的sql

SELECT b.sid,  
       b.username 用户名,  
       b.serial#,  
       spid 操作系统ID,
       paddr,  
       sql_text 正在执行的SQL,  
       b.machine 计算机名称
FROM v$process a, v$session b, v$sqlarea c  
WHERE a.addr = b.paddr  
   AND b.sql_hash_value = c.hash_value 

oracle查询含有某个字段的表

select table_name  from DBA_TAB_COLUMNS where COLUMN_NAME='字段名';

ORA-00031: session marked for kill

使用下面sql查询正在执行的sql进程号：

select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=123

在linux上通过 kill -9 spid 杀掉进程