怎么编写oracle_ray.sh常用的oracle sql功能脚本
发表于:2025-02-19 作者:千家信息网编辑
千家信息网最后更新 2025年02月19日,这篇文章主要讲解了"怎么编写oracle_ray.sh常用的oracle sql功能脚本",文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习"怎么编写orac
千家信息网最后更新 2025年02月19日怎么编写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安全错误
数据库的锁怎样保障安全
软件开发包含的项目范围
瞩目服务器
数据库怎么改成自动
网络安全领域职称评审条件
dell服务器内存条吗
显示连接服务器已关怎么回事
数据库安全设备问题
微信打开载入数据库
网络安全图标简笔画
街道信息网络安全工作自查报告
色花服务器
上海启梦网络技术
2020最先进的网络技术
贺利坚数据库技术与应用页数
进入别人服务器提取数据库
二手服务器折价
西子奥的斯服务器按键图解
怎么降低服务器的风险
笔记本dns辅服务器未响应
帆软10数据库连接
枣庄安卓软件开发哪家靠谱
江苏前端软件开发定制
倪光南院士谈网络技术
网络技术对应大专专业
华夏恒生互联网科技etf申购
java中级软件开发工程师面试
华为服务器的声音
无线网络安全向导网络名
软件开发考研报考什么专业
大华网络服务器