Check Oracle Tables for Changes

[Peterg001]

Hi Everyone,

Im using the following function to get a list of all tables in the database:

Public Function getTableList(ByVal strOracleConnection As String)
        'Get table list from database
        Dim tblReturned As New Collection
        Dim strQuery As String = String.Empty
        strQuery = "SELECT * FROM TAB " & _
                    "WHERE TABTYPE='TABLE'"
        Services.postEvent(m_strOracleConnection, "getTableList", "Querying database for list of tables.", Services.NotificationType.Notification, True, False)

        Using oraConn As New OracleConnection
            oraConn.ConnectionString = strOracleConnection
            Using oraCmd As New OracleCommand(strQuery, oraConn)
                'Try to open the connection
                Try
                    oraCmd.Connection.Open()
                    Using oraRdr = oraCmd.ExecuteReader()
                        If oraRdr.HasRows Then
                            oraRdr.Read()
                            For intLoop As Integer = 0 To (oraRdr.FieldCount - 1)
                                Dim strColName = oraRdr.GetName(intLoop)
                                tblReturned.Add(getDBV(oraRdr, strColName), strColName)
                            Next
                            Services.postEvent(m_strOracleConnection, "getTableList", "Table list acquired successfully", Services.NotificationType.Notification, True, False)
                        End If
                    End Using
                Catch oraException As OracleException
                    Dim excToThrow As New Exception("[0010105X] " & oraException.Number & " " & oraException.Message, oraException)
                    Throw (excToThrow)
                Catch exError As Exception
                    Dim excToThrow As New Exception("[0010106] " & exError.Message)
                    Throw (excToThrow)
                Finally
                    MDL.DatabaseAccess.closeConnection(oraConn)
                End Try
            End Using
        End Using
        Return tblReturned
    End Function

I then use a timer to wait 10 minutes which then runs another function which is essentially the same as above but the returned collection is named differently.

I then want to compare the data in both collections against each other to see if there have been any changes made to the Tables i.e. if a table has been created or deleted or it one has been renamed.

Does anyone know how i might do this?

 

[cjard]

Use these SQLs:

CREATE TABLE tab_hist (t DATE, n VARCHAR2(64));

INSERT INTO tab_hist
SELECT sysdate, table_name FROM user_tables;

SELECT
  COALESCE(nu.n, old.n) as table_name,
  CASE
    WHEN nu.n IS NULL AND old.n IS NOT NULL THEN 'Table has disappeared'
    WHEN nu.n IS NOT NULL AND old.n IS NULL THEN 'Table has appeared'
    ELSE 'Table persists'
  END as fate
FROM
  (SELECT * FROM tab_hist WHERE t BETWEEN sysdate-(15/24/60) AND sysdate-(5/24/60)) old
  FULL OUTER JOIN
  (SELECT * FROM tab_hist WHERE t > SYSDATE-(5/24/60)) nu
  ON nu.n = old.n;

DELETE FROM tab_hist WHERE t < SYSDATE-(5/24/60);

5/24/60 is 5 minutes

Run the first statement manually once
Then run the other 3 in sequence every 10 minutes

The rest should be fairly self explanatory.. check back if you don't get it