DB version: 11.2.0.4
OS : Oracle Linux 6.5
1.Are all hidden parameters static ? ie. The DB needs to be bounced ?
2. Why can't this user query info on Hidden parameters ?
In my Shop, Application DBAs do not have SYS access (no OS level access for OS user oracle). Instead , they have a DB level user created and granted
SELECT ANY DICTIONARY and SELECT ANY TABLE privileges (through roles) as shown below. But, when application DBAs query internal tables to get info on Hidden parameters, they get ORA-00942: table or view does not exist error as shown below. What privilege is required to query internal tables like X$K tables like x$ksppi, x$ksppcv, etc.. ?
SQL> SHOW USER
USER is "APP_DBA347"
SQL>
SQL> select granted_role, username from user_role_privs;
GRANTED_ROLE USERNAME
------------------------------ ------------------------------
BAU_MONITORING APP_DBA347
BAU_TA APP_DBA347
CONNECT APP_DBA347
SQL>
SQL>
SQL> --- Object privileges for BAU_MONITORING and BAU_TA roles
SQL> select privilege, table_name from dba_tab_privs where grantee= 'BAU_MONITORING';
PRIVILEGE TABLE_NAME
---------------------------------------- ------------------------------
SELECT DBA_HIST_DATABASE_INSTANCE
SELECT DBA_HIST_SNAPSHOT
EXECUTE DBMS_WORKLOAD_REPOSITORY
SQL> Select privilege, table_name from dba_tab_privs where grantee = 'BAU_TA';
PRIVILEGE TABLE_NAME
---------------------------------------- ------------------------------
EXECUTE DBMS_WORKLOAD_REPOSITORY
SELECT DB_ALERTEXT
SQL> --- System privileges for BAU_MONITORING and BAU_TA roles
SQL> Select privilege from dba_sys_privs where grantee= 'BAU_MONITORING';
PRIVILEGE
----------------------------------------
ADVISOR
ALTER ANY INDEX
CREATE ANY INDEX
SELECT ANY DICTIONARY
ALTER SESSION
SELECT ANY TABLE
CREATE SESSION
7 rows selected.
SQL> Select privilege from dba_sys_privs where grantee= 'BAU_TA';
PRIVILEGE
----------------------------------------
ALTER SESSION
SELECT ANY TABLE
SELECT ANY DICTIONARY
SQL>
SQL> SHOW USER
USER is "APP_DBA347"
SQL>
SQL>
SQL>
SQL>
SQL> --- Trying to retrieve info for a hidden parameter
SQL> select
2 ksppinm parm_name,
3 session_tab.ksppstvl current_value,
4 instance_tab.ksppstvl default_value
5 from
6 x$ksppi parm_tab,
7 x$ksppcv session_tab,
8 x$ksppsv instance_tab
9 where
10 parm_tab.indx = session_tab.indx
11 and
12 session_tab.indx = instance_tab.indx
and
13 14 upper(ksppinm) like '%OPTIM_PEEK_USER_BINDS%';
x$ksppsv instance_tab
*
ERROR at line 8:
ORA-00942: table or view does not exist