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

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

《oracle常用经典sql查询Word文档格式.docx》由会员分享，可在线阅读，更多相关《oracle常用经典sql查询Word文档格式.docx（30页珍藏版）》请在冰豆网上搜索。

（initial_extent/1024）InitialExtent,（next_extent/1024）NextExtent,

max_extents,v.curextCurExtent

Fromdba_rollback_segsr,v$rollstatv

Wherer.segment_id=v.usn（+）

orderbysegment_name;

4、查看控制文件

selectnamefromv$controlfile;

5、查看日志文件

selectmemberfromv$logfile;

6、查看表空间的使用情况

selectsum（bytes）/（1024*1024）asfree_space,tablespace_name

fromdba_free_space

groupbytablespace_name;

SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,

（B.BYTES*100）/A.BYTES"

%USED"

（C.BYTES*100）/A.BYTES"

%FREE"

FROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREEC

WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEAND

A.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、查看数据库库对象

selectowner,object_type,status,count（*）count#fromall_objectsgroupbyowner,object_type,

status;

8、查看数据库的版本

SelectversionFROMProduct_component_versionWhereSUBSTR（PRODUCT,1,6）='

Oracle'

;

9、查看数据库的创建日期和归档方式

SelectCreated,Log_Mode,Log_ModeFromV$Database;

10、捕捉运行很久的SQL

columnusernameformata12

columnopnameformata16

columnprogressformata8

selectusername,sid,opname,

round（sofar*100/totalwork,0）||'

%'

asprogress,time_remaining,sql_text

fromv$session_longops,v$sql

wheretime_remaining<

>

0

andsql_address=address

andsql_hash_value=hash_value

/

11。

查看数据表的参数信息

SELECTpartition_name,high_value,high_value_length,tablespace_name,pct_free,pct_used,ini_trans,max_trans,initial_extent,next_extent,min_extent,max_extent,pct_increase,FREELISTS,freelist_groups,LOGGING,BUFFER_POOL,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,sample_size,last_analyzed

FROMdba_tab_partitions

--WHEREtable_name=:

tnameANDtable_owner=:

towner

ORDERBYpartition_position

12.查看还没提交的事务

select*fromv$locked_object;

select*fromv$transaction;

13。

查找object为哪些进程所用

Selectp.spid,s.sid,s.serial#serial_num,s.usernameuser_name,a.typeobject_type,s.osuseros_user_name,a.owner,a.objectobject_name,

decode（sign（48-command）,1,to_char（command）,'

ActionCode#'

||to_char（command））action,

p.programoracle_process,s.terminalterminal,s.programprogram,s.statussession_status

fromv$sessions,v$accessa,v$processp

wheres.paddr=p.addrands.type='

USER'

anda.sid=s.sidanda.object='

SUBSCRIBER_ATTR'

orderbys.username,s.osuser14。

回滚段查看

selectrownum,sys.dba_rollback_segs.segment_nameName,v$rollstat.extents

Extents,v$rollstat.rssizeSize_in_Bytes,v$rollstat.xactsXActs,

v$rollstat.getsGets,v$rollstat.waitsWaits,v$rollstat.writesWrites,

sys.dba_rollback_segs.statusstatusfromv$rollstat,sys.dba_rollback_segs,

v$rollnamewherev$rollname.name（+）=sys.dba_rollback_segs.segment_nameand

v$rollstat.usn（+）=v$rollname.usnorderbyrownum

15。

耗资源的进程（topsession）

selects.schemanameschema_name,decode（sign（48-command）,1,to_char（command）,'

||to_char（command））action,status

session_status,s.osuseros_user_name,s.sid,p.spid,s.serial#

serial_num,nvl（s.username,'

[Oracleprocess]'

）user_name,s.terminalterminal,s.programprogram,st.valuecriteria_valuefromv$sesstatst,v$sessions,v$processp

wherest.sid=s.sidandst.statistic#=to_number（'

38'

）and（'

ALL'

='

ors.status='

）andp.addr=s.paddrorderbyst.valuedesc,p.spidasc,s.usernameasc,

s.osuserasc

16。

查看锁（lock）情况

select/*+RULE*/ls.osuseros_user_name,ls.usernameuser_name,decode（ls.type,'

RW'

'

Rowwaitenqueuelock'

TM'

DMLenqueuelock'

TX'

'

Transactionenqueuelock'

UL'

Usersuppliedlock'

）lock_type,o.object_nameobject,decode（ls.lmode,1,null,2,'

RowShare'

3,

'

RowExclusive'

4,'

Share'

5,'

ShareRowExclusive'

6,'

Exclusive'

null）lock_mode,o.owner,ls.sid,ls.serial#serial_num,ls.id1,ls.id2

fromsys.dba_objectso,（selects.osuser,s.username,l.type,l.lmode,s.sid,s.serial#,l.id1,l.id2fromv$sessions,v$locklwheres.sid=l.sid）lswhereo.object_id=ls.id1ando.owner<

'

SYS'

orderbyo.owner,o.object_name

17。

查看等待（wait）情况

SELECTv$waitstat.class,v$waitstat.countcount,SUM（v$sysstat.value）sum_value

FROMv$waitstat,v$sysstatWHEREv$sysstat.nameIN（'

dbblockgets'

consistentgets'

）

groupbyv$waitstat.class,v$waitstat.count

18。

查看sga情况

SELECTNAME,BYTESFROMSYS.V_$SGASTATORDERBYNAMEASC

19。

查看catchedobject

SELECTowner,name,db_link,namespace,type,sharable_mem,loads,executions,locks,pins,keptFROMv$db_object_cache

20。

查看V$SQLAREA

SELECTSQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_ME