MSSQL网页设计DBA经常使用SQL语句
“数据行级锁定”的意思是指在事务操作的执行过程中锁定正在被处理的个别记录，不让其他用户进行访问。这种锁定将影响到（但不限于）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE命令以及INSERT、UPDATE和DELETE命令。语句本人总结的经常使用SQL语句，发明对本人事情匡助挺年夜的！
检察表空间的称号及巨细:
SQL>selectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_size
fromdba_tablespacest,dba_data_filesdwheret.tablespace_name=d.tablespace_namegroupbyt.tablespace_name;
检察表空间物理文件的称号及巨细:
SQL>selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name;
检察回滚段称号及巨细:
SQL>selectsegment_name,tablespace_name,r.status,
(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,
max_extents,v.curextCurExtentFromdba_rollback_segsr,v$rollstatv
Wherer.segment_id=v.usn(+)orderbysegment_name;
怎样检察某个回滚段内里，跑的甚么事物大概正在实行甚么sql语句:
SQL>selectd.sql_text,a.name
fromv$rollnamea,v$transactionb,v$sessionc,v$sqltextd
wherea.usn=b.xidusnandb.addr=c.taddrandc.sql_address=
d.addressandc.sql_hash_value=d.hash_value
anda.usn=1;
(备注：你要看哪一个，就把usn=?写成几就好了)
检察把持文件:
SQL>select*fromv$controlfile;
检察日记文件:
SQL>colmemberformata50
SQL>select*fromv$logfile;
怎样检察以后SQL*PLUS用户的sid和serial#:
SQL>selectsid,serial#,statusfromv$sessionwhereaudsid=userenv(sessionid);
怎样检察以后数据库的字符集:
SQL>selectuserenv(language)fromdual;
SQL>selectuserenv(lang)fromdual;
怎样判别以后正在利用何种SQL优化体例:
用explainplan发生EXPLAINPLAN，反省PLAN_TABLE中ID=0的POSITION列的值。
SQL>selectdecode(nvl(position,-1),-1,RBO,1,CBO)fromplan_tablewhereid=0;
怎样检察体系以后最新的SCN号：
SQL>selectmax(ktuxescnw*power(2,32)+ktuxescnb)fromx$ktuxe;
在ORACLE中查找TRACE文件的剧本:
SQL>selectu_dump.value||/||instance.value||_ora_||
v$process.spid||nvl2(v$process.traceid,_||v$process.traceid,null)||.trc"TraceFile"fromv$parameteru_dumpcrossjoinv$parameterinstancecrossjoinv$processjoinv$sessiononv$process.addr=v$session.paddrwhereu_dump.name=user_dump_destand
instance.name=instance_nameandv$session.audsid=sys_context(userenv,sessionid);
SQL>selectd.value||/ora_||p.spid||.trctrace_file_name
from(selectp.spidfromsys.v_$mystatm,sys.v_$sessions,
sys.v_$processpwherem.statistic#=1and
s.sid=m.sidandp.addr=s.paddr)p,(selectvaluefromsys.v_$parameterwherename=user_dump_dest)d;
怎样检察客户端上岸的IP地点:
SQL>selectsys_context(userenv,ip_address)fromdual;
怎样在临盆数据库中创立一个追踪客户端IP地点的触发器：
SQL>createorreplacetriggeron_logon_trigger
afterlogonondatabase
begin
dbms_application_info.set_client_info(sys_context(userenv,ip_address));
end;
REM纪录上岸信息的触发器
CREATEORREPLACETRIGGERLOGON_HISTORY
AFTERLOGONONDATABASE--WHEN(USER=WACOS)--ONLYFORUSERWACOS
BEGIN
insertintosession_history
selectusername,sid,serial#,AUDSID,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT(USERENV,IP_ADDRESS),TERMINAL,machine,PROGRAM
fromv$sessionwhereaudsid=userenv(sessionid);
END;
查询以后日期:
SQL>selectto_char(sysdate,yyyy-mm-dd,hh24:mi:ss)fromdual;
检察一切表空间对应的数据文件名：
SQL>selectdistinctfile_name,tablespace_name,AUTOEXTENSIBLEfromdba_data_files;
检察表空间的利用情形:
SQL>selectsum(bytes)/(1024*1024)asfree_space,tablespace_name
fromdba_free_spacegroupbytablespace_name;
SQL>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_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;
columntablespace_nameformata18;
columnSum_Mformata12;
columnUsed_Mformata12;
columnFree_Mformata12;
columnpto_Mformat9.99;
selects.tablespace_name,ceil(sum(s.bytes/1024/1024))||MSum_M,ceil(sum(s.UsedSpace/1024/1024))||MUsed_M,ceil(sum(s.FreeSpace/1024/1024))||MFree_M,sum(s.UsedSpace)/sum(s.bytes)PTUSED
from(selectb.file_id,b.tablespace_name,b.bytes,
(b.bytes-sum(nvl(a.bytes,0)))UsedSpace,
sum(nvl(a.bytes,0))FreeSpace,(sum(nvl(a.bytes,0))/(b.bytes))*100FreePercentRatiofromsys.dba_free_spacea,sys.dba_data_filesb
wherea.file_id(+)=b.file_idgroupbyb.file_id,b.tablespace_name,b.bytes
orderbyb.tablespace_name)sgroupbys.tablespace_name
orderbysum(s.FreeSpace)/sum(s.bytes)desc;
检察数据文件的hwm（能够resize的最小空间）和文件头巨细:
SELECTv1.file_name,v1.file_id,
num1totle_space,
num3free_space,
num1-num3"USED_SPACE(HWM)",
nvl(num2,0)data_space,
num1-num3-nvl(num2,0)file_head
FROM
(SELECTfile_name,file_id,SUM(bytes)num1FROMDba_Data_FilesGROUPBYfile_name,file_id)v1,
(SELECTfile_id,SUM(bytes)num2FROMdba_extentsGROUPBYfile_id)v2,
(SELECTfile_id,SUM(BYTES)num3FROMDBA_FREE_SPACEGROUPBYfile_id)v3
WHEREv1.file_id=v2.file_id(+)
ANDv1.file_id=v3.file_id(+);
数据文件巨细及头巨细:
SELECTv1.file_name,v1.file_id,
num1totle_space,
num3free_space,
num1-num3Used_space,
nvl(num2,0)data_space,
num1-num3-nvl(num2,0)file_head
FROM
(SELECTfile_name,file_id,SUM(bytes)num1FROMDba_Data_FilesGROUPBYfile_name,file_id)v1,
(SELECTfile_id,SUM(bytes)num2FROMdba_extentsGROUPBYfile_id)v2,
(SELECTfile_id,SUM(BYTES)num3FROMDBA_FREE_SPACEGROUPBYfile_id)v3
WHEREv1.file_id=v2.file_id(+)
ANDv1.file_id=v3.file_id(+);
(运转以上查询，我们能够以下信息：
Totle_pace:该数据文件的总巨细，字节为单元
Free_space:该数据文件的剩于巨细，字节为单元
Used_space:该数据文件的已用空间，字节为单元
Data_space:该数据文件中段数据占用空间，也就是数据空间，字节为单元
File_Head:该数据文件头部占用空间，字节为单元)
数据库各个表空间增加情形的反省：
SQL>selectA.tablespace_name,(1-(A.total)/B.total)*100used_percent
From(selecttablespace_name,sum(bytes)totalfromdba_free_spacegroupbytablespace_name)A,(selecttablespace_name,sum(bytes)totalfromdba_data_filesgroupbytablespace_name)BwhereA.tablespace_name=B.tablespace_name;
SQL>SELECTUPPER(F.TABLESPACE_NAME)"表空间名",
D.TOT_GROOTTE_MB"表空间巨细(M)",
D.TOT_GROOTTE_MB-F.TOTAL_BYTES"已利用空间(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),990.99)"利用比",F.TOTAL_BYTES"余暇空间(M)",
F.MAX_BYTES"最年夜块(M)"FROM(SELECTTABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,
ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTES
FROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,
(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDD
GROUPBYDD.TABLESPACE_NAME)DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAME
ORDERBY4DESC;
检察各个表空间占用磁盘情形:
SQL>coltablespace_nameformata20;
SQL>selectb.file_idfile_ID,
b.tablespace_nametablespace_name,
b.bytesBytes,
(b.bytes-sum(nvl(a.bytes,0)))used,
sum(nvl(a.bytes,0))free,
sum(nvl(a.bytes,0))/(b.bytes)*100Percent
fromdba_free_spacea,dba_data_filesb
wherea.file_id=b.file_id
groupbyb.tablespace_name,b.file_id,b.bytes
orderbyb.file_id;
数据库工具下一扩大与表空间的free扩大值的反省：
SQL>selecta.table_name,a.next_extent,a.tablespace_name
fromall_tablesa,(selecttablespace_name,max(bytes)asbig_chunk
fromdba_free_spacegroupbytablespace_name)fwheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunk
unionselecta.index_name,a.next_extent,a.tablespace_name
fromall_indexesa,(selecttablespace_name,max(bytes)asbig_chunk
fromdba_free_spacegroupbytablespace_name)fwheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunk;
DiskRead最高的SQL语句的猎取：
SQL>selectsql_textfrom(select*fromv$sqlareaorderbydisk_reads)
whererownum<=5;
查找前十条功能差的sql
SELECT*FROM(SELECTPARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
sql_textFROMv$sqlareaORDERBYdisk_readsDESC)
WHEREROWNUM<10;
守候工夫最多的5个体系守候事务的猎取：
SQL>select*from(select*fromv$system_eventwhereeventnotlikeSQL%orderbytotal_waitsdesc)whererownum<=5;
检察以后守候事务的会话:
colusernameformata10
setline120
colEVENTformata30
selectSE.Sid,s.Username,SE.Event,se.Total_Waits,SE.Time_Waited,SE.Average_Wait
fromv$sessionS,v$session_eventSEwhereS.UsernameisnotnullandSE.Sid=S.Sid
andS.Status=ACTIVEandSE.Eventnotlike%SQL*Net%;
selectsid,event,p1,p2,p3,wait_time,seconds_in_wait,statefromv$session_waitwhereeventnotlike%message%andeventnotlikeSQL*Net%andeventnotlike%timer%andevent!=wakeuptimemanager;
找到与所毗连的会话有关确当前守候事务：
selectSW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_WaitSEC_IN_WAIT
fromv$sessionS,v$session_waitSWwhereS.UsernameisnotnullandSW.Sid=S.Sid
andSW.eventnotlike%SQL*Net%orderbySW.Wait_TimeDesc;
Oracle一切回滚段形态的反省：
SQL>selectsegment_name,owner,tablespace_name,initial_extent,next_extent,dba_rollback_segs.statusfromdba_rollback_segs,v$datafilewherefile_id=file#;
Oracle回滚段扩大信息的反省:
colnameformata10
setlinesize140
selectsubstr(name,1,40)name,extents,rssize,optsize,aveactive,extends,wraps,shrinks,hwmsize
fromv$rollnamern,v$rollstatrswhere(rn.usn=rs.usn);
extents:回滚段中的盘区数目。
Rssize:以字节为单元的回滚段的尺寸。
optsize：为optimal参数设定的值。
Aveactive:从回滚段中删除盘区时开释的以字节为单元的均匀空间的巨细。
Extends:体系为回滚段增添的盘区的次数。
Shrinks:体系从回滚段中扫除盘区（即回滚段压缩）的次数。回滚段每次扫除盘区时，体系大概会从这个回滚段中打消一个或多个盘区。
Hwmsize:回滚段尺寸的下限，即回滚段已经到达的最年夜尺寸。
(假如回滚段均匀尺寸靠近OPTIMAL的值，那末申明OPTIMAL的值设置准确，假如回滚段静态增加次数或压缩次数很高，那末必要进步OPTIMAL的值)
检察回滚段的利用情形，哪一个用户正在利用回滚段的资本:
selects.username,u.namefromv$transactiont,v$rollstatr,
v$rollnameu,v$sessionswheres.taddr=t.addrand
t.xidusn=r.usnandr.usn=u.usnorderbys.username;
怎样检察一下某个shared_server正在忙甚么:
SELECTa.username,a.machine,a.program,a.sid,
a.serial#,a.status,c.piece,c.sql_text
FROMv$sessiona,v$processb,v$sqltextc
WHEREb.spid=13161ANDb.addr=a.paddr
ANDa.sql_address=c.address(+)ORDERBYc.piece;
数据库共享池功能反省:
Selectnamespace,gets,gethitratio,pins,pinhitratio,reloads,
Invalidationsfromv$librarycachewherenamespacein
(SQLAREA,TABLE/PROCEDURE,BODY,TRIGGER);
反省数据重载比率:
selectsum(reloads)/sum(pins)*100"reloadratio"from
v$librarycache;
反省数据字典的射中率:
select1-sum(getmisses)/sum(gets)"datadictionaryhit
ratio"fromv$rowcache;
(关于librarycache,gethitratio和pinhitratio应当年夜于90%,关于数据重载比率,reloadratio应当小于1%,关于数据字典的射中率,datadictionaryhitratio应当年夜于85%)
反省共享内存的残剩情形:
selectrequest_misses,request_failuresfromv$shared_pool_reserved;
(关于共享内存的残剩情形,request_misses和request_failures应当靠近0)
数据高速缓冲区功能反省:
select1-p.value/(b.value+c.value)"dbbuffercachehit
ratio"fromv$sysstatp,v$sysstatb,v$sysstatcwhere
p.name=physicalreadsandb.name=dbblockgetsand
c.name=consistentgets;
反省bufferpoolHIT_RATIO实行
selectname,(physical_reads/(db_block_gets+consistent_gets))
"MISS_HIT_RATIO"FROMv$buffer_pool_statisticsWHERE(db_block_gets+consistent_gets)>0;
(一般时dbbuffercachehitratio应当年夜于90%,一般时bufferpoolMISS_HIT_RATIO应当小于10%)
数据库回滚段功能反省:
反省Ratio实行
selectsum(waits)*100/sum(gets)"Ratio",sum(waits)
"Waits",sum(gets)"Gets"fromv$rollstat;
反省count/value实行:
selectclass,countfromv$waitstatwhereclasslike%undo%;
selectvaluefromv$sysstatwherename=consistentgets;
(二者的value值相除)
反省average_wait实行:
selectevent,total_waits,time_waited,average_waitfromv$system_event
whereeventlike%undo%;
反省RBSheadergetratio实行:
selectn.name,s.usn,s.wraps,decode(s.waits,0,1,1-s.waits/s.gets)"RBS
headergetratio"fromv$rollstats,v$rollnamenwheres.usn=n.usn;
(一般时Ratio应当小于1%,count/value应当小于0.01%,average_wait最好为0，该值越小越好,RBSheadergetratio应当年夜于95%)
杀会话的剧本:
selectA.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600LAST_HOUR,A.STATUS,
orakill||sid||||spidHOST_COMMAND,
altersystemkillsession||A.sid||,||A.SERIAL#||SQL_COMMAND
fromv$sessionA,V$PROCESSBwhereA.PADDR=B.ADDRANDSID>6;
检察排序段的功能:
SQL>SELECTname,valueFROMv$sysstatWHEREnameIN(sorts(memory),sorts(disk));
7、检察数据库库工具:
selectowner,object_type,status,count(*)count#fromall_objectsgroupbyowner,object_type,status;
8、检察数据库的版本:
Select*fromv$version;
9、检察数据库的创立日期和回档体例:
SelectCreated,Log_Mode,Log_ModeFromV$Database;
10、捕获运转好久的SQL:
columnusernameformata12
columnopnameformata16
columnprogressformata8
selectusername,sid,opname,round(sofar*100/totalwork,0)||%asprogress,time_remaining,sql_textfromv$session_longops,v$sqlwheretime_remaining0andsql_address=addressandsql_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_analyzedFROMdba_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_statusfromv$sessions,v$accessa,v$processpwheres.paddr=p.addrands.type=USERanda.sid=s.sidanda.object=SUBSCRIBER_ATTRorderbys.username,s.osuser;
14、检察回滚段:
SQL>colnameformata10
SQL>setlinesize100
SQL>selectrownum,sys.dba_rollback_segs.segment_nameName,v$rollstat.extentsExtents,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_nameandv$rollstat.usn(+)=v$rollname.usnorderbyrownum;
15、耗资本的历程(topsession):
selects.schemanameschema_name,decode(sign(48-command),1,to_char(command),ActionCode#||to_char(command))action,statussession_status,s.osuseros_user_name,s.sid,p.spid,s.serial#serial_num,nvl(s.username,)user_name,s.terminalterminal,s.programprogram,st.valuecriteria_valuefromv$sesstatst,v$sessions,v$processpwherest.sid=s.sidandst.statistic#=to_number(38)and(ALL=ALLors.status=ALL)andp.addr=s.paddrorderbyst.valuedesc,p.spidasc,s.usernameasc,s.osuserasc;
依据PID查找响应的语句:
SELECTa.username,
a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text
FROMv$sessiona,v$processb,v$sqltextcWHEREb.spid=spid
ANDb.addr=a.paddrANDa.sql_address=c.address(+)ORDERBYc.piece;
依据SID找ORACLE的某个历程:
SQL>selectpro.spidfromv$sessionses,v$processprowhereses.sid=21andses.paddr=pro.addr;
监控以后数据库谁在运转甚么SQL语句:
SQL>SELECTosuser,username,sql_textfromv$sessiona,v$sqltextb
wherea.sql_address=b.addressorderbyaddress,piece;
怎样检察数据库中某用户，正在运转甚么SQL语句
SQL>SELECTSQL_TEXTFROMV$SQLTEXTT,V$SESSIONSWHERET.ADDRESS=S.SQL_ADDRESS
ANDT.HASH_VALUE=S.SQL_HASH_VALUEANDS.MACHINE=XXXXXORUSERNAME=WACOS;
怎样查出前台正在收回的sql语句:
SQL>selectuser_name,sql_textfromv$open_cursorwheresidin(selectsidfrom(selectsid,serial#fromv$sessionwherestatus=ACTIVE));
查询以后所实行的SQL语句：
SQL>selectprogram,sql_addressfromv$sessionwherepaddrin(selectaddr
fromv$processwherespid=3556);
PROGRAMSQL_ADDRESS
----------------------------------------------------------------
sqlplus@ctc20(TNSV1-V3)000000038FCB1A90
SQL>selectsql_textfromv$sqlareawhereaddress=000000038FCB1A90;
找出损耗CPU最高的历程对应的SQL语句：
setline240
setverifyoff
columnsidformat999
columnpidformat999
columnS_#format999
columnusernameformatA9heading"ORAUser"
columnprogramformata29
columnSQLformata60
COLUMNOSnameformata9Heading"OSUser"
SELECTP.pidpid,S.sidsid,P.spidspid,S.usernameusername,
S.osuserosname,P.serial#S_#,P.terminal,P.programprogram,
P.background,S.status,RTRIM(SUBSTR(a.sql_text,1,80))SQL
FROMv$processP,v$sessionS,v$sqlareaAWHEREP.addr=s.paddr
ANDS.sql_address=a.address(+)ANDP.spidLIKE%&1%;
Entervaluefor1:PID（这里输出占用CPU最高的历程对应的PID）
settermoutoff
spoolmaxcpu.txt
SELECT++||S.usernameusername,
RTRIM(REPLACE(a.sql_text,chr(10),))||;FROMv$processP,v$sessionS,
v$sqlareaAWHEREP.addr=s.paddrANDS.sql_address=a.address(+)
ANDP.spidLIKE%&&1%;
Entervaluefor1:PID（这里输出占用CPU最高的历程对应的PID）
spooloff(这句放在最初实行)
CPU用率最高的2条SQL语句的猎取
实行：top，经由过程top取得CPU占用率最高的历程的pid。
SQL>selectsql_text,spid,v$session.program,processfromv$sqlarea,v$session,v$processwherev$sqlarea.address=v$session.sql_addressandv$sqlarea.hash_value=v$session.sql_hash_value
andv$session.paddr=v$process.addrandv$process.spidin(pid);
colmachineformata30
colprogramformata40
setline200
SQL>selectsid,serial#,username,osuser,machine,program,process,to_char(logon_time,yyyy/mm/ddhh24:mi:ss)fromv$sessionwherepaddrin(selectaddrfromv$processwherespidin([$spid]));
selectsql_textfromv$sqltext_with_newlines
wherehash_value=(selectSQL_HASH_VALUEfromv$sessionwheresid=&sid)
orderbypiece;
16、检察锁（lock）情形:
SQL>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.id2fromsys.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.ownerSYSorderbyo.owner,o.object_name;
SQL>selectsys.v_$session.osuser,sys.v_$session.machine,v$lock.sid,
sys.v_$session.serial#,decode(v$lock.type,MR,MediaRecovery,
RT,RedoThread,UN,UserName,TX,Transaction,TM,DML,
UL,PL/SQLUserLock,DX,DistributedXaction,CF,ControlFile,
IS,InstanceState,FS,FileSet,IR,InstanceRecovery,
ST,DiskSpaceTransaction,TS,TempSegment,IV,LibraryCacheInvalida-tion,LS,LogStartorSwitch,RW,RowWait,SQ,SequenceNumber,TE,ExtendTable,TT,TempTable,Unknown)LockType,
rtrim(object_type)||||rtrim(owner)||.||object_nameobject_name,decode(lmode,0,None,1,Null,2,Row-S,3,Row-X,4,Share,
5,S/Row-X,6,Exclusive,Unknown)LockMode,decode(request,0,None,1,Null,2,Row-S,3,Row-X,4,Share,5,S/Row-X,
6,Exclusive,Unknown)RequestMode,ctime,blockb
fromv$lock,all_objects,sys.v_$session
wherev$Lock.sid>6
andsys.v_$session.sid=v$lock.sid
andv$lock.id1=all_objects.object_id;
以DBA脚色,检察以后数据库里锁的情形能够用以下SQL语句：
colownerfora12
colobject_namefora16
selectb.owner,b.object_name,l.session_id,l.locked_mode
fromv$locked_objectl,dba_objectsb
whereb.object_id=l.object_id;
SQL>selectt2.username,t2.sid,t2.serial#,t2.logon_time
fromv$locked_objectt1,v$sessiont2
wheret1.session_id=t2.sidorderbyt2.logon_time;
SQL>Selectsql_addressfromv$sessionwheresid=;
SQL>Select*fromv$sqltextwhereaddress=;
SQL>selectCOMMAND_TYPE,PIECE,sql_textfromv$sqltextwhereaddress=(selectsql_addressfromv$sessionawheresid=18);
SQL>selectobject_idfromv$locked_object;
SQL>selectobject_name,object_typefromdba_objectswhereobject_id=’’;
假如有临时呈现的一列，多是没有开释的锁。我们能够用上面SQL语句杀失落临时没有开释非一般的锁：
SQL>altersystemkillsessionsid,serial#;
17、检察守候（wait）情形:
SQL>SELECTv$waitstat.class,v$waitstat.countcount,SUM(v$sysstat.value)sum_valueFROMv$waitstat,v$sysstatWHEREv$sysstat.nameIN(dbblockgets,consistentgets)groupbyv$waitstat.class,v$waitstat.count;
18、检察sga情形:
SQL>SELECTNAME,BYTESFROMSYS.V_$SGASTATORDERBYNAMEASC;
19、检察catchedobject:
SQL>SELECTowner,name,db_link,namespace,type,sharable_mem,loads,executions,locks,pins,keptFROMv$db_object_cache;
20、检察V$SQLAREA:
SQL>SELECTSQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,
VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,
USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,
DISK_READS,BUFFER_GETS,ROWS_PROCESSEDFROMV$SQLAREA;
21、检察object分类数目:
selectdecode(o.type#,1,INDEX,2,TABLE,3,CLUSTER,4,VIEW,5,SYNONYM,6,SEQUENCE,OTHER)object_type,count(*)quantityfromsys.obj$owhereo.type#>1groupbydecode(o.type#,1,INDEX,2,TABLE,3,CLUSTER,4,VIEW,5,SYNONYM,6,SEQUENCE,OTHER)unionselectCOLUMN,count(*)fromsys.col$unionselectDBLINK,count(*)fromall_objects;
22、有关connection的相干信息:
1）检察有哪些用户毗连
selects.osuseros_user_name,decode(sign(48-command),1,to_char(command),
ActionCode#||to_char(command))action,p.programoracle_process,
statussession_status,s.terminalterminal,s.programprogram,
s.usernameuser_name,s.fixed_table_sequenceactivity_meter,query,
0memory,0max_memory,0cpu_usage,s.sid,s.serial#serial_num
fromv$sessions,v$processpwheres.paddr=p.addrands.type=USER
orderbys.username,s.osuser;
2）依据v.sid检察对应毗连的资本占用等情形
selectn.name,v.value,n.class,n.statistic#
fromv$statnamen,v$sesstatvwherev.sid=18andv.statistic#=n.statistic#orderbyn.class,n.statistic#;
3）依据sid检察对应毗连正在运转的sql
select/*+PUSH_SUBQ*/command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,
loaded_versions,open_versions,users_opening,executions,users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,rows_processed,sysdatestart_time,sysdatefinish_time,>||addresssql_address,
Nstatusfromv$sqlareawhereaddress=(selectsql_addressfromv$sessionwheresid=8);
依据pid检察sql语句:
selectsql_textfromv$sql
whereaddressin
(selectsql_addressfromv$session
wheresidin
(selectsidfromv$sessionwherepaddrin(selectaddrfromv$processwherespid=&pid)));
23、查询表空间利用情形:
selecta.tablespace_name"表空间称号",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)"占用率(%)",
round(a.bytes_alloc/1024/1024,2)"容量(M)",
round(nvl(b.bytes_free,0)/1024/1024,2)"余暇(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2)"利用(M)",
Largest"最年夜扩大段(M)",to_char(sysdate,yyyy-mm-ddhh24:mi:ss)"采样工夫"from(selectf.tablespace_name,sum(f.bytes)bytes_alloc,
sum(decode(f.autoextensible,YES,f.maxbytes,NO,f.bytes))maxbytes
fromdba_data_filesfgroupbytablespace_name)a,
(selectf.tablespace_name,sum(f.bytes)bytes_free
fromdba_free_spacefgroupbytablespace_name)b,
(selectround(max(ff.length)*16/1024,2)Largest,ts.nametablespace_name
fromsys.fet$ff,sys.file$tf,sys.ts$ts
wherets.ts#=ff.ts#andff.file#=tf.relfile#andts.ts#=tf.ts#
groupbyts.name,tf.blocks)cwherea.tablespace_name=b.tablespace_nameanda.tablespace_name=c.tablespace_name;
SELECTUPPER(F.TABLESPACE_NAME)"表空间名",
D.TOT_GROOTTE_MB"表空间巨细(M)",
D.TOT_GROOTTE_MB-F.TOTAL_BYTES"已利用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,
2),
990.99)"利用比",
F.TOTAL_BYTES"余暇空间(M)",
F.MAX_BYTES"最年夜块(M)"
FROM(SELECTTABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,
ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTES
FROMSYS.DBA_FREE_SPACE
GROUPBYTABLESPACE_NAME)F,
(SELECTDD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MB
FROMSYS.DBA_DATA_FILESDD
GROUPBYDD.TABLESPACE_NAME)D
WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME
ORDERBY4DESC;
24、查询表空间的碎片水平:
SQL>selecttablespace_name,count(tablespace_name)fromdba_free_spacegroupbytablespace_namehavingcount(tablespace_name)>10;
SQL>altertablespacenamecoalesce;
SQL>altertabletable_namedeallocateunused;
SQL>createorreplaceviewts_blocks_vas
selecttablespace_name,block_id,bytes,blocks,freespacesegment_namefromdba_free_spaceunionall
selecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;
SQL>select*fromts_blocks_v;
SQL>selecttablespace_name,sum(bytes),max(bytes),count(block_id)fromdba_free_spacegroupbytablespace_name;
SQL>selectaltertablespace||TABLESPACE_NAME||coalesce;
fromDBA_FREE_SPACE_COALESCEDwherePERCENT_EXTENTS_COALESCED<100
orPERCENT_BLOCKS_COALESCED<100;
因为自在空间碎片是由几部分构成，如局限数目、最年夜局限尺寸等，我们可
用fsfi--freespacefragmentationindex（自在空间碎片索引）值来直不雅表现:
fsfi=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents)))
remfsfivaluecompute
remfsfi.sql
columnfsfiformat999,99
selecttablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks))))fsfi
fromdba_free_space
groupbytablespace_nameorderby1;
spoolfsfi.rep;
/
spooloff;
能够看出，fsfi的最年夜大概值为100（一个幻想的单文件表空间）。跟着范
围的增添，fsfi值迟缓下落，而跟着最年夜局限尺寸的削减，fsfi值会敏捷下落。
好比，在某数据库运转剧本fsfi.sql,失掉以下fsfi值：
tablespace_namefsfi
-------------------------------------
rbs74.06
system100.00
temp22.82
tools75.79
users100.00
user_tools100.00
ydcx_data47.34
ydcx_idx57.19
ydjf_data33.80
ydjf_idx75.55
----统计出了数据库的fsfi值，就能够把它作为一个可比参数。在一个有着充足
无效自在空间，且fsfi值凌驾30的表空间中，很少会碰见无效自在空间的成绩。
当一个空间将要靠近可比参数时，就必要做碎片收拾了。
25、查询有哪些数据库实例在运转:
selectinst_namefromv$active_instances;
26、以DBA脚色,检察以后数据库里锁的情形：
selectobject_id,session_id,locked_modefromv$locked_object;
selectt2.username,t2.sid,t2.serial#,t2.logon_timefromv$locked_objectt1,v$sessiont2
wheret1.session_id=t2.sidorderbyt2.logon_time;
27、检察表是不是是分区表：
selectTABLE_NAME,PARTITIONEDfromuser_tableswhereTABLE_NAME=LOCALUSAGE;
TABLE_NAMEPAR
------------------------------------------
LOCALUSAGEYES
28、检察分区表的分区名和响应的表空间名：
selectTABLE_NAME,PARTITION_NAME,TABLESPACE_NAMEfromuser_tab_partitionswheretable_namelike‘%USAGE%’;
29、检察索引是不是是分区索引：
SELECTINDEX_NAME,TABLE_NAME,STATUS,PARTITIONEDFROMUSER_INDEXESWHERETABLE_NAMELIKE%USAGE;
30、假如前往的PATITIONED为YES，请再实行以下语句来查询分区索引的范例：SELECTindex_name,table_name,localityFROMuser_part_indexes;
31、Dual是Oracle中的一个实践存在的表，任何用户都可读取，经常使用在没有方针表的Select中.
检察体系工夫：
selectto_char(sysdate,yy-mm-ddhh24:mi:ss)shijianfromdual;
32、检察索引段中extent的数目：
selectsegment_name,count(*)fromdba_extents
wheresegment_type=INDEXandowner=SCOTTgroupbysegment_name;
33、检察体系表中的用户索引（用来反省在system表空间内其他用户索引的存在）：
SQL>selectcount(*)fromdba_indexeswheretablespace_name=’SYSTEM’andownerNOTIN(‘SYS’,’SYSTEM’);
34、检察wacos表空间内的索引的扩大情形：
SELECTSUBSTR(segment_name,1,20)"SEGMENTNAME",bytes,COUNT(bytes)
FROMdba_extentsWHEREsegment_nameIN(SELECTindex_nameFROMdba_indexes
WHEREtablespace_name=WACOS)GROUPBYsegment_name,bytesORDERBYsegment_name;
35、检察表空间数据文件的读写功能:
SQL>Selectname,phyrds,phywrts,avgiotim,miniotim,maxiowtm,maxiortmfromv$filestat,v$datafilewherev$filestat.file#=v$datafile.file#;
SQL>Selectfs.namename,f.phyrds,f.phyblkrd,f.phywrts,f.phyblkwrt,f.readtim,f.writetim
fromv$filestatf,v$datafilefswheref.file#=fs.file#orderbyfs.name;
(注重：假如phyblkrd与phyrds很靠近的话，则标明这个表空间中存在全表扫描的表，这些表必要调剂索引或优化SQL语句)
36、转换表空间为local体例办理:
SQL>execsys.dbms_space_admin.tablespace_migrate_to_local(TBS_TEST);
37、检察一下哪一个用户在用一时段:
SELECTusername,sid,serial#,sql_address,machine,program,tablespace,segtype,
contentsFROMv$sessionse,v$sort_usagesuWHEREse.saddr=su.session_addr;
38、检察占io较年夜的正在运转的session:
SELECTse.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changesFROMv$sessionse,v$session_waitst,v$sess_iosi,v$processprWHEREst.sid=se.sidANDst.sid=si.sidANDse.PADDR=pr.ADDRANDse.sid>6ANDst.wait_time=0ANDst.eventNOTLIKE%SQL%ORDERBYphysical_readsDESC;
39、查找前十条功能差的sql:
SELECT*FROM(SELECTPARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_textFROMv$sqlareaORDERBYdisk_readsDESC)WHEREROWNUM<10;
40、删除用户下一切表的语句:
selectdroptable||table_name||cascadeconstraints;fromuser_tables;
41、检察LOCK，并杀失落会话：
setlinesize132pagesize66
breakonKillonusernameonterminal
columnKillheadingKillStringformata13
columnresheadingResourceTypeformat999
columnid1format9999990
columnid2format9999990
columnlmodebeadingLockHeldformata20
columnrequestheadingLockRequestedformata20
columnserial#format99999
columnusernameformata10heading"Username"
columnterminalheadingTermformata6
columntabformata35heading"tableName"
columnownerformata9
columnAddressformata18
selectnvl(S.USERNAME,Internal)username,
nvl(S.TERMINAL,None)terminal,
L.SID||,||S.SERIAL#Kill,
U1.NAME||,||substr(T1.NAME,1,20)tab,
decode(L.LMODE,1,NoLock,
2,RowShare,
3,RowExclusive,
4,Share,
5,ShareRowExclusive,
6,Exclusive,null)lmode,
decode(L.REQUEST,1,NoLock,
2,RowShare,
3,RowExclusive,
4,Share,
5,ShareRowExclusive,
6,Exclusive,null)request
fromV$LOCKL,
V$SESSIONS,
SYS.USER$U1,
SYS.OBJ$T1
whereL.SID=S.SID
andT1.OBJ#=decode(L.ID2,0,L.ID1,L.ID2)
andU1.USER#=T1.OWNER#
andS.TYPE!=BACKGROUND
orderby1,2,5;
--altersystemkillsession,;
columnusernameformatA15
columnsidformat9990headingSID
columntypeformatA4
columnlmodeformat990headingHELD
columnrequestformat990headingREQ
columnid1format9999990
columnid2format9999990
breakonid1skip1dup
spooltfslckwt.lst
selectsn.username,
m.sid,
m.type,
DECODE(m.lmode,0,None,
1,Null,
2,RowShare,
3,RowExcl.,
4,Share,
5,S/RowExcl.,
6,Exclusive,
lmode,ltrim(to_char(lmode,990)))lmode,
DECODE(m.request,0,None,
1,Null,
2,RowShare,
3,RowExcl.,
4,Share,
5,S/RowExcl.,
6,Exclusive,
request,ltrim(to_char(m.request,990)))request,
m.id1,
m.id2
fromv$sessionsn,
v$lockm
where(sn.sid=m.sidandm.request!=0)
or(sn.sid=m.sidand
m.request=0andlmode!=4and
(id1,id2)in(selects.id1,
s.id2
fromv$locks
whererequest!=0ands.id1=m.id1ands.id2=m.id2)
)
orderbyid1,id2,m.request;
spooloff
clearbreaks
42.检察WACOS表空间下一切的索引:
SQL>selectanalyzeindex||segment_name||validatestructure;fromdba_segmentswheretablespace_name=’WACOS’andsegment_type=’INDEX’;
43.如何辨认IO合作和负载均衡:
col文件名formata35
select
df.name文件名,
fs.phyrds读次数,
fs.phywrts写次数,
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds))读工夫,
(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts))写工夫
from
v$datafiledf,
v$filestatfs
wheredf.file#=fs.file#
orderbydf.name
/
文件名读次数写次数读工夫写工夫
------------------------------------------------------------------------------------
C:ORACLEORADATAORADBDR01.DBF88588300
C:ORACLEORADATAORADBINDX01.DBF88588300
C:ORACLEORADATAORADBOEM_REPOSITORY.ORA88588300
C:ORACLEORADATAORADBRBS01.DBF9252230600
C:ORACLEORADATAORADBYSTEM01.DBF5080415502500
C:ORACLEORADATAORADBTEMP01.DBF88789400
C:ORACLEORADATAORADBTOOLS01.DBF88689200
C:ORACLEORADATAORADBUSERS01.DBF88588300
已选择8行。
个中：ORADB为数据库名，由于本例中数据库使默许安装，没有举行过优化、调剂，
以是，一向在system表空间上做操纵，招致system表空间地点的数据文件SYSTEM01.DBF被读写的次数最多，
这也申明了，只管不要在system表空间做与体系有关的操纵，应给各个用户创建独自的表空间。
44.检察哪些session正在利用哪些回滚段:
col回滚段名formata10
colSIDformat9990
col用户名formata10
col操纵程序formata80
colstatusformata6trunc
SELECTr.name回滚段名,
s.sid,
s.serial#,
s.username用户名,
t.status,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
substr(s.program,1,78)操纵程序
FROMsys.v_$sessions,sys.v_$transactiont,sys.v_$rollnamer
WHEREt.addr=s.taddrandt.xidusn=r.usn
ORDERBYt.cr_get,t.phy_io;
45.反省谁Lock了甚么工具:
setline200
col"O/S-User"formata10
col"Ora-User"formata10
col"ObjLocked"formata30
select/*+RULE*/s.machine,s.osuser"O/S-User",s.username"Ora-User",s.sid"Session-ID",
s.serial#"Serial",s.process"Process-ID",s.status"Status",l.name"ObjLocked",
l.mode_held"LockMode"
fromv$sessions,dba_dml_locksl,v$processp
wherel.session_id=s.sidandp.addr=s.paddr
/
46.形成守候的LOCK的信息，好比LOCK范例等：
coleventformata30
setline160
colmachineformata10
colusernameformata15
selectb.sid,b.serial#,b.username,machine,event,wait_time,chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1,16711680)/65535)"EnqueueType"fromv$session_waita,v$sessionb
whereeventnotlikeSQL*N%andeventnotlikerdbms%anda.sid=b.sid
andb.sid>8andevent=enqueue
orderbyusername
/
47.ListofthelockedOracleobjects
setline120
columnobject_nameformata32
columnOS_USER_NAMEformata12
columnorauserformata12
columnsql_textformata32
columnserial#format999999
columnsidformat99999
SELECTOS_USER_NAME,ORACLE_USERNAMEASorauser,s.sid,o.object_name,
o.object_type,s.serial#,a.sql_text
FROMv$locked_objectl,dba_objectso,v$sessions,v$sqlareaa
WHEREl.object_id=o.object_id
ANDs.SQL_ADDRESS=a.address
ANDl.SESSION_ID=s.sid;
SELECTALTERSYSTEMKILLSESSION||TO_CHAR(s.sid)||,||TO_CHAR(s.serial#)||;
AS"Statementtokill"
FROMv$locked_objectl,dba_objectso,v$sessions
WHEREl.object_id=o.object_id
ANDl.SESSION_ID=s.sid;
oracle数据库功能监控的SQL
1.监控事例的守候
SQL>selectevent,sum(decode(wait_Time,0,0,1))"Prev",sum(decode(wait_Time,0,1,0))"Curr",count(*)"Tot"fromv$session_Waitgroupbyeventorderby4;
2.回滚段的争用情形
SQL>selectname,waits,gets,waits/gets"Ratio"fromv$rollstata,v$rollnamebwherea.usn=b.usn;
3.监控表空间的I/O比例
SQL>selectdf.tablespace_namename,df.file_name"file",f.phyrdspyr,
f.phyblkrdpbr,f.phywrtspyw,f.phyblkwrtpbwfromv$filestatf,dba_data_filesdfwheref.file#=df.file_id
orderbydf.tablespace_name;
4.监控文件体系的I/O比例
SQL>selectsubstr(a.file#,1,2)"#",substr(a.name,1,30)"Name",
a.status,a.bytes,b.phyrds,b.phywrtsfromv$datafilea,v$filestatb
wherea.file#=b.file#;
5.在某个用户下找一切的索引
SQL>selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column_namefromuser_ind_columns,user_indexeswhereuser_ind_columns.index_name=user_indexes.index_name
anduser_ind_columns.table_name=user_indexes.table_name
orderbyuser_indexes.table_type,user_indexes.table_name,
user_indexes.index_name,column_position;
6.监控SGA的射中率
SQL>selecta.value+b.value"logical_reads",c.value"phys_reads",
round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFERHITRATIO"fromv$sysstata,v$sysstatb,v$sysstatcwherea.statistic#=38andb.statistic#=39andc.statistic#=40;
7.监控SGA中字典缓冲区的射中率
SQL>selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100"missratio",(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio"fromv$rowcachewheregets+getmisses0groupbyparameter,gets,getmisses;
8.监控SGA中共享缓存区的射中率，应当小于1%
SQL>selectsum(pins)"TotalPins",sum(reloads)"TotalReloads",
sum(reloads)/sum(pins)*100libcachefromv$librarycache;
SQL>selectsum(pinhits-reloads)/sum(pins)"hitradio",sum(reloads)/sum(pins)"reloadpercent"fromv$librarycache;
9.显现一切数据库工具的种别和巨细
SQL>selectcount(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_requiredfromdba_object_sizegroupbytypeorderby2;
10.监控SGA中重做日记缓存区的射中率，应当小于1%
SQL>SELECTname,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
FROMv$latchWHEREnameIN(redoallocation,redocopy);
11.监控内存和硬盘的排序比率，最好使它小于.10，增添sort_area_size
SQL>SELECTname,valueFROMv$sysstatWHEREnameIN(sorts(memory),sorts(disk));
12.监控以后数据库谁在运转甚么SQL语句
SQL>SELECTosuser,username,sql_textfromv$sessiona,v$sqltextb
wherea.sql_address=b.addressorderbyaddress,piece;
13.监控字典缓冲区
SQL>SELECT(SUM(PINS-RELOADS))/SUM(PINS)"LIBCACHE"FROMV$LIBRARYCACHE;
SQL>SELECT(SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS)"ROWCACHE"FROMV$ROWCACHE;
SQL>SELECTSUM(PINS)"EXECUTIONS",SUM(RELOADS)"CACHEMISSESWHILEEXECUTING"FROMV$LIBRARYCACHE;(后者除之前者,此比率小于1%,靠近0%为好)
SQL>SELECTSUM(GETS)"DICTIONARYGETS",SUM(GETMISSES)"DICTIONARYCACHEGETMISSES"FROMV$ROWCACHE;
14.查找ORACLE字符集
SQL>select*fromsys.props$wherename=NLS_CHARACTERSET;
15.监控MTS
SQL>selectbusy/(busy+idle)"sharedserversbusy"fromv$dispatcher;
(此值年夜于0.5时，参数需加年夜)
SQL>selectsum(wait)/sum(totalq)"dispatcherwaits"fromv$queuewheretype=dispatcher;
SQL>selectcount(*)fromv$dispatcher;
SQL>selectservers_highwaterfromv$mts;
(servers_highwater靠近mts_max_servers时，参数需加年夜)
16.碎片水平
SQL>selecttablespace_name,count(tablespace_name)fromdba_free_spacegroupbytablespace_namehavingcount(tablespace_name)>10;
SQL>altertablespacenamecoalesce;
SQL>altertablenamedeallocateunused;
SQL>createorreplaceviewts_blocks_vas
selecttablespace_name,block_id,bytes,blocks,freespacesegment_namefromdba_free_space
unionall
selecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;
select*fromts_blocks_v;
SQL>selecttablespace_name,sum(bytes),max(bytes),count(block_id)fromdba_free_spacegroupbytablespace_name;
检察碎片水平高的表
SQL>SELECTsegment_nametable_name,COUNT(*)extents
FROMdba_segmentsWHEREownerNOTIN(SYS,SYSTEM)GROUPBY
segment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);
17.表、索引的存储情形反省
SQL>selectsegment_name,sum(bytes),count(*)ext_quanfromdba_extentswheretablespace_name=&tablespace_nameandsegment_type=TABLEgroupbytablespace_name,segment_name;
SQL>selectsegment_name,count(*)fromdba_extentswheresegment_type=INDEXandowner=&ownergroupbysegment_name;
18、找利用CPU多的用户session
SQL>selecta.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value/60/100valuefromv$sessiona,v$processb,v$sesstatc
wherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;
(12是cpuusedbythissession)
表空间统计
A、剧本申明：
这是我最经常使用的一个剧本，用它能够显现出数据库中一切表空间的形态，如表空间的巨细、已利用空间、利用的百分比、余暇空间数及如今表空间的最年夜块是多年夜。
B、剧本原文:
SELECTupper(f.tablespace_name)"表空间名",
d.Tot_grootte_Mb"表空间巨细(M)",
d.Tot_grootte_Mb-f.total_bytes"已利用空间(M)",
to_char(round((d.Tot_grootte_Mb-f.total_bytes)/d.Tot_grootte_Mb*100,2),990.99)"利用比",
f.total_bytes"余暇空间(M)",
f.max_bytes"最年夜块(M)"
FROM
(SELECTtablespace_name,
round(SUM(bytes)/(1024*1024),2)total_bytes,
round(MAX(bytes)/(1024*1024),2)max_bytes
FROMsys.dba_free_space
GROUPBYtablespace_name)f,
(SELECTdd.tablespace_name,round(SUM(dd.bytes)/(1024*1024),2)Tot_grootte_Mb
FROMsys.dba_data_filesdd
GROUPBYdd.tablespace_name)d
WHEREd.tablespace_name=f.tablespace_name
ORDERBY4DESC;
检察没法扩大的段
A、剧本申明：
ORACLE对一个段好比表段或索引没法扩大时，取决的并非表空间中残剩的空间是几，而是取于这些残剩空间中最年夜的块是不是够表比索引的“NEXT”值年夜，以是偶然一个表空间残剩几个G的余暇空间，在你利用时ORACLE仍是提醒某个表或索引没法扩大，就是因为这一点，这时候申明空间的碎片太多了。这个剧本是找出没法扩大的段的一些信息。
B、剧本原文：
SELECTsegment_name,
&提供用于管理、检查、优化数据库操作的管理工具。 一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。 SQL语言是学习所有数据库产品的基础，无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块，如果是做数据库管理（DBA），侧重点应该放在SQLServer的系统管理上. SP4包括用于以下SQLServer2000组件的程序包：Database组件（下载文件：SQL2000-KB884525-SP4-x86.EXE）更新SQLServer2000的32位Database组件，包括数据库引擎、复制、客户端连接组件及工具。有关其他信息，请参阅ReadmeSql2k32Sp4.htm。AnalysisServices组件（下载文件：SQL2000.AS-KB884525-SP4-x86.EXE）更新SQLServer2000的32位AnalysisServices。 多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。 我是一个ERP初学者，对于前台运用基本熟悉，但对于后台SQLServer的运用一点也不懂，特想学习下相关资料。至少懂得一些基本的运用。希望各位能给于建议，小弟再谢过！ 在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。 我是一个ERP初学者，对于前台运用基本熟悉，但对于后台SQLServer的运用一点也不懂，特想学习下相关资料。至少懂得一些基本的运用。希望各位能给于建议，小弟再谢过！ 是要和操作系统进行Socket通讯的场景。否则建议慎重！
页:
[1]