oracle常用sql语法集合

oracle常用sql语法集合

表：
　　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 <>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)>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>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;