[Pratical] How to list hidden parameters

Publié le 23 décembre 2009 par LAITI Majda

For RAC environnement, I sill don't know How to see them without connecting directly to the instance ...

set lines 200 pages 500

col value for a10
col isdefault for a10
col name for a40
col DESCRIPTION for a60
col SESMOD for a10
col SYSMOD for a10

SELECT
       X.INST_ID,
       X.KSPPINM      NAME,
        ksppstvl AS     VALUE,
        ksppstdf AS    isdefault,
       DECODE(BITAND(KSPPIFLG/256, 1), 1, 'TRUE', 'FALSE') SESMOD,
       DECODE( BITAND(KSPPIFLG/65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE' ) SYSMOD,
       KSPPDESC DESCRIPTION
FROM
        SYS.X$KSPPI   X,
         SYS.x$ksppsv Y
WHERE
          x.indx = y.indx
and X.KSPPINM in( '_optim_peek_user_binds', '_gby_hash_aggregation_enabled','_b_tree_bitmap_plans')
;

   INST_ID NAME                                     VALUE      ISDEFAULT SESMOD     SYSMOD     DESCRIPTION
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ----------------------------------------
         1 _b_tree_bitmap_plans                     FALSE      FALSE      TRUE       IMMEDIATE enable the use of bitmap plans for table
                                                                                                s w. only B-tree indexes

         1 _optim_peek_user_binds                   FALSE      FALSE      TRUE       IMMEDIATE enable peeking of user binds
         1 _gby_hash_aggregation_enabled            FALSE      TRUE       TRUE       IMMEDIATE enable group-by and aggregation using ha
                                                                                                sh scheme