Perl DBI -> Oracle 
Author Message
 Perl DBI -> Oracle

I have a perl script which are deleting bunch of
rows from Oracle database. Sometime this scripts
fails because rollback segment of Oracle is small.
So DBA created new rollback segment.
I want to add "set transaction use rollback
segment R_BIG" statement prior to my delete
statement, how do use it ?

Here are my steps
connect()
$statement = "set.."
prepare()
execute()
$staement = "delete xx"
prepare()
execute()
finish()
disconnect()

This does not worked it is still using default
rollback segment. Any idea how to do it?

Thanks,

Chiku

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Sat, 15 Feb 2003 12:23:13 GMT  
 Perl DBI -> Oracle
: I have a perl script which are deleting bunch of
: rows from Oracle database. Sometime this scripts
: fails because rollback segment of Oracle is small.
: So DBA created new rollback segment.
: I want to add "set transaction use rollback
: segment R_BIG" statement prior to my delete
: statement, how do use it ?

Perhaps you can send a single block as the statement

$sql='
BEGIN
SET TRANSACTION USE ROLLBACK SEGMENT R_BIG;
DELETE FROM the_table;
COMMIT;
END;
';

 $0.02



Sat, 15 Feb 2003 03:00:00 GMT  
 Perl DBI -> Oracle
Or, instead of deleting so many rows in a single transaction, break it
up into
chunks of 5 or 10 thousand rows and commit after each chunk.

This way you can leave the DBA out of it as well...

Mark B.

Quote:


> : I have a perl script which are deleting bunch of
> : rows from Oracle database. Sometime this scripts
> : fails because rollback segment of Oracle is small.
> : So DBA created new rollback segment.
> : I want to add "set transaction use rollback
> : segment R_BIG" statement prior to my delete
> : statement, how do use it ?

> Perhaps you can send a single block as the statement

> $sql='
> BEGIN
> SET TRANSACTION USE ROLLBACK SEGMENT R_BIG;
> DELETE FROM the_table;
> COMMIT;
> END;
> ';

>  $0.02



Thu, 13 Mar 2003 03:00:00 GMT  
 Perl DBI -> Oracle
: Or, instead of deleting so many rows in a single transaction, break it
: up into
: chunks of 5 or 10 thousand rows and commit after each chunk.

: This way you can leave the DBA out of it as well...
(good idea)
E.g. maybe something like this pseudo code

        do { $deleted = row_count_of(
                delete from THE_TABLE
                where --delete-clause--
                AND ROWNUM < 5000);
        } while (deleted == 5000);

: Mark B.

: > : I have a perl script which are deleting bunch of
: > : rows from Oracle database. Sometime this scripts
: > : fails because rollback segment of Oracle is small.
: > : So DBA created new rollback segment.
: > : I want to add "set transaction use rollback
: > : segment R_BIG" statement prior to my delete
: > : statement, how do use it ?
: >
: > Perhaps you can send a single block as the statement
: >
: > $sql='
: > BEGIN
: > SET TRANSACTION USE ROLLBACK SEGMENT R_BIG;
: > DELETE FROM the_table;
: > COMMIT;
: > END;
: > ';
: >
: >  $0.02

--



Thu, 13 Mar 2003 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. NT ORACLE PERL DBD DBI->connect format

2. NT ORACLE PERL DBD DBI->connect format

3. DBI->connect Oracle - ODBC - TNS Service Name

4. $sth->rows function in DBI-Oracle

5. Oracle DBI <--> CGI nasty problem

6. $sth->rows function in DBI-Oracle

7. Oracle on NT <-> DBI on Linux

8. WinNT->DBI->DBD::ODBC->Jet->INSERT == SQL-S1000

9. How to evaluate Oracle<->Perl<->Web Interfaces

10. Perl DBI->DBD::ODBC->SQl*Net->Oracle don't work?

11. Oracle DBD/DBI on HP-UX: can't load Oracle.sl

12. dbi:: oracle on oracle 8i 8.6.1

 

 
Powered by phpBB® Forum Software