怎么编写oracle_ray.sh常用的oracle sql功能脚本
发表于：2025-11-12 作者：千家信息网编辑
千家信息网最后更新 2025年11月12日，这篇文章主要讲解了"怎么编写oracle_ray.sh常用的oracle sql功能脚本"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"怎么编写orac
千家信息网最后更新 2025年11月12日怎么编写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_profilefiif [ -e ~/.profile ];then . ~/.profilefi####################################################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'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 esacdone####################################################To judge whether the type is empty###################################################if [ ! ${ExecType} ]; then echo "The TYPE must be specified!!" exit 2fi####################################################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 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功能脚本这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！
功能
常用
脚本
学习
内容
信息
就是
思路
情况
文章
更多
知识
知识点
磁盘
篇文章
跟着
问题
b.
c.
实践
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
数据库创表 text 类型
无线网络技术具体有哪些应用
属于数据库管理的方式有
大冶有色有软件开发
手机屏幕是用什么软件开发的
91连接服务器中
射频识别卡怎么用数据库
佰悦互联网科技有限公司
张家港运营网络技术市场价格
胡姓软件开发公司
本地服务器节点转发
软件开发的存在的风险点
网络安全法规定的范围
工业物联网网络安全工具
数据库怎么分布
云算盘网络安全宣传
特朗普解雇美国网络安全局
网络安全分法试题及答案
少数民族软件开发
vs建立数据库列怎么右移动
铁路网络技术达人
明日方舟进哪个服务器
东莞创致网络技术有限公司
mfc操作sql数据库
海东网络技术推荐厂家
计算机与网络技术月薪
db2怎么查数据库日期
数据库及其应用是软件课吗
实施人员服务器搭建
量子密钥数据库加密系统
