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.
>
> 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: [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).
