ORA-24761: transaction rolled back

[liquidchild]

Hi

I am trying to move data from one database into another and am using datatables to achieve this.

I populate the datatable and then start up a new transaction using the TransactionScope object and insert the values in the datatable into the new database, after this I then create another connection and try to remove the values from the source DB.

However during this process (around 11 minutes into it, it would seem), the process falls over and I get the above error in the log files.

I can't figure out why it is doing this, do I need to set some timeout that I have not done?

I have seen a couple of posts in various places that there mite be a problem with .net doing such large datasets (about 4 tables each with 40,000-70,000 rows). Does anyone know if this is the case?

I have included a section of the code below to give you an idea of what is going on in the application.

Should also add that i am using

VB.net (.net V2.0)
Oracle 9iR2

Public Shared Function ArchiveTables(ByVal archiveDate As Date, ByVal statusReport As StatusReport) As Boolean

        'Tables to hold the data being archived
        Dim exMsgContent As DataTable
        Dim exPkgMsgAssoc As DataTable
        Dim exPkgMsgDet As DataTable
        Dim exPkgMsgHdr As DataTable

        Try
            '
            'Retrieve the data to be archived, store the counts to the status report
            '
            exMsgContent = GetListOfExMsgContent(archiveDate)
            statusReport.ExMsgContentToBeArchived = exMsgContent.Rows.Count

            Logger.LogInfo("Number of EX_MSG_CONTENT rows to archive: " & statusReport.ExMsgContentToBeArchived)

            exPkgMsgAssoc = GetListOfExPkgMsgAssoc(archiveDate)
            statusReport.ExPkgMsgAssocToBeArchived = exPkgMsgAssoc.Rows.Count

            Logger.LogInfo("Number of EX_PKGMSG_ASSOC rows to archive: " & statusReport.ExPkgMsgAssocToBeArchived)

            exPkgMsgDet = GetListOfExPkgMsgDet(archiveDate)
            statusReport.ExPkgMsgDetToBeArchived = exPkgMsgDet.Rows.Count

            Logger.LogInfo("Number of EX_PKGMSG_DET rows to archive: " & statusReport.ExPkgMsgDetToBeArchived)

            exPkgMsgHdr = GetListOfExPkgMsgHdr(archiveDate)
            statusReport.ExPkgMsgHdrToBeArchived = exPkgMsgHdr.Rows.Count

            Logger.LogInfo("Number of EX_PKGMSG_HDR rows to archive: " & statusReport.ExPkgMsgHdrToBeArchived)
        Catch aEx As ArchiverException
            Logger.LogError("Error while trying to retrieve the data to be archived")

            Throw aEx
        End Try

        Try
            '
            'Attempt to archive the data
            '
            Dim TransOptions As New TransactionOptions
            Dim TransScope As New TransactionScopeOption()

            TransOptions.Timeout = System.TimeSpan.FromMinutes(30000)

            'Start the connection and transaction to the archive DB
            Using scope As New TransactionScope(TransScope, TransOptions)

                Using archiveConnection As OracleConnection = OracleDatabaseHelper.SetupOracleConnection(AppConfig.ArchiveDB)

                    archiveConnection.Open()

                    'Try to archive the database tables
                    Logger.LogDebug("Saving the EX_PKGMSG_ASSOC table data")
                    statusReport.ExPkgMsgAssocArchived = saveListOfExPkgMsgAssoc(exPkgMsgAssoc, archiveConnection, statusReport)

                    Logger.LogDebug("Saving the EX_PKGMSG_DET data")
                    statusReport.ExPkgMsgDetArchived = saveListOfExPkgMsgDet(exPkgMsgDet, archiveConnection, statusReport)

                    Logger.LogDebug("Saving the EX_PKGMSG_HDR data")
                    statusReport.ExPkgMsgHdrArchived = saveListOfExPkgMsgHdr(exPkgMsgHdr, archiveConnection, statusReport)

                    Logger.LogDebug("Saving the EX_MSG_CONTENT data")
                    statusReport.ExMsgContentArchived = saveListOfExMsgContent(exMsgContent, archiveConnection, statusReport)

                    'Set the status of the archive part to successful, this mite be overwritten later to failure if we
                    'don't successfully purge the table
                    statusReport.setDBArchiveSuccessful()

                    '
                    'Purge the data from the source database
                    '

                    Using sourceConnection As OracleConnection = OracleDatabaseHelper.SetupOracleConnection(AppConfig.SourceDB)

                        sourceConnection.Open()

                        statusReport.ExPkgContentDeleted = deleteListOfExMsgContent(exMsgContent, sourceConnection)
                        statusReport.ExPkgMsgAssocDeleted = deleteListOfExPkgMsgAssoc(exPkgMsgAssoc, sourceConnection)
                        statusReport.ExPkgMsgDetDeleted = deleteListOfExPkgMsgDet(exPkgMsgDet, sourceConnection)
                        statusReport.ExPkgMsgHdrDeleted = deleteListOfExPkgMsgHdr(exPkgMsgHdr, sourceConnection)

                        '
                        'Commit the changes
                        '

                    End Using
                End Using
                'The complete method commits the transaction.  If an exception has been thrown 
                'complete is called and the transaction is rolled back
                scope.Complete()
            End Using
        Catch aEx As ArchiverException
            Logger.LogError("Error while trying to archive the database tables")

            'Update the report
            statusReport.setDBArchiveUnSuccessful()
            statusReport.setDBPurgeUnSuccessful()

            Throw aEx
        Catch ex As Exception
            'Update the report
            statusReport.setDBArchiveUnSuccessful()
            statusReport.setDBPurgeUnSuccessful()

            'Pass the exception up to be caught
            Throw New ArchiverException(ArchiverException.UnexpectedExceptionWhenDoingTheDBArchive_code, ArchiverException.UnexpectedExpcetionWhenDoingTheDBArchive_msg, ex)
        End Try
    End Function

 

[cjard]

You'd be better off doing this, in a query analyzer like TOAD:

Login to your main database
Use http://www.softics.ru/docs/oracle10r2/server.101/b10759/statements_5005.htm to CREATE DATABASE LINK to the database you want to archive to
e.g. CREATE DATABASE LINK my_db_link CONNECT TO scott IDENTIFIED BY tiger USING 'TNS_NAME_ON_MAIN_DB_SERVER'
(Yes your main db server needs the oracle client installed. You can also do this on the archive machine and pull the data)


Then you can do this in toad or vb.net
Call something like this to push the data:

INSERT INTO archive_Table@my_db_link SELECT * FROM my_table_to_archive

TRUNCATE TABLE my_table_to_archive



Oracle does the donkeywork

 

[liquidchild]

Thanks for the reply, while I know this is one (possibly better) way of doing it, unfort I have inherited the code like this and along with the database it does do other stuff - like moves files around etc. So i need to keep the app, there is no reason why the app shouldn't be able to handle this anyway.

 

[cjard]

You can have VB break as soon as an exception occurrs.. See Debug menu, Exceptions... item. Tick next to CLR Exceptions

 

[liquidchild]

I don't see the option you are saying, I am using VS2005, its not in the debug menu. Will this tell me what is causing the roll back? i.e. a timeout issue or something else, as currently the only error being thrown is the ORA one posted.

 

[cjard]

A few people say they have trouble finding this. You can try the keyboard combo for it:

Ctrl+D, E


But bear in mind I have my environment set up for C# so it might be a different combo.

You can also customize your menus yourslef
Right click any toolbar and customize it
In the Debug section on the left, find Exceptions... on the right and drag to a toolbar or menu

ps; no it wont be a magic button that fixes all your problems! But it will cause VB to break at the exact point the exception starts to occur so you can see for certain which operation is causing it