檢視引數（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

#檢視字符集，總的來說，透過兩種方式檢視。