Add space to Tablespace

===============================
Resize Datafile.
===============================
1. Check current datafile size.

SELECT file_name, tablespace_name, 
       ROUND(bytes/1024/1024) AS bytes_mb, 
       ROUND(maxbytes/1024/1024) AS maxbytes_mb
FROM DBA_DATA_FILES;

2. Resize datafile.
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' RESIZE 2000M;

===============================
Create Tablespace
===============================

CREATE TABLESPACE MY_TABLESPACE 

DATAFILE '/oracle_db/db1/db_sid/my_index_01.dbf' 
SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED;
[EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO]

===============================
Add new Datafile to existing Tablespace
===============================
ALTER TABLESPACE MY_TABLESPACE 
ADD DATAFILE '/oracle_db/db1/db_sid/my_index_02.dbf' 
SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED;
[EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO]

===============================
Handle ORA-30036:unable to extend segment
===============================

ORA-30036: unable to extend segment is thrown when when Tablespace is running out of space:

Steps to resolve this error:
1. Check host space availability.

On Linix:
oracle@host:/software/oracle/admin/sid>df . -h
Filesystem  Size  Used  Avail Use% Mounted on
/dev/mapper/Volume00-LogVol14
            7.9G  4.5G  3.4G  58%  /software/oracle

2. Add space to Tablespace

Option A.  Add Datafile to Tablespace

When adding new datafile, existing storage definitions of existing Tablespace cannot be changed.

Example I. Datafile with pre-defined and unlimited size:

ALTER TABLESPACE MY_TBS 
ADD DATAFILE '/path/to/data/file/name/ora_tbs_02.dbf' 
SIZE 100M AUTOEXTEND ON maxsize 500M;

Example II. Datafile with unlimited size: (OS limit, 32 Gb)

ALTER TABLESPACE MY_TBS 
ADD DATAFILE '/path/to/data/file/name/ora_tbs_02.dbf'
SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED;

Option B. Resize Existing Datafile:
Current situation:
SELECT tablespace_name, 
       file_name, 
       max_bytes/1024/1024 AS max_bytes_MB
FROM DBA_DATA_FILES;

To add more space:
Resize the datafile:
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' RESIZE 2000M;

or increase maxsize:
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' AUTOEXTEND ON MAXSIZE 2000M;

or, set to unlimited:
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' 
      AUTOEXTEND ON MAXSIZE UNLIMITED;

===============================
Free up space to Linux
===============================

DROP TABLESPACE my_tbs INCLUDING CONTENTS AND DATAFILES;

To free up space taken up a datafile.
ALTER DATABASE DATAFILE '/oracle_db/db1/db_orainst/orainst_01.dbf' RESIZE 1M;
ORA-03214: File Size specified is smaller than minimum required

SELECT TABLESPACE_NAME, BLOCK_SIZE, INITIAL_EXTENT 
FROM DBA_TABLESPACES 
WHERE TABLESPACE_NAME LIKE '%SOME_TBS%'
  
TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT
------------------------------ ---------- --------------
SOME_TBS_TABLE                       8192          65536
SOME_TBS_INDEX                       8192          65536

The size of datafile cannot be smaller than the size of initial extent.

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_gin_01.dbf' RESIZE 70M;
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_ginindex_01.dbf' RESIZE 70M;
Database altered.

===============================
RESIZE vs AUTOEXTEND ON MAXSIZE
===============================
To add more space is is possible to use either:
Resize the datafile:
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' RESIZE 2000M;

or increase Maxsize:
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' AUTOEXTEND ON MAXSIZE 2000M;

When using the RESIZE Syntax, Oracle does not update maxbytes value in DBA_DATA_FILES.
This can lead to weird findings. 
To sync maxbytes to the actual size, use 'AUTOEXENT ON MAXSIZE' command

For example:
SET LINESIZE 140
SET PAGESIZE 200
COL tablespace_name FOR A30
COL file_name FOR A60
COL Mb FOR 9999999999999
COL MAX_MB FOR 9999999999999

SELECT tablespace_name, 
       file_name, 
       ROUND(bytes/1024/1024) AS Mb,   
       ROUND(maxbytes/1024/1024) AS MAX_MB
 FROM DBA_DATA_FILES
ORDER BY tablespace_name, file_name;  

Initial Situation. IGT_TABLE tablespace is 100% full

TABLESPACE_NAME FILE_NAME                                        MB      MAX_MB
--------------- ------------------------------------------- ------- -----------
IGT_INDEX       /oracle_db/db1/db_igt/ora_igt_index_01.dbf     8000       12000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_01.dbf    14000       14000
SYSAUX          /oracle_db/db1/db_igt/ora_sysaux_01.dbf         500        6000
SYSTEM          /oracle_db/db1/db_igt/ora_system_01.dbf         700        6000
UNDOTBS         /oracle_db/db1/db_igt/ora_undotbs_01.dbf        900        2000
WORKAREA        /oracle_db/db1/db_igt/ora_workarea_01.dbf       600        6000

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' RESIZE 24000M;

TABLESPACE_NAME FILE_NAME                                        MB      MAX_MB
--------------- ------------------------------------------- ------- -----------
IGT_INDEX       /oracle_db/db1/db_igt/ora_igt_index_01.dbf     8000       12000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_01.dbf    24000       14000
SYSAUX          /oracle_db/db1/db_igt/ora_sysaux_01.dbf         500        6000
SYSTEM          /oracle_db/db1/db_igt/ora_system_01.dbf         700        6000
UNDOTBS         /oracle_db/db1/db_igt/ora_undotbs_01.dbf        900        2000
WORKAREA        /oracle_db/db1/db_igt/ora_workarea_01.dbf       600        6000

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' AUTOEXTEND ON MAXSIZE 24000M;

TABLESPACE_NAME FILE_NAME                                        MB      MAX_MB
--------------- ------------------------------------------- ------- -----------
IGT_INDEX       /oracle_db/db1/db_igt/ora_igt_index_01.dbf     8000       12000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_01.dbf    24000       24000
SYSAUX          /oracle_db/db1/db_igt/ora_sysaux_01.dbf         500        6000
SYSTEM          /oracle_db/db1/db_igt/ora_system_01.dbf         700        6000
UNDOTBS         /oracle_db/db1/db_igt/ora_undotbs_01.dbf        900        2000
WORKAREA        /oracle_db/db1/db_igt/ora_workarea_01.dbf       600        6000

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' RESIZE 18000M;

TABLESPACE_NAME FILE_NAME                                        MB      MAX_MB
--------------- ------------------------------------------- ------- -----------
IGT_INDEX       /oracle_db/db1/db_igt/ora_igt_index_01.dbf     8000       12000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_01.dbf    18000       24000
SYSAUX          /oracle_db/db1/db_igt/ora_sysaux_01.dbf         500        6000
SYSTEM          /oracle_db/db1/db_igt/ora_system_01.dbf         700        6000
UNDOTBS         /oracle_db/db1/db_igt/ora_undotbs_01.dbf        900        2000
WORKAREA        /oracle_db/db1/db_igt/ora_workarea_01.dbf       600        6000

And this is the actual size of the file, on Linux:
-rw-r----- 1 oracle dba 18874376192 Mar 15 11:45 ora_igt_table_01.dbf

18874376192/1024/1024=18000


===============================
Switch UNDO Datafile
===============================

Create new tablespace UNDO_02

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/YOUR/NEW/LOCATION/UNDOTBS02.DBF' SIZE 1000M AUTOEXTEND ON MAXSIZE 6000M;;

Switch to new UNDO_02 Tablespace.

ALTER SYSTEM SET UNDO_TABLESPACE= UNDOTBS2;

ALTER TABLESPACE UNDOTBS OFFLINE;

Drop the old UNDO Tablespace.

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS;

ORA-30042: Cannot offline the undo tablespace

In case of an error ORA-30042: Cannot offline the undo tablespace

SQL>  ALTER TABLESPACE UNDOTBS OFFLINE;

 ALTER TABLESPACE UNDOTBS OFFLINE

*

ERROR at line 1:

ORA-30042: Cannot offline the undo tablespace

1. Check that no rollback segment is in 'NEEDS RECOVERY' status:

SQL> SELECT DISTINCT status from DBA_ROLLBACK_SEGS;

STATUS

-----------------------

ONLINE

OFFLINE

2. After couple of minutes issue same command again.

SQL>  ALTER TABLESPACE UNDOTBS OFFLINE;

Tablespace altered.

===============================

Switch Temporary Datafile
===============================

Create new tablespace TEMP_02

CREATE TEMPORARY TABLESPACE TEMP_02

TEMPFILE ‘/u01/oradata/TESTDB/temp2_01.dbf’ SIZE 1000M AUTOEXTEND ON MAXSIZE 6000M;

Switch to new TEMP_02 Tablespace.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_02;

Drop the old TEMP Tablespace.

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Appendix

Altering Indexes
Costs and Benefits of Coalescing or Rebuilding Indexes

bat, perl, sqlplus by example I

================================
General
================================
Example of a process, that is reading from ini file several DB connection strings, loops on these connection strings, and generated a report from data fetched from these DB connections.

================================
The flow:
================================
Prepare db_list.ini file, to define the databases connection string.
For each entry in db_list.ini file, connect to the listed DB and execute a SQL file.
Append output from each DB connection, to one file.
Read the generated file, and build a new formatted file.
Save the name of the generated file, in another file, so the generated file can be accessed by a mailing program.


DB related code
Configuration File:
db_list.ini

Code:
main_from_perl.bat
run_sql.bat
refresh_log_sql.sql

Report building code

Configuration File:
refresh_report.ini

Code:
refresh_report_main.bat
refresh_report_main.pl
format_db_report.pl

Generated files:
gates_refresh.log
last_db_report.txt
refresh_log_report_20140423_0400000.rep

db_list.ini
userA/paswdA@connectionStringA
userB/paswdB@connectionStringB
userC/paswdC@connectionStringC
userD/paswdD@connectionStringD
userE/paswdE@connectionStringE
userF/paswdF@connectionStringF
userG/paswdG@connectionStringG
userH/paswdH@connectionStringH

userJ/paswdJ@connectionStringJ
userK/paswdK@connectionStringK

main_from_perl.bat
ECHO OFF

SET inifile=%1%
SET REFRESH_LOG=%2%
SET TEMP_REFRESH_LOG=temp_gates_refresh_log_stats.txt

ECHO.
ECHO =============================================
ECHO main is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%REFRESH_LOG%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO handling %%i
    ECHO. 2>%TEMP_REFRESH_LOG%
    call run_sql.bat %TEMP_REFRESH_LOG% %%i
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
    ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================



run_sql.bat

SET spool_to_file=%1
SET db_name=%2
REM ECHO got DB name: %db_name%
REM ECHO refresh_log_sql %db_name%

sqlplus -l -s %db_name% @refresh_log_sql %spool_to_file% %db_name%

refresh_log_sql.sql
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL &&1
SELECT '&&2' AS SITE_NAME, COUNT(*) AS REFRESH_LOG_ENTRIES 
  FROM REFRESH_LOG;
SPOOL OFF
EXIT;

/

refresh_report.ini
MAIN_HOME_WIN=L:\refresh_report
INI_FILE_FOR_DB=db_list.ini
BAT_CMD=main_from_perl.bat
DB_LOG=gates_refresh.log
REPORT_FILE=refresh_log_report
MAIL_CMD=sendMail.bat
MAIL_HEADER=mail_header.txt
MAIL_FOOTER=mail_footer.txt

LAST_DB_REPORT_FILE=last_db_report.txt

refresh_report_main.bat
perl refresh_report_main.pl

refresh_report_main.pl
#! /usr/bin/perl
#Daily process for reading refresh_log table on remote sites.

use DateTime;

use strict;
use warnings; 

#===================
#Global Parameters
#===================
my $INI_FILE='refresh_report.ini'; 
my $LOG_FILE_BASE_NAME='Gates_Refresh_Log';
my $display_delimiter="======================================\n";
my %hashTable;

my $MAIN_HOME_WIN='';
my $BAT_CMD = '';
my $MAIL_CMD= '';
my $MAIL_HEADER='';
my $MAIL_FOOTER='';
my $MAIL_PATH='';

my $INI_FILE_FOR_DB;
my $DB_LOG='';
my $LOG_FILE_PATH='';
my $LOG_FILE_NAME='';
my $REPORT_FILE_PATH='';
my $REPORT_FILE='';
my $LAST_DB_REPORT_FILE='';

my $timestamp='';
my $logFile='';
my $dbLogFile='';
my $reportFile='';
my $mailCmd='';
my $mailHeaderFile='';
my $mailFooterFile='';
my $lastReportFile;
my $run_date='';
my $result='';

#==================================
# subs
#==================================

sub setRundate{
  my $dateTime=DateTime->now;
  my $month=$dateTime->month;
  my $year=$dateTime->year;
  
  if ($month < 10){ $month="0".$month ; }
  my $day=$dateTime->day;
  if ($day < 10){ $day="0".$day ; }
  my $hh=$dateTime->hour;
  if ($hh < 10){ $hh="0".$hh ; }
  my $mm=$dateTime->minute;
  if ($mm < 10){ $mm="0".$mm ; }
  my $ss=$dateTime->second;
  if ($ss == 60){ $ss=0; };
  if ($ss == 61){ $ss=1; };
  if ($ss < 10){ $ss="0".$mm ; }

  $run_date=$dateTime->year.$month.$day;
  
  $timestamp=$run_date."_".$hh.$mm.$ss;
  print "Running time: ".$timestamp."\n";
}

sub fileFullPathNames{
$reportFile=$REPORT_FILE_PATH."\\".$REPORT_FILE."_".$timestamp.".rep";
$dbLogFile=$LOG_FILE_PATH."\\".$DB_LOG;
$mailCmd=$MAIL_PATH."\\".$MAIL_CMD;
$mailHeaderFile=$MAIL_PATH."\\".$MAIL_HEADER;
$mailFooterFile=$MAIL_PATH."\\".$MAIL_FOOTER;
}


sub setLogFileName{
 $logFile=$LOG_FILE_PATH."\\".$LOG_FILE_NAME."_".$timestamp.".log";
  my $status=0;
  #print "Will try to create now new folder: ".$LOG_FILE_PATH;
  if (!-d "$LOG_FILE_PATH" ){
my $cmd = "mkdir "."\"".$LOG_FILE_PATH."\"";
$status=system($cmd);
  }
  if ($status == 0 ){  
    print "\n";
    print $display_delimiter;
    print "\n";
    print "Log File: ".$logFile."\n";
    print "\n";
    print $display_delimiter;
    print "\n";
  }
  $status;
}

sub readIniFile{
  my $i_ini_file=shift;
  
  my $line='';
  my $key='';
  my $value='';    
  my $match_str = "CUSTOMER_[0-9][0-9]"; 
  
  #read from ini file
  open(FILE_HANDLER, "$i_ini_file") or die ("Could not open ini file: "."$i_ini_file"."\n");
  my $count=0;
  foreach $line (<FILE_HANDLER>){
    chomp($line);
    if ($line =~ /^$/) { 
#nothing. Ignore empty lines
    }elsif($line =~ /^#/) { 
#nothing. Ignore # comment lines lines
    }else{
       ($key, $value)=split(/=/,$line);
       $hashTable{$key} = $value;   
   }
 }
 close (FILE_HANDLER);
}

sub sendMail{
  my @mailArg = "";
  my $sendMailInd=0;
  print "Running command: ".$mailCmd." to send contents of file: ".$reportFile;
  @mailArg=($mailCmd,$reportFile);
  system(@mailArg);
}

sub printMultipath{
  my $str = shift;
  my $log_handler = shift;
    print $log_handler ($str);
print ($str);  
}

sub setGeneralParams{
   #from ini file
   $MAIN_HOME_WIN = $hashTable{"MAIN_HOME_WIN"}; 
   $INI_FILE_FOR_DB=$hashTable{"INI_FILE_FOR_DB"};
   $BAT_CMD = $hashTable{"BAT_CMD"}; 
   $DB_LOG=$hashTable{"DB_LOG"};
   $REPORT_FILE=$hashTable{"REPORT_FILE"};
   $MAIL_CMD = $hashTable{"MAIL_CMD"};
   $MAIL_HEADER = $hashTable{"MAIL_HEADER"};
   $MAIL_FOOTER = $hashTable{"MAIL_FOOTER"};
   $LAST_DB_REPORT_FILE= $hashTable{"LAST_DB_REPORT_FILE"};
   
   #concatinations
   $LOG_FILE_PATH=$MAIN_HOME_WIN."\\"."Logs";
   $REPORT_FILE_PATH=$MAIN_HOME_WIN."\\"."Reports";
   $LOG_FILE_NAME=$LOG_FILE_BASE_NAME."_".$timestamp.".log";
   $MAIL_PATH=$MAIN_HOME_WIN."\\"."Mail";
   $lastReportFile=$MAIN_HOME_WIN."\\".$LAST_DB_REPORT_FILE;

   #report
   print $display_delimiter;
   print "Running With General Parameters:"."\n";
   print "MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n";
   print "BAT_CMD: ".$BAT_CMD."\n";
   print "INI_FILE_FOR_DB: ".$INI_FILE_FOR_DB."\n";
   print "DB_LOG: ".$DB_LOG."\n";
   print "Report: ".$REPORT_FILE."\n";
   print $display_delimiter;
}


sub runOsCommand{
  my $log_handler = shift;
  my $cmd = shift;
  my $ini_file = shift;
  print "Running Command: ".$cmd."\n";
  #$result=system($^X,$cmd);
  my @SendArg=($cmd,$ini_file, $dbLogFile);
    $result=system(@SendArg);
    #print "Exit point after call to ".$cmd." Exist status is: ".$result."\n";
  $result;
}

sub updateLastDbReport{
  my $lastReportFile=shift;
  my $reportFile=shift;  
  
  open OUT_FILE,">",$lastReportFile or die $!;
  print OUT_FILE $reportFile."\n";
  close OUT_FILE or die $!;
}

sub formatReportFile{
  my $log_handler = shift;
  my $sourceFile = shift;
  my $targetFile = shift;
  my $header=shift;
  my $footer=shift;
    $result=system($^X,"format_db_report.pl",$sourceFile,$targetFile, $header,$footer);
$result;
}

sub reportGeneralParameters{
  my $log_handler = shift;
  
  printMultipath ("\n",$log_handler);
  printMultipath ($display_delimiter,$log_handler);
  printMultipath ("Input Parameters: \n",$log_handler);
  printMultipath ("Run Date:".$run_date."\n",$log_handler);
  printMultipath ("Log File: ".$logFile."\n",$log_handler);
  printMultipath ("MAIN_HOME_WIN: ".$MAIN_HOME_WIN."\n",$log_handler);    
  printMultipath ("BAT_CMD: ".$BAT_CMD."\n",$log_handler);    
  printMultipath ("MAIL_CMD: ".$MAIL_CMD."\n",$log_handler);    
  printMultipath ($display_delimiter,$log_handler);
  printMultipath ("\n",$log_handler);
}

sub reportStartOfProcess{
  my $log_handler = shift;

  printMultipath ("\n",$log_handler);
  printMultipath ($display_delimiter,$log_handler);
  printMultipath ("Refresh Log On Gates Checkup Process Started at: ".$timestamp."\n",$log_handler);
  printMultipath ($display_delimiter,$log_handler);
  printMultipath ("\n",$log_handler);
}

sub reportEndOfProcess{
  my $log_handler = shift;
  my $status = shift;

  printMultipath ("\n",$log_handler);
  printMultipath ($display_delimiter,$log_handler);
  if ($status == 0){
    printMultipath ("Process has Finished Successfully"."\n" ,$log_handler);
  }else{
    printMultipath ("Error!!!! Process has Terminated with Error."."\n",$log_handler);
  }
  printMultipath ($display_delimiter,$log_handler);
  printMultipath ("\n",$log_handler);
    $status;
}

sub handleStatus{
  my $status = shift;
  my $method = shift;
  my $log_handler = shift;
  
  if ($status != 0){
    if (defined $log_handler){
      printMultipath ("Error in Routine: ".$method." Status: ".$status."\n",$log_handler);
    }else{
      print ("Error in Routine: ".$method." Status: ".$status."\n");
    }
    return $status;
  }
  $status;
}

#==================================
# The code starts here
#==================================

   $result=main();
   exit $result; 
   
#==================================
# sub main
#==================================
sub main{   
   no warnings 'uninitialized'; 

   setRundate(); 
   readIniFile("$INI_FILE"); 
   setGeneralParams();
   $result=setLogFileName();   
   handleStatus($result, "setLogFileName",undef); 
   open my $LOG_HANDLER, '>', $logFile or die ("Could not open log file: ".$logFile); 
   fileFullPathNames();
   reportStartOfProcess($LOG_HANDLER);  
   reportGeneralParameters($LOG_HANDLER);  
   my $cmd='"'.$MAIN_HOME_WIN."\\".$BAT_CMD.'"';
   $result=runOsCommand($LOG_HANDLER,$cmd,$INI_FILE_FOR_DB);
   handleStatus($result, $cmd,undef); 
   #$result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,$mailHeaderFile,$mailFooterFile);
   $result=formatReportFile($LOG_HANDLER,$dbLogFile,$reportFile,"NULL","NULL");
   handleStatus($result, "formatReportFile",undef); 
   updateLastDbReport($lastReportFile,$reportFile);
   reportEndOfProcess($LOG_HANDLER, $result); 
   close $LOG_HANDLER; 
   #sendMail(); - mail is send from a server with access to mail server

}


format_db_report.pl
#====================================================
# Declaration
#====================================================
my $pl_script_name='';
my $sourceFile='';
my $targetFile='';
my $headerFile = '';
my $footerFile = '';
  
my $status='0';

my $header="GATE_NAME                     REFRESH_LOG_ENTRIES";
my $hliner="----------------------------- -------------------";

#====================================================
# Sub routines Start Here
#====================================================

sub reportInputParams{
  print "Input Parameters:"."\n";
  print "sourceFile = ".$sourceFile."\n";
  print "targetFile = ".$targetFile."\n";
}

sub readAndWrite{
  $status='0';
  my $sourceFile = shift;
  my $targetFile = shift;
  my $headerFile = shift;
  my $footerFile = shift;
  
  my $i_line='';
  my $o_line='';
  
  my $line_counter=0;
  my $header_ind='Y';
  my @words;
  
  my $db_name='';
  my $enrties='';
  
  #=================================
  #Add Header
  #=================================
  open OUT_FILE,">",$targetFile or die $!;
  close OUT_FILE or die $!;
  
  if ($headerFile ne "NULL"){
    open IN_FILE,$headerFile or die $!;
    open OUT_FILE,">>",$targetFile or die $!;
  
      print OUT_FILE "\n";
    while ($i_line = <IN_FILE>){
      print OUT_FILE $i_line;
    }
    print OUT_FILE "\n";
    print OUT_FILE "\n";
    print OUT_FILE "\n";

    close IN_FILE or die $!;
    close OUT_FILE or die $!;
 }  
  #=================================
  #Add Contents
  #=================================
  
  open IN_FILE,$sourceFile or die $!;
  open OUT_FILE,">>",$targetFile or die $!;
  
  print OUT_FILE $header."\n";
  print OUT_FILE $hliner."\n";
  
  while ($i_line = <IN_FILE>){
    $line_counter+=1;
    if ($line_counter == 1){
      #do nothing - this is an empty line
    }elsif($line_counter == 2){
      #do nothing - this is header
    }elsif($line_counter == 3){
      #do nothing - this is underline
    }elsif($line_counter == 4){
      @words=split(" ",$i_line);
      $db_name=@words[0];
      $enrties=@words[1];
      if ($enrties > 0){
        print OUT_FILE sprintf("%-50s %-10s\n",$db_name,$enrties);
      }
      $line_counter=0;
    }
  }
  close IN_FILE or die $!;
  close OUT_FILE or die $!;
  
  #=================================
  #Add Footer
  #=================================
  if ($footerFile ne "NULL"){
    open IN_FILE,$footerFile or die $!;
    open OUT_FILE,">>",$targetFile or die $!;
    print OUT_FILE "\n";
    print OUT_FILE "\n";
    print OUT_FILE "\n";
    while ($i_line = <IN_FILE>){
      print OUT_FILE $i_line;
    }
    print OUT_FILE "\n";
 
    close IN_FILE or die $!;
    close OUT_FILE or die $!;
  }
  $status;
}

#====================================================
# Code Start Here
#====================================================

  $pl_script_name="format_db_report.pl";
  $sourceFile = shift;
  $targetFile = shift;
  $headerFile = shift;
  $footerFile = shift;
  
  reportInputParams();
  $status=readAndWrite($sourceFile, $targetFile, $headerFile, $footerFile);
  exit $status;

#====================================================
# Code End Here
#====================================================

gates_refresh.log

GATE_NAME                        REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERA/USERA@CONNECTION_STRINGA                     0

GATE_NAME                        REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERB/USERB@CONNECTION_STRINGB                   0

GATE_NAME                        REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERC/USERC@CONNECTION_STRINGC                   0

GATE_NAME                        REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERD/USERE@CONNECTION_STRINGE                   0

GATE_NAME                        REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERF/USERF@CONNECTION_STRINGF                   0

GATE_NAME                        REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERG/USERG@CONNECTION_STRINGG               90009

GATE_NAME                        REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERH/USERH@CONNECTION_STRINGH                   1

GATE_NAME                        REFRESH_LOG_ENTRIES
-------------------------------- -------------------
USERJ/USERJ@CONNECTION_STRINGJ                   0

GATE_NAME                        REFRESH_LOG_ENTRIES
-------------------------------- -------------------

USERK/USERK@CONNECTION_STRINGK               89999

last_db_report.txt

L:\refresh_report\Reports\refresh_log_report_20140423_0400000.rep

refresh_log_report_20140423_0400000.rep
GATE_NAME                         REFRESH_LOG_ENTRIES
--------------------------------- -------------------
userA/paswdA@connectionStringA                  90009     
userB/paswdB@connectionStringB                      1         
userC/paswdC@connectionStringC                  89999     
userD/paswdD@connectionStringD                  90036     
userE/paswdE@connectionStringE                      1         
userF/paswdF@connectionStringF                      3         
userG/paswdG@connectionStringG                      3         
userH/paswdH@connectionStringH                 128509    
userJ/paswdJ@connectionStringJ                 132225    
userK/paswdK@connectionStringK                  90442