Re: create tablespace script

From: mkb <mkb125_at_yahoo.com>
Date: Tue, 21 Jan 2003 13:10:00 -0800
Message-ID: <F001.00536974.20030121131000@fatcity.com>

Helmut,

I have the following in perl. It handles multiple datafiles for a tablespace. See the in-line comments below. You can modify this logic for PL/SQL. I think I also have a script in PL/SQL (somewhere) that I wrote and converted to perl. Let me know if you'd like to take a look at that.

hth

mohammed

#!/usr/local/bin/perl

###################################################################
#
# Get tablespace DDL from Oracle databases (8x, 8i)
# Create 2001/10/01 - MKB
# Modification History
# 2001/10/11 - MKB - Pass command line opts to program
###################################################################

use strict;
use DBI;

my ($username, $passwd, $connstrg, $filename); my $usage = "usage: username password connect_string filename \n";
$usage = $usage . "Example: scott tiger remt_db tablespace.log \n";

if ($#ARGV != 3) { die($usage) }

else { $username = $ARGV[0];
       $passwd = $ARGV[1];
       $connstrg = $ARGV[2];
       $filename = $ARGV[3];
     };

# Declare variables to hold col names, col typs and
col lengths
my ($ts_name, $prev_val, $fl_name, $size); my ($init_ext, $nxt_ext, $min_ext, $max_ext, $pct_incr, $bytes);

###########################################
# This variable is set to 1 indicating more
# than one datafile per tablespace
###########################################
my $dup = 0;

# CREATE TABLESPACE string

my ($create_ts, $datafile, $def_strg1, $def_strg2, $def_strg3, $alt_ts);

my $dbh = DBI -> connect ("dbi:Oracle:$connstrg", $username, $passwd)

          || die "Database connection not made: $DBI::errstr";

# open file to write out tablespace info
open my $fh, ">$filename" or die

   "Can't create $!";

my $sql = qq{ select t.tablespace_name,
t.initial_extent,

               t.next_extent, t.min_extents, t.max_extents,

               t.pct_increase, df.bytes, df.file_name, df.relative_fno

            from dba_data_files df, dba_tablespaces t
            where t.tablespace_name =
df.tablespace_name
            order by t.tablespace_name,

df.relative_fno };

my $sth = $dbh -> prepare( $sql );
$sth -> execute();

my ($tablespace_name, $initial_extent, $next_extent,
    $min_extents, $max_extents, $pct_increase, $bytes,
    $file_name, $relative_fno);

$sth -> bind_columns(\$tablespace_name,
\$initial_extent, \$next_extent,

           \$min_extents, \$max_extents, \$pct_increase, \$bytes,

           \$file_name, \$relative_fno);

while( $sth -> fetch() ) {

   $ts_name = $tablespace_name;

###########################################
# Here is where I check if there are more
# than one datafile per tablespace
###########################################

   if ( $prev_val eq $ts_name ) {

      $dup = 1;
      $alt_ts = "ALTER TABLESPACE " . $ts_name;

   }
   else {

      $dup = 0;
      $prev_val = $ts_name;
   }
      
   $fl_name = $file_name;
   $init_ext = $initial_extent;
   $nxt_ext = $next_extent;
   $min_ext = $min_extents;  
   $max_ext = $max_extents;

   $pct_incr = $pct_increase;
   $size = $bytes;

###########################################
# if $dup is 0 than I only have one datafile
# per tablespace else I set this to 1 which
# I have more than one datafile per
# tablespace so I use an ALTER statement
# add the extra datafile to the tablespace
###########################################

   if ( $dup == 0 ) {

      $alt_ts = "CREATE TABLESPACE " . $ts_name;
      print $fh $alt_ts . "\n"; 

   }
   else {

      $alt_ts = "ALTER TABLESPACE " . $ts_name;
      print $fh $alt_ts . "\n";

   }

   if ( $dup == 0 ) {

      $datafile = "DATAFILE '" . $fl_name . "' SIZE " . $size;

      print $fh $datafile . "\n";
      $def_strg1 = "DEFAULT STORAGE (\n\tINITIAL " .
$init_ext;
      $def_strg1 = $def_strg1 . "\n\tNEXT " .
$nxt_ext;
      $def_strg2 = "\n\tMINEXTETNTS " . $min_ext .
"\n\tMAXEXTENTS " . $max_ext;
      $def_strg3 = "\n\tPCTINCREASE " . $pct_incr .
");";
      print $fh $def_strg1, $def_strg2, $def_strg3 .

"\n" . "\n";

   }
   else {

      $datafile = "ADD DATAFILE " . $fl_name . " SIZE " . $size . ";";

      print $fh $datafile . "\n" . "\n";    }

}

$sth -> finish();
$dbh -> disconnect();

$dbh -> disconnect();

close $fh;

• "Daiminger, Helmut" <Helmut.Daiminger_at_KirchGruppe.de> wrote: > Hi! > > I want to write a "create tablespace" script that > creates all "create > tablespace" statements for a database. > > I got this script working if each tablesspace has > only one datafile. But how > would I handle it if a tablespace consists of 2 > datafiles, e.g. datafile 5 > and 87 from dba_data_files... > > Is there an id for the datafiles within the > tablespace??? > > Any ideas? > > Thanks, > Helmut >

  ---

Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: mkb
  INET: mkb125_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jan 21 2003 - 15:10:00 CST