February 23, 2010
The are a large number of initialization parameters that configure the behavior of an Oracle database instance, and it seems that the number of hidden parameters (those parameters that begin with _ ) continues to grow with each new Oracle Database release. In some cases, parameters that were once normal parameters became hidden parameters in later releases (the SPIN_COUNT, and _SPIN_COUNT parameters are one example). In other cases, the normal parameter defines the minimum requested parameter value and the hidden parameter defines the current value (DB_CACHE_SIZE and __DB_CACHE_SIZE parameters, respectively).
For documentation purposes it might be helpful to permanently record the values of the normal and hidden initialization parameters, and that is the purpose of the VBS script in this article. The script uses a SQL statement that was originally found here, and then was modified so that the normal and hidden versions of the parameters will sort into adjacent rows when dislayed on screen. Once the parameter values are written to Excel, the Excel worksheet is automatically saved with the value of the DB_NAME initialization parameter and the current date and time. When targeting an 11.2.0.1 database, the output might look like the following screen capture:
Unfortunately, we have a slight problem. The SQL statement in the script must be executed as the SYS user, and if the O7_DICTIONARY_ACCESSIBILITY initialization parameter is set to FALSE (the default starting with Oracle 9.0.1), the normal connection string used in previous scripts will not work (I have not found a way to pass AS SYSDBA when Provider=OraOLEDB.Oracle is specified in the connection string). If you do not want to set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to TRUE, you will need to create an ODBC connection to the database.
To create an ODBC connection, launch the ODBC Data Source Administrator utility from the Control Panel, and switch to the System DSN tab. Click Add… then select one of the ODBC providers offered by Oracle Corp (you may need to perform a custom install of the Oracle Client for Oracle’s ODBC client to appear in the list – note that there is a separate 32 bit and 64 bit ODBC Administrator on 64 bit Windows, with the 32 bit version located in the Windows\SysWow64 folder, see this article for more information):
Pick a generic name for the ODBC connection, such as MyODBC (this is the name used in the script) and then enter the database name from the Tnsnames.ora file into the TNS Service Name box (I specified or112) – note that you can change this database name at a later time to point the script at a different database. Click OK.
The VBS script to extract the normal and hidden parameters follows (modify the script to specify the correct password for the SYS user):
'Version 1.0
Const adCmdText = 1
Const adCmdStoredProc = 4
Const adParamInput = 1
Const adVarNumeric = 139
Const adBigInt = 20
Const adDecimal = 14
Const adDouble = 5
Const adInteger = 3
Const adLongVarBinary = 205
Const adNumeric = 131
Const adSingle = 4
Const adSmallInt = 2
Const adTinyInt = 16
Const adUnsignedBigInt = 21
Const adUnsignedInt = 19
Const adUnsignedSmallInt = 18
Const adUnsignedTinyInt = 17
Const adDate = 7
Const adDBDate = 133
Const adDBTimeStamp = 135
Const adDBTime = 134
Const adVarChar = 200
Const adUseClient = 3
Dim strUsername
Dim strPassword
Dim strDatabase
Dim strSQL 'SQL statement
Dim objExcel 'For sending the output to Excel
Dim snpData 'ADO Recordset object used to retrieve the user's data
Dim dbDatabase 'ADO database connection object
Dim intTempCount 'Counter of the number of Excel sheets that have been created
Dim strDBNAME 'DB_NAME parameter from the database parameters
Dim strLastColumn 'Column identifier in Excel of the right-most column
Dim i 'Counter
On Error Resume Next
Set snpData = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")
'Create an Excel connection
Set objExcel = CreateObject("Excel.Application")
'Set up to allow exporting, if requested
objExcel.DisplayAlerts = False
objExcel.Workbooks.Add
strUsername = "sys" 'Must connect as the SYS user
strPassword = "SysPassword"
strDatabase = "MyODBC" 'Must use an ODBC connection if O7_DICTIONARY_ACCESSIBILITY = FALSE
If UCase(strUsername) <> "SYS" Then
'Can use this for SYS if O7_DICTIONARY_ACCESSIBILITY = TRUE, ODBC connection then not required
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
Else
'Must use an ODBC connection if O7_DICTIONARY_ACCESSIBILITY = FALSE
dbDatabase.ConnectionString = "Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & " AS SYSDBA;"
End If
dbDatabase.Open
'Should verify that the connection attempt was successful, but I will leave that for someone else to code
If Err <> 0 Then
MsgBox "Not Connected, Error: " & Err
Else
'Adapted from the SQL statement at http://www.jlcomp.demon.co.uk/params.html
strSQL = "SELECT " & vbCrLf
strSQL = strSQL & " UPPER(NAM.KSPPINM) NAME," & vbCrLf
strSQL = strSQL & " VAL.KSPPSTVL VALUE," & vbCrLf
strSQL = strSQL & " NAM.INDX+1 NUM," & vbCrLf
strSQL = strSQL & " NAM.KSPPITY TYPE," & vbCrLf
strSQL = strSQL & " VAL.KSPPSTDF ISDEFAULT," & vbCrLf
strSQL = strSQL & " DECODE(BITAND(NAM.KSPPIFLG/256,1),1,'TRUE','FALSE') ISSES_MODIFIABLE," & vbCrLf
strSQL = strSQL & " DECODE(BITAND(NAM.KSPPIFLG/65536,3)," & vbCrLf
strSQL = strSQL & " 1,'IMMEDIATE'," & vbCrLf
strSQL = strSQL & " 2,'DEFERRED' ," & vbCrLf
strSQL = strSQL & " 3,'IMMEDIATE'," & vbCrLf
strSQL = strSQL & " 'FALSE') ISSYS_MODIFIABLE," & vbCrLf
strSQL = strSQL & " DECODE(BITAND(VAL.KSPPSTVF,7)," & vbCrLf
strSQL = strSQL & " 1,'MODIFIED'," & vbCrLf
strSQL = strSQL & " 4,'SYSTEM MODIFIED'," & vbCrLf
strSQL = strSQL & " 'FALSE') ISMODIFIED," & vbCrLf
strSQL = strSQL & " DECODE(BITAND(VAL.KSPPSTVF,2),2,'TRUE', 'FALSE') ISADJUSTED," & vbCrLf
strSQL = strSQL & " NAM.KSPPDESC DESCRIPTION" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " X$KSPPI NAM," & vbCrLf
strSQL = strSQL & " X$KSPPSV VAL" & vbCrLf
strSQL = strSQL & "WHERE " & vbCrLf
strSQL = strSQL & " NAM.INDX = VAL.INDX " & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " UPPER(DECODE(SUBSTR(NAM.KSPPINM,1,2),'__',SUBSTR(NAM.KSPPINM,3)," & vbCrLf
strSQL = strSQL & " DECODE(SUBSTR(NAM.KSPPINM,1,1),'_',SUBSTR(NAM.KSPPINM,2),NAM.KSPPINM)))," & vbCrLf
strSQL = strSQL & " UPPER(NAM.KSPPINM)"
snpData.Open strSQL, dbDatabase
If snpData.State = 1 Then
If Not (snpData.EOF) Then
With objExcel
.Visible = True
.ActiveWorkbook.Sheets.Add
.ActiveSheet.Name = "DB Parameters"
For i = 0 To snpData.Fields.Count - 1
.ActiveSheet.Cells(1, i + 1).Value = snpData.Fields(i).Name
Next
.ActiveSheet.Range(.ActiveSheet.Cells(1, 1), .ActiveSheet.Cells(1, snpData.Fields.Count)).Font.Bold = True
'Format the columns in the spreadsheet
For i = 0 To snpData.Fields.Count - 1
strLastColumn = Chr(64 + ((i + 1) Mod 26))
.Columns(strLastColumn).Select
Select Case snpData.Fields(i).Type
Case adDate, adDBDate, adDBTimeStamp, adDBTime
.Selection.HorizontalAlignment = -4152
.Selection.NumberFormat = "mm/dd/yy hh:nn AM/PM"
Case adBigInt, adInteger, adSmallInt, adTinyInt, adUnsignedBigInt, adUnsignedInt, adUnsignedSmallInt, adUnsignedTinyInt
.Selection.HorizontalAlignment = -4152
.Selection.NumberFormat = "0"
Case adVarNumeric, adDecimal, adDouble, adNumeric, adSingle
.Selection.HorizontalAlignment = -4152
.Selection.NumberFormat = "0"
Case adVarChar
.Selection.HorizontalAlignment = -4131
.Selection.NumberFormat = "@"
End Select
Next
strLastColumn = Chr(64 + ((snpData.Fields.Count + 1) Mod 26))
.ActiveSheet.Range("A2").CopyFromRecordset snpData
'Auto-fit up to columns
.ActiveSheet.Columns("A:" & strLastColumn).AutoFit
.ActiveSheet.Range("B2").Select
.ActiveWindow.FreezePanes = True
.Application.DisplayAlerts = False
'Remove the default worksheets
For i = 1 To ActiveWorkbook.Sheets.Count
If .ActiveWorkbook.Sheets(i).Name = "Sheet1" Then
.Sheets("Sheet1").Select
.ActiveWindow.SelectedSheets.Delete
End If
If .ActiveWorkbook.Sheets(i).Name = "Sheet2" Then
.Sheets("Sheet2").Select
.ActiveWindow.SelectedSheets.Delete
End If
If .ActiveWorkbook.Sheets(i).Name = "Sheet3" Then
.Sheets("Sheet3").Select
.ActiveWindow.SelectedSheets.Delete
End If
Next
End With
snpData.Close
'Repeat the SQL statement to find the value of the DB_NAME parameter
snpData.Open strSQL, dbDatabase
Do While Not (snpData.EOF)
If UCase(snpData("name")) = "DB_NAME" Then
strDBNAME = snpData("value")
Exit Do
End If
snpData.MoveNext
Loop
Else
MsgBox "No Rows Returned"
End If
Else
MsgBox "Could Not Open the SQL Statement " & Err
End If
snpData.Close
objExcel.ActiveWorkbook.SaveAs "C:\OracleParameters " & strDBNAME & " " & Replace(Replace(Now, "/", "-"), ":", "-") & ".xls"
End If
'Clean Up
Set objExcel = Nothing
Set snpData = Nothing
dbDatabase.Close
Set dbDatabase = Nothing
When the script runs it will create a spreadsheet in the root of the C:\ drive – move the spreadsheet as needed for documentation purposes.
Charles Hooper's Oracle Notes 
Leave a comment