Find internal of "show parameter" by session tracing
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> alter session set sql_trace=true;
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL> oradebug tracefile_name
/u01/app/oracle/admin/test1/udump/test1_ora_20533.trc
SQL> oradebug CLOSE_TRACE
Statement processed.
Find internal sql statement in trace file. The major statement in trace file looks like following.
"show parameter" actually make query on view v$parameter.
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,
DECODE(TYPE,
1,
'boolean',
2,
'string',
3,
'integer',
4,
'file',
5,
'number',
6,
'big integer',
'unknown') TYPE,
DISPLAY_VALUE VALUE_CL_PLUS_SHOW_PARAM
FROM V$PARAMETER
WHERE UPPER(NAME) LIKE UPPER('%optimizer%')
ORDER BY NAME_COL_PLUS_SHOW_PARAM, ROWNUM;
Examing definition of view v$parameter
SQL> SELECT view_definition FROM v$fixed_view_definition WHERE view_name = 'V$PARAMETER';
VIEW_DEFINITION
-----------------------------------------------------
SELECT NUM,
NAME,
TYPE,
VALUE,
DISPLAY_VALUE,
ISDEFAULT,
ISSES_MODIFIABLE,
ISSYS_MODIFIABLE,
ISINSTANCE_MODIFIABLE,
ISMODIFIED,
ISADJUSTED,
ISDEPRECATED,
ISBASIC,
DESCRIPTION,
UPDATE_COMMENT,
HASH
FROM GV$PARAMETER
WHERE inst_id = USERENV ('Instance')
Examing definition of view gv$parameter
hidden init parameters start with character '_'
SQL> SELECT view_definition FROM v$fixed_view_definition WHERE view_name = 'GV$PARAMETER';
VIEW_DEFINITION
--------------------------------------------------------------------------------
SELECT x.inst_id,
x.indx + 1,
ksppinm,
ksppity,
ksppstvl,
ksppstdv l,
ksppstdf,
DECODE (BITAND (ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
DECODE (BITAND (ksppiflg / 65536, 3),
1, 'IMMEDIATE',
2, 'DEFERRED',
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 x$ksppi x, x$ksppcv y
WHERE (x.indx = y.indx)
AND ( (TRANSLATE (ksppinm, '_', '#') NOT LIKE '##%')
AND ( (TRANSLATE (ksppinm, '_', '#') NOT LIKE '#%')
OR (ksppstdf = 'FALSE')
OR (BITAND (ksppstvf, 5) > 0)))
Create pseudo-view of v$paramter
[oracle@test1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 4 16:32:50 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> show user
USER is "SYS"
CREATE OR REPLACE VIEW my_v$parameter_with_hidden
(
NUM,
NAME,
TYPE,
display_Value,
ISDEFAULT,
ISSES_MODIFIABLE,
ISSYS_MODIFIABLE,
ISMODIFIED,
ISADJUSTED,
DESCRIPTION,
UPDATE_COMMENT
)
AS
SELECT x.indx + 1,
ksppinm,
ksppity,
ksppstvl,
ksppstdf,
DECODE (BITAND (ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
DECODE (BITAND (ksppiflg / 65536, 3),
1, 'IMMEDIATE',
2, 'DEFERRED',
3, 'IMMEDIATE',
'FALSE'),
DECODE (BITAND (ksppstvf, 7),
1, 'MODIFIED',
4, 'SYSTEM_MOD',
'FALSE'),
DECODE (BITAND (ksppstvf, 2), 2, 'TRUE', 'FALSE'),
ksppdesc,
ksppstcmnt
FROM x$ksppi x, x$ksppcv y
WHERE (x.indx = y.indx)
/
View created.
SQL> grant select on my_v$parameter_with_hidden to jordan;
Grant succeeded.
SQL> conn jordan/123456
Enter password:
Connected.
SQL> create synonym v$parameter for sys.my_v$parameter_with_hidden;
Synonym created.
able to use "show parameter" to show hidden init parameters
SQL> show user
USER is "JORDAN"
SQL> create synonym v$parameter for sys.my_v$parameter_with_hidden;
Synonym created.
SQL> show parameter unnest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_distinct_view_unnesting boolean FALSE
_unnest_subquery boolean TRUE
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_db_file_optimizer_read_count integer 8
_optimizer_adjust_for_nulls boolean TRUE
_optimizer_autostats_job boolean TRUE
_optimizer_better_inlist_costing string ALL
_optimizer_block_size integer 8192
_optimizer_cache_stats boolean FALSE
_optimizer_cartesian_enabled boolean TRUE
_optimizer_cbqt_factor integer 50
_optimizer_cbqt_no_size_restriction boolean TRUE
_optimizer_ceil_cost boolean TRUE
_optimizer_choose_permutation integer 0
_optimizer_complex_pred_selectivity boolean TRUE
_optimizer_compute_index_stats boolean TRUE
_optimizer_correct_sq_selectivity boolean TRUE
_optimizer_cost_based_transformation string LINEAR
_optimizer_cost_filter_pred boolean FALSE
_optimizer_cost_hjsmj_multimatch boolean TRUE
_optimizer_cost_model string CHOOSE
