数据库常用查询
引自: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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/276487/viewspace-673776/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/276487/viewspace-673776/