Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

August 24, 2010

How to check available partition

Filed under: Uncategorized — orawiss @ 3:48 pm

• 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
###————————————————-###

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: