oracle常用经典sql查询Word文档格式.docx

1、（init ial_extent/1024） InitialExtent,（next_extent/1024） NextExtent,max_extents, v.curext CurExtentFrom dba_rollback_segs r, v$rollstat vWhere r.segment_id = v.usn（+）order by segment_name;4 、查看控制文件select name from v$controlfile;5 、查看日志文件select member from v$logf ile;6 、查看表空间的使用情况select sum（bytes）/（10

2、24*1024） as free_space,tablespace_namefrom dba_free_spacegroup by tablespace_name;SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,（B.BYTES*100）/A.BYTES % USED,（C.BYTES*100）/A.BYTES % FREEFROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE CWHERE A.TABLESPACE_NAME=B.TABLESPACE_NA

3、ME ANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;7 、查看数据库库对象select owner, object_type, status, count（*） count# from all_objects group by owner, object_type,status;8 、查看数据库的版本Select version FROM Product_component_version Where SUBSTR（PRODUCT,1,6）=Oracle;9 、查看数据库的创建日期和归档方式Select Created, Log_Mode, Log_Mode F

4、rom V$Database;10 、捕捉运行很久的 SQLcolumn username format a12column opname format a16column progress format a8select username,sid,opname,round（sofar*100 / totalwork,0） | % as progress,time_remaining,sql_textfrom v$session_longops , v$sqlwhere time_remaining 0and sql_address = addressand sql_hash_value =

5、hash_value/11 。查看数据表的参数信息SELECT partition_name, high_value, high_value_length, tablespace_name,pct_free, pct_used, ini_trans, max_trans, init ial_extent,next_extent, min_extent, max_extent, pct_increase, FREELISTS,freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,empty_blocks, avg_space, chain

6、_cnt, avg_row_len, sample_size,last_analyzedFROM dba_tab_partit ions-WHERE table_name = :tname AND table_owner = :townerORDER BY partition_position12. 查看还没提交的事务select * from v$locked_object;select * from v$transaction;13 。查找 object 为哪些进程所用Select p.spid,s.sid,s.serial# serial_num,s.username user_name

7、,a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,decode（sign（48 - command）,1,to_char（command）, Action Code # | to_char（command） ） action,p.program oracle_process,s.terminal terminal,s.program program,s.status session_statusfrom v$session s, v$access a, v$process pwhere s.paddr

8、= p.addr and s.type = USER and a.sid = s.sid and a.object=SUBSCRIBER_ATTRorder by s.username, s.osuser14 。回滚段查看select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extentsExtents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,v$rollstat.gets Gets, v$rollstat.waits Waits, v$ro

9、llstat.writes Writes,sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,v$rollname where v$rollname.name（+） = sys.dba_rollback_segs.segment_name andv$rollstat.usn （+） = v$rollname.usn order by rownum15 。耗资源的进程（ top session ）select s.schemaname schema_name, decode（sign（48 - co

10、mmand）, 1,to_char（command）, | to_char（command） ） action, statussession_status, s.osuser os_user_name, s.sid, p.spid , s.serial#serial_num,nvl（s.username, Oracle process） user_name, s.terminal terminal,s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process pwhere st.sid

11、 = s.sid and st.statistic# = to_number（38） and （ALL = or s.status = ） and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc,s.osuser asc16 。查看锁（ lock ）情况select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,decode（ls.type, RW, Row wait enqueue lockTMDML enqueue lockTX,Tr

12、ansaction enqueue lockULUser supplied lock） lock_type,o.object_name object, decode（ls.lmode, 1, null, 2, Row Share, 3,Row Exclusive, 4, Share, 5, Share Row Exclusive, 6, Exclusive, null）lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2from sys.dba_objects o, （ select s.osuser, s.user

13、name, l.type,l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,v$lock l where s.sid = l.sid ） ls where o.object_id = ls.id1 and o.owner SYS order by o.owner, o.object_name17 。查看等待（ wait ）情况SELECT v$waitstat.class, v$waitstat.count count, SUM（v$sysstat.value） sum_valueFROM v$waitstat, v$syssta

14、t WHERE v$sysstat.name IN （db block getsconsistent gets） group by v$waitstat.class, v$waitstat.count18 。查看 sga 情况SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC19 。查看 catched objectSELECT owner, name, db_link, namespace,type, sharable_mem, loads,executions,locks, pins, kept FROM v$db_object_cache20 。查看 V$SQLAREASELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_ME