这篇文章主要讲解了“怎么编写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 / as sysdba set linesize 300 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 for a30; col PCT_FREE for a10; col PCT_USED for a10; col USED_MAX% for a10 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 maxbytes when 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 from dba_data_files f group by tablespace_name) a, ( select f.tablespace_name, sum(f.bytes) bytes_free from dba_free_space f group by tablespace_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%" from sys.v_\$TEMP_SPACE_HEADER h, sys.v_\$Temp_extent_pool p, dba_temp_files f where p.file_id(+) = h.file_id and p.tablespace_name(+) = h.tablespace_name and f.file_id = h.file_id and f.tablespace_name = h.tablespace_name group by h.tablespace_name ORDER BY 1; exit RAY } ################################################### #functions for ASM DiskGroup usage ################################################### getAsmDiskgroup(){ sqlplus -s /nolog <<-RAY conn / as sysdba set linesize 300 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 for a60 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_mb from v$asm_diskgroup adg,v$asm_disk ad where adg.GROUP_NUMBER=ad.GROUP_NUMBER order by ad.name; RAY exit } ################################################### #functions for Redo Log infomation ################################################### getRedoInfo(){ sqlplus -s /nolog <<-RAY conn / as sysdba set linesize 500 set pages 1000 col group# for 999 col mb for 9999 col member for a60 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" from v\$log_history GROUP by to_char(first_time,'YYYY-MM-DD') order by day desc; exit RAY } ################################################### #functions for Tablespace include datafile infomation ################################################### getTablespaceAndDatafile(){ sqlplus -s /nolog <<-RAY conn / as sysdba set linesize 300 set pages 1000 col ts_name for a30 col df_name for a100 select ts.name ts_name,df.name df_name from 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 off set pages 1000 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 from v\$sql a,v\$session b,v\$process p where a.SQL_ID=b.SQL_ID and b.PADDR=p.ADDR and b.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_length from dba_hist_sqltext where sql_id=sqlid; while l_pos<l_clob_length loop select DBMS_LOB.SUBSTR(sql_text,l_amount,l_pos) into l_buffer from dba_hist_sqltext where sql_id=sqlid; dbms_output.put(l_buffer); l_pos:=l_pos+l_amount; end loop; dbms_output.put_line(' '); end; / spool off exit RAY fi if [ $2 == "MEMORY" ];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_fulltext) into l_clob_length from v\$sqlarea where sql_id=sqlid; while l_pos<l_clob_length loop select DBMS_LOB.SUBSTR(sql_fulltext,l_amount,l_pos) into l_buffer from v\$sqlarea where sql_id=sqlid; dbms_output.put(l_buffer); l_pos:=l_pos+l_amount; end loop; dbms_output.put_line(' '); end; / spool off exit 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 for a50 select table_name, TABLE_TYPE, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, max(columns) columns from (SELECT ui.table_name, ui.TABLE_TYPE, ui.INDEX_NAME, ui.INDEX_TYPE, uic.TABLE_OWNER, to_char(wm_concat (uic.COLUMN_NAME) over(partition by ui.table_name,ui.TABLE_TYPE,ui.INDEX_NAME,ui.INDEX_TYPE,uic.TABLE_OWNER order by uic.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 / as sysdba set linesize 300 set pages 10000 spool ./$1.txt select * from table(dbms_xplan.display_cursor('$1',null,'advanced')); spool off exit RAY fi if [ $2 == "AWR" ];then sqlplus -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 off exit 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 9999999999 col PARTITION_NAME for a15 col HIGH_VALUE for a30 col NUM_ROWS for 9999999999 col TABLESPACE_NAME for a15 col COLUMN_NAME for a20 col LAST_ANALYZED for a15 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 and segment_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 can't 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} in DGAPPLIED) 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 else getPartTableInfo "${tname}" fi ;; FULLSQL) if [ ! ${fm} ];then echo "The from of parameter must be specified!" elif [ ${fm} == "HIST" ];then getFullSqlText "${sqlid}" "HIST" elif [ ${fm} == "MEMORY" ];then getFullSqlText "${sqlid}" "MEMORY" else echo "The from of parameter only is HIST or MEMORY!!" echo "" exit 4 fi ;; INDEX) if [ ! ${tname} ];then echo "The table of parameter must be specified!!" echo "" exit 5 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功能脚本这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。