Show parameter & Table Not exists
问题描述
在尝试通过show parameter来查看一个参数的时候，遇到了ora-00942: table or view does not exist的错误提示，如下，
SQL> show user
USER is "SCOTT"
SQL> show parameter nls_sort;
ORA-00942: table or view does not exist
问题分析
乍看到这个错误，很是惊愕了一下，因为平时几乎没碰到这个问题。但是错误提示给出的信息意思很明显，show parameter的时候访问了某个不存在或者没权限访问的表或者视图。那么是哪个表或者视图呢? 因为还记得访问参数的值，可以直接访问动态新能视图v$parameter, 因此尝试访问了一下，结果也报出了同样的错误提示，
SQL> select value from v$parameter where name='nls_sort';
select value from v$parameter where name='nls_sort'
*
ERROR at line 1:
ORA-00942: table or view does not exist
其实至此，上面的问题已经显而易见了，show parameter命令应该就是访问v$parameter。因为当前user scott没有权限访问这个视图，因此会报出table or view does not exist的错误信息。解决问题的方法也自然简单，给scott赋予相应的权限即可，
SQL> show user
USER is "SYS"
SQL> grant select on v_$parameter to scott;
Grant succeeded.
现在重新尝试一下，发现问题没有了....
SQL> show user
USER is "SCOTT"
SQL> show parameter nls_sort;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
SQL> select value from v$parameter where name='nls_sort';
VALUE
-------------------------------------------------------------------------------
扩展
sql*plus提供了很多SHOW命令来方便对一些设置的查看，比如show sga，这些应该也是对相应的视图的访问。关于sqlplus的SHOW命令，可以参见官方文档
2。 关于parameter 视图
Oracle 提供了几个parameter视图，如下
v$parameter / v$parameter2
v$system_parameter / v$system_parameter2
v$spparameter
其中v$parameter(2)反映的是当前session中各个参数的值，v$system_parameter(2)则是instance级别的参数的值，v$spparameter则是spfile中存储的参数的值。
v$parameter和v$parameter2的区别在于，对于那些值为多个的参数， v$parameter2会对将每个值都显示成独立的一行，而v$parameter则会将所有的值都显示在一行中。 v$system_parameter和v$system_parameter2类似。
select * from v$parameter where name='control_files'
select * from v$parameter2 where name='control_files'
新的疑问
注意到通过alter session命令更改了参数nls_sort之后，通过show parameter或者查询v$parameter却查询不到变化，如下所示...
SQL> show user
USER is "SCOTT"
SQL> show parameter nls_sort
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
SQL> alter session set nls_sort=binary_ci;
Session altered.
SQL> show parameter nls_sort
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
SQL> select value from v$parameter where name='nls_sort';
VALUE
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
这个岂不怪哉，难倒对nls_sort参数的改动没有任何效果，但是经过测试发现，当前session的行为确实变成了大小写不敏感了。那么为什么这个参数值的改变没有体现在v$parameter这个视图中间呢？
无意中发现有个视图叫v$nls_parameters (注意这个视图的名字是复数形式），那么查询这个这个视图看看怎么个情况...
SQL> show user
USER is "SCOTT"
SQL> select * from v$nls_parameters where parameter='nls_sort';
PARAMETER VALUE
---------------------------------------------------------------- ----------------
NLS_SORT BINARY_CI
SQL>
可以看到这个参数确实是起作用的，因为我查询v$nls_parameter的时候，用的是'nls_sort' 而不是'NLS_SORT'来做查询限制条件！那么由此可以推测出通过alter session来更改NLS相关的参数的时候，改变是通过v$nls_parameters这个动态视图体现出来的。
另外还注意到几个静态视图跟NLS相关，包括NLS_DATABASE_PARAMETERS, NLS_INSTANCE_PARAMETERS, NLS_SESSION_PARAMETERS. 根据视图的名字不难推断出这几个视图分别显示的是什么内容，NLS_SESSION_PARAMETERS 和 v$nls_parameters应该反映的是同样的信息，都是当前session的NLS参数的设置情况。
可以通过查看查询计划的方式来看一下这些视图究竟是建立在哪些表上的，如下...
(1) NLS_DATABASE_PARAMETERS
SQL> set autotrace traceonly explain
SQL> select * from nls_database_parameters;
Execution Plan
----------------------------------------------------------
Plan hash value: 415205717
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PROPS$ | 1 | 28 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME" LIKE 'NLS%')
SQL>
可以看到NLS_DATABASE_PARAMETERS是建立在表数据字典表PROP$之上的，从中只取出NLS相关的参数。
（2） NLS_INSTANCE_PARAMETERS & v$parameter
SQL> select * from nls_instance_parameters;
Execution Plan
----------------------------------------------------------
Plan hash value: 1128103955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 358 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 358 | 1 (100)| 00:00:01 |
|* 2 | FIXED TABLE FULL| X$KSPPI | 1 | 68 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL| X$KSPPCV | 100 | 29000 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"."INDX"="Y"."INDX")
filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
"KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
2 - filter("KSPPINM" LIKE 'nls%' AND
"X"."INST_ID"=USERENV('INSTANCE') AND TRANSLATE("KSPPINM",'_','#') NOT
LIKE '##%')
SQL> select * from v$parameter;
Execution Plan
----------------------------------------------------------
Plan hash value: 1128103955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 926 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 926 | 1 (100)| 00:00:01 |
|* 2 | FIXED TABLE FULL| X$KSPPI | 1 | 249 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL| X$KSPPCV | 100 | 67700 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"."INDX"="Y"."INDX")
filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
"KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
2 - filter("X"."INST_ID"=USERENV('INSTANCE') AND
TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
SQL>
可以看到NLS_INSTANCE_PARAMETERS和v$parameter都建立在内部表X$KSPPI和X$KSPPCV之上。唯一的区别在于NLS_INSTANCE_PARAMETERS多了一个限制条件"KSPPINM" LIKE 'nls%'
（3） NLS_SESSION_PARAMETERS & v$nls_parameters
SQL> select * from nls_session_parameters;
Execution Plan
----------------------------------------------------------
Plan hash value: 1805486652
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$NLS_PARAMETERS | 1 | 81 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PARAMETER"<>'NLS_CHARACTERSET' AND
"PARAMETER"<>'NLS_NCHAR_CHARACTERSET' AND "PARAMETER"<>'NLS_SPECIAL_CHARS'
AND "INST_ID"=USERENV('INSTANCE'))
SQL> select * from v$nls_parameters;
Execution Plan
----------------------------------------------------------
Plan hash value: 1805486652
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$NLS_PARAMETERS | 1 | 81 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PARAMETER"<>'NLS_SPECIAL_CHARS' AND
"INST_ID"=USERENV('INSTANCE'))
SQL>
可以看到nls_session_parameters和v$nls_parameters都建立在内部表X$NLS_PARAMETERS之上，但是nls_session_parameters比v$nls_parameters少两个参数NLS_CHARACTERSET 和 LS_NCHAR_CHARACTERSET
SQL> select * from v$nls_parameters
2 minus
3 select * from nls_session_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16
因此查询nls相关的session级别的参数设置，不要查询v$parameter(nls_instance_parameters), 要查询v$nls_parameters(nls_session_parameters)
SQL> select * from nls_instance_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
SQL> select * from nls_session_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY_CI
NLS_TIME_FORMAT hh24:mi:ss
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP ANSI
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
--------------------------------------
Regards,
FangwenYu
