11g range partition addition script in perl

Recently I wrote a perl script that adds partitions to an existing table
The scripts makes a few assumptions
– The table exists and is range partitioned on date
– There is one partition per day
– There is a local (pre-fixed or non pre-fixed) index on the table
– There is a partition (named tableshortname_default) that is the upperbound of the date range (This can be split into the partitions that we need).
– The partition names are of the format tableshortname_mmddyyyy

The script takes the following arguments
– The table full name
– The table short name (Which becomes the prefix for the partition names)
– The number of partitions to create

The script does the following
– Queries the data dictionary (user_tab_partitions) to find out the highest partition in the table (ie the highest date for which the partition has been created)
– Uses the perl function Add_Delta_Days (Which is the part of the perl library Date::Calc) to generate the future dates for which partitions need to be created.
– Generates partition creation scripts and executes them.
– When you create a table partition, oracle automatically creates the new local index partition too.

The script has been tested against an oracle 11g database.

#There are two arguments to this script
#The first argument is the tablename
#The second argument is the tableshortname used for prefixing the partition names
#The third argument is the number of days for which you want to create new partitions

#This program
#   Assumes that the table is partitioned on date with one partition per day
#   Assumes that the highest partition is the one defined with maxvalues
#   Assumes that the maxvalue partition is called tableshortname_default
#   Takes the tablename
#   Queries the data dictionary to find the partition that has the highest date
#   Creates alter table split partition statements to create the additional partitions (determined by the 3rd argument)
#When the table is split the local index automatically gets split
#But you will have to rename the index partition(So that it gets a good prefix) (The code for that has to be added to this script)

use DBI;
use Date::Calc qw(Add_Delta_Days);

if ( @ARGV < 3 )
print “Syntax : crpartitions.pl tablename tableshortname numberofpartitions \n”;

# Connect to the database
# LongReadLen and LongTruncOk are required to specify the buffer size
# For the high_value column from user_tab_partitions which is a long datatype

my $db = DBI->connect( “dbi:Oracle:OracleSid”, “username”, “password” )
|| die( $DBI::errstr . “\n” );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;
$db->{LongReadLen}   = 5242880;
$db->{LongTruncOk}   = 0;

#Setup tablenames for target table
#Setup a tablename and tableshortname in upper case, to be used in queries

my $l_tablename=shift;
my $l_tableshortname=shift;
my $l_numpartitions=shift;

my $l_tablename_uc=uc($l_tablename);
my $l_tableshortname_uc=uc($l_tableshortname);

# Query user_tab_partitions to determine the highest partition
# And the date that sets the limit for rows that are in that partition

$l_highvalue = $db->selectrow_array(”
select high_value from user_tab_partitions
where table_name = ‘$l_tablename_uc’ and
partition_position = (select max(partition_position)-1 from user_tab_partitions
where table_name = ‘$l_tablename_uc’)

# The output of the query is a string
# eg: TO_DATE(‘ 2008-11-16 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
# Parse the string to get the full date, year, month and day

$l_highdate = substr $l_highvalue,10,19;
$l_year     = substr $l_highdate,0,4;
$l_month    = substr $l_highdate,5,2;
$l_day      = substr $l_highdate,8,2;

# Loop through each new partition you need to create
# Use the Add_Delta_Days function from the Date::Calc perl library
# To add days to the start date to get the upper limit of dates that go into each partition
# Prepare the alter table split partition statement and then invoke the function createPartition
# to execute the statement

for ($i=1 ; $i <= $l_numpartitions; $i++) {

$l_alterstmt=”alter table “.$l_tablename_uc.” split partition “.$l_tableshortname_uc.”_default at (TO_DATE(‘”.$n_day.”-“.$n_month.”-“.$n_year.”‘, ‘DD-MM-YYYY’)) into
(PARTITION “.$l_tableshortname_uc.”_”.$n_month.$n_day.$n_year.”,partition “.$l_tableshortname_uc.”_default)”;



# Takes the statement passed in as argument and executes it
sub createPartition {

$ldb = shift;
# execute alter table statement
my $sthd = $ldb->prepare(“$p_alterstmt”)
|| die(“could not prepare alter statement”. $sthd->errstr . “\n” );

|| die(“could not execute drop statement”. $sthd->errstr . “\n” );


Installing Grid control with oracle database for the repository

Below is what i think is the quickest (And possibly the only supported way to do this directly (instead of installing, finishing the grid control install and upgrading to of steps to install grid control, while using oracle database version for the grid control management repository on Linux x86.

Typically you would install grid control, which installs a database as the management repository, then you would upgrade enterprise manager to and then eventually upgrade the database to Apart from the numerous steps in this method you also take more time because the entreprise manager database is fully configured when you install and then it takes even more time updating all this configuration when you install the patch. In the method outlined below all the configuration is done when you run the configuration script in the end, saving you precious time.

  • Install Oracle RDBMS
  • Upgrade Oracle RDBMS to
  • Create a listener
  • Create a Database
    • When you create/configure the database make sure that, you DO NOT choose the option to enable database control for the instance. (If you choose to enable database control, you will have to de-configure it).
  • Configure the Initialization parameters
  • Install Oracle Grid Control, software only.
    • Install Oracle Grid Control, software only, Subsection titled ” Enterprise Manager Grid Control Using an Existing Database“.
    • Here be extremely careful when you are configuring the em_using_existing_db.rsp file. When you are setting the value for the parameter s_reposPort=”1521″, remember to use the double quotes around the 1521 or else you will have issues with the installation.
  • Install Oracle Grid Control, software only.
    • In the section of the documentation you were following above, follow instructions in “Step 7 Apply the patch set to OMS
  • Apply the patch set to the Agent on the management server
    • Follow instructions in “Step 9, “Apply the patch set to Agent
  • Run the configuration scripts to configure grid control to use the already created database.
    • Follow instructions in “Step 10, 11 Configure Enterprise Manager Grid control by running the ConfigureGc.pl….

Instructions on how to upgrade this installation to Enterprise Manager can be found in my followup post , Upgrading Enterprise Manager to

Some Useful Metalink Notes for Grid control Installation and Upgrades

Documentation Reference for Install and Upgrades

Installing Enterprise Manager using an 11g Database for the repository

Steps to upgrade or higher Repository to 11g

Install grid control on enterprise linux 4

Install grid control on enterprise linux 5

You can find samples of the rsp files below



Installing Ruby and Rails 2 on Ubuntu Lucid running Oracle 11g

Updated this post from Ubuntu Jaunty to Lucid.

Unless you have been living on one of the planets orbiting alpha centauri (or if you have nothing to do with computers, in which case you would not be reading this post), you must have heard of ruby on rails.

Below are the steps i went through to get ruby and rails installed and configured on a Ubuntu Jaunty (10.04) system.

Make sure that you have installed Oracle 11g or the Oracle 11g instant client on the Ubuntu server, before you start.

Get the ruby packages for ubuntu

sudo apt-get install ruby  ruby-dev  libopenssl-ruby1.8 irb ri rdoc

sudo apt-get install sqlite3

sudo apt-get install libsqlite3-ruby libsqlite3-dev

Download, Install setup Gems

wget  http://production.cf.rubygems.org/rubygems/rubygems-1.3.6.zip
unzip rubygems-1.3.6.zip
cd rubygems-1.3.6
sudo ruby setup.rb

sudo ln -s /usr/bin/gem1.8 /usr/local/bin/gem

Install Rails 2

sudo gem install rails

Download and install the ruby interface for oracle

Download ruby-oci8 from http://rubyforge.org/frs/?group_id=256


tar -xzvf ruby-oci8-2.0.4.tar.gz

cd ruby-oci8-2.0.4/


sudo make install

Get the Enhanced ActiveRecord adapter for oracle

wget http://rubyforge.org/frs/download.php/64456/activerecord-oracle_enhanced-adapter-1.2.2.gem

sudo gem install activerecord-oracle_enhanced-adapter-1.2.2.gem

You are all set to write and test your first ruby program using the rails framework .

Please see my followup post on writing a simple rails program with 11g