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 dfwhere f.file#=df.file_id4.监空文件系统的I/O比例：select substr(a.file#,1,2) "#",substr(a.name,1,30) "name",a.status,a.bytes,b.phyrds,b.phywrtsfrom v$datafile a,v$filestat bwhere a.file#=b.file#5.在某个用户下找所有的索引：select user_indexes.table_name, user_indexes.index_name,uniqueness, column_namefrom user_ind_columns, user_indexeswhere user_ind_columns.index_name = user_indexes.index_nameand 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 cwhere 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 <>0group by parameter, gets, getmisses;8. 监控 SGA 中共享缓存区的命中率，应该小于1%select sum(pins) "Total Pins", sum(reloads) "Total Reloads",sum(reloads)/sum(pins) *100 libcachefrom 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) ratio2FROM v$latch WHERE name IN ('redo allocation', 'redo copy');11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_sizeSELECT 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 bwhere 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$ROWCACHE14. 找ORACLE 字符集select * from sys.props$ where name='NLS_CHARACTERSET';15. 监控 MTSselect 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 asselect tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_spaceunion allselect 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(*) extentsFROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_nameHAVING 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多的用户session12是cpu used by this sessionselect a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 valuefrom v$session a,v$process b,v$sesstat cwhere 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 rewhere rbar.name='redo buffer allocation retries'and re.name='redo entries';19、查看运行过的SQL 语句：SELECT SQL _TEXTFROM 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_columnswhere 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 bwhere a.file_id=b.file_id(+)group by a.tablespace_name,a.file_id,a.bytes order by a.tablespace_name查询某个模式下面数据不为空的表declareCursor c is select TNAME from tab;vCount Number;table_nm Varchar2(100);sq varchar2(300);beginfor r in c looptable_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;客户端主机信息SELECTSYS_CONTEXT('USERENV','TERMINAL') TERMINAL,SYS_CONTEXT('USERENV','HOST') HOST,SYS_CONTEXT('USERENV','OS_USER') OS_USER,SYS_CONTEXT('USERENV','IP _ADDRESS') IP _ADDRESSFROM 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 optFROM dba_rollback_segs a , dba_segments b , v$rollname c , v$rollstat dWHERE a.segment_name = b.segment_name AND a.segment_name = c.name (+) AND c.usn = d.usn (+)ORDER BY a.segment_name;
转载链接：http://www.gispower.org/article/db/2007/925/079251397I7DFC178AF6GJ4H8GIBF.html
转载于:https://www.cnblogs.com/wuhenke/archive/2010/07/08/1773311.html
