SAP Note 1171650

1171650 – Automated Oracle DB parameter check

Sometimes it’s necessary to do some maintenance in old databases. And in this particular case I needed to do an Oracle Database Parameter Check from SAP Note 1171650.

Database Version: Oracle 10.2

Script: parameter_check_102.txt for Oracle release 10.2

I tried to execute it from ST04N transaction and I got the following message:


“Old versions of the SQL editor in the DBA Cockpit or ST04N and old versions of the report RSORADJV might have execution problems and might report Oracle syntax errors or – particularly in the case of Basis”

/wp-content/uploads/2016/03/report_897652.jpg

So I realized that I needed to execute this on SQLPLUS. This is known for bad formatting but I was confident because the same note had the following remark:

To ensure an acceptable output with SQLPLUS, execute the following SQLPLUS commands in advance:


set linesize 360

set pagesize 1000

column name format a40

column set format a8

column remark format a60

column recommendation format a120

column is_value format a50

column should_be_value format a50


Well this caught me off-guard because the formatting with this configuration isn’t great at all and the data volume was huge… So I needed something more configurable, so I could see (without getting blind or anything like it) the database parameter’s and their respective values!

Here’s an example exported to Microsoft Excel of the data output from the SAP Note 1171650 “acceptable output”.

/wp-content/uploads/2016/03/report2_897702.jpg

Several problems with this kind of output, but the biggest one is time consuming work to get the right value in the right column (328 Rows in my database).

After searching for a while I found a pretty good solution which solved these kind of problems. You just need to configure a bit more the parameters in SQL PLUS, here is what I did:

SET COLSEP ‘;’ /* column separator */

SET ECHO OFF

SET FEEDBACK OFF

SET TERMOUT OFF

SET PAGESIZE 32766

SET LINESIZE 32766

SET VERIFY OFF

SET TERM OFF

SET TRIMS ON

SET TRIMSPOOL ON

SET UNDERLINE OFF

column name format a40

column set format a8

column remark format a60

column recommendation format a120

column is_value format a50

column should_be_value format a50

SPOOL ./dba_users.csv               /* destination file name */

@/<path to your SQL Script>/parameter_check_102.sql

SPOOL OFF

With this kind of setup you can import the CSV file and define the column separator you want. For my case was “;” and import it to the Excel sheet.

Works like a charm!

Hope this procedure can help someone.

Take care!

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply