Folks, I'm wondering if anyone can provide some insight. recently we did an online table redefinition with Oracle 10.2.0.3/ SLES10/Linux x86_64. Once the redefinition completed successfully, we had a number of failures from our perl programs that failed with the following:
DBD::Oracle::st execute failed: ORA-12096: error in materialized view log on "SCOTT"."TEST_TABLE" ORA-00942: table or view does not exist (DBD ERROR: OCIStmtExecute) [for Statement " It seems that if we alter the structure of the table (in my case I was adding new partition definitions), after the $dbh->prepare(), subsequent $sth->execute() calls will fail. In Oracle once the table structure changes, all cached cursors/execution plans are invalidated. I guess there is no way to push that information to DBI? The test case was simple and easily reproducible with an UPDATE statement: 1) obtain a new $dbh 2) call $sth = $dbh->prepare($yourquery) 3) in a loop call $sth->execute(@params) 4) << perform table rebuild in Oracle via dbms_redefinition package >> 5) once rebuild finishes, the $sth->execute() will fail with the above ORA-12096 and ORA-00942 error. I'm guessing this is probably old news, but I was hoping to get some feedback about this. This happens whether we redefine a table with a new or same structure (same columns,partitions etc). The oracle instant client version is 10.1.0.2 prompt> perl -MDBD::Oracle -le 'print $DBD::Oracle::VERSION;' => 1.16 prompt> perl -MDBI -le 'print $DBI::VERSION;' => 1.48 I suspect that there is really no mystery here. The $sth has some metadata information that becomes invalid when the redefinition complete, and the execute() method does not handle it well. Let me your thoughts. -peter
