怎么编写oracle_ray.sh常用的oracle sql功能脚本
发表于：2024-11-30 作者：千家信息网编辑
千家信息网最后更新 2024年11月30日，这篇文章主要讲解了"怎么编写oracle_ray.sh常用的oracle sql功能脚本"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"怎么编写orac
千家信息网最后更新 2024年11月30日怎么编写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安全错误
数据库的锁怎样保障安全
wincc安装数据库一直失败
湛江软件开发外包
坚果pro邮箱无法连接服务器
网络安全知识大赛答题活动
怎么保证服务器ip不变
软件开发培训班的服务水平
万得非公开数据库
南京沁华园网络技术有限公司
软件开发编程工程
耕地集中连片数据库
欧盟加强网络安全一揽子计划
数据库系统硬件
数据库表导出到另一张表怎么测试
吴忠市网络安全宣传
网络安全属于国家安全体系吗
字符串类型数据库
总票房电影票房排行榜数据库
计算机网络技术要用什么电脑
软件开发工程师语言
网络安全线上展厅
电子工程可以从事软件开发吗
怪物猎人世界各大服务器地图
宁州电厂网络安全
网络安全常识qq安全
将本地数据库复制到服务器上
量化交易软件开发
ios软件开发方案报价
数据库连接池 探测
2018国家网络安全宣传周主题
如何构建网络安全防护系统
