数据库常用查询

引自：http://wt871031.blog.163.com/blog/static/648675162010210102133127/

-------根据系统进程PID找到该进程相关信息（sid，sql_text，username等）

登入到oracle的sqlplus，以DBA身份执行:

select se.sid,se.serial#,se.username,se.machine,sq.cpu_time,sq.sql_text,se.STATUS,se.OSUSER,se.logon_time
from sys.v_$session se ,sys.v_$process p,sys.v_$sqlarea sq
where   p.addr=se.paddr and se.sql_hash_value=sq.hash_value
and p.spid=&spid;

-------根据pid可以找出该pid对应的session的 sid 和sql_text：

select se.username,se.machine,se.sid,sq.cpu_time,sq.sql_text from
v$process p,v$session se,v$sqlarea sq
where p.addr=se.paddr and se.sql_hash_value=sq.hash_value and p.pid='&pid';

-------列出cpu_time占用top 10的sql _text：

select cpu_time,sql_text
from (select sql_text,cpu_time,
rank() over (order by cpu_time desc) exec_rank
from v$sql
      )
where exec_rank <=10;

-------执行次数最多的top 10 sql_text：

select sql_text,executions
from (select sql_text,executions,
   rank() over
    (order by executions desc) exec_rank
   from v$sql)
where exec_rank <=10;

------以下两个sql可以查数据库的等待事件和SQL

select event,count(*) from v$session_wait

group by event

order by 2 desc;

select s.sid,s.event,l.SQL_TEXT,l.EXECUTIONS,l.ROWS_PROCESSED,l.DISK_READS,l.MODULE,

l.cpu_time,l.ELAPSED_TIME,e.MACHINE,l.HASH_VALUE from v$session_wait s,v$sqlarea l,v$session e

where s.sid=e.sid and e.SQL_HASH_VALUE=l.HASH_VALUE and s.EVENT like '-------'

-------查询某用户下的表的大小：
select segment_name,sum(bytes)/1024/1024 from dba_segments
where wner='HNUNICOM' AND segment_type='TABLE' GROUP BY segment_name
order by 2 desc

-------查看某表空间某对象的大小：

select segment_name,owner,segment_type,sum(bytes)/1024/1024 from dba_segments
where tablespace_name='CSS_APP'
--AND segment_type='TABLE'
GROUP BY segment_name,owner,segment_type
order by 4 desc

-------查询表空间使用情况：

SELECT D.TABLESPACE_NAME,
     SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY "USED_RATE(%)" DESC