Estimate tablespaces usage before database refresh

We have been facing missing tablespaces/inadequate space in tablespaces issue while performing database refresh. The import jobs are failed or suspended due to tablespaces issue it leads delay

In database refresh. we have developed the scripts(tablespaces_usage.sh & compare_files.pl)  to work around the issue. These scripts are generate a report about  missing tablespaces/inadequate space in tablespaces in target database. I hope this will help resolve our issue.

  tablespaces_usage.sh:

#!/bin/bash
#######################################################################
 #Programmer : ADINARAYANA
 #Purpose    : Estimate tablespace usages in source & target databases
 ########################################################################
echo "1) Source Database:"
 echo "2) Target Database:"
 echo "3) EXIT:"
 read -p 'Please Enter the choice :' CHOICE
 case $CHOICE in
 1)
 read -p 'Please Enter ORACLE Sid : ' sid
 read -p 'Please Enter ORACLE Home: ' home
 read -p 'Please Enter Schema: ' user
 stty -echo
 read -p 'Please Enter Schema Password: ' pass
 stty echo
 echo ""
 export ORACLE_SID=$sid
 export ORACLE_HOME=$home
 file=Tablespace_$sid.log
 if [  -f $file ]; then
 {
 /dev/null > $file 2>/dev/null
 }
 fi
 stat_1="select tablespace_name,sum(bytes/1024/1024) from  user_segments group by tablespace_name order by tablespace_name";
 read_sql_stmt() {
 typeset stmt=$1
 typeset login=$2
 echo "set feedback off verify off heading off pagesize 0
 $stmt;
 exit
 " |  $ORACLE_HOME/bin/sqlplus -s  $login
 }
 read_sql_stmt "$stat_1" "$user/$pass" | while read var_1 var_2
 do
 echo "$var_1 ,  $var_2" >> $file
 done
 ;;
 2)
 read -p 'Please Enter ORACLE Sid : ' sid
 read -p 'Please Enter ORACLE Home: ' home
 read -p 'Please Enter the FileName: ' filename
 read -p 'Please Enter Schema: ' user
 stty -echo
 read -p 'Please Enter Schema Password: ' pass
 stty echo
 echo ""
 export ORACLE_SID=$sid
 export ORACLE_HOME=$home
 export resultset=$user
 file=Tablespace_$sid.log
 if [  -f $file ]; then
 {
 /dev/null > $file 2>/dev/null
 }
 fi
 read_sql_stmt() {
 typeset stmt=$1
 typeset login=$2
 echo "set feedback off verify off heading off pagesize 0
 define app_user='$resultset';
 $stmt;
 exit
 " |  $ORACLE_HOME/bin/sqlplus -s  $login
 }
 read_sql_stmt "select b.tablespace_name ,a.free_space , b.tmax_size,b.tbs_size from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space  from dba_free_space where tablespace_name in(select distinct(TABLESPACE_NAME) from dba_segments where OWNER=upper('&app_user')) group by tablespace_name) a,(select tablespace_name, sum(bytes)/1024/1024 as tbs_size,sum(MAXBYTES)/1024/1024as tmax_size from dba_data_files where tablespace_name in(select distinct(TABLESPACE_NAME) from dba_segments where OWNER=upper('&app_user')) group by tablespace_name) b where a.tablespace_name(+)=b.tablespace_name order by tablespace_name" "$user/$pass" | while read var_1 var_2 var_3 var_4
 do
 echo "$var_1 , $var_2, $var_3, $var_4" >> $file
 done
 ;;
*) exit ;;
 esac

compare_files.pl:

#!/usr/bin/perl
######################################################################
 #Programmer : ADINARAYANA
 #Purpose    : Compare Sourcefile & Target file
 ######################################################################
 my $var_1 = 0;
 my ($day,$month,$date,$hour,$year) = split /\s+/,scalar localtime;
 my $output_file = 'report_tablespaces-'.$date.'.'.$month.'.log';
 print "Enter the source filename: ";
 my $source_file = <STDIN>;
 print "Enter the target filename: ";
 my $target_file = <STDIN>;
 my %comp;
 my %myhash;
 my %atthash;
 open (LOGFILE, ">$output_file") or  die ("Could not open $output_file!");
 open (R, $source_file) or  die ("Could not open $source_file!");
 while(<R>){
 chomp;
 my ($k,$v) = split(',');
 #my ($k,$v1,$v2) = split(',');
 push (@{$comp{'R'}{$k}},$v);
 }
 close(R);
open (P, $target_file) or die ("Could not open $target_file!");
 while(<P>){
 chomp;
 my ($k,$v1,$v2,$v3) = split(',');
 push (@{$comp{'P'}{$k}},$v1);
 push (@{$myhash{$k}},$v2);
 push (@{$atthash{$k}},$v3);
 }
 close(P);
 #foreach my $Z (keys%{$comp{'P'}}) {
 #printf "%s %s\n",$Z,"Key";
 #printf "%s %s\n",@{$comp{'P'}{$Z}},"Value";
 #}
printf  LOGFILE "************MISSING TABLESPACES**************\n";
foreach my $T (keys%{$comp{'R'}}) {
 if ( !exists($comp{'P'}{$T})) {
 $var_1++;
 printf  LOGFILE "%s %g %s\n",$T, @{$comp{'R'}{$T}},"MB";
 }
 }
if($var_1 == 0){
 printf  LOGFILE "---------THERE ARE NO MISSING TABLESPACES-----------\n";
 }
 printf LOGFILE  "*****************************************************************************************************************\n";
 printf LOGFILE  "TablespaceName\t\tSize(MB)\t\tFreeSpace(MB)\t\tMaxSize(MB)\t\tAdditionalSpaceRequired for Import(MB)\n";
 printf LOGFILE  "*****************************************************************************************************************\n";
foreach my $A (keys%{$comp{'R'}}) {
 if (exists $comp{'P'}{$A}) {
 foreach my $classR (@{$comp{'R'}{$A}}) {
 foreach my $classP ( @{$comp{'P'}{$A}} ) {
 if($classR > $classP) {
 my $B = $A;
 $B =~ s/\s+$//;
 #printf  "%s %g %s %g %s\n","Need additional",$classR-$classP,"MB space is required in $B  tablespace for import.Tablespace maxsize is",@{$myhash{$A}},"MB";
printf LOGFILE "%-9s %s %-6g %s %-6g %s %-6g %s %-6g\n",$B,"\t\t",@{$atthash{$A}},"\t\t",@{$comp{'P'}{$A}},"\t\t",@{$myhash{$A}},"\t\t",$classR-$classP ;
 }
 }
 }
 }
 }
 close(LOGFILE);

 1.    Source database(BETA):

       

      Source tablespace log:

3

.       Target database(ALPHA):

Note: Run this script after target database objects(schema/tables) are dropped.

 

 

 

 

Target tablespace log:

Compare Source &target tablespace logs:

 

 

 

Final results log file:

 

 

 

 

If any tablespaces are missed out it will generate output something below.

 

 

 

 

 

To prepare this script took over a moth to test and deliver it successfully after so many testings between Source & Destination database. Happy Reading!!! 🙂