Brian,
Try checking the permissions on the underlying datafiles versus what user
the Oracle instance is running as. Often, the oracle datafiles will be
read/write permissions to only the user who created the database in the
first place, but the database daemons will happily start up as someone
else; then the database cannot do anything despite having ample space
available.
Lane Hoy
University of Michigan School of Dentistry Programming Services
On Tue, 10 Jun 2003, Forquer, Brian R. wrote:
> I have unlimited space in Table Space, I have 30 gig of hard drive space, I
> am confuesd. Here is what I got posting whole code because I have two
> problems. On Mandrake 9 current versionsDBI and DBD::Oracle
> First Error is:
>
> DBD::Oracle::st execute failed: ORA-01658: unable to create INITIAL extent
> for segment in tablespace WEBDATA (DBD ERROR: OCIStmtExecute) [for statement
> ``CREATE TABLE IS_Volume (
> iPeriod INTEGER,
> icVolume INTEGER,
> icRevenue INTEGER,
> icBonus INTEGER,
> ipVolume INTEGER,
> ipRevenue INTEGER,
> ipBonus INTEGER
> )
> '' with params: ]) at ./importv2 line 111.
> Cannot Create table IS_Volume:
> ORA-01658: unable to create INITIAL extent for segment in tablespace WEBDATA
> (DBD ERROR: OCIStmtExecute)
>
>
> So not to be held back I #'ed out all the IS_Volume related stuff and ran
> the script and got this:
>
> DBD::Oracle::st execute failed: ORA-01653: unable to extend table
> WEBDATA.SALES_VOLUME by 256 in tablespace WEBDATA (DBD ERROR:
> OCIStmtExecute) [for statement ``INSERT INTO Sales_Volume VALUES ( '200302',
> 'SR2313', '45.000', '1260.00', '405.00', '0.00', '35.000', '980.00' )'' with
> params: ]) at ./importv2 line 348, <SALES_VOLUME> line 55229.
> Can not execute Sales_vol data import:
> ORA-01653: unable to extend table WEBDATA.SALES_VOLUME by 256 in tablespace
> WEBDATA (DBD ERROR: OCIStmtExecute)
>
>
> To preface I am in no way a Oracle DB and this is my first major perl
> undertaking so any feed back will be extremely helpful, even if on either
> Oracle or Perl side.
>
>
>
>
>
>
>
>
> CODE
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ------------------------------------------------------------------------
> #!/usr/bin/perl
> use DBI; #Opens DBI
> print "\n\n\n\nBegining import of files from SAP\n";
> if ($ARGV[0] eq "new") { #determines wether to delete files or nor
> print "\n\n\n\nDeleting old files\n\n";
> my $deleted = unlink glob "*.txt";
> print "Deleted $deleted files.\n";
> #Retrieves files from ftp
> @files = qw ( Distributor.txt Sales_Rep.txt Sales_vol.txt
> 2003_GOV_Sales.txt 2003_SBS_Sales.txt 2003_SB_NAS.txt );
> foreach $files (@files) {
> system "wget ftp:/(REMOVED)";
> print "\n\n\n\nReceived file $files\n\n\n\n";
> }
> }
> #Connection to database
> my $dbh = DBI->connect( "dbi REMOVED )
> or die "Cannot connect to Oracle Server\n: $DBI::errstr\n";
>
> #Dropping Tables
> #Sales_Rep
> my $sth = $dbh->prepare( "DROP TABLE Sales_Rep")
> or die "Can not Prep Drop Table rep\n: $DBI::errstr\n";
> $sth->execute
> or die "Can not execute Drop Table rep\n: $DBI::errstr\n";
> #NAS_Volume
> $sth = $dbh->prepare( "DROP TABLE NAS_Volume" )
> or die "Can not Prep Drop Table NAS_Volume:\n $DBI::errstr\n";
> $sth->execute
> or die "Can not Execute Dropt Table NAS_Volume:\n $DBI::errstr\n";
> #Sales_Volume
> $sth = $dbh->prepare( "DROP TABLE Sales_Volume" )
> or die "Can not Prep Drop Table Sales_Volume:\n $DBI::errstr\n";
> $sth->execute
> or die "Can not Execute Drop Table Sales_Volume:\n $DBI::errstr\n";
> #GOV_Volume
> $sth = $dbh->prepare( "DROP TABLE GOV_Volume" )
> or die "Can not Prep Drop Table GOV_Volume:\n $DBI::errstr\n";
> $sth->execute
> or die "Can not Execute Drop Table GOV_Volume:\n $DBI::errstr\n";
> #Creatinng Tables
> #Sales_Rep
> $sth = $dbh->prepare( "CREATE TABLE Sales_Rep (
> sRepID VARCHAR(6),
> sName VARCHAR(30)
> )
> ")
> or die "Cannot prep for table rep\n: $DBI::errstr\n";
> $sth->execute
> or die "Cannot create table rep\n: $DBI::errstr\n";
>
> #Sales_Volume
> $sth = $dbh->prepare( "CREATE TABLE Sales_Volume (
> iPeriod INTEGER,
> sTSM VARCHAR(10),
> icVolume INTEGER,
> icRevenue INTEGER,
> icBonus INTEGER,
> ipVolume INTEGER,
> ipRevenue INTEGER,
> ipBonus INTEGER
> )
> " )
> or die "Cannot prep for table Sales_Volume:\n $DBI::errstr\n";
> $sth->execute
> or die "Cannot Create table Sales_Volume:\n $DBI::errstr\n";
>
> #NAS_Volume
> $sth = $dbh->prepare( "CREATE TABLE NAS_Volume (
> sNAD VARCHAR(10),
> iPeriod INTEGER,
> icVolume INTEGER,
> icRevenue INTEGER,
> icBonus INTEGER,
> ipVolume INTEGER,
> ipRevenue INTEGER,
> ipBonus INTEGER
> )
> " )
> or die "Cannot prep for table NAS_Volume:\n $DBI::errstr\n";
> $sth->execute
> or die "Cannot Create table NAS_Volume:\n $DBI::errstr\n";
>
> #GOV_Volume
> $sth = $dbh->prepare( "CREATE TABLE GOV_Volume (
> sGOV VARCHAR(10),
> iPeriod INTEGER,
> icVolume INTEGER,
> icRevenue INTEGER,
> icBonus INTEGER,
> ipVolume INTEGER,
> ipRevenue INTEGER,
> ipBonus INTEGER
> )
> " )
> or die "Cannot prep for table GOV_Volume:\n $DBI::errstr\n";
> $sth->execute
> or die "Cannot Create table GOV_Volume:\n $DBI::errstr\n";
>
> #IS_Volume
> $sth = $dbh->prepare( "CREATE TABLE IS_Volume (
> iPeriod INTEGER,
> icVolume INTEGER,
> icRevenue INTEGER,
> icBonus INTEGER,
> ipVolume INTEGER,
> ipRevenue INTEGER,
> ipBonus INTEGER
> )
> " )
> or die "Cannot prep for table IS_Volume:\n $DBI::errstr\n";
> $sth->execute
> or die "Cannot Create table IS_Volume:\n $DBI::errstr\n";
>
>
>
>
> #Opening Sales_Rep.txt
> unless (open SALES_REP, "Sales_Rep.txt") {
> die "Can not open Sales_Rep.txt:\n $!";
> }
> while (<SALES_REP>) {
> chomp;
> ($srnu,$srna) = split /\^/, $_;
> my $qsrnu = $dbh->quote( $srnu );
> my $qsrna = $dbh->quote( $srna );
> $sth = $dbh->prepare( "INSERT INTO Sales_Rep VALUES ( $qsrnu, $qsrna
> )" )
> or die "Can not prep Sales_Rep data for import:\n $DBI::errstr\n";
> $sth->execute
> or die "Can not execute Sales_Rep data import:\n $DBI::errstr\n";
> }
> close SALES_REP;
> #OPENING NAS_VOLUME
> unless (open NAS_VOLUME, "2003_SB_NAS.txt") {
> die "Can not open 2003_SB_NAS.tx";
> }
> while (<NAS_VOLUME>) {
> if ($. gt 1 ) {
> chomp;
> (undef, undef, undef, undef, $sNAD, $iPeriod, $icVolume,
> $icRevenue, $icBonus, $ipVolume, $ipRevenue, $ipBonus, undef) = split /\~/,
> $_;
> $sNAD =~ s/\s+//g;
> $iPeriod =~ s/\s+//g;
> $icVolume =~ s/\s+//g;
> $icRevenue =~ s/\s+//g;
> $icBonus =~ s/\s+//g;
> $ipVolume =~ s/\s+//g;
> $ipRevenue =~ s/\s+//g;
> $ipBonus =~ s/\s+//g;
> if ($icVolume =~ /\-$/) {
> my $minus = chop $icVolume;
> $icVolume = $minus . $icVolume;
> }
> if ($icRevenue =~ /\-$/) {
> my $minus = chop $icRevenue;
> $icRevenue = $minus . $icRevenue;
> }
> if ($icBonus =~ /\-$/) {
> my $minus = chop $icBonus;
> $icBonus = $minus . $icBonus;
> }
> if ($ipVolume =~ /\-$/) {
> my $minus = chop $ipVolume;
> $ipVolume = $minus . $ipVolume;
> }
> if ($ipRevenue =~ /\-$/) {
> my $minus = chop $ipRevenue;
> $ipRevenue = $minus . $ipRevenue;
> }
> if ($ipBonus =~ /\-$/) {
> my $minus = chop $ipBonus;
> $ipBonus = $minus . $ipBonus;
> }
> my $qiPeriod = $dbh->quote( $iPeriod );
> my $qsNAD = $dbh->quote( $sNAD );
> my $qicVolume = $dbh->quote( $icVolume );
> my $qicRevenue = $dbh->quote( $icRevenue );
> my $qicBonus = $dbh->quote( $icBonus );
> my $qipVolume = $dbh->quote( $ipVolume );
> my $qipRevenue = $dbh->quote( $ipRevenue );
> my $qipBonus = $dbh->quote( $ipBonus );
> $sth = $dbh->prepare( "INSERT INTO NAS_Volume VALUES (
> $qsNAD, $qiPeriod, $qicVolume, $qicRevenue, $qicBonus, $qipVolume,
> $qipRevenue, $qipBonus )" )
> or die "Can not prep NAS_vol data for import:\n
> $DBI::errstr\n";
> $sth->execute
> or die "Can not execute NAS_vol data import:\n
> $DBI::errstr\n";
> print "NAS: $.\n"
> }
> }
> close NAS_VOLUME;
>
>
> #OPENING GOV_VOLUME
> unless (open GOV_VOLUME, "2003_GOV_Sales.txt") {
> die "Can not open 2003_GOV_Sales.tx";
> }
> while (<GOV_VOLUME>) {
> if ($. gt 1 ) {
> chomp;
> (undef, undef, undef, undef, $sGOV, $iPeriod, $icVolume,
> $icRevenue, $icBonus, $ipVolume, $ipRevenue, $ipBonus, undef) = split /\~/,
> $_;
> $sGOV =~ s/\s+//g;
> $iPeriod =~ s/\s+//g;
> $icVolume =~ s/\s+//g;
> $icRevenue =~ s/\s+//g;
> $icBonus =~ s/\s+//g;
> $ipVolume =~ s/\s+//g;
> $ipRevenue =~ s/\s+//g;
> $ipBonus =~ s/\s+//g;
> if ($icVolume =~ /\-$/) {
> my $minus = chop $icVolume;
> $icVolume = $minus . $icVolume;
> }
> if ($icRevenue =~ /\-$/) {
> my $minus = chop $icRevenue;
> $icRevenue = $minus . $icRevenue;
> }
> if ($icBonus =~ /\-$/) {
> my $minus = chop $icBonus;
> $icBonus = $minus . $icBonus;
> }
> if ($ipVolume =~ /\-$/) {
> my $minus = chop $ipVolume;
> $ipVolume = $minus . $ipVolume;
> }
> if ($ipRevenue =~ /\-$/) {
> my $minus = chop $ipRevenue;
> $ipRevenue = $minus . $ipRevenue;
> }
> if ($ipBonus =~ /\-$/) {
> my $minus = chop $ipBonus;
> $ipBonus = $minus . $ipBonus;
> }
> my $qiPeriod = $dbh->quote( $iPeriod );
> my $qsGOV = $dbh->quote( $sGOV );
> my $qicVolume = $dbh->quote( $icVolume );
> my $qicRevenue = $dbh->quote( $icRevenue );
> my $qicBonus = $dbh->quote( $icBonus );
> my $qipVolume = $dbh->quote( $ipVolume );
> my $qipRevenue = $dbh->quote( $ipRevenue );
> my $qipBonus = $dbh->quote( $ipBonus );
> $sth = $dbh->prepare( "INSERT INTO GOV_Volume VALUES (
> $qsGOV, $qiPeriod, $qicVolume, $qicRevenue, $qicBonus, $qipVolume,
> $qipRevenue, $qipBonus )" )
> or die "Can not prep GOV_vol data for import:\n
> $DBI::errstr\n";
> $sth->execute
> or die "Can not execute GOV_vol data import:\n
> $DBI::errstr\n";
> print "GOV: $.\n"
> }
> }
> close GOV_VOLUME;
> #OPENING IS_VOLUME
> unless (open IS_VOLUME, "2003_SBS_Sales.txt") {
> die "Can not open 2003_SBS_SALES.tx";
> }
> while (<IS_VOLUME>) {
> if ($. gt 1 ) {
> chomp;
> (undef, undef, undef, $iPeriod, $icVolume, $icRevenue,
> $icBonus, $ipVolume, $ipRevenue, $ipBonus, undef) = split /\~/, $_;
> $iPeriod =~ s/\s+//g;
> $icVolume =~ s/\s+//g;
> $icRevenue =~ s/\s+//g;
> $icBonus =~ s/\s+//g;
> $ipVolume =~ s/\s+//g;
> $ipRevenue =~ s/\s+//g;
> $ipBonus =~ s/\s+//g;
> if ($icVolume =~ /\-$/) {
> my $minus = chop $icVolume;
> $icVolume = $minus . $icVolume;
> }
> if ($icRevenue =~ /\-$/) {
> my $minus = chop $icRevenue;
> $icRevenue = $minus . $icRevenue;
> }
> if ($icBonus =~ /\-$/) {
> my $minus = chop $icBonus;
> $icBonus = $minus . $icBonus;
> }
> if ($ipVolume =~ /\-$/) {
> my $minus = chop $ipVolume;
> $ipVolume = $minus . $ipVolume;
> }
> if ($ipRevenue =~ /\-$/) {
> my $minus = chop $ipRevenue;
> $ipRevenue = $minus . $ipRevenue;
> }
> if ($ipBonus =~ /\-$/) {
> my $minus = chop $ipBonus;
> $ipBonus = $minus . $ipBonus;
> }
> my $qiPeriod = $dbh->quote( $iPeriod );
> my $qicVolume = $dbh->quote( $icVolume );
> my $qicRevenue = $dbh->quote( $icRevenue );
> my $qicBonus = $dbh->quote( $icBonus );
> my $qipVolume = $dbh->quote( $ipVolume );
> my $qipRevenue = $dbh->quote( $ipRevenue );
> my $qipBonus = $dbh->quote( $ipBonus );
> $sth = $dbh->prepare( "INSERT INTO IS_Volume VALUES (
> $qiPeriod, $qicVolume, $qicRevenue, $qicBonus, $qipVolume, $qipRevenue,
> $qipBonus )" )
> or die "Can not prep IS_vol data for import:\n
> $DBI::errstr\n";
> $sth->execute
> or die "Can not execute IS_vol data import:\n
> $DBI::errstr\n";
> print "IS: $.\n"
> }
> }
> close IS_VOLUME;
> #OPENING SALES_VOLUME
>
> unless (open SALES_VOLUME, "Sales_vol.txt") {
> die "Can not open Sales_vol.txt";
> }
> while (<SALES_VOLUME>) {
> if ($. gt 1) {
> chomp;
> ($iPeriod, undef, $sTSM, undef, undef, undef, undef, undef,
> $icVolume, $icRevenue, $icPGCP, $ipVolume, $ipRevenue, $ipPGCP) = split
> /\^/, $_;
> if ( length($sTSM) == 6 ) {
> $sTSMk = $sTSM;
> } else {
> $sTSMk = "NODATA";
> }
> if ($icVolume =~ /\-$/) {
> my $minus = chop $icVolume;
> $icVolume = $minus . $icVolume;
> }
> if ($icRevenue =~ /\-$/) {
> my $minus = chop $icRevenue;
> $icRevenue = $minus . $icRevenue;
> }
> if ($icPGCP =~ /\-$/) {
> my $minus = chop $icPGCP;
> $icPGCP = $minus . $icPGCP;
> }
> if ($ipVolume =~ /\-$/) {
> my $minus = chop $ipVolume;
> $ipVolume = $minus . $ipVolume;
> }
> if ($ipRevenue =~ /\-$/) {
> my $minus = chop $ipRevenue;
> $ipRevenue = $minus . $ipRevenue;
> }
> if ($ipPGCP =~ /\-$/) {
> my $minus = chop $ipPGCP;
> $ipPGCP = $minus . $ipPGCP;
> }
> my $qiPeriod = $dbh->quote( $iPeriod );
> my $qsTSM = $dbh->quote( $sTSMk );
> my $qicVolume = $dbh->quote( $icVolume );
> my $qicRevenue = $dbh->quote( $icRevenue );
> my $qicPGCP = $dbh->quote( $icPGCP );
> my $qipVolume = $dbh->quote( $ipVolume );
> my $qipRevenue = $dbh->quote( $ipRevenue );
> my $qipPGCP = $dbh->quote( $ipPGCP );
> $sth = $dbh->prepare( "INSERT INTO Sales_Volume VALUES (
> $qiPeriod, $qsTSM, $qicVolume, $qicRevenue, $qicPGCP, $qipVolume,
> $qipRevenue, $qipPGCP )" )
> or die "Can not prep Sales_vol data for import:\n
> $DBI::errstr\n";
> $sth->execute
> or die "Can not execute Sales_vol data import:\n
> $DBI::errstr\n";
> print "Sales: $.\n"
> }
> }
> close SALES_VOLUME;
> #Close Database
> $dbh->disconnect
> or die "Disconnection Fialed: $DBI::errstr\n";
>
>
>
>
