这篇文章主要讲解了“怎么编写oracle_ray.sh常用的oracle sql功能脚本”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么编写oracle_ray.sh常用的oracle sql功能脚本”吧！

获取帮助：

oracle_ray.sh help=y

更新:

第二版：
添加了asm磁盘信息查询

第三版：

修正了获取执行计划指定from=cursor的bug

#!/bin/bash#byray#2017-09-29#version3.0####################################################readconfigurationfile###################################################if[-e~/.bash_profile];then.~/.bash_profilefiif[-e~/.profile];then.~/.profilefi####################################################functions#######################################################################################################functionsforDataGuardApplied###################################################getDgApplied(){sqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300setpages10000colnamefora100selectdest_id,sequence#,name,appliedfromv\$archived_logwherenameisnotnullorderbysequence#;exitRAY}####################################################functionsforTablespaceusage###################################################getTablespaceInfo(){sqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300setpages10000colTABLESPACE_NAMEfora30;colPCT_FREEfora10;colPCT_USEDfora10;colUSED_MAX%fora10selecta.tablespace_name,round(a.bytes_alloc/1024/1024,2)megs_alloc,round(nvl(b.bytes_free,0)/1024/1024,2)megs_free,round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2)megs_used,round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)||'%'Pct_Free,100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)||'%'Pct_used,round(maxbytes/1048576,2)Max,round(round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2)/round((casemaxbyteswhen0thena.bytes_allocelsemaxbytesend)/1048576,2)*100,2)||'%'"USED_MAX%"from(selectf.tablespace_name,sum(f.bytes)bytes_alloc,sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes))maxbytesfromdba_data_filesfgroupbytablespace_name)a,(selectf.tablespace_name,sum(f.bytes)bytes_freefromdba_free_spacefgroupbytablespace_name)bwherea.tablespace_name=b.tablespace_name(+)unionallselecth.tablespace_name,round(sum(h.bytes_free+h.bytes_used)/1048576,2)megs_alloc,round(sum((h.bytes_free+h.bytes_used)-nvl(p.bytes_used,0))/1048576,2)megs_free,round(sum(nvl(p.bytes_used,0))/1048576,2)megs_used,round((sum((h.bytes_free+h.bytes_used)-nvl(p.bytes_used,0))/sum(h.bytes_used+h.bytes_free))*100,2)||'%'Pct_Free,100-round((sum((h.bytes_free+h.bytes_used)-nvl(p.bytes_used,0))/sum(h.bytes_used+h.bytes_free))*100,2)||'%'pct_used,round(sum(f.maxbytes)/1048576,2)max,round(round(sum(nvl(p.bytes_used,0))/1048576,2)/round(sum(casef.maxbyteswhen0then(h.bytes_free+h.bytes_used)elsef.maxbytesend)/1048576,2)*100,2)||'%'"USED_MAX%"fromsys.v_\$TEMP_SPACE_HEADERh,sys.v_\$Temp_extent_poolp,dba_temp_filesfwherep.file_id(+)=h.file_idandp.tablespace_name(+)=h.tablespace_nameandf.file_id=h.file_idandf.tablespace_name=h.tablespace_namegroupbyh.tablespace_nameORDERBY1;exitRAY}####################################################functionsforASMDiskGroupusage###################################################getAsmDiskgroup(){sqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300selectname,total_mb,free_mbfromv\$asm_diskgroup;RAYexit}####################################################functionsforASMDiskinfomation###################################################getAsmDiskInfo(){sqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300setpages1000colnamefora15colpathfora60selectadg.name,adg.TOTAL_MBgroup_TOTAL_MB,adg.free_mbgroup_free_mb,ad.path,ad.name,ad.TOTAL_MBdisk_totle_mb,ad.free_mbdisk_free_mbfromv$asm_diskgroupadg,v$asm_diskadwhereadg.GROUP_NUMBER=ad.GROUP_NUMBERorderbyad.name;RAYexit}####################################################functionsforRedoLoginfomation###################################################getRedoInfo(){sqlplus-s/nolog<<-RAYconn/assysdbasetlinesize500setpages1000colgroup#for999colmbfor9999colmemberfora60colthread#for999colarchivedfora10selecta.group#,a.BYTES/1024/1024mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_timefromgv\$loga,gv\$logfilebwherea.GROUP#=b.GROUP#groupbya.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_timeorderbygroup#;exitRAY}####################################################functionsforRedoLogshiftfrequency###################################################getRedoShiftFrequ(){sqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300setpages10000SELECTto_char(first_time,'YYYY-MM-DD')day,to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999')"00",to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999')"01",to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999')"02",to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999')"03",to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999')"04",to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999')"05",to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999')"06",to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999')"07",to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999')"08",to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999')"09",to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999')"10",to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999')"11",to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999')"12",to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999')"13",to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999')"14",to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999')"15",to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999')"16",to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999')"17",to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999')"18",to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999')"19",to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999')"20",to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999')"21",to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999')"22",to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999')"23"fromv\$log_historyGROUPbyto_char(first_time,'YYYY-MM-DD')orderbydaydesc;exitRAY}####################################################functionsforTablespaceincludedatafileinfomation###################################################getTablespaceAndDatafile(){sqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300setpages1000colts_namefora30coldf_namefora100selectts.namets_name,df.namedf_namefromv\$tablespacets,v\$datafiledfwherets.ts#=df.ts#groupbyts.name,df.nameorderbyts.name;exitRAY}####################################################functionsforexecutingsql###################################################getExecutingSQL(){sqlplus-s/nolog<<-RAYconn/assysdbasetlinesize80setheadingoffsetpages1000colsidfor999999colSERIAL#for99999colspidfor999999colLAST_CALL_ETfora20colsql_idfora20colstatusfora20coleventfora40selectdistinct'sid:'||b.SID,'serial#:'||b.SERIAL#,'spid:'||p.SPID,'last_call:'||b.LAST_CALL_ETasLAST_CALL_ET,'sql_id:'||a.sql_id,'status:'||b.status,'event:'||b.event,'module:'||b.MODULE,'os_user:'||b.OSUSER,'machine:'||b.MACHINE,'sql_text:'||a.sql_textfromv\$sqla,v\$sessionb,v\$processpwherea.SQL_ID=b.SQL_IDandb.PADDR=p.ADDRandb.STATUS='ACTIVE'orderbyLAST_CALL_ETdesc;RAY}####################################################functionsforgetingfullsqltext###################################################getFullSqlText(){if[$2=="HIST"];thensqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300setserveroutputonsetfeedbackoffspool./$1.txtdeclarel_buffervarchar2(3000);l_amountbinary_integer:=3000;l_posint:=1;l_clob_lengthint;sqlidvarchar2(100):='$1';beginselectDBMS_LOB.getlength(sql_text)intol_clob_lengthfromdba_hist_sqltextwheresql_id=sqlid;whilel_pos<l_clob_lengthloopselectDBMS_LOB.SUBSTR(sql_text,l_amount,l_pos)intol_bufferfromdba_hist_sqltextwheresql_id=sqlid;dbms_output.put(l_buffer);l_pos:=l_pos+l_amount;endloop;dbms_output.put_line('');end;/spooloffexitRAYfiif[$2=="MEMORY"];thensqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300setserveroutputonsetfeedbackoffspool./$1.txtdeclarel_buffervarchar2(3000);l_amountbinary_integer:=3000;l_posint:=1;l_clob_lengthint;sqlidvarchar2(100):='$1';beginselectDBMS_LOB.getlength(sql_fulltext)intol_clob_lengthfromv\$sqlareawheresql_id=sqlid;whilel_pos<l_clob_lengthloopselectDBMS_LOB.SUBSTR(sql_fulltext,l_amount,l_pos)intol_bufferfromv\$sqlareawheresql_id=sqlid;dbms_output.put(l_buffer);l_pos:=l_pos+l_amount;endloop;dbms_output.put_line('');end;/spooloffexitRAYfi}####################################################functionsforindexesintable###################################################getIndexInTable(){sqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300coltable_namefora20colindex_namefora30colindex_typefora10colcolumnsfora50selecttable_name,TABLE_TYPE,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,max(columns)columnsfrom(SELECTui.table_name,ui.TABLE_TYPE,ui.INDEX_NAME,ui.INDEX_TYPE,uic.TABLE_OWNER,to_char(wm_concat(uic.COLUMN_NAME)over(partitionbyui.table_name,ui.TABLE_TYPE,ui.INDEX_NAME,ui.INDEX_TYPE,uic.TABLE_OWNERorderbyuic.COLUMN_POSITION))columnsFROMdba_indexesui,dba_IND_COLUMNSuicWHEREui.INDEX_NAME(+)=uic.INDEX_NAMEANDui.TABLE_NAME=UPPER('$1'))GROUPBYtable_name,TABLE_TYPE,INDEX_NAME,INDEX_TYPE,TABLE_OWNER;exitRAY}####################################################functionsforgetingexecutingplan###################################################getXplan(){if[$2=="CURSOR"];thensqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300setpages10000spool./$1.txtselect*fromtable(dbms_xplan.display_cursor('$1',null,'advanced'));spooloffexitRAYfiif[$2=="AWR"];thensqlplus-s/nolog<<-RAYconn/assysdbasetlinesize300setpages10000spool./$1.txtselect*fromtable(dbms_xplan.DISPLAY_AWR('$1',format=>'advanced'));spooloffexitRAYfi}####################################################functionsforgetingaspecifiedpartitiontableinfomation###################################################getPartTableInfo(){sqlplus-s/nolog<<-RAYconn/assysdbasetlinesize400setpages1000coltable_ownerfora10coltable_namefora30colMfor9999999999colPARTITION_NAMEfora15colHIGH_VALUEfora30colNUM_ROWSfor9999999999colTABLESPACE_NAMEfora15colCOLUMN_NAMEfora20colLAST_ANALYZEDfora15SELECTa.TABLE_OWNER,a.table_name,c.M,a.PARTITION_NAME,a.HIGH_VALUE,a.NUM_ROWS,a.TABLESPACE_NAME,b.COLUMN_NAME,A.LAST_ANALYZEDFROMdba_TAB_PARTITIONSA,dba_PART_KEY_COLUMNSb,(SELECTSUM(bytes/1024/1024)M,segment_name,partition_nameFROMdba_segmentsWHEREsegment_typeLIKE'%TABLE%'ANDpartition_nameISNOTNULLandsegment_name=upper('$1')GROUPBYsegment_name,partition_nameORDERBYsegment_name,partition_nameDESC)cWHEREA.TABLE_NAME=b.NAME(+)ANDA.table_name=c.SEGMENT_NAME(+)ANDA.partition_name=c.PARTITION_NAME(+)ANDA.table_name=upper('$1')ORDERBYA.TABLE_NAME,partition_nameDESC;RAY}####################################################functionsforhelp###################################################func_help(){echo"Example:"echo"/bin/bashoracle_ray.shtype=*******"echo"Parameter:"echo"type:"echo"value:"echo"DGAPPLIED:tocheckarchivelogfileappliedinfomationforDataGuard."echo"TABLESPACE:tochecktablespaceusage."echo"ASMDISKGROUP:tocheckASMDiskgroupusage."echo"ASMDISK:tocheckASMDiskinfomation."echo"REDOINFO:togetredologinfomation."echo"REDOSHIFTtogetredologfileshiftfrequency."echo"TSDFtogetdatafilesfortablespace."echo"EXECNOW:togetexecutingsql."echo"FULLSQL:togetfullsqltext,theparametermustbeusedwithfromandsqlid."echo"theparameteronlyuseMEMORY/memory/HIST/histforfrom."echo"Example:/bin/bashoracle_ray.shype=FULLSQLfrom=memorysqlid=********"echo"INDEX:togetindexesforatables,theparametermustbeusedwithtable."echo"Example:/bin/bashoracle_ray.shtype=INDEXtable=*********"echo"XPLAN:togetexecutingplanforasql,theparametermustbeusedwithfromandsqlid."echo"theparameteronlyuseCURSOR/cursor/AWR/awrforfrom."echo"Evenyoucan'tusethisparameter,from.Cause,thereisdefaultvalue,cursor,forfrom."echo"Example:/bin/bashoracle_ray.shtype=XPLANfrom=cursorsqlid=****"echo"/bin/bashoracle_ray.shtype=XPLANsqlid=****"echo"PARTITIONINFOtogetallofpartitionwhichwillbespecifiedinfomation"echo"theparametermustbeusedwithtable."echo"Example:/bin/bashoracle_ray.shtype=PARTITIONINFOtable=**********"echo"from:"echo"value:"echo"HIST:togetfullsqltextfromhistorytable."echo"MEMORY:togetfullsqltextfrommemory."echo"CURSOR:togetXplantextfrommemory."echo"AWR:togetXplanfromawrview."echo"sqlid:specifyasqlid."echo"table:specifyatablename."echo""echo""}####################################################getparameter###################################################argvs=($@)foriin${argvs[@]}docase`echo$i|awk-F='{print$1}'|tr[a-z][A-Z]`inTYPE)ExecType=`echo$i|awk-F='{print$2}'|tr[a-z][A-Z]`;;FROM)fm=`echo$i|awk-F='{print$2}'|tr[a-z][A-Z]`;;SQLID)sqlid=`echo$i|awk-F='{print$2}'`;;TABLE)tname=`echo$i|awk-F='{print$2}'|tr[a-z][A-Z]`;;HELP)if[!`echo$i|awk-F='{print$2}'|tr[a-z][A-Z]`];thenecho"Ifyouwanttogethelp,pleasusehelp=y!"exit1elif[`echo$i|awk-F='{print$2}'|tr[a-z][A-Z]`=='Y'];thenfunc_helpexit0elseecho"Ifyouwanttogethelp,pleasusehelp=y!"exit1fiesacdone####################################################Tojudgewhetherthetypeisempty###################################################if[!${ExecType}];thenecho"TheTYPEmustbespecified!!"exit2fi####################################################execfunction###################################################case${ExecType}inDGAPPLIED)getDgApplied;;TABLESPACE)getTablespaceInfo;;ASMDISKGROUP)getAsmDiskgroup;;ASMDISK)getAsmDiskInfo;;REDOINFO)getRedoInfo;;REDOSHIFT)getRedoShiftFrequ;;TSDF)getTablespaceAndDatafile;;EXECNOW)getExecutingSQL;;PARTITIONINFO)if[!${tname}];thenecho"Thetableofparametermustbespecified!!"echo""exit3elsegetPartTableInfo"${tname}"fi;;FULLSQL)if[!${fm}];thenecho"Thefromofparametermustbespecified!"elif[${fm}=="HIST"];thengetFullSqlText"${sqlid}""HIST"elif[${fm}=="MEMORY"];thengetFullSqlText"${sqlid}""MEMORY"elseecho"ThefromofparameteronlyisHISTorMEMORY!!"echo""exit4fi;;INDEX)if[!${tname}];thenecho"Thetableofparametermustbespecified!!"echo""exit5elsegetIndexInTable"${tname}"fi;;XPLAN)if[!${sqlid}];thenecho"Thesqlidofparametermustbespecified!!"echo""exit6elseif[!${fm}];thengetXplan"${sqlid}""CURSOR"elif[${fm}=="CURSOR"];thengetXplan"${sqlid}""CURSOR"elif[${fm}=="AWR"];thengetXplan"${sqlid}""AWR"elseecho"Thefromofparameteronlyarecursororawr!!"fifi;;*)echo"Youhaveenteredainvalidparametervalue!!"echo"Ifyouwanttohelp,Youcanusetheparameter:--help.";;esac

感谢各位的阅读，以上就是“怎么编写oracle_ray.sh常用的oracle sql功能脚本”的内容了，经过本文的学习后，相信大家对怎么编写oracle_ray.sh常用的oracle sql功能脚本这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！