SQL语句整理大全免费收藏.docx
《SQL语句整理大全免费收藏.docx》由会员分享，可在线阅读，更多相关《SQL语句整理大全免费收藏.docx（33页珍藏版）》请在冰豆网上搜索。
SQL语句整理大全免费收藏
SQL语句整理大全（免费收藏）
如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句:
SQL>SELECTD.SQL_TEXT,A.NAME
FROMV$ROLLNAMEA,V$TRANSACTIONB,V$SESSIONC,V$SQLTEXTD
WHEREA.USN=B.XIDUSN
ANDB.ADDR=C.TADDR
ANDC.SQL_ADDRESS=D.ADDRESS
ANDC.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_DEST'AND
INSTANCE.NAME='INSTANCE_NAME'ANDV$SESSION.AUDSID=SYS_CONTEXT（'USERENV','SESSIONID'）;
SQL>SELECTD.VALUE||'/ORA_'||P.SPID||'.TRC'TRACE_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_TRIGGERAFTERLOGONONDATABASE
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO（SYS_CONTEXT（'USERENV','IP_ADDRESS'））;
END;
REM记录登陆信息的触发器
CREATEORREPLACETRIGGERLOGON_HISTORY
AFTERLOGONONDATABASE--WHEN（USER='WACOS'）--ONLYFORUSER'WACOS'
BEGIN
INSERTINTOSESSION_HISTORYSELECTUSERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT（'USERENV','IP_ADDRESS'）,TERMINAL,MACHINE,PROGRAMFROMV$SESSIONWHEREAUDSID=USERENV（'SESSIONID'）;
END;
查看表空间的名称及大小:
SQL>SELECTT.TABLESPACE_NAME,ROUND（SUM（BYTES/（1024*1024））,0）TS_SIZE
FROMDBA_TABLESPACEST,DBA_DATA_FILESD
WHERET.TABLESPACE_NAME=D.TABLESPACE_NAME
GROUPBYT.TABLESPACE_NAME;
查看表空间物理文件的名称及大小:
SQL>SELECTTABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND（BYTES/（1024*1024）,0）TOTAL_SPACE
FROMDBA_DATA_FILES
ORDERBYTABLESPACE_NAME;
查看回滚段名称及大小:
SQL>SELECTSEGMENT_NAME,
TABLESPACE_NAME,
R.STATUS,
（INITIAL_EXTENT/1024）INITIALEXTENT,
（NEXT_EXTENT/1024）NEXTEXTENT,
MAX_EXTENTS,
V.CUREXTCUREXTENT
FROMDBA_ROLLBACK_SEGSR,V$ROLLSTATV
WHERER.SEGMENT_ID=V.USN（+）
ORDERBYSEGMENT_NAME;
查询当前日期:
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））||'M'SUM_M,CEIL（SUM（S.USEDSPACE/1024/1024））||'M'USED_M,CEIL（SUM（S.FREESPACE/1024/1024））||'M'FREE_M,SUM（S.USEDSPACE）/SUM（S.BYTES）PTUSEDFROM（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_FILESBWHEREA.FILE_ID（+）=B.FILE_IDGROUPBYB.FILE_ID,B.TABLESPACE_NAME,B.BYTESORDERBYB.TABLESPACE_NAME）SGROUPBYS.TABLESPACE_NAMEORDERBYSUM（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_EVENTWHEREEVENTNOTLIKE'SQL%'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='ACTIVE'ANDSE.EVENTNOTLIKE'%SQL*NET%';
SELECTSID,EVENT,P1,P2,P3,WAIT_TIME,SECONDS_IN_WAIT,STATEFROMV$SESSION_WAITWHEREEVENTNOTLIKE'%MESSAGE%'ANDEVENTNOTLIKE'SQL*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%,对于数据重载比率,reloadrati
升级会员 