oracle查看parameter,查看参数（parameter）的字典与数据库字符集

在日常管理数据库当中，离不开数据库字典，包括数据库的参数字典以及数据字典，

通常使用show parameter 或者select方式查看相关的字典。总的来说，使用show parameter

方式查看更多，因为使用方便。但两者之间是查看不同的内容，parameter展示的是数据库里

的参数，而select查看的是相关数据字典的视图，展示更详细更多的内容。从以下的例子可以很直观的体现到。

---数据库的各种参数还是通过select方式查看的：

--查看v$parameter字典的视图结构：

sys@PROD>desc v$parameter

Name                                      Null?    Type

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

NUM                                                NUMBER

NAME                                               VARCHAR2(80)

TYPE                                               NUMBER

VALUE                                              VARCHAR2(4000)

DISPLAY_VALUE                                      VARCHAR2(4000)

ISDEFAULT                                          VARCHAR2(9)

ISSES_MODIFIABLE                                   VARCHAR2(5)

ISSYS_MODIFIABLE                                   VARCHAR2(9)

ISINSTANCE_MODIFIABLE                              VARCHAR2(5)

ISMODIFIED                                         VARCHAR2(10)

ISADJUSTED                                         VARCHAR2(5)

ISDEPRECATED                                       VARCHAR2(5)

ISBASIC                                            VARCHAR2(5)

DESCRIPTION                                        VARCHAR2(255)

UPDATE_COMMENT                                     VARCHAR2(255)

HASH                                               NUMBER

---查看参数字典里的参数：

sys@PROD>Col name for a35;

sys@PROD>select name,type,value from v$parameter

2  order by name;

NAME                                      TYPE VALUE

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

O7_DICTIONARY_ACCESSIBILITY                  1 FALSE

active_instance_count                        3

aq_tm_processes                              3 1

archive_lag_target                           3 0

asm_diskgroups                               2

asm_diskstring                               2

asm_power_limit                              3 1

asm_preferred_read_failure_groups            2

audit_file_dest                              2 /u01/app/oracle/admin/PROD/adump

... ...

NAME                                      TYPE VALUE

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

undo_management                              2 AUTO

undo_retention                               3 1200

undo_tablespace                              2 UNDOTBS1

use_indirect_data_buffers                    1 FALSE

use_large_pages                              2 TRUE

user_dump_dest                               2 /u01/app/oracle/diag/rdbms/prod/PROD/trace

utl_file_dir                                 2 /home/oracle/logmnr

workarea_size_policy                         2 AUTO

xml_db_events                                2 enable

352 rows selected.

#从这里可以看到，获取数据库里有哪些主要的参数，都要通过select方式查看。

---查看数据库的字符集：

--通过dual伪表查看数据库的字符集：

sys@PROD>select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.AL32UTF8

--通过v$nls_parameters字典查看系统参数：

sys@PROD>col value for a30

sys@PROD>col parameter for a30

sys@PROD>select * from v$nls_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_CHARACTERSET               AL32UTF8

NLS_SORT                       BINARY

NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE

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

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_NCHAR_CHARACTERSET         AL16UTF16

NLS_COMP                       BINARY

NLS_LENGTH_SEMANTICS           BYTE

NLS_NCHAR_CONV_EXCP            FALSE

19 rows selected.

--直接查看字符集参数：

sys@PROD>select * from v$nls_parameters

2  where parameter ='NLS_CHARACTERSET';

PARAMETER                      VALUE

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

NLS_CHARACTERSET               AL32UTF8

#查看字符集，总的来说，通过两种方式查看。