As with the other scripts in this series you can choose whether to send the output to the screen or to a file. If you choose a file then either the utl_file_dir parameter needs to be set correctly or a DIRECTORY object must exist pointing at the correct directory and also the user running the script must have been granted the correct privileges on the DIRECTORY.
Unlike the other scripts in this series this script needs a little extra set-up. Two new views need to be created as SYS and SELECT ACCESS granted on them to the user who will use the script. The views can be created as follows:
SQL> connect sys/change_on_install@sans as sysdba
Connected.
SQL> create or replace view x_$ksppcv as select * from x$ksppcv;
View created.
SQL> create or replace public synonym x$ksppcv for x_$ksppcv;
Synonym created.
SQL> create or replace view x_$ksppi as select * from x$ksppi;
View created.
SQL> create or replace public synonym x$ksppi for x_$ksppi;
Synonym created.
SQL>
The second view x$ksppi is not needed in some installations as its created by $ORACLE_HOME/rdbms/admin/catsnmp.sql and is used by OEM and also STATSPACK. So check first if it exists already, this will depend on what options you chose when installing the software. If the view is not there already then create the view as shown above.
Grant access to the user who needs to access it as follows:
SQL> connect sys/change_on_install@sans as sysdba
Connected.
SQL> grant select on x_$ksppcv to oscan;
Grant succeeded.
SQL> grant select on x_$ksppi to oscan;
Grant succeeded.
SQL>
Even if the x$ksppi view exists already you may still need to grant access to the user who will run this script. Check if necessary with the script who_can_access.sql if the view can already be accessed.
The script can be used to check the values of normal parameters and hidden or undocumented parameters. Let’s look at an example of a normal parameter - in this case utl_file_dir. Here we are:
check_parameter: Release 1.0.0.0.0 - Production on Fri Oct 22 17:39:02 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
PARAMETER TO CHECK [utl_file_dir]: utl_file_dir
CORRECT VALUE [null]: null
OUTPUT METHOD Screen/File [S]: s
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Investigating parameter => utl_file_dir
====================================================================
Name : utl_file_dir
Value : C:\temp
Type : STRING
Is Default : ***SPECIFIED IN INIT.ORA
Is Session modifiable : FALSE
Is System modifiable : FALSE
Is Modified : FALSE
Is Adjusted : FALSE
Description : utl_file accessible directories list
Update Comment :
-------------------------------------------------------------------------
value ***C:\temp*** is incorrect
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
SQL>
This is an interesting example. You can see that I passed in the name of the parameter and also its "safe" value (i.e. null) and then ran the script to output to the screen. The result at the end shows that the value is incorrect as a "safe" value is to set this parameter to nothing, a null string. The script also indicates that the parameter has been set in the ini.ora file. Every detail of the parameter is displayed.
The next example is to show you that the script can also be used to test hidden parameters, those that start with an underscore. Here we are checking the _trace_files_public parameter. This is used to allow the umask value used by the Oracle kernel when creating trace files to set read permissions for world.
check_parameter: Release 1.0.0.0.0 - Production on Fri Oct 22 16:26:15 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
PARAMETER TO CHECK [utl_file_dir]: _trace_files_public
CORRECT VALUE [null]: FALSE
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
Investigating parameter => _trace_files_public
====================================================================
Name : _trace_files_public
Value : FALSE
Type : BOOLEAN
Is Default : DEFAULT VALUE
Is Session modifiable : FALSE
Is System modifiable : FALSE
Is Modified : FALSE
Is Adjusted : FALSE
Description : Create publicly accessible trace files
Update Comment :
-------------------------------------------------------------------------
value is correct
PL/SQL procedure successfully completed.
For updates please visit /tools.htm
The example is pretty straight forward and shows that the current value for this parameter in my database is correct being set to FALSE.
