SQL Scripts to manage Oracle Tablespaces

Oracle Company has developed a lot of Applications (EBS, Fusion, PeopleSoft, etc.) to help companies to do business. And typically those applications are running upon Oracle Database.

It is very common that during daily development people have to add tablespaces to their applications and this work is a bit prone to error. For iinstance, in EBS 11i, it has dozen of tablesapces and it is a time consuming task that increase tablespaces for them one by one.

Below is some SQL scripts that could help in this task and I will also provide a sample that how we could automated to increase tablepsaces by using perl script.

How to query tablespaces in Oracle Database:

select a.tablespace_name "Tablespace Name",
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id))/1024/1024/1024 "Total_GB",
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) - sum(a.bytes)/count( distinct b.file_id ))/1024/1024/1024 "Used_GB",
(sum(a.bytes)/count( distinct b.file_id ))/1024/1024/1024 "Free_GB",
100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) - (sum(a.bytes)/count( distinct b.file_id ) )) / (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id)) PCT_USED
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;

How to query where is your database files locate?

(for example for tablespace SYSTEM):

SELECT FILE_NAME FROM sys.dba_data_files 
where tablespace_name='SYSTEM';

How to add a datafile for SYSTEM:

(For example, in previous query it returns /u01/oracle/data/system011.dbf as the last row, and now you want to add 1GB with autoextend datafile to TABLESPACE SYSTEM):

ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/oracle/data/system012.dbf' 
SIZE 1024M AUTOEXTEND ON;

Below is the logic that how you can write a script to automate this,

#########################################################################################################
## Below script could be run from Windows and Unix based platform
## Pls. note, you have to prepare a method ConvertOSPath() to help convert the path to your ## nativeOS
## Basically your ConvertOSPath() should take care of the '/' and '\' for your OS.
##
## 1. Get the datafile directory
## 2. Get the Stats. of TABLESPACE_NAME, TOTAL_GB, USED_GB, FREE_GB, PCT_USED. - Pre
## 3. Get the datafile details (for example for tb SYSTEM, if the last one is system01.dbf, then it should add from system02.dbf.)
## 4. Add free spaces to all of the tablespaces and ensure that the freespace is 30% for every tablespace.
########################################################################################################

############################################################
## Get the datafile directory
############################################################
my $DATAFILE_DIR = "";
my $DATAFILE_FILE = "";
my $SQL = ConvertOSPath("datafile_dir.sql");

open (SQLS, ">$SQL");
print SQLS "set heading off\n";
print SQLS "set feedback off\n";
print SQLS "spool datafile_dir.log\n";
if ($Config{osname} eq "MSWin32") {
print SQLS "select distinct substr(file_name, 1, instr(file_name, '\\',-1)) from dba_data_files where rownum=1;\n";
} else {
print SQLS "select distinct substr(file_name, 1, instr(file_name, '/',-1)) from dba_data_files where rownum=1;\n";
}
print SQLS "spool off\n";
print SQLS "/\n";
print SQLS "exit;\n";
close SQLS ;
runsqlplus("$SQL","system/$ENV{dbpwd}","");

my $SQL = ConvertOSPath("datafile_dir.log");
open (SQLS, "$SQL");
my @lines = <SQLS>;
my $find = 0;
foreach $line (@lines) {
$line =~ s/^\s+|\s+$//g;
if (length($line) > 1) {
$DATAFILE_DIR = ConvertOSPath("$line");
}
}
printsl("The datafile directory is $DATAFILE_DIR ........");
close SQLS ;
################################################################################
## TABLESPACE_NAME, TOTAL_GB, USED_GB, FREE_GB, PCT_USED. - Pre
################################################################################
my $SQL = ConvertOSPath("init_tb_pct.sql");

open (SQLS, ">$SQL");
print SQLS "set heading off\n";
print SQLS "set feedback off\n";
print SQLS "set pages 1000\n";
print SQLS "spool init_tb_pct.log\n";
print SQLS "select a.tablespace_name Tablespace_Name,(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id))/1024/1024/1024 Total_GB,(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id) - sum(a.bytes)/count( distinct b.file_id ))/1024/1024/1024 Used_GB,(sum(a.bytes)/count( distinct b.file_id ))/1024/1024/1024 Free_GB,100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) - (sum(a.bytes)/count( distinct b.file_id ) )) / (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id)) PCT_USED from sys.dba_free_space a, sys.dba_data_files b where a.tablespace_name = b.tablespace_name group by a.tablespace_name, b.tablespace_name;\n";
print SQLS "spool off\n";
print SQLS "/\n";
print SQLS "exit;\n";
close SQLS ;
runsqlplus("$SQL");

my $SQL = ConvertOSPath("init_tb_pct.log");
open (SQLS, "$SQL");
my @lines = <SQLS>;

foreach $line (@lines) {
$line =~ s/^\s+|\s+$//g;
if (length($line) > 1) {
my ($TABLESPACE_NAME, $TOTAL_GB, $USED_GB, $FREE_GB, $PCT_USED) = split(/\s+/, $line);
if ($PCT_USED > 70) {
print("$TABLESPACE_NAME ........");
print("$TOTAL_GB ........");
print("$USED_GB ........");
print("$FREE_GB ........");
printsl("$PCT_USED ........");

# Get the biggest number of datafile
printsl("Get the biggest datafile number for tablespace $TABLESPACE_NAME running ........");
my $BIGGEST = "";
my $file_temp= "";
my $SQL = ConvertOSPath("biggest.sql");

open (SQLS, ">$SQL");
print SQLS "set heading off\n";
print SQLS "set feedback off\n";
print SQLS "spool biggest.log\n";
print SQLS "set lines 1000\n";
if ($Config{osname} eq "MSWin32") {
print SQLS "select biggest, file_name from (select to_number(REGEXP_SUBSTR((substr(file_name, instr(file_name, '\\',-1)+1,50)), '\\d+')) biggest, (REGEXP_SUBSTR((substr(file_name, instr(file_name, '\\',-1)+1,50)), '^[^0-9\\.]+')) file_name from dba_data_files where tablespace_name='$TABLESPACE_NAME' order by 1 desc) where rownum=1;\n";
} else {
print SQLS "select biggest,file_name from (select to_number(REGEXP_SUBSTR((substr(file_name, instr(file_name, '/',-1)+1,50)), '\\d+')) biggest, (REGEXP_SUBSTR((substr(file_name, instr(file_name, '/',-1)+1,50)), '^[^0-9\\.]+')) file_name from dba_data_files where tablespace_name='$TABLESPACE_NAME' order by 1 desc) where rownum=1;\n";
}
print SQLS "spool off\n";
print SQLS "/\n";
print SQLS "exit;\n";
close SQLS ;
runsqlplus("$SQL");

my $SQL = ConvertOSPath("biggest.log");
open (SQLS, "$SQL");
my @lines = <SQLS>;
foreach $line (@lines) {
$line =~ s/^\s+|\s+$//g;
if (length($line) > 0) {
($BIGGEST, $file_temp) = split(/\s+/, $line);
$file_temp =~s/\d+//g ;
if ($file_temp eq '') {
$file_temp = $BIGGEST;
$BIGGEST = 0;
}
}
}

if ( $BIGGEST eq '') {
$BIGGEST = 0;
}
printsl("The biggest datafile number for tablespace $TABLESPACE_NAME is $BIGGEST ........");
printsl("The file_temp for tablespace $TABLESPACE_NAME is $file_temp ........");
close SQLS ;
#############

printsl("Increase tablespace for $TABLESPACE_NAME running ........");
for ($number=0; $number< (((0.3 * $TOTAL_GB) - $FREE_GB)/0.7); $number=$number+2) {
my $SQL_R = ConvertOSPath("$logdir/increase_tablespace.sql");
$BIGGEST = $BIGGEST + 1;
if ($BIGGEST < 10) {
$token = "0";
} else {
$token = "";
}
open (SQLS_R, ">$SQL_R");
print SQLS_R "set lines 1000\n";
print SQLS_R "set pages 1000\n";
print SQLS_R "set termout off\n";
print SQLS_R "set feedback off\n";
print SQLS_R "set heading off\n";
print SQLS_R "spool increase_system_tablespace.log\n";
$DATAFILE_FILE=ConvertOSPath("$DATAFILE_DIR" . "$file_temp" . "$token" . "$BIGGEST.dbf");
print SQLS_R "alter tablespace $TABLESPACE_NAME add datafile '$DATAFILE_FILE' size 2000M AUTOEXTEND ON;\n";
print SQLS_R "spool off\n";
print SQLS_R "/\n";
print SQLS_R "exit;\n";
close SQLS_R ;
my $rt = runsqlplus("$SQL_R");
printsl("Command returned $rt\n");
if ($rt != 0) {
printsl("Adding datafile for $TABLESPACE_NAME $DATAFILE_FILE $SQL_R: Error running statement.") ;
## print error here
}
else {
printsl("Adding datafile for $TABLESPACE_NAME $DATAFILE_FILE $SQL_R: Successfully completed.........");
}
}

}
}
}

close SQLS ;

#Enjoy!