怎么编写oracle_ray.sh常用的oracle sql功能脚本
发表于：2024-09-22 作者：千家信息网编辑
千家信息网最后更新 2024年09月22日，这篇文章主要讲解了"怎么编写oracle_ray.sh常用的oracle sql功能脚本"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"怎么编写orac
千家信息网最后更新 2024年09月22日怎么编写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安全错误
数据库的锁怎样保障安全
网络安全方面研讨问题
数据库统一接口组件
以月份分组查数据库
探岳gte高速服务器能否充电
我心中的网络安全1500字论文
软件开发 转销售
造梦西游ol服务器看不见人
软件开发模型 之迭代
软件开发aop
闵行区好的软件开发有什么
汇盈互联网科技
阿里巴巴俄罗斯服务器
下列关于数据库的说法不正确的有
超频服务器的价格
鄂尔多斯手机软件开发
银行系统网络安全等级保护测评
娄底学数据库软件应用能力在哪学
易语言服务器断开
信息技术高中走进数据库
软件开发收费标准文件
上海楼宇数据库
软件开发甲方不验收
利用网络技术支持课堂教学改革
苹果软件开发商怎么设置信任
山东仓库库存软件开发
musql 数据库如何优化
福建服务器硬盘质保期
c数据库类
db2导入txt数据库
数据库设置查询条件
