Show parameter can also display implicit Parameters

The database initialization parameters used for Oracle Database startup are usually obtained using the show parameter command in sqlplus. However, only some public parameters and modified hidden parameters are shown here. Oracle also has a number of implicit parameters that are useful when debugging or implementing some functions.

On the Internet, you can easily find out how to query these implicit parameters. However, what if I want to continue to use the show parameter Method to display implicit parameters? How can this be achieved?

Here I will introduce a method to achieve this goal. The purpose of this method is not only to show hidden parameters, but also to analyze the implementation process of show parameter. You can refer to this method when you need to diagnose database problems.

First, we will analyze how the show parameter command displays the initialization parameter values.

Log on to sqlplus/as sysdba

Use alter session set SQL _trace = true; To trace the execution process of show parameter.

SQL> alter session set SQL _trace = true;

Session altered.

SQL> show parameter "_ pga_max_size"

SQL> host;

Bash-3.2 $ more/u01/oracle/app/oracle/admin/htzq/udump/htzq2_ora_19678.trc

/U01/oracle/app/oracle/admin/htzq/udump/htzq2_ora_19678.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

And Real Application Testing options

ORACLE_HOME =/u01/oracle/app/oracle/product/10.2.0/db

System name: Linux

Node name: webdg2

Release: 2.6.18-92. el5

Version: #1 SMP Tue Apr 29 13:16:15 EDT 2008

Machine: x86_64

Instance name: htzq2

Redo thread mounted by this instance: 2

Oracle process number: 30

Unix process pid: 19678, image: oracle @ webdg2 (TNS V1-V3)

==================================
Www.bkjia.com
==================================

Parsing in cursor #2 len = 290 dep = 0 uid = 0 oct = 3 lid = 0 tim = 1307409201601477 hv = 1905048613 ad = 'c9e0000d8'

Select name NAME_COL_PLUS_SHOW_PARAM, DECODE (TYPE, 1, 'boolean', 2, 'string', 3, 'integer', 4, 'file', 5, 'number', 6, 'Big integer', 'un

Known ') TYPE, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM from v $ parameter where upper (NAME) like upper (' % _ pga_max_size % ') order by name _

COL_PLUS_SHOW_PARAM, ROWNUM

END OF STMT

PARSE #2: c = 4999, e = 4042, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 0, og = 1, tim = 1307409201601471

EXEC #2: c = 0, e = 63, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, tim = 1307409201601611

FETCH #2: c = 4999, e = 5583, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, tim = 1307409201607254

Bash-3.2 $ exit

Exit

The result shows that show parameter queries the V $ PARAMETER view.

Next, we will analyze the definition of the V $ PARAMETER view.

SQL> SELECT * FROM v $ fixed_view_definition WHERE view_name = 'v $ PARAMETER ';

VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
V $ PARAMETER
Select NUM, NAME, TYPE, VALUE, DISPLAY_VALUE, ISDEFAULT, ISSES_MODIFIABLE
, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE, ISMODIFIED, ISADJUSTED, ISDEPRECAT
ED, DESCRIPTION, UPDATE_COMMENT, HASH from GV $ PARAMETER where inst_id = USERENV
('Instance ')

SQL> r

1 * SELECT * FROM v $ fixed_view_definition WHERE view_name = 'gv $ PARAMETER'

VIEW_NAME

------------------------------

VIEW_DEFINITION

--------------------------------------------------------------------------------

GV $ PARAMETER

Select x. inst_id, x. indx + 1, ksppinm, ksppity, ksppstvl, ksppstdvl, ksppstdf, decode

(Bitand (ksppiflg/256, 1), 1, 'true', 'false'), decode (bitand (ksppiflg/65536,3), 1, 'I

MMEDIATE ', 2, 'referred', 3, 'immediate', 'false '),

Decode (bitand (ksppiflg, 4), 4, 'false', decod

E (bitand (ksppiflg/65536,3), 0, 'false', 'true'), decode (bitand (ksppstvf, 7 ),

1, 'modified', 4, 'System _ mod', 'false'), decode (bitand (ksppstvf, 2), 2, 'true', 'false'

'), Decode (bitand (ksppilrmflg/64, 1), 1, 'true', 'false'), ksppdesc, ksppstcen

T, ksppihash from x $ ksppi x, x $ ksppcv y where (x. indx = y. indx) and (translat

E (ksppinm, '_', '#') not like '# %') and (translate (ksppinm, '_', '#') not like

'# %') Or (ksppstdf = 'false') or (bitand (ksppstvf, 5)> 0 )))

The definition of V $ PARAMETER mainly comes from GV $ PARAMETER, both of which are synonyms and come from v _ $ parameter and gv _ $ parameter.

The conditions of the most important part of this definition Statement (translate (ksppinm, '_', '#') not like '# %. It filters out the implicit parameter starting. Because the condition ksppstdf = 'false' is added, this ensures that if you have manually modified the implicit parameter, the modified implicit parameter is displayed when show parameter is used.

Finally, recreate the gv $ parameter view.

Create or replace view gv _ $ parameter (INST_ID, NUM, NAME, TYPE, VALUE, DISPLAY_VALUE, ISDEFAULT, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, writable, ISMODIFIED, ISADJUSTED, ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH)
Select x. inst_id,
X. indx + 1,
Ksppinm,
Ksppity,
Ksppstvl,
Ksppstdvl,
Ksppstdf,
Decode (bitand (ksppiflg/256, 1), 1, 'true', 'false '),
Decode (bitand (ksppiflg/65536, 3 ),
1,
'Immediate ',
2,
'Referred ',
3,
'Immediate ',
'False '),
Decode (bitand (ksppiflg, 4 ),
4,
'False ',
Decode (bitand (ksppiflg/65536, 3), 0, 'false', 'true ')),
Decode (bitand (ksppstvf, 7), 1, 'modified', 4, 'System _ mod', 'false '),
Decode (bitand (ksppstvf, 2), 2, 'true', 'false '),
Decode (bitand (ksppilrmflg/64, 1), 1, 'true', 'false '),
Ksppdesc,
Ksppstcmnt,
Ksppihash
From sys. x $ ksppi x, sys. x $ ksppcv y
Where (x. indx = y. indx) and (translate (ksppinm, '_', '#') not like '##% ');

Have a question:

When show parameter is displayed, It is the synonym gv $ parameter. This synonym cannot be deleted and rebuilt.

SQL> create synonym GV $ PARAMETER for GV _ $ PARAMETER;
Create synonym GV $ PARAMETER for GV _ $ PARAMETER
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> drop synonym GV $ PARAMETER;
Drop synonym GV $ PARAMETER
*
ERROR at line 1:

ORA-02030: can only select from fixed tables/views

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12