Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

• Goal: What information to collect to check available partition?
• Fact: Oracle 10G Server – Enterprise Edition
• OS : Redhat Linux
• Script Name : Check_table_partition.sh

###—————————————————————###
ExecQuery ()
{
echo `$ORACLE_HOME/bin/sqlplus -s “DBA_USER/SMCmap060″ << EOF
set head off
set ver off
set echo off
set feed off
set pagesize 0
$1;
exit
EOF
`
}

CheckTablePArtitions()
{
sqlplus -s “DBA_USER/SMCmap060″ < $LOG_DIR/${YYYYMMDD}_check_table_partitions_”$1″.log
set head off
set ver off
set echo off
set feed off
set linesize 850
set serveroutput on
declare

high_month_part varchar2(8);
curr_month varchar2(8);
next_month varchar2(8);
check_curr_month varchar2(4);
check_next_month varchar2(4);

high_value_vchar varchar2(4000);

cursor c_part_high_values_list is
select DISTINCT table_name,table_owner from sys.dba_tab_partitions
where table_owner||’.'|| table_name IN
(SELECT KC.OWNER ||’.'||KC.NAME
–,(SELECT DATA_TYPE FROM DBA_TAB_COLS WHERE OWNER=KC.OWNER AND TABLE_NAME=KC.NAME AND COLUMN_NAME=KC.COLUMN_NAME)
FROM DBA_PART_KEY_COLUMNS KC
WHERE OBJECT_TYPE=’TABLE’
AND KC.COLUMN_NAME LIKE ‘%MONTH%’) ;

cursor c_part_high_values (p_tname sys.dba_tab_partitions.table_name%TYPE,p_towner sys.dba_tab_partitions.table_OWNER%TYPE) is
select high_value from sys.dba_tab_partitions
where table_name = p_tname and table_owner = p_towner;

c_part_high_values_list_rec c_part_high_values_list%ROWTYPE;

begin
dbms_output.put_line(‘Start check table partitions’);

curr_month:=TO_CHAR(SYSDATE,’YYYYMM’);
next_month:=TO_CHAR(ADD_MONTHS(SYSDATE,+1),’YYYYMM’);

check_curr_month:=’N';
check_next_month:=’N';

OPEN c_part_high_values_list;

LOOP

FETCH c_part_high_values_list INTO c_part_high_values_list_rec;
EXIT WHEN c_part_high_values_list%NOTFOUND;

check_curr_month:=’N';
check_next_month:=’N';

–
for r_part_high_values in c_part_high_values (c_part_high_values_list_rec.table_name,c_part_high_values_list_rec.table_owner)
loop

SELECT r_part_high_values.high_value into high_value_vchar FROM DUAL;

if substr(high_value_vchar,2,6)=curr_month then
check_curr_month:=’Y';
end if ;

if substr(high_value_vchar,2,6)=next_month then
check_next_month:=’Y';
end if ;

high_month_part:=substr(high_value_vchar,2,6);

end loop;

if (check_curr_month=’N’ or check_next_month=’N’ ) then
dbms_output.put_line(‘Table_Name: ‘ || c_part_high_values_list_rec.table_owner || ‘.’ ||c_part_high_values_list_rec.table_name);
dbms_output.put_line(‘ |curr_month_table_part_exist :’ || check_curr_month );
dbms_output.put_line(‘ |next_month_table_part_exist :’ || check_next_month );
dbms_output.put_line(‘ |current_high_month_table_part :’ || high_month_part );
–else
–dbms_output.put_line(‘..ok’);
end if;
–

END LOOP;

CLOSE c_part_high_values_list;
dbms_output.put_line(‘done.’);
end ;
/
exit
QUERY
}

CheckTablePArtitions_remote ()
{
ssh $1 < $LOG_DIR/${YYYYMMDD}_check_table_partitions_”$1″.log
sqlplus -s “DBA_USER/$3″ < ok < |’);
end if;
–

END LOOP;

CLOSE c_part_high_values_list;
dbms_output.put_line(‘done.’);
end ;
/
exit
QUERY
EOF
}

#—————————————
usage () {
printf “\n”
printf “Shell check table partions \n”
printf “Usage: ./check_table_partitions par1 par2 \n”
printf ” par1 : DataBase Server Name (ssh connection enabled)\n”
printf ” par2 : Instance Name\n”
printf “\n”
}
#—————————————

####– MAIN –####
. /home/oracle/bin/oracle.conf

if (test -z “$1″) || (test -z “$2″) ; then
##|| (test -z “$3″) || (test -z “$4″)

echo ” ”
echo “error: one or more parameter missing”
echo ” “

usage
echo “Examples :”
echo “./check_table_partitions.sh cdb01 CDB01″
echo “./check_table_partitions.sh x3 DB03″
echo “./check_table_partitions.sh x2 DB02″
echo “./check_table_partitions.sh x1 DB01″

else

echo ” “

# get db objects size from dba_segments
if (test “$1″ = “$HOSTNAME”); then

# check_table_partitions
CheckTablePArtitions “$1″ “$2″

else

#get_pwd
pwd=$(ExecQuery “select pwd from common.user_access where trim(upper(username))=’DBA_USER’ and dbserver=’$1′”)

# Connect via ssh and check_table_partitions
CheckTablePArtitions_remote “$1″ “$2″ “$pwd”

fi

echo “…done”
echo “( see details in $LOG_DIR/${YYYYMMDD}_check_table_partitions_${1}.log )”

fi
###————————————————-###