老爹寫程式

這一個錯誤是因為 Bug# 6966286 導致, 可能發生的版本於 10.2 ~ 11.2.
嚴重可以使 DB Crashed, 有 patch Set , 所以遇到了, 就上一上問題就解了.
如果不行, 也可以 set _library_cache_advice = false

誤刪 oracle data file 有怎樣的情況呢?
1. 輕微的, select 到的 table 是儲存此 data file 會有 error.
2. DB Crashed.
如果只是狀況一, 可以直接將該 table 所屬的 table space(如果沒有別的 table 使用), table 刪除.

 
Oracle over partition by
假設一個公司的薪資資料如下.
DEPT  NAME  SALARY
-------------------------
RD1 ANDY 120
RD1 KEN 140
RD1 KUO 180
HR1 BEN 180
RD2 YANG 130
RD2 RAY 180
1. 統計每個人佔部門總薪資的比例
select dept,name,salary, salary/sum(salary)over(partition by dept) as ratio from salary
HR1 BEN 180 1
RD1 ANDY 120 0.27
RD1 KEN 140 0.31
RD1 KUO 180 0.40
RD2 YANG 130 0.41
RD2 RAY 180 0.58
2. 統計每個人佔整公司的比例
select dept,name,salary, salary/sum(salary)over(partition by null) as ratio from salary
RD1 ANDY 120 0.12
RD1 KEN 140 0.15
RD1 KUO 180 0.19
HR1 BEN 180 0.19
RD2 YANG 130 0.13
RD2 RAY 180 0.19
3. 排序每個部門薪資由高至低
select dept,name,salary, rank() over(partition by dept order by salary desc) as rank from salary
HR1 BEN 180 1
RD1 KUO 180 1
RD1 KEN 140 2
RD1 ANDY 120 3
RD2 RAY 180 1
RD2 YANG 130 2
4. 排序整公司薪資由高至低 rank()
select dept,name,salary, rank() over(partition by null order by salary desc) as rank from salary
RD1 KUO 180 1
HR1 BEN 180 1
RD2 RAY 180 1
RD1 KEN 140 4
RD2 YANG 130 5
RD1 ANDY 120 6
4. 排序整公司薪資由高至低 dense_rank()
select dept,name,salary, dense_rank() over(partition by null order by salary desc) as rank from salary
RD1 KUO 180 1
HR1 BEN 180 1
RD2 RAY 180 1
RD1 KEN 140 2
RD2 YANG 130 3
RD1 ANDY 120 4
5. 最後要提一個觀念就是 partition by 的欄位值與 order by 是無關的.
select dept,name,salary, rank() over(partition by dept order by salary desc) as rank from salary order by name
RD1 ANDY 120 3
HR1 BEN 180 1
RD1 KEN 140 2
RD1 KUO 180 1
RD2 RAY 180 1
RD2 YANG 130 2

 
ODP 不同版本間的差異
1. 今天又被 ODP 擺一道, 相同的程式碼( OracleDataAdapter.Update ) 在 Production 及 別人的電腦會有 Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. 的錯誤, 卻可以在我的電腦正常執行, 因為已經有研究過 Concurrency 的議題過, 所以很直覺是 ODP 不同版本支援程度不同導致, 使用 ProcessExplorer 確認一下 Oracle.DataAccess 版本分別為 9.2.0.4 與 10.2.0.1, 然後自行指定 OracleDataAdapter.UpdateCommand 問題就解決了.
PS. 當 DataSet 內只有 Insert 的資料, 不會有錯誤, 但當 DataSet 內同時存在 insert/update 的資料時, 才有以上的差異.
2. 今天又來一次了, 兩個人跑相同的 unit test 程式, 一個可以過, 另一個 fail. fail 的地方是 insert 到 DB 後的資料確認, 發現欄位資料不一致. trace 一下卻發現, 有一個版本的 ODP 會將 space 去除, 例如 insert “ABC  ” (最後有兩個空白), select 出來的資料一個是 “ABC  ”(空白依然存在), “ABC”(空白不見了).

Dropping / Recreating Temporary Tablespace
當我們想要改變 temporary tablespace 大小空間. 可依循下步驟.
Step 1 : 建立另一個 temporary tablespace (假設叫 TEMP2).
SQL> CREATE TEMPORARY TABLESPACE temp2
2  TEMPFILE '/oradata/temp2_01.dbf' SIZE 5M REUSE
3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Step 2 : 改變資料庫內目前所使用的  temporary tablespace , (當然也可以依據每個 user 改變)
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Oracle 提供 bluk collect 語法可以一次取出多筆紀錄, 比起原先 loop fetch 一筆效能增加不少.
範例一：
(1)定義 type
　　CREATE OR REPLACE TYPE string_array AS TABLE OF VARCHAR2(100);
(2)在存儲過程裏面測試
　　DECLARE
　　v_array string_array;
　　BEGIN
　　SELECT cust_name BULK COLLECT INTO v_array
　　FROM all_tables c;
　　FORALL idx IN 1..v_table.COUNT
　　INSERT INTO cust_test VALUES(v_array(idx));
　　COMMIT;
　　END;
---------------------------------------------------------------------------------------------------------------
PROCEDURE BULK_INSERT
IS
TYPE t_record IS TABLE OF all_talbes%ROWTYPE;
 l_table t_record;
 CURSOR curTab
 IS
SELECT *
FROM all_tables a;
BEGIN
 OPEN curOriTab;
 LOOP
FETCH curTab BULK COLLECT INTO l_table LIMIT 5000;
   
FORALL i IN 1..l_table.COUNT
INSERT INTO cust_test VALUES l_table(i);
EXIT
 WHEN curTab%NOTFOUND;
 END LOOP;
 CLOSE curTab;
END;
---------------------------------------------------------------------------------------------------------------

Oracle 帳號密碼管理
資料庫管理者一定有管理密碼的行為，常遇到的問題諸如根據資訊安全政策，希望每90天要變更密碼。
作業方式：
(1) 可用以下指令變更自己的密碼：
ALTER USER 帳號 IDENTIFIED BY 密碼;
(2) 可執行以下指令，查看各帳號的資訊：
SELECT * from SYS.user$ where NAME = 'account'
重要欄位說明：
NAME：帳號名稱
CTIME：此帳號建立的日期與時間
PTIME：此帳號最後一次修改密碼的日期與時間
EXPTIME：此帳號的使用期限(日期與時間)
LTIME：此帳號最後一次被LOCK的日期與時間
所以可以做到系統在PTIME + X 天後，發mail通知此帳號擁有者變更密碼。
(3)  LOCK 帳號指令如下：
ALTER USER account ACCOUNT LOCK;
  ALTER USER account ACCOUNT UNLOCK;

 
ORA-03135 when connecting to Database [ID 404724.1]
這一個錯誤在網路上的搜尋, 有會導致 DB Hung 的情況, 在 Metalink上的說明是這樣的 :
另外這一個問題於10.2.0.3已經被改善了
Symptoms
On 10.2.0.2 in Production:
When attempting to connect to the primary database,the following error occurs.
ERROR
ORA-03135 connection lost contact
Cause
這一個問題是因為: AUDSES$ cache size is set to 20 (default).
Bug.5621770 ORA-3135 CONNECTING TO PRIMARY DATABASE WHEN SHUTDOWN STANDBY HOST.
AUDSES$ is a sequence which is getting updated on connection and is cached for 20 values so only every 20th connection or increment to audses$ will cause an update. If there is a problem to update the seq$, then the client connection will receive ORA-3135.
Solution
依循下列步驟解決此問題:
1. login to database
connect / as sysdba
2. increase the cache size of AUDSES$ from 20 (default) to 10000
ALTER SEQUENCE sys.audses$ cache 10000;

 
ORA-3135/ORA-3136 Connection Timeouts when the Fault is in the Database
最近發現 alert file 中又有 ORA - 3136 WARNING Inbound Connection Timed Out 的錯誤, 查看 metalink 說以下三種可能造成的原因, 分別為惡意的 client, 超時的認證, DB Server 忙碌時, 完整的說明如下 :
1.    Server gets a connection request from a malicious client which is not supposed to connect to the database , in which case the error thrown is the correct behavior. You can get the client address for which the error was thrown via sqlnet log file.
2.    The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.
3.    The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.
解決的方法是設定 qlnet.ora 的 SQLNET.INBOUND_CONNECT_TIMEOUT = 0.
原因是10.2.0.1之後的版本預設值是60秒.

 
ALTER PROFILE
Edit the resource limits associated with a user profile
Syntax:
  ALTER PROFILE profile_name LIMIT limit(s) range
KEY
  limit  =  
SESSIONS_PER_USER --Number of concurrent multiple sessions allowed per user
CPU_PER_SESSION      --Maximum CPU time per session (100ths of a second)
CPU_PER_CALL             --Maximum CPU time per call (100ths of a second)
CONNECT_TIME              --Allowable connect time per session in minutes
IDLE_TIME                        --Allowed idle time before user is disconnected (minutes)
LOGICAL_READS_PER_SESSION  --Maximum number of database blocks read per session
LOGICAL_READS_PER_CALL         --Maximum number of database blocks read per call
COMPOSITE_LIMIT         --Maximum weighted sum of: CPU_PER_SESSION, CONNECT_TIME,
LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. If this limit is exceeded, Oracle aborts the session and returns an error.
PRIVATE_SGA                --Maximum integer bytes of private space in the SGA
(useful for systems using multi-threaded server MTS)
range  =  UNLIMITED | DEFAULT | integer
for PRIVATE_SGA specify K or M
e.g.
ALTER PROFILE MyProfile LIMIT PRIVATE_SGA 50 K
New with Oracle 8 are password related profile limits...