Q
Problem solve Get help with specific problems with your technologies, process and projects.

Finding hidden parameters in Oracle9i

How can I get a list of hidden Oracle parameters in Oracle9i?
Every version of Oracle has varying numbers of undocumented initialization parameters, in addition to the documented ones. These undocumented initialization parameters are usually only used in emergencies and only under the direction of a senior DBA or Oracle support. Source 2.5 shows a script for getting the undocumented initialization parameters out of a 7.3, 8.0, 8.1 or 9.x instance. The undocumented parameters for an Oracle9i (9.0.1) database are shown in the appendix.

<        undoc.sql

-**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail info@rampant.cc
--
-- *********************************************

COLUMN parameter           FORMAT a37

COLUMN description         FORMAT a30 WORD_WRAPPED

COLUMN "Session Value"     FORMAT a10

COLUMN "Instance Value"    FORMAT a10

SET LINES 100

SET PAGES 0

SPOOL undoc.lis


SELECT

   a.ksppinm  "Parameter",

   a.ksppdesc "Description",

   b.ksppstvl "Session Value",

   c.ksppstvl "Instance Value"

FROM

   x$ksppi a,

   x$ksppcv b,

   x$ksppsv c

WHERE

   a.indx = b.indx

   AND

   a.indx = c.indx

   AND

   a.ksppinm LIKE '/_%' escape '/'

/


SPOOL OFF

SET LINES 80 PAGES 20

CLEAR COLUMNS

Note that each undocumented parameter in the table begins with an underscore (_) character. Some of you may notice that a few of these "undocumented" parameters used to be documented. Some, such as _offline_rollback_segments, may be familiar, while others will never be seen or used. Just be aware that there are more parameters than those listed in a user's manual, and that Oracle support may be needed if you see one (for example, _corrupted_rollback_segments, that may just be helpful in a sticky situation).

This was last published in June 2004

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close