怎么编写oracle_ray.sh常用的oracle sql功能脚本

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

获取帮助：

    oracle_ray.sh help=y

    更新:

        第二版：

添加了asm磁盘信息查询

        第三版：

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

#!/bin/bash #by ray #2017-09-29 #version 3.0 ################################################### #read configuration file ################################################### if [ -e ~/.bash_profile ];then . ~/.bash_profile fi if[ -e ~/.profile ];then . ~/.profile fi ################################################### #functions ################################################### ################################################### #functions for DataGuard APPlied ################################################### getDgApplied(){ sqlplus -s /nolog <<-RAY conn / assysdba set linesize300 set pages 10000 col name for a100 select dest_id,sequence#,name,applied from v\$archived_log where name is not null order by sequence#; exit RAY } ################################################### #functions for Tablespace usage ###################################################getTablespaceInfo(){ sqlplus -s /nolog <<-RAY conn /as sysdba set linesize 300 set pages 10000 col TABLESPACE_NAME fora30; col PCT_FREEfor a10; col PCT_USED for a10; col USED_MAX% fora10 select  a.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((case maxbyteswhen 0 then a.bytes_alloc else maxbytes end)/1048576,2) * 100,2) || % "USED_MAX%"  from( select  f.tablespace_name,                sum(f.bytes) bytes_alloc,                sum(decode(f.autoextensible,YES,f.maxbytes,NO, f.bytes)) maxbytes         fromdba_data_files f         groupbytablespace_name) a,       ( select  f.tablespace_name,                sum(f.bytes)  bytes_freefrom dba_free_space f         group bytablespace_name) b where a.tablespace_name = b.tablespace_name (+) union all select h.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(case f.maxbytes when 0 then (h.bytes_free + h.bytes_used) else f.maxbytes end) / 1048576, 2) * 100,2)||% "USED_MAX%"  fromsys.v_\$TEMP_SPACE_HEADER h, sys.v_\$Temp_extent_pool p, dba_temp_files f where  p.file_id(+) = h.file_idand    p.tablespace_name(+) = h.tablespace_name andf.file_id = h.file_idand    f.tablespace_name = h.tablespace_name  group byh.tablespace_name ORDER BY1; exit RAY } ################################################### #functions for ASM DiskGroup usage ################################################### getAsmDiskgroup(){ sqlplus -s /nolog <<-RAY conn / assysdba set linesize300 select name,total_mb,free_mb from v\$asm_diskgroup; RAY exit } ################################################### #functions for ASM Disk infomation ###################################################getAsmDiskInfo(){ sqlplus -s /nolog <<-RAY conn /as sysdba set linesize 300 set pages 1000 col name for a15 col path fora60 select adg.name,adg.TOTAL_MB group_TOTAL_MB,adg.free_mb group_free_mb,ad.path,ad.name,ad.TOTAL_MB disk_totle_mb,ad.free_mb disk_free_mbfromv$asm_diskgroup adg,v$asm_disk ad where adg.GROUP_NUMBER=ad.GROUP_NUMBER orderby ad.name; RAY exit } ################################################### #functions for Redo Log infomation ################################################### getRedoInfo(){ sqlplus -s /nolog <<-RAY conn / assysdba set linesize500 set pages 1000 col group# for 999 col mb for 9999 col member fora60 col thread# for 999 col archived for a10 select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv\$log a,gv\$logfile b where a.GROUP#=b.GROUP# group by a.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#; exit RAY } ################################################### #functions for Redo Log shift frequency ###################################################getRedoShiftFrequ(){ sqlplus -s /nolog <<-RAY conn /as sysdba set linesize 300 set pages 10000 SELECT  to_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_history    GROUPby     to_char(first_time,YYYY-MM-DD) order byday desc; exit RAY }################################################### #functions for Tablespace include datafile infomation ###################################################getTablespaceAndDatafile(){ sqlplus -s /nolog <<-RAY conn /as sysdba set linesize 300set pages1000 col ts_name for a30 col df_name fora100 select ts.name ts_name,df.name df_namefrom v\$tablespace ts,v\$datafile df where ts.ts#=df.ts# group by ts.name,df.name order by ts.name;exit RAY }################################################### #functions for executing sql ###################################################getExecutingSQL(){ sqlplus -s /nolog <<-RAY conn /as sysdba set linesize 80 set heading offset pages1000 col sid for 999999 col SERIAL# for 99999 col spid for 999999 col LAST_CALL_ET for a20 col sql_id for a20 col status for a20 col event for a40 select distinct sid: ||b.SID,                 serial#: ||b.SERIAL#,                 spid: ||p.SPID,                 last_call: ||b.LAST_CALL_ET as LAST_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_text              fromv\$sql a,v\$session b,v\$process p              where a.SQL_ID=b.SQL_IDand b.PADDR=p.ADDR andb.STATUS=ACTIVE order by LAST_CALL_ET desc; RAY } ################################################### #functions for geting full sqltext ################################################### getFullSqlText(){ if [ $2 == "HIST" ];then sqlplus -s /nolog <<-RAY conn / as sysdba set linesize 300 set serveroutput on set feedback off spool ./$1.txt declare  l_buffer varchar2(3000); l_amount binary_integer :=3000; l_pos int :=1; l_clob_length int; sqlid varchar2(100) := $1; begin select DBMS_LOB.getlength(sql_text) into l_clob_lengthfrom dba_hist_sqltext where sql_id=sqlid; while l_pos<l_clob_length loopselect DBMS_LOB.SUBSTR(sql_text,l_amount,l_pos) into l_bufferfromdba_hist_sqltext where sql_id=sqlid; dbms_output.put(l_buffer); l_pos:=l_pos+l_amount; endloop; dbms_output.put_line(); end; / spooloff exit RAY fi if [ $2 == "MEMORY" ];then sqlplus -s /nolog <<-RAY conn / as sysdba set linesize 300 set serveroutput onset feedbackoff spool ./$1.txt declare  l_buffer varchar2(3000); l_amount binary_integer :=3000; l_pos int :=1; l_clob_length int; sqlid varchar2(100) := $1; begin select DBMS_LOB.getlength(sql_fulltext) into l_clob_lengthfromv\$sqlarea where sql_id=sqlid;while l_pos<l_clob_length loopselect DBMS_LOB.SUBSTR(sql_fulltext,l_amount,l_pos) into l_bufferfromv\$sqlarea where sql_id=sqlid; dbms_output.put(l_buffer); l_pos:=l_pos+l_amount; endloop; dbms_output.put_line(); end; / spool offexit RAY fi }################################################### #functions for indexes in table ###################################################getIndexInTable(){ sqlplus -s /nolog <<-RAY conn /as sysdba set linesize 300 col table_name for a20 col index_name for a30 col index_type for a10 col columns fora50 select table_name,      TABLE_TYPE,      INDEX_NAME,      INDEX_TYPE,      TABLE_OWNER,      max(columns) columnsfrom(SELECT      ui.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_OWNER orderbyuic.COLUMN_POSITION)) columns FROM      dba_indexes ui,      dba_IND_COLUMNS uic WHERE      ui.INDEX_NAME (+) = uic.INDEX_NAME AND ui.TABLE_NAME = UPPER ($1)) GROUP BY      table_name,      TABLE_TYPE,      INDEX_NAME,      INDEX_TYPE,      TABLE_OWNER; exit RAY }################################################### #functions for geting executing plan ################################################### getXplan(){ if [ $2 == "CURSOR" ];then sqlplus -s /nolog <<-RAY conn / assysdba set linesize300 set pages 10000 spool ./$1.txt select * fromtable(dbms_xplan.display_cursor($1,null,advanced));   spool off exit RAY fi if [ $2 == "AWR" ];thensqlplus -s /nolog <<-RAY conn /as sysdba set linesize 300 set pages 10000 spool ./$1.txt select *from table(dbms_xplan.DISPLAY_AWR($1,format=>advanced)); spool offexit RAY fi }################################################### #functions for geting a specified partition table infomation ################################################### getPartTableInfo(){ sqlplus -s /nolog <<-RAY conn / as sysdba set linesize 400 set pages 1000 col table_owner for a10 col table_name for a30 col M for 9999999999col PARTITION_NAMEfor a15  col HIGH_VALUE for a30 col NUM_ROWS for 9999999999 col TABLESPACE_NAME for a15 col COLUMN_NAME for a20 col LAST_ANALYZED fora15 SELECT     a.TABLE_OWNER,     a.table_name,     c. M,     a.PARTITION_NAME,     a.HIGH_VALUE,     a.NUM_ROWS,     a.TABLESPACE_NAME,     b.COLUMN_NAME,     A.LAST_ANALYZED FROM     dba_TAB_PARTITIONS A,     dba_PART_KEY_COLUMNS b,     (         SELECT             SUM (bytes/ 1024 / 1024) M,             segment_name,             partition_name         FROM             dba_segments         WHERE segment_type LIKE%TABLE%         AND partition_name IS NOT NULL         andsegment_name = upper($1)         GROUP BY             segment_name,             partition_name         ORDER BY             segment_name,             partition_name DESC     ) c WHERE     A .TABLE_NAME = b. NAME(+) AND A .table_name = c.SEGMENT_NAME(+) AND A .partition_name = c.PARTITION_NAME(+) AND A .table_name = upper($1) ORDER BY     A .TABLE_NAME,     partition_name DESC; RAY }################################################### #functions for help ################################################### func_help(){ echo "Example:" echo " /bin/bash oracle_ray.sh type=*******" echo "Parameter:" echo "   type:" echo " value:" echo " DGAPPLIED: to check archive logfile applied infomation for DataGuard." echo " TABLESPACE: to check tablespace usage."echo" ASMDISKGROUP: to check ASM Diskgroup usage." echo " ASMDISK: to check ASM Disk infomation." echo " REDOINFO: to get redo log infomation." echo " REDOSHIFT to get redo logfile shift frequency." echo " TSDF to get datafiles for tablespace." echo " EXECNOW: to get executing sql." echo " FULLSQL: to get full sql text,the parameter must be used with from and sqlid." echo " the parameter only use MEMORY/memory/HIST/hist for from." echo " Example: /bin/bash oracle_ray.sh ype=FULLSQL from=memory sqlid=********" echo " INDEX: to get indexes for a tables,the parameter must be used with table." echo " Example: /bin/bash oracle_ray.sh type=INDEX table=*********" echo " XPLAN: to get executing plan for a sql,the parameter must be used with from and sqlid." echo " the parameter only use CURSOR/cursor/AWR/awr for from." echo " Even you cant use this parameter,from.Cause,there is default value,cursor,for from." echo " Example: /bin/bash oracle_ray.sh type=XPLAN from=cursor sqlid=****" echo " /bin/bash oracle_ray.sh type=XPLAN sqlid=****" echo " PARTITIONINFO to get all of partition which will be specified infomation" echo " the parameter must be used with table." echo " Example: /bin/bash oracle_ray.sh type=PARTITIONINFO table=**********" echo "   from:" echo " value:" echo " HIST: to get full sql text from history table." echo " MEMORY: to get full sql text from memory." echo " CURSOR: to get Xplan text from memory." echo " AWR: to get Xplan from awr view." echo "   sqlid: specify a sql id." echo "   table: specify a table name." echo ""echo"" } ################################################### #get parameter ################################################### argvs=($@) for i in ${argvs[@]} do         case `echo $i | awk -F= {print $1} | tr [a-z] [A-Z]` in          TYPE)             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]` ];then          echo "If you want to get help,pleas use help=y!"          exit 1          elif [ `echo $i | awk -F={print $2} | tr [a-z] [A-Z]` == Y ];then              func_help              exit 0             else              echo "If you want to get help,pleas use help=y!"              exit 1             fi         esac done ################################################### #To judge whether the type is empty ################################################### if [ ! ${ExecType} ]; then       echo "The TYPE must be specified!!"     exit 2 fi ################################################### #exec function ################################################### case ${ExecType} inDGAPPLIED) getDgApplied ;; TABLESPACE) getTablespaceInfo ;; ASMDISKGROUP) getAsmDiskgroup ;; ASMDISK) getAsmDiskInfo ;; REDOINFO) getRedoInfo ;; REDOSHIFT) getRedoShiftFrequ ;; TSDF) getTablespaceAndDatafile ;; EXECNOW) getExecutingSQL ;; PARTITIONINFO)if [ ! ${tname} ];then      echo "The table of parameter must be specified!!"      echo ""      exit 3 elsegetPartTableInfo"${tname}"     fi  ;; FULLSQL) if [ ! ${fm} ];then echo "The from of parameter must be specified!" elif [ ${fm} == "HIST" ];then       getFullSqlText "${sqlid}" "HIST"   elif [ ${fm} == "MEMORY" ];thengetFullSqlText"${sqlid}" "MEMORY"   else       echo "The from of parameter only is HIST or MEMORY!!"       echo ""       exit 4fi ;; INDEX)if [ ! ${tname} ];then      echo "The table of parameter must be specified!!"      echo ""exit5 else      getIndexInTable "${tname}"     fi ;; XPLAN)     if [ ! ${sqlid} ];then      echo "The sqlid of parameter must be specified!!"      echo ""      exit 6     else      if [ ! ${fm} ];then      getXplan "${sqlid}" "CURSOR"elif [ ${fm} =="CURSOR" ];then      getXplan "${sqlid}" "CURSOR"      elif [ ${fm} == "AWR" ];then      getXplan "${sqlid}" "AWR"      else      echo "The from of parameter only are cursor or awr!!"fi     fi ;; *)     echo"You have entered a invalid parameter value!!" echo "If you want to help, You can use the parameter: --help ." ;; esac

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