---
title: "ORACLE常用SQL"
author: "winzenghua"
date: "2009-09-25T10:20:00Z"
tags: []
slug: "oracle-sql"
draft: false
lastmod: "2009-09-25T10:20:00Z"
id: 987949
url: "https://java4gis.iteye.com/blog/987949"
---

表：  
　　select * from cat;  
　　select * from tab;  
　　select table\_name from user\_tables;  
视图：  
　　select text from user\_views where view\_name=upper('&view\_name');  
索引：  
　　select index\_name,table\_owner,table\_name,tablespace\_name,status from user\_indexes order by table\_name;  
触发器：  
　　select trigger\_name,trigger\_type,table\_owner,table\_name,status from user\_triggers;  
快照：  
　　select owner,name,master,table\_name,last\_refresh,next from user\_snapshots order by owner,next;  
同义词：  
　　select * from syn;  
序列：  
　　select * from seq;  
数据库链路：  
　　select * from user\_db\_links;  
约束限制：  
　　select TABLE\_NAME,CONSTRAINT\_NAME,SEARCH\_CONDITION,STATUS  
from user\_constraints WHERE TABLE\_name=upper('&TABLE\_Name');  
本用户读取其他用户对象的权限：  
　　select * from user\_tab\_privs;  
本用户所拥有的系统权限：  
　　select * from user\_sys\_privs;  
用户：  
　　select * from all\_users order by user\_id;  
表空间剩余自由空间情况：  
　　select tablespace\_name,sum(bytes) 总字节数,max(bytes),count(\*) from dba\_free\_space group by tablespace\_name；  
数据字典：  
　　select table\_name from dict order by table\_name;  
锁及资源信息：  
　　select * from v$lock;不包括DDL锁  
数据库字符集：  
　　select name,value$ from props$ where name='NLS\_CHARACTERSET';  
inin.ora参数：  
　　select name,value from v$parameter order by name;  
SQL 共享池：  
　　select sql \_text from v$sqlarea;  
数据库：  
　　select * from v$database  
控制文件：  
　　select * from V$controlfile;  
重做日志文件信息：  
　　select * from V$logfile;  
来自控制文件中的日志文件信息：  
　　select * from V$log;  
来自控制文件中的数据文件信息：  
　　select * from V$datafile;  
NLS参数当前值：  
　　select * from V$nls\_parameters;  
ORACLE 版本信息：  
　　select * from v$version;  
描述后台进程：  
　　select * from v$bgprocess;  
查看版本信息：  
　　select * from product\_component\_version;  
Oracle -常用监控SQL  
1.监控事例的等待：  
select event,sum(decode(wait\_time,0,0,1)) prev, sum(decode(wait\_time,0,1,0)) curr,count(\*)  
from v$session\_wait  
group by event order by 4;  
2.回滚段的争用情况：  
select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;  
3.监控表空间的I/O比例：  
select df.tablespace\_name name,df.file\_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,  
f.phyblkwrt pbw  
from v$filestat f,dba\_data\_files df  
where f.file#=df.file\_id  
4.监空文件系统的I/O比例：  
select substr(a.file#,1,2) "#",substr(a.name,1,30) "name",a.status,a.bytes,  
b.phyrds,b.phywrts  
from v$datafile a,v$filestat b  
where a.file#=b.file#  
5.在某个用户下找所有的索引：  
select user\_indexes.table\_name, user\_indexes.index\_name,uniqueness, column\_name  
from user\_ind\_columns, user\_indexes  
where user\_ind\_columns.index\_name = user\_indexes.index\_name  
and user\_ind\_columns.table\_name = user\_indexes.table\_name  
order by user\_indexes.table\_type, user\_indexes.table\_name,  
user\_indexes.index\_name, column\_position;  
6\. 监控 SGA 的命中率  
select a.value + b.value "logical\_reads", c.value "phys\_reads",  
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"  
from v$sysstat a, v$sysstat b, v$sysstat c  
where a.statistic# = 38 and b.statistic# = 39  
and c.statistic# = 40;  
7\. 监控 SGA 中字典缓冲区的命中率  
select parameter, gets,Getmisses , getmisses/(gets+getmisses)\*100 "miss ratio",  
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))\*100 "Hit ratio"  
from v$rowcache  
where gets+getmisses &lt;&gt;0  
group by parameter, gets, getmisses;  
8\. 监控 SGA 中共享缓存区的命中率，应该小于1%  
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",  
sum(reloads)/sum(pins) \*100 libcache  
from v$librarycache;  
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"  
from v$librarycache;  
9\. 显示所有数据库对象的类别和大小  
select count(name) num\_instances ,type ,sum(source\_size) source\_size ,  
sum(parsed\_size) parsed\_size ,sum(code\_size) code\_size ,sum(error\_size) error\_size,  
sum(source\_size) +sum(parsed\_size) +sum(code\_size) +sum(error\_size) size\_required  
from dba\_object\_size  
group by type order by 2;  
10\. 监控 SGA 中重做日志缓存区的命中率，应该小于1%  
SELECT name, gets, misses, immediate\_gets, immediate\_misses,  
Decode(gets,0,0,misses/gets\*100) ratio1,  
Decode(immediate\_gets+immediate\_misses,0,0,  
immediate\_misses/(immediate\_gets+immediate\_misses)\*100) ratio2  
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');  
11\. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort\_area\_size  
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');  
12\. 监控当前数据库谁在运行什么SQL 语句  
SELECT osuser, username, sql \_text from v$session a, v$sqltext b  
where a.sql \_address =b.address order by address, piece;  
13\. 监控字典缓冲区  
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;  
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;  
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;  
后者除以前者,此比率小于1%,接近0%为好。  
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"  
FROM V$ROWCACHE  
14\. 找ORACLE 字符集  
select * from sys.props$ where name='NLS\_CHARACTERSET';  
15\. 监控 MTS  
select busy/(busy+idle) "shared servers busy" from v$dispatcher;  
此值大于0.5时，参数需加大  
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';  
select count(\*) from v$dispatcher;  
select servers\_highwater from v$mts;  
servers\_highwater接近mts\_max\_servers时，参数需加大  
16\. 碎片程度  
select tablespace\_name,count(tablespace\_name) from dba\_free\_space group by tablespace\_name  
having count(tablespace\_name)&gt;10;  
alter tablespace name coalesce;  
alter table name deallocate unused;  
create or replace view ts\_blocks\_v as  
select tablespace\_name,block\_id,bytes,blocks,'free space' segment\_name from dba\_free\_space  
union all  
select tablespace\_name,block\_id,bytes,blocks,segment\_name from dba\_extents;  
select * from ts\_blocks\_v;  
select tablespace\_name,sum(bytes),max(bytes),count(block\_id) from dba\_free\_space  
group by tablespace\_name;  
查看碎片程度高的表  
SELECT segment\_name table\_name , COUNT(\*) extents  
FROM dba\_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment\_name  
HAVING COUNT(\*) = (SELECT MAX( COUNT(\*) ) FROM dba\_segments GROUP BY segment\_name);  
17\. 表、索引的存储情况检查  
select segment\_name,sum(bytes),count(\*) ext\_quan from dba\_extents where  
tablespace\_name='&tablespace\_name' and segment\_type='TABLE' group by tablespace\_name,segment\_name;  
select segment\_name,count(\*) from dba\_extents where segment\_type='INDEX' and owner='&owner'  
group by segment\_name;  
18、找使用CPU多的用户session  
12是cpu used by this session  
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value  
from v$session a,v$process b,v$sesstat c  
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;  
20.监控log\_buffer的使用情况：(值最好小于1%，否则增加log\_buffer 的大小)  
select rbar.name,rbar.value,re.name,re.value,(rbar.value\*100)/re.value||'%' "radio"  
from v$sysstat rbar,v$sysstat re  
where rbar.name='redo buffer allocation retries'  
and re.name='redo entries';  
19、查看运行过的SQL 语句：  
SELECT SQL \_TEXT  
FROM V$SQL  
Oracle 一些常用的SQL  
查询表结构  
select substr(table\_name,1,20) tabname,  
substr(column\_name,1,20)column\_name,  
rtrim(data\_type)||'('||data\_length||')' from system.dba\_tab\_columns  
where owner='username'  
表空间使用状态  
select a.file\_id "FileNo",a.tablespace\_name "Tablespace\_name",  
round(a.bytes/1024/1024,4) "Total MB",  
round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4) "Used MB",  
round(sum(nvl(b.bytes,0))/1024/1024,4) "Free MB",  
round(sum(nvl(b.bytes,0))/a.bytes\*100,4) "%Free"  
from dba\_data\_files a, dba\_free\_space b  
where a.file\_id=b.file\_id(+)  
group by a.tablespace\_name,  
a.file\_id,a.bytes order by a.tablespace\_name  
查询某个模式下面数据不为空的表  
declare  
Cursor c is select TNAME from tab;  
vCount Number;  
table\_nm Varchar2(100);  
sq varchar2(300);  
begin  
for r in c loop  
table\_nm:=r.TNAME;  
sq:='select count(\*) from '|| table\_nm;  
execute immediate sq into vCount;  
if vCount&gt;0 then  
dbms\_output.put\_line(r.tname);  
end if;  
end loop;  
end;  
客户端主机信息  
SELECT  
SYS\_CONTEXT('USERENV','TERMINAL') TERMINAL,  
SYS\_CONTEXT('USERENV','HOST') HOST,  
SYS\_CONTEXT('USERENV','OS\_USER') OS\_USER,  
SYS\_CONTEXT('USERENV','IP \_ADDRESS') IP \_ADDRESS  
FROM DUAL  
查看回滚段名称及大小  
COLUMN roll\_name FORMAT a13 HEADING 'Rollback Name'  
COLUMN tablespace FORMAT a11 HEADING 'Tablspace'  
COLUMN in\_extents FORMAT a20 HEADING 'Init/Next Extents'  
COLUMN m\_extents&nbsp; FORMAT a10 HEADING 'Min/Max Extents'  
COLUMN status FORMAT a8 HEADING 'Status'  
COLUMN wraps FORMAT 999 HEADING 'Wraps'  
COLUMN shrinks FORMAT 999 HEADING 'Shrinks'  
COLUMN opt FORMAT 999,999,999 HEADING 'Opt. Size'  
COLUMN bytes FORMAT 999,999,999 HEADING 'Bytes'  
COLUMN extents FORMAT 999 HEADING 'Extents'  
SELECT  
a.owner || '.' || a.segment\_name roll\_name  
, a.tablespace\_name tablespace  
, TO\_CHAR(a.initial\_extent) || ' / ' ||  
TO\_CHAR(a.next\_extent) in\_extents  
, TO\_CHAR(a.min\_extents) || ' / ' ||  
TO\_CHAR(a.max\_extents) m\_extents  
, a.status status  
, b.bytes bytes  
, b.extents extents  
, d.shrinks shrinks  
, d.wraps wraps  
, d.optsize opt  
FROM  
dba\_rollback\_segs a  
, dba\_segments b  
, v$rollname c  
, v$rollstat d  
WHERE  
a.segment\_name = b.segment\_name  
AND a.segment\_name = c.name (+)  
AND c.usn = d.usn (+)  
ORDER BY a.segment\_name;