ORACLE的參數檔: 執行個體中的參數

參數檔可以透過以下VIEW查詢 執行中INSTANCE的參數(記憶體中)： V$PARAMETER 其結構如下:

DISK儲存的靜態參數(檔案為SPFILE{$SID}.ora，例如，SID為SEDB，則檔案為SPFILESEDB.ORA)： V$SPPARAMETER 其結構如下:

查詢【基本參數Basic Parameter】的方法： select name, value from v$parameter where isbasic='TRUE' order by name;

查詢SPFILE和記憶體中的基本參數(但是SPPARAMETER的欄位較少)： select s.name, s.value from v$spparameter s join v$parameter p on s.name=p.name where p.isbasic='TRUE' order by name;

欲修改靜態參數，需使用ALTER SYSTEM，並且在命令後面加上改變範圍，將改變範圍設為SPFILE，或是BOTH(預設值)，例如： alter system set log_buffer=6m scope=both; 或是 alter system set log_buffer=6m scope=SPFILE;

與SESSION有關的參數和設定

與SESSION 有關的VIEW為 NLS_SESSION_PARAMETERS 查詢所有與SESSION有關的參數 select * from nls_session_parameters

例如，欲更改幣值 ALTER SESSION set NLS_CURRENCY='GBP';

TO_DATE ORA-01843:not a valid month 錯誤處理

例如，在筆電上，執行

select to_date('25-DEC-2010') from dual;

ORA-01843: 不是有效的月份

這個問題的本質是系統不能識別英文的月簡寫，而能識別中文。

Oracle系統的語言配置主要保存在V$NLS_PARAMETERS數據字典視圖中。查詢該視圖關於語言的設置值。如下：

SQL> select * from v$nls_parameters where parameter like '%DATE%';

SQL> alter session set nls_date_language='american';

Session altered

修改後，參數的日期的語言設定更改如下:

然後，再執行

select to_date('25-DEC-2010') from dual;

結果就正常了

從資料字典中，找出某表格的INDEX資訊

select index_name, column_name, index_type, uniqueness from user_indexes natural join user_ind_columns where table_name='CUSTOMERS';

保險理賠的理與賠

【中視新聞】 "笑"死了... 富邦人壽拒賠意外險敗訴 20141212

三商美邦補拒賠孝子事件

三商美邦騙老榮民

保險業務偽造簽名代投保

腿斷了，一家賠，一家不賠。

大腸癌被拒賠

吼！住院切息肉 新光保險不理賠

路見不平篇

五星級保障，平民化價格

說笑間猝死　富邦人壽拒賠意外險敗訴

富邦逼自家員工買長照險保單

付十年保費，只理賠一年(神經炎)

千萬意外險拒賠，卻勾結理賠黃牛A錢

北部全賠，中南部七成

嗅覺喪失拒賠

關於ORACLE的字元型態和其長度

資料庫儲存字元的常用型態有CHAR 與 VARCHAR2

而在使用這兩個型態的時候

常常很難決定到底要不要加上單位?

定義 varcha2(30) ，varchar2(30 byte)，還是varchar2(30 char)

哪種方式長度才夠? 尤其我們必須處理中文字的時候就很捆擾

在資料庫中，有一個參數 NLS_LENGTH_SEMANTICS

就定義，如果不加上長度單位的時候，那預設的單位為何

(可以用以下SQL去查詢你的定義為何:

select * from v$parameter where name like '%nls_length%'; )

如果不去特別定義，這個參數的預設值是 BYTE

要注意

每個中文字元長度佔 3 bytes

每個英數字長度佔 1 bytes

所以在定義欄位長度時，如果是varchar2

建議使用 char 當作欄位長度的單位

如果不用任何長度單位，那會使用系統預設值，也就是 byte

所以如果定義任何一個欄位長度為 varchar2(3 char)

那它就可以儲存三個中文字

如果定義為 varchar2(3) 則長度為 3 bytes

也就是說，只能儲存一個中文字

幾個常見的字元函數傳回值的型態要注意

length ==>傳回的長度，以char為單位 所以 length('中文字') = 3

lengthb ==>傳回的長度，以byte為單位 所以 length('中文字') = 9

substr 以CHAR為單位，所以 substr('中文字',1,2)，傳回 '中文'

substrb 以BYTE為單位，所以 substrb('中文字',1,6)，傳回 '中文'

至於資料型態 CHAR 與 VARCHAR2 的根本差異

要注意前者是固定長度字串

後者是變動長度字串

前者，在儲存資料時，會將長度不足之處補上空白

例如，假如宣告char(3)，而儲存的字串是'NO'，則在DB裡面會儲存 'NO '，包含一個空白字元

而且在比較的時候，也會把那個空白字元一起拿來比較

後者，varchar2，則是變動長度字串

後者，在儲存資料的時候，不會補上空白字元

在比較的時候，也不會把字元後面的空白拿來一起比

所以，如果宣告varchar2(3)，而儲存的長度是'NO'，則在DB裡面會儲存 'NO'，不含空白字元

安裝設定ORACLE Enterprise Manager (EM)

1. /etc/hosts:

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

192.168.0.xx hostxx.toplogis.com hostxx

2. 移除錯誤的設定檔：

( http://www.stefanocislaghi.eu/2009/12/unable-to-run-dbconsole-oc4j.html )

$cd /oracle/app/oracle/product/11.2.0/dbhome_1/bin

[oracle@tap07 bin]$ ./emca -deconfig dbcontrol db -repos drop  

STARTED EMCA at Jan 21, 2014 11:22:25 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: ivdb
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
Jan 21, 2014 11:22:47 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/app/oracle/cfgtoollogs/emca/ivdb/emca_2014_01_21_11_22_25.log.
Jan 21, 2014 11:22:48 AM oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
WARNING: ORA-28000: the account is locked

Jan 21, 2014 11:22:48 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Jan 21, 2014 11:22:48 AM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed. Some of the possible reasons may be:
 1) EM is configured with different hostname then physical host. Set environment variable ORACLE_HOSTNAME= and re-run EMCA script
 2) ORACLE_HOSTNAME is set. Unset it and re-run EMCA script
Jan 21, 2014 11:22:48 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Jan 21, 2014 11:24:56 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 21, 2014 11:24:56 AM

3. 進入該SID的環境

[oracle@tap07 bin]$ . setivdb

4. 新增設定檔

[oracle@tap07 bin]$ ./emca -config dbcontrol db -repos create

STARTED EMCA at Jan 21, 2014 11:25:37 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: ivdb
Listener port number: 1521
Listener ORACLE_HOME [ /oracle/app/oracle/product/11.2.0/dbhome_1 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /oracle/app/oracle/product/11.2.0/dbhome_1

Local hostname ................ tap07.toplogis.com
Listener ORACLE_HOME ................ /oracle/app/oracle/product/11.2.0/dbhome_1
Listener port number ................ 1521
Database SID ................ ivdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
Jan 21, 2014 11:26:04 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/app/oracle/cfgtoollogs/emca/ivdb/emca_2014_01_21_11_25_37.log.
Jan 21, 2014 11:26:05 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jan 21, 2014 11:33:01 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jan 21, 2014 11:33:07 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Jan 21, 2014 11:34:25 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Jan 21, 2014 11:34:29 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Jan 21, 2014 11:34:37 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Jan 21, 2014 11:34:37 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jan 21, 2014 11:35:35 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jan 21, 2014 11:35:35 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://tap07.toplogis.com:1158/em <<<<<<<<<<<
Jan 21, 2014 11:35:37 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /oracle/app/oracle/product/11.2.0/dbhome_1/tap07.toplogis.com_ivdb/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 21, 2014 11:35:37 AM

5. 啟動與結束

啟動：emctl start dbconsole

結束：emctl stop dbconsole

6. 連線：

https://xx.xx.xx.xx:1158/em