概述

今天主要分享一下两个shell脚本，主要是为了统计Oracle快照信息及数据库表明细，下面一起来看看吧~

数据库连接脚本

use script settdb.sh for DB login details registry

#!/bin/bash
tmp_username=$SH_USERNAME
tmp_password=$SH_PASSWORD
tmp_db_sid=$SH_DB_SID

#check $1 and $2 should be mandatory from input
if [[ -z $1 ]] || [[ -z $2 ]]; then
echo '***********************************************'
echo 'WARNING :UserName And PassWord Is Needed!'
echo '***********************************************'

exit
fi
if [[ -z $3 ]] && [[ -z $ORACLE_SID ]];then
echo '***********************************************'
echo 'WARNING :There is Instance can be used !'
echo '***********************************************'
exit
fi

SH_USERNAME=`echo "$1"|tr '[a-z]' '[A-Z]'`
SH_PASSWORD=$2
echo '***********************************************'

if [[ -z $3 ]]
then
 SH_DB_SID=$ORACLE_SID
 echo 'Using Default Instance :'$ORACLE_SID
 echo .
else
 SH_DB_SID=`echo "$3"|tr '[a-z]' '[A-Z]'`
fi

if [[ $SH_DB_SID = $tmp_db_sid ]] && [[ $SH_USERNAME = $tmp_username ]] && [[ $SH_PASSWORD = $tmp_password ]];then
 echo 'Instance '$SH_DB_SID 'has been connected'
 echo '***********************************************'
 exit
fi

export SH_USERNAME=$SH_USERNAME
export SH_DB_SID=$SH_DB_SID
export SH_PASSWORD=$SH_PASSWORD
export DB_CONN_STR=$SH_USERNAME/$SH_PASSWORD
#echo $DB_CONN_STR
listfile=`pwd`/listdb
Num=`echo show user | $ORACLE_HOME/bin/sqlplus -s $DB_CONN_STR@$SH_DB_SID| grep -i 'USER ' | wc -l`
if [ $Num -gt 0 ]
 then
 ## ok - instance is up
 echo 'Instance '$SH_DB_SID 'has been connected'
 echo -e '--' `date`'-- \n--'$SH_USERNAME@$SH_DB_SID 'has been connected --\n' >> listdb
 echo '***********************************************'
 echo 'Initalize DB login details registry OK!'
 echo 'Now you can Execution script~'
 echo '***********************************************'
 $SHELL

 else
 ## inst is inaccessible 
 echo Instance: $SH_DB_SID Is Invalid Or UserName/PassWord Is Wrong 
 echo '***********************************************'
 exit
 fi
del_length=3
tmp_txt=$(sed -n '$=' listdb) 
echo '***********************************************'
echo '********* ' $SH_USERNAME'@'$SH_DB_SID '**********'
echo '***********************************************'
curr_len=`cat $listfile|wc -l`
if [ $curr_len -gt $del_length ]; then
echo ' There Are Below Sessions Still Alive '
echo '***********************************************'
fi
sed $((${tmp_txt}-${del_length}+1)),${tmp_txt}d $listfile | tee tmp_listfile
mv tmp_listfile $listfile

输出：./settdb.sh 用户名 用户密码

showsnap.sh

脚本内容如下：

#!/bin/bash
echo "======================================查看数据库快照信息(时间:$1 $2-$3)================================================"
sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOF
break on db_name
set pages 500
set linesize 1000
col snapdate format a20
col db_name format a20
col inst_name format a20
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid
 , d.name db_name
 , i.instance_number inst_num
 , i.instance_name inst_name
 from v\$database d,
 v\$instance i;
select 
db_name
,begin_snap
,end_snap
,snapdate
,lvl
,round(((END_INTERVAL_TIME+0)-(BEGIN_INTERVAL_TIME+0 ))*24*60) duration_mins
,round((select round((sum(e.value) -
 sum(b.value)) / 1000000 /60,2) dbtime
 FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL b
 WHERE
 e.STAT_NAME = 'DB time'
 and b.snap_id=begin_snap
 and e.snap_id =end_snap
 AND b.STAT_NAME = 'DB time'
 group by e.snap_id,b.snap_id)) dbtime
from
( 
select 
 di.db_name db_name
 , s.snap_id begin_snap
 ,lead(s.snap_id ,1,s.snap_id ) over(order by s.end_interval_time ) end_snap
 , to_char(s.end_interval_time,'dd Mon YYYY HH24:mi') snapdate
 , s.snap_level lvl 
 ,s.end_interval_time
 ,s.begin_interval_time
 from dba_hist_snapshot s
 , dba_hist_database_instance di
 where 
 ( di.dbid,di.instance_number) in
 (select d.dbid dbid
 , i.instance_number inst_num
 from v\$database d,
 v\$instance i)
 and di.dbid = s.dbid
 and di.instance_number = s.instance_number
 and di.startup_time = s.startup_time
 and to_char(END_INTERVAL_TIME,'yyyymmdd')='$1'
 and EXTRACT(HOUR FROM END_INTERVAL_TIME) between $2 and $3 
 order by db_name, instance_name, snap_id
 ); 
EOF

输出：

--假设查看2019年11月11日 8点到9点信息
./showsnap.sh 20191111 8 9

showtab.sh

脚本内容如下：

#!/bin/bash
echo "==================================================查看数据库用户$1的$2信息==================================================================="
TAB_OWNER=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<END
set pagesize 300 feedback off verify off heading on echo off 
set linesize 1000
col owner_name format a20
col table_name format a30
select owner,object_name table_name from dba_objects where object_name=upper('$2') and owner=upper('$1') and object_type='TABLE'; 
exit; 
END` 

if [ -z "$TAB_OWNER" ]; then 
 echo "no object exists, please check again" 
 exit 0 
else 
 echo '*******************************************'
 echo " $TAB_OWNER " 
 echo '*******************************************'
fi 


sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOF
set serveroutput on
set pages 1000 
set head on
set linesize 1000
col table_name format a30
col partitoned format a10
col tablespace_name format a10
col status format a6
!echo '********** TABLE GENERAL INFO *****************';
select table_name,partitioned,tablespace_name,status,ini_trans,num_rows,blocks,empty_blocks,logging,monitoring,row_movement,last_analyzed from dba_tables where owner=upper('$1') and table_name=upper('$2');
!echo '********** TABLE STORAGE INFO *****************';
col initext format 99999999
col nxtext format 99999999
col minext format 999999999
col maxext format 9999999999
col compres format a10
select initial_extent initext,next_extent nxtext,min_extents minext,max_extents maxext,freelists,avg_space,chain_cnt,avg_row_len,cache,temporary,dependencies,compression compres from dba_tables where owner=upper('$1') and t
able_name=upper('$2'); !echo '********** TABLE columns INFO *****************';
col name format a20
set pages 1000
col table_name format a30 
col column_name format a30
col data_type format a15
col nullable format a10
col data_default format a20
SELECT t1.column_id, 
 t1.COLUMN_NAME, 
 t1.DATA_TYPE 
 || DECODE ( 
 t1.DATA_TYPE, 
 'NUMBER', DECODE ( 
 '(' 
 || NVL (TO_CHAR (t1.DATA_PRECISION), '*') 
 || ',' 
 || NVL (TO_CHAR (t1.DATA_SCALE), '*') 
 || ')', 
 '(*,*)', NULL, 
 '(*,0)', '(38)', 
 '(' 
 || NVL (TO_CHAR (t1.DATA_PRECISION), '*') 
 || ',' 
 || NVL (TO_CHAR (t1.DATA_SCALE), '*') 
 || ')'), 
 'FLOAT', '(' || t1.DATA_PRECISION || ')', 
 'DATE', NULL, 
 'TIMESTAMP(6)', NULL, 
 '(' || t1.DATA_LENGTH || ')') 
 AS DATA_TYPE, 
 t1.DATA_LENGTH, 
 t1.NULLABLE ,
 t1.DATA_DEFAULT
 FROM dba_TAB_COLUMNS t1
where table_name=upper('$2')
 and owner=upper('$1')
order by t1.column_id;

!echo '********** CONSTRAINTS DETAILS INFO *****************';
col column_name format a20
col search_condition format a45
col constraint_name format a30
col r_owner format a10
col r_constraint_name format a15
col index_name format a30
select t2.column_name, t1.constraint_name,t1.constraint_type,t1.search_condition,t1.deferred,t1.deferrable,t1.rely,t1.index_name
from (select constraint_name,constraint_type,search_condition,deferred,deferrable,rely,index_name from dba_constraints
where table_name=upper('$2') and owner=upper('$1') ) t1,
(select constraint_name,column_name from dba_cons_columns where table_name=upper('$2') and owner=upper('$1') ) t2
where t1.constraint_name=t2.constraint_name
;


!echo '********** INDEX DETAILS INFO *****************';
col column_list format a30
col index_name format a30
col index_type format a10
col table_type format a10
select ind.index_name,ind.tablespace_name,ind.index_type,ind.uniqueness,ind.partitioned,temp_cols.column_list,
ind.table_type,ind.status,ind.num_rows,ind.last_analyzed,ind.generated from 
(SELECT index_name, SUBSTR (MAX (SYS_CONNECT_BY_PATH (column_name, ',')), 2) column_list
FROM (SELECT /*+rule*/ index_name, column_name, rn, LEAD (rn) OVER (PARTITION BY index_name ORDER BY rn) rn1
 FROM (SELECT index_name, column_name, ROW_NUMBER () OVER (ORDER BY column_position desc) rn
 FROM dba_ind_columns where table_name=upper('$2') and table_owner=upper('$1')))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY index_name) temp_cols,dba_indexes ind
where ind.table_name=upper('$2') and ind.table_owner=upper('$1')
and ind.index_name=temp_cols.index_name
;

 select t.table_name,i.index_name,
 i.clustering_factor,t.blocks,t.num_rows
 from dba_indexes i,dba_tables t
 where i.table_name=t.table_name
 and i.table_owner=t.owner
 and t.table_name = upper('$2')
 and t.owner=upper('$1')
 order by t.table_name,i.index_name;



!echo '********** ROLE GRANTS DETAILS INFO *****************';

col grantor format a15
col privilege format a15
col object_name format a30
col role_name format a20
select grantor,privilege,table_name object_name,grantee role_name,grantable,hierarchy 
from dba_tab_privs 
where table_name=upper('$2') and owner=upper('$1') 
and grantee in (select role from dba_roles )
;



!echo '********** OBJECT GRANTS DETAILS INFO *****************';

select grantor,privilege,table_name object_name,grantee role_name,grantable,hierarchy 
from dba_tab_privs 
where table_name=upper('$2') and owner=upper('$1') 
and grantee in (select username from dba_users);


!echo '********** SYNONYMS DETAILS INFO *****************';
col db_link format a20
col owner format a20
col synonym_name format a30
col table_name format a30
col table_owner format a15
select owner,synonym_name,table_owner,table_name,db_link 
from dba_synonyms
 where table_name=upper('$2') and table_owner=upper('$1');
!echo '********** TRIGGER DETAIL INFO *****************';
col triggering_event format a20
col owner format a15
col trigger_name format a20
col trigger_type format a15
col base_object_type format a10
col status format a8
select owner,trigger_name,trigger_Type,triggering_event,base_object_Type,status,action_type 
from dba_triggers where table_name=upper('$2') and owner=upper('$1'); 
EOF
exit

输出：

./showtab.sh 用户名 表名

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下