Mining Your Own Business, Is Supplemental Logging Enabled?

January 12, 2010

A couple years ago the following question was asked in the comp.databases.oracle.server Usernet group:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/7d4bfb37a4fd4a33/

How can I determine if SUPPLEMENTAL LOGGING as been enable at the database level?

Done by this command:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA  (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

The original poster did not mention why he wanted to check the status of supplemental logging, but I suspect that he wanted to use LogMiner.

The response that I provided to the OP follows:

Take a look at the output of the following:

SELECT
  SUPPLEMENTAL_LOG_DATA_MIN,
  SUPPLEMENTAL_LOG_DATA_PK,
  SUPPLEMENTAL_LOG_DATA_UI
FROM
  V$DATABASE;

SUPPLEME SUP SUP
-------- --- ---
NO       NO  NO

Now, execute your statement:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA  (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

Then check again: 

SELECT
  SUPPLEMENTAL_LOG_DATA_MIN,
  SUPPLEMENTAL_LOG_DATA_PK,
  SUPPLEMENTAL_LOG_DATA_UI
FROM
  V$DATABASE;

SUPPLEME SUP SUP
-------- --- ---
IMPLICIT YES YES

From the Oracle Database Reference 10g Release 2 pg 6-54 (PDF page 670):
“SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8) Ensures that LogMiner (and any products building on LogMiner technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables:

•  NO – None of the database-wide supplemental logging directives are enabled 
• IMPLICIT – Minimal supplemental logging is enabled because all or a combination of primary key, unique key, and foreign key supplemental logging is enabled
• YES – Minimal supplemental logging is enabled through an ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement

See Also: Oracle Database SQL Reference for additional information about the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement

SUPPLEMENTAL_LOG_DATA_PK
VARCHAR2(3) For all tables with a primary key, indicates whether all columns of the primary key are placed into the redo log whenever an update is performed (YES) or not (NO)
See Also: Oracle Database SQL Reference for more information about the ALTER DATABASE ADD SUPPLEMENTAL LOG supplemental_id_key_clause statement

SUPPLEMENTAL_LOG_DATA_UI
VARCHAR2(3) For all tables with a unique key, indicates whether all other columns belonging to the unique key are placed into the redo log if any of the unique key columns are modified (YES) or not (NO) See Also: Oracle Database SQL Reference for more information about the
ALTER DATABASE ADD SUPPLEMENTAL LOG supplemental_id_key_clause statement”

Select From or Update a Database Table Based on the Contents of an Excel Spreadsheet

January 12, 2010

Let’s say that there is an Excel spreadsheet containing a list of customer order IDs in column A, and you would like to query an Oracle database using the value in column A, and then display a message on the screen showing the results of the query.  The following macro code will do just that:

Sub CheckSpreadsheet() 
    Dim dbMyDB As New ADODB.Connection 
    Dim snpData As New ADODB.Recordset 
    Dim intLastRowChecked 
    Dim intFoundFirstBlank 
    Dim intResult As Integer 
    Dim intColumn 
    Dim strColumn 
    Dim strFilename 
    Dim strWorkbookname 
    Dim strSheet 
    Dim strExcelValue 
    Dim strSQL 
    Dim strMessage
    'You must create a reference to Microsoft ActiveX Data Objects (Tools menu)
    'Make sure that we don't crash - will look ugly if our macro crashes
    On Error Resume Next
    'Replace MyODBCConnection with an ODBC connection name, MyUserName with a database user name and MyPassword with the user's password 
    dbMyDB.ConnectionString = "Data Source=MyODBCConnection;User ID=MyUserName;Password=MyPassword;"
    dbMyDB.ConnectionTimeout = 40 
    dbMyDB.CursorLocation = adUseClient 
    dbMyDB.Open
    strWorkbookname = Right(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) -InStrRev(ActiveWorkbook.FullName, "\")) 
    strSheet = ActiveSheet.Name
    intLastRowChecked = 1 'Set to skip the first row 
    intColumn = 65  'Column A 
    strColumn = Chr(intColumn)
    Do While intFoundFirstBlank = False 
        intLastRowChecked = intLastRowChecked + 1
        'Read the value from the spreadsheet 
        strExcelValue = Format(Workbooks(strWorkbookname).Worksheets(strSheet).Range(strColumn & Format(intLastRowChecked)).Value)
        If strExcelValue = "" Then 
            intFoundFirstBlank = True 
        Else 
            'Could perform an INSERT statement rather than a SELECT statement 
            strSQL = "SELECT" & vbCrLf 
            strSQL = strSQL & "  LINE_NO," & vbCrLf 
            strSQL = strSQL & "  PART_ID," & vbCrLf 
            strSQL = strSQL & "  ORDER_QTY," & vbCrLf 
            strSQL = strSQL & "  DESIRED_SHIP_DATE" & vbCrLf 
            strSQL = strSQL & "FROM" & vbCrLf 
            strSQL = strSQL & "  CUST_ORDER_LINE" & vbCrLf 
            strSQL = strSQL & "WHERE" & vbCrLf 
            strSQL = strSQL & "  CUST_ORDER_ID='" & strExcelValue & "'" & vbCrLf 
            strSQL = strSQL & "ORDER BY" & vbCrLf 
            strSQL = strSQL & "  DESIRED_SHIP_DATE"
            snpData.Open strSQL, dbMyDB
            Do While Not snpData.EOF 
                strMessage = strExcelValue & "/" & Format(snpData("line_no")) & " " & Format(snpData("desired_ship_date"), "m/d/yyyy") & _
                             " " & snpData("part_id") & " Qty " & Format(snpData("order_qty")) 
                MsgBox strMessage
                snpData.MoveNext 
            Loop 
            snpData.Close 
        End If 
    Loop
    Set snpData = Nothing 
    dbMyDB.Close 
    Set dbMyDB = Nothing 
End Sub

Note that there are a couple minor issues with the above script:

• The script runs until it finds a blank cell in column A, rather than using an Excel feature to identify the bounds of the range.
• The script requires an ODBC (32 bit) to be created on the computer.  Search the other articles on this blog to see how to establish a connection to the database without creating an ODBC connection.
• The script does not use bind variables.  Search the other articles on this blog to see how to implement bind variables in an Excel macro.

By changing the script slightly, the SELECT statement could be modified to be an UPDATE statement, allowing an easy method to update the database based on data contained in the Excel spreadsheet.