Jared,
In answer to my question re "is it on cpan etc..."
duh! should have checked before. Yes it is.
Thanks, looks good. I'll download it and play with it
some.
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).
