Jared, Is that a perl module? downloadable from CPAN?
thanks mohammed --- [EMAIL PROTECTED] wrote: > Since you're using Perl already, give DDL::Oracle a > try. > > Takes a lot less code. > > Jared > > > > > > > mkb <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/21/2003 01:09 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: Re: create tablespace script > > > 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" > <[EMAIL PROTECTED]> wrote: > > Hi! > > > > I want to write a "create tablespace" script that > > creates all "create > > tablespace" statements for a database. > > > === message truncated === __________________________________________________ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).