Oracle Performance Tunning Practice - INDEX RANGE SCAN with CPU high utilization

1. set autotrace on  and execute sql .
   Using index by "INDEX RANGE SCAN"
2. Through AWR report found the sql is top1 in  SQL ordered by CPU Time part.
3.  alter session set events '10046 trace name context forever, level 8';
    Execute sql again  and found  STAT line for the traced query contains 'cnt=xxx' problem.
4. Check table records found the value are skewed cause by program bug.
5. Remove abnormal data and  analyze index column .

  execute dbms_stats.gather_table_stats('xxx','xxxxxxx',method_opt=>'FOR COLUMNS xxx');

6. High CPU utilization was solved.

    

Reset oracle sequence procedure

Oracle sequence cannot reset by command , need workaround to achieve original start value.

CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2) AS
cval INTEGER;
inc_by VARCHAR2(30);
BEGIN
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
IF cval < 0 THEN
inc_by := ' INCREMENT BY ';
cval:= ABS(cval);
ELSE
inc_by := ' INCREMENT BY -';
END IF;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by || cval;
EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY 1';
END reset_sequence;
/

Apply patcheset Oracle 10.2.0.4 on AIX 6.1

--When apply 10.2.0.4 on AIX 6.1 , failed with "Java process exist" message and apply process stop.
--Workground
As root:
1) rename /usr/sbin/fuser
2) touch /usr/sbin/fuser
3) chmod +x /usr/sbin/fuser
As oracle software owner:
1) Try to install the patchset (or base release)
(fuser can now return nothing, hence the Installer should continue)
After successful install, as root:
1) rename renamed_fuser back to its original name

select view and function throufh db link

select view and function throufh db link.

get_entryvalue is a function in remote DB.

-----------
SELECT ip3.ID,
ip3.CLASS,
ip3.subclass,
ip3.item_number,
ip3.description,
ip3.list32,
ip3.list33,
A.get_entryvalue@A_DB_LINK (ip3.list32) type,
A.get_entryvalue@A_DB_LINK (ip3.list33) group
FROM A.item_p3@A_DB_LINK ip3
WHERE subclass = '123456'

AIX 5.3 - install perl DBI and oracle DBD

1. down DBI http://search.cpan.org/~timb/DBI/
2. install DBI
perl Makefile.PL
make
make test
make install
3. download DBD http://search.cpan.org/~timb/DBD-Oracle-1.16/
$ perl Makefile.PL
Edit Makefile with following commands(OS and Oracle is 64bit,perl is 32bit. Need to set oracle lib as 32 bit):
1,$s?/lib/ ?/lib32/ ?g
1,$s?-q64??g
1,$s?/lib/sysliblist?/lib32/sysliblist?g
Now perform normal commands to perform the testing/making:
$ make
$ make test
$ make install

Oracle NLS_LANG environment setting meaning

NLS_LANG=＜Language＞_＜Territory＞.＜Clients Characterset＞
NLS_LANG各部分含義如下:
LANGUAGE指定:
-Oracle訊息使用的語言
-日期中月份和日顯示
TERRITORY指定
-貨幣和數位格式
-地區和計算星期及日期的習慣
CHARACTERSET:
-控制用戶端應用程式使用的字元集
通常設置或者等於用戶端(如Windows)內碼表
或者對於unicode應用設置為UTF8
在Windows上查看目前系統的內碼表可以使用chcp命令:
Example:
C:\＞set NLS_LANG=TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
C:\＞sqlplus "/ as sysdba"
SQL＞ select sysdate from dual;

SYSDATE
----------
01-11月-03

C:\＞set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
C:\＞sqlplus "/ as sysdba"
SQL＞ select sysdate from dual;

SYSDATE
----------
01-NOV-03

function , procedure return cursor operation

Permitted Cursor Variable Parameter Modes
Operation IN IN OUT OUT
OPEN No Yes No
FETCH Yes Yes No
CLOSE Yes Yes No

--- function return cursor--
CREATE OR REPLACE FUNCTION emp_by_job (p_job VARCHAR2)
RETURN SYS_REFCURSOR
IS
emp_refcur SYS_REFCURSOR;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job;
RETURN emp_refcur;
END;


DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_job emp.job%TYPE := 'SALESMAN';
v_emp_refcur SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB ' || v_job);
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
v_emp_refcur := emp_by_job(v_job);
LOOP
FETCH v_emp_refcur INTO v_empno, v_ename;
EXIT WHEN v_emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE v_emp_refcur;
END;


----------procedure return cursor ---------------------------------------

CREATE OR REPLACE PROCEDURE open_all_emp (
p_emp_refcur IN OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN p_emp_refcur FOR SELECT empno, ename FROM emp;
END;

CREATE OR REPLACE PROCEDURE open_emp_by_dept (
p_emp_refcur IN OUT SYS_REFCURSOR,
p_deptno emp.deptno%TYPE
)
IS
BEGIN
OPEN p_emp_refcur FOR SELECT empno, ename FROM emp
WHERE deptno = p_deptno;
END;

CREATE OR REPLACE FUNCTION open_dept (
p_dept_refcur IN OUT SYS_REFCURSOR
) RETURN SYS_REFCURSOR
IS
v_dept_refcur SYS_REFCURSOR;
BEGIN
v_dept_refcur := p_dept_refcur;
OPEN v_dept_refcur FOR SELECT deptno, dname FROM dept;
RETURN v_dept_refcur;
END;

CREATE OR REPLACE PROCEDURE fetch_emp (
p_emp_refcur IN OUT SYS_REFCURSOR
)
IS
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH p_emp_refcur INTO v_empno, v_ename;
EXIT WHEN p_emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
END;


CREATE OR REPLACE PROCEDURE fetch_dept (
p_dept_refcur IN SYS_REFCURSOR
)
IS
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('DEPT DNAME');
DBMS_OUTPUT.PUT_LINE('---- ---------');
LOOP
FETCH p_dept_refcur INTO v_deptno, v_dname;
EXIT WHEN p_dept_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_deptno || ' ' || v_dname);
END LOOP;
END;



CREATE OR REPLACE PROCEDURE close_refcur (
p_refcur IN OUT SYS_REFCURSOR
)
IS
BEGIN
CLOSE p_refcur;
END;



DECLARE
gen_refcur SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('ALL EMPLOYEES');
open_all_emp(gen_refcur);
fetch_emp(gen_refcur);
DBMS_OUTPUT.PUT_LINE('****************');

DBMS_OUTPUT.PUT_LINE('EMPLOYEES IN DEPT #10');
open_emp_by_dept(gen_refcur, 10);
fetch_emp(gen_refcur);
DBMS_OUTPUT.PUT_LINE('****************');

DBMS_OUTPUT.PUT_LINE('DEPARTMENTS');
fetch_dept(open_dept(gen_refcur));
DBMS_OUTPUT.PUT_LINE('*****************');

close_refcur(gen_refcur);
END;

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

Oracle 綜合問題

一：SQL tuning 類
1：列舉幾種表連接方式答：merge join，hash join，nested loop

2：不借助第三方工具，怎樣查看sql的執行計畫？答：sqlplus
set autotrace ...
utlxplan.sql 創建 plan_table 表
3：如何使用CBO，CBO與RULE的區別？答：在初始化參數裏面設置 optimizer_mode=choose/all_rows/first_row 等可以使用 cbo。
rbo會選擇不合適的索引，cbo需要統計資訊。
4：如何定位重要（消耗資源多）的SQL？答：根據v$sqlarea 中的邏輯讀/disk_read。以及尋找CPU使用過量的session，查出當前session的當前SQL語句，或者：監控WIN平臺Oracle的運行
5：如何跟蹤某個session的SQL？答：先找出對應的'sid,serial'，然後調用system_system.set_sql_trace_in_session(sid,serial,true)；參考：跟蹤某個會話
6：SQL調整最關注的是什麼？答：邏輯讀。IO量
7：說說你對索引的認識（索引的結構、對dml影響、對查詢影響、為什麼提高查詢性能）答：默認的索引是b-tree。
對insert的影響：分裂，要保證tree的平衡。
對delete的影響：刪除行的時候要標記改節點為刪除。
對update的影響：如果更新表中的索引欄位，則要相應的更新索引中的鍵值。查詢中包含索引欄位的鍵值和行的物理位址。
8：使用索引查詢一定能提高查詢的性能嗎？為什麼？答：不能。如果返回的行數目較大，使用全表掃描的性能較好。
9：綁定變數是什麼？綁定變數有什麼優缺點？答：通俗的說，綁定變數就是變數的一個占位元符，使用綁定變數可以減少只有變數值不同的語句的解析。
10：如何穩定(固定)執行計畫？答：使用stored outline。
11：和排序相關的記憶體在8i和9i分別怎樣調整，臨時表空間的作用是什麼？答：8i：使用sort_area_size，hash_area_size，每個session分配相同的值，不管有無使用。
9i：使用pga_aggregate來統一管理。臨時表空間的作用：
在sort_area_size中不能完成的部分在臨時表空間完成，臨時表空間在重建索引，創建臨時表等都要用到。
還有hash join不能完成的也在臨時表空間中做。
12：存在表T(a,b,c,d)，要根據欄位c排序後取第21—30條記錄顯示，請給出sqlselect a,b,c,d from (select a,b,c,d from T order by c) where rownum<=30
minus
select a,b,c,d from (select a,b,c,d from T order by c) where rownum <=20;
或者：
select * from (select rownum rn,a.* from (select a,b,c,d from T order by c) a )where rn between 21 and 30;

二：資料庫基本概念類
1：pctused and pctfree 表示什麼含義有什麼作用？答：表示資料塊什麼時候移入和移出freelist。
pctused：如果資料塊的使用率小於pctused的值，則該資料塊重新加入到fresslist中。
pctfree：如果資料塊的使用率高於pctfree的值，則該資料塊從freelist中移出。
2：簡單描述table / segment / extent / block之間的關係答：一個table至少是一個segment，如果分區表，則每個分區是一個segment，table可以看成是一個邏輯上的概念，segment可以看成是這個邏輯概念的物理實現；
segment由一個或多個extents組成，segment不可以跨表空間但可以跨資料檔案；
extent由多個連續的blocks組成，不可以跨資料檔案；
block由1-多個os塊組成，是oracle i/o的最小存儲單位。
3：描述tablespace和datafile之間的關係答：tablespace是邏輯上的概念，datafile是物理上的概念。
一個tablespace可以由多個datafile組成，一個datafile不能跨越多個tablespace。
4：本地管理表空間和字典管理表空間的特點，ASSM有什麼特點？答：一個使用freelist管理，一個使用點陣圖管理。
5：回滾段的作用是什麼？答：保存資料的前像，保證資料讀取的時間點一致性。Oracle裏資料的多版本特性就是通過回滾段來實現的，正因為此，Oracle資料庫實現了讀寫不競爭的性能優勢！
6：日誌的作用是什麼？答：記錄對資料庫的操作，便於恢復。
7：SGA主要有那些部分，主要作用是什麼？答：db_cache(緩存資料塊)，shared_pool(緩存sql，執行計畫，資料字典資訊等)，large_pool(MTS模式、parallel 、rman等要用到)，java pool(java程式如SQLJ存儲過程運行時要用到)。
8：Oracle系統進程主要有哪些，作用是什麼？答：smon（合併空間，實例恢復），pmon（清理失敗的進程），歸檔進程（負責在日誌切換的時候歸檔日誌檔），lgmr（日誌書寫器進程，負責寫日誌），ckpt（檢查點進程，觸發檢查點），dbwr（資料庫寫入器，負責把資料寫入導datafile）。

三：備份恢復類
1：備份如何分類？答：邏輯備份(exp)與物理備份。或者冷備份與熱備份。
2：歸檔是什麼含義？答：把日誌檔放到另一個地方。
3：如果一個表在2004-08-04 10:30:00 被drop，在有完善的歸檔和備份的情況下，如何恢復答：拷貝備份，
recover database until time 2004-08-04 10:30:00
alter database open resetlogs;
4：rman是什麼，有何特點？答：rman叫恢復管理器。
特點很多。可以線上備份，到少目標資料庫是mount狀態。1）熱備份。
2）可以存儲腳本。
3）可以增量備份。
4）自動管理備份集。
5：standby的特點答：利用傳輸重做日誌來達到同步的目的。可以設定多個保護級別，9i後支援邏輯standbyDB。
6：對於一個要求恢復時間比較短的系統(資料庫50G，每天歸檔5G)，你如何設計備份策略答：每天一個全備份。

四：系統管理類
1：對於一個存在系統性能的系統，說出你的診斷處理思路答：做一個statspack，根據top 5，system load，top sql等來做相應的調整。
2：列舉幾種診斷IO、CPU、性能狀況的方法答：hp-unix:iostat -x 1 5；
top/vmstat/glance
3：對statspack有何認識？答：一個性能診斷工具而已，其本質就是在兩個時間點採樣兩個系統資料。(動態性能視圖)，然後根據兩個snapshot，產生一個報告。
4：如果系統現在需要在一個很大的表上創建一個索引，你會考慮那些因素，如何做以儘量減小對應用的影響答：
1）增大sort_area_size（8i）/pga_aggregate_target（9i）值。
2）如果表有分區（一般大表都要用到分區的），按分區逐個建索引，如果是本地索引的話。
3）系統空閒的時候建。
5：對raid10 和raid5有何認識？答：raid10是先鏡像後條帶，適合對寫入速度要求較高的資料庫系統，特別是online redolog檔，raid5適合大部分的資料庫系統和資料倉庫系統，讀性能優於寫性能。

五：綜合隨意類
1：你最擅長的是oracle哪部分?答：性能/sql 調優、備份恢復。
2：喜歡oracle嗎？喜歡上論壇嗎？或者偏好oracle的哪一部分？答：http://www.itpub.net/，Oracle的SQL 優化。
3：隨意說說你覺得oracle最有意思的部分或者最困難的部分答：SQL調優，最困難的是Oracle的網路管理，Oracle的Connect Manager沒用過。
4：為何要選擇做DBA呢?答：愛好加職業歷史積累

甲骨文Oracle技術筆試題

甲骨文Oracle技術筆試題

1. 解釋冷備份和熱備份的不同點以及各自的優點
　　解答：熱備份針對歸檔模式的資料庫，在資料庫仍舊處於工作狀態時進行備份。而冷備份指在資料庫關閉後，進行備份，適用於所有模式的資料庫。熱備份的優點在於當備份時，資料庫仍舊可以被使用並且可以將資料庫恢復到任意一個時間點。冷備份的優點在於它的備份和恢復操作相當簡單，並且由於冷備份的資料庫可以工作在非歸檔模式下,資料庫性能會比歸檔模式稍好。(因為不必將archive log寫入硬碟)
　　2. 你必須利用備份恢復資料庫，但是你沒有控制檔，該如何解決問題呢?
　　解答：重建控制檔，用帶backup control file 子句的recover 命令恢復
　　資料庫。
　　3. 如何轉換init.ora到spfile?
　　解答：使用create spfile from pfile 命令.
　　4. 解釋data block , extent 和 segment的區別(這裏建議用英文術語)
　　解答：data block是資料庫中最小的邏輯存儲單元。當資料庫的物件需要更多的物理存儲空間時，連續的data block就組成了extent . 一個資料庫物件
　　擁有的所有extents被稱為該物件的segment.
　　5. 給出兩個檢查表結構的方法
　　解答：1。DESCRIBE命令
　　2. DBMS_METADATA.GET_DDL 包
　　6. 怎樣查看資料庫引擎的報錯
　　解答：alert log.
　　7. 比較truncate和delete 命令
　　解答：兩者都可以用來刪除表中所有的記錄。區別在於：truncate是DDL操作，它移動HWK，不需要 rollback segment .而Delete是DML操作, 需要rollback segment 且花費較長時間.
　　8. 使用索引的理由
　　解答：快速訪問表中的data block
　　9. 給出在STAR SCHEMA中的兩種表及它們分別含有的資料
　　解答：Fact tables 和dimension tables. fact table 包含大量的主要的資訊而 dimension tables 存放對fact table 某些屬性描述的資訊
　　10. FACT Table上需要建立何種索引?
　　解答：點陣圖索引 (bitmap index)
　　11. 給出兩種相關約束?
　　解答：主鍵和外鍵
　　12. 如何在不影響子表的前提下，重建一個母表
　　解答：子表的外鍵強制實效，重建母表，啟動外鍵
　　13. 解釋歸檔和非歸檔模式之間的不同和它們各自的優缺點
　　解答：歸檔模式是指你可以備份所有的資料庫 transactions並恢復到任意一個時間點。非歸檔模式則相反，不能恢復到任意一個時間點。但是非歸檔模式可以帶來資料庫性能上的少許提高
　　.
　　14. 如何建立一個備份控制檔?
　　解答：Alter database backup control file to trace.
　　15. 給出資料庫正常啟動所經歷的幾種狀態 ?
　　解答：
　　STARTUP NOMOUNT – 資料庫實例啟動
　　STARTUP MOUNT - 資料庫裝載
　　STARTUP OPEN – 資料庫打開
　　16. 哪個column可以用來區別V$視圖和GV$視圖?
　　解答： INST_ID 指明集群環境中具體的 某個instance 。
　　17. 如何生成explain plan?
　　解答：運行utlxplan.sql. 建立plan 表
　　針對特定SQL語句，使用 explain plan set statement_id = 'tst1' into plan_table
　　運行utlxplp.sql 或 utlxpls.sql察看explain plan
　　18. 如何增加buffer cache的命中率?
　　解答：在資料庫較繁忙時，適用buffer cache advisory 工具，查詢v$db_cache_advice . 如果有必要更改，可以使用 alter system set db_cache_size 命令
　　19. ORA-01555的應對方法?
　　解答：具體的出錯資訊是snapshot too old within rollback seg , 通常可以通過
　　增大rollback seg來解決問題。當然也需要察看一下具體造成錯誤的SQL文本
　　20. 解釋$ORACLE_HOME和$ORACLE_BASE的區別?
　　解答：ORACLE_BASE是oracle的根目錄，ORACLE_HOME是oracle產品
　　的目錄。
　　21. 如何判斷資料庫的時區?
　　解答：SELECT DBTIMEZONE FROM DUAL;
　　22. 解釋GLOBAL_NAMES設為TRUE的用途
　　解答：GLOBAL_NAMES指明聯接資料庫的方式。如果這個參數設置為TRUE,在建立資料庫鏈結時就必須用相同的名字連結遠端資料庫
　　23。如何加密PL/SQL程式?
　　解答：WRAP
　　24. 解釋FUNCTION,PROCEDURE和PACKAGE區別
　　解答：function 和procedure是PL/SQL代碼的集合，通常為了完成一個任務。procedure 不需要返回任何值而function將返回一個值在另一
　　方面，Package是為了完成一個商業功能的一組function和proceudre的集合
　　25. 解釋TABLE Function的用途
　　解答：TABLE Function是通過PL/SQL邏輯返回一組紀錄，用於普通的表/視圖。他們也用於pipeline和ETL過程。
　　26. 舉出3種可以收集three advisory statistics
　　解答：Buffer Cache Advice, Segment Level Statistics, Timed Statistics
　　27. Audit trace 存放在哪個oracle目錄結構中?
　　解答：unix $ORACLE_HOME/rdbms/audit Windows the event viewer
　　28. 解釋materialized views的作用
　　解答：Materialized views 用於減少那些匯總，集合和分組的資訊的集合數量。它們通常適合於資料倉庫和DSS系統。
　　29. 當用戶進程出錯，哪個後臺進程負責清理它
　　解答： PMON
　　30. 哪個後臺進程刷新materialized views?
　　解答：The Job Queue Processes.
　　31. 如何判斷哪個session正在連結以及它們等待的資源?
　　解答：V$SESSION / V$SESSION_WAIT
　　32. 描述什麼是 redo logs
　　解答：Redo Logs 是用於存放資料庫資料改動狀況的物理和邏輯結構。可以用來修復資料庫.
　　33. 如何進行強制LOG SWITCH?
　　解答：ALTER SYSTEM SWITCH LOGFILE;
　　34. 舉出兩個判斷DDL改動的方法?
　　解答：你可以使用 Logminer 或 Streams
　　35. Coalescing做了什麼?
　　解答：Coalescing針對於字典管理的tablespace進行碎片整理，將臨近的小extents合併成單個的大extent.
　　36. TEMPORARY tablespace和PERMANENT tablespace 的區別是?
　　解答：A temporary tablespace 用於臨時物件例如排序結構而 permanent tablespaces用來存儲那些'真實'的物件(例如表，回滾段等)
　　37. 創建資料庫時自動建立的tablespace名稱?
　　解答：SYSTEM tablespace.
　　38. 創建用戶時，需要賦予新用戶什麼許可權才能使它聯上資料庫。
　　解答：CONNECT
　　39. 如何在tablespace裏增加資料檔案?
　　解答：ALTER TABLESPACE ADD DATAFILE SIZE
　　40. 如何變動資料檔案的大小?
　　解答：ALTER DATABASE DATAFILE RESIZE ;
　　41. 哪個VIEW用來檢查資料檔案的大小?
　　解答： DBA_DATA_FILES
　　42. 哪個VIEW用來判斷tablespace的剩餘空間
　　解答：DBA_FREE_SPACE
　　43. 如何判斷誰往表裏增加了一條紀錄?
　　解答：auditing
　　44. 如何重構索引?
　　解答： ALTER INDEX REBUILD;
　　45. 解釋什麼是Partitioning(分區)以及它的優點。
　　解答：Partition將大表和索引分割成更小，易於管理的分區。
　　46. 你剛剛編譯了一個PL/SQL Package但是有錯誤報導，如何顯示出錯資訊?
　　解答：SHOW ERRORS
　　47. 如何搜集表的各種狀態資料?
　　解答： ANALYZE
　　The ANALYZE command.
　　48. 如何啟動SESSION級別的TRACE
　　解答: DBMS_SESSION.SET_SQL_TRACE
　　ALTER SESSION SET SQL_TRACE = TRUE;
　　49. IMPORT和SQL*LOADER 這2個工具的不同點
　　解答：這兩個ORACLE工具都是用來將資料導入資料庫的。
　　區別是：IMPORT工具只能處理由另一個ORACLE工具EXPORT生成
　　的數據。而SQL*LOADER可以導入不同的ASCII格式的資料源
　　50。用於網路連接的2個檔?
　　解答： TNSNAMES.ORA and SQLNET.ORA
　　後面五道題，
　　1,什麼叫Normalization.什麼叫3NF,並請列出1NF和2NF..
　　2,什麼叫revised key index
　　3,什麼叫鎖死，舉一個引起資料庫操作中鎖死的例子
　　4, BYTE[] buf = BYTE[1024];
　　in.read(buf)
　　in是一個接收圖像資料的網路IO流，請指出這段代碼有什麼問題，
　　並請用java代碼改進它
　　5,你正在分析一個子系統的介面，發現介面很多。然後你同事勸你用Fecade,
　　問你用Fecade有什麼好處
百度PHP/JSP職位線上筆試真題
　1、請寫一個程式，隨機列印“0”、“1”、“2”，但保證“0”、“1”、“2”出現的概率是可配置的，例如2：1：3。
　　2、有一個基於資料庫的通知發佈系統，需要能夠靈活配置通知生效的時間：
　　a)可以設置在某幾個小時內生效，如指定"2046年12月24日 18時到25日0時生效
　　b)可以設置在一周的某幾天發佈，如可以設置週六、周日生效
　　c)可以設置一月的某幾天生效，如可以設置5號、6號生效
　　d)可以設置某一月的奇數日或者偶數日生效，如1、3、5號生效
　　請設計資料庫表結構，並簡要說明設計思路。
　　3、為某圖書館開發線上流覽系統，使用戶可以通過自定義的圖書別名流覽相關聯的圖書內容。假設該圖書館有1000萬註冊用戶，館藏圖書1000萬部。線上流覽系統允許用戶自定義分類名稱，每個分類可以包含若干部書籍。用戶可以添加、刪除分類，修改分類的名稱(同一用戶不允許有名稱相同的分類)，可以在分類下添加、刪除書籍，修改書籍的別名(同一分類下不允許有名稱相同的別名)。現在設定每個用戶最多可以自定義100個分類，每個分類最多可以包含100部書籍。
　　A、假定用資料庫解決存儲問題，請設計相關的資料表結構，並給出設計考慮。
　　B、請給出下列操作的SQL語句

問題：在一個檔中有 10G 個整數，亂序排列，要求找出中位數。記憶體限制為 2G。只寫出思路即可。
　　Peak Wong：
　　1，把整數分成256M段，每段可以用64位元整數保存該段資料個數，256M*8 = 2G記憶體，先清0
　　2，讀10G整數，把整數映射到256M段中，增加相應段的記數
　　3，掃描256M段的記數，找到中位數的段和中位數的段前面所有段的記數，可以把其他段的記憶體釋放
　　4，因中位數段的可能整數取值已經比較小(如果是32bit整數，當然如果是64bit整數的話，可以再次分段)，對每個整數做一個記數，再讀一次10G整數，唯讀取中位數段對應的整數，並設置記數。
　　5，對新的記數掃描一次，即可找到中位數。
一:SQL tuning 類
　　1:列舉幾種表連接方式
　　2:不借助第三方工具，怎樣查看sql的執行計畫
　　3:如何使用CBO,CBO與RULE的區別
　　4:如何定位重要(消耗資源多)的SQL
　　5:如何跟蹤某個session的SQL
　　6:SQL調整最關注的是什麼
　　7:說說你對索引的認識(索引的結構、對dml影響、對查詢影響、為什麼提高查詢性能)
　　8:使用索引查詢一定能提高查詢的性能嗎?為什麼
　　9:綁定變數是什麼?綁定變數有什麼優缺點?
　　10:如何穩定(固定)執行計畫
　　11:和排序相關的記憶體在8i和9i分別怎樣調整，臨時表空間的作用是什麼
　　12:存在表T(a,b,c,d),要根據欄位c排序後取第21—30條記錄顯示，請給出sql
　　二:資料庫基本概念類
　　1:pctused and pctfree 表示什麼含義有什麼作用
　　2:簡單描述table / segment / extent / block之間的關係
　　3:描述tablespace和datafile之間的關係
　　4:本地管理表空間和字典管理表空間的特點，ASSM有什麼特點
　　5:回滾段的作用是什麼
　　6:日誌的作用是什麼
　　7:SGA主要有那些部分，主要作用是什麼
　　8racle系統進程主要有哪些，作用是什麼
　　三:備份恢復類
　　1:備份如何分類
　　2:歸檔是什麼含義
　　3:如果一個表在2004-08-04 10:30:00 被drop，在有完善的歸檔和備份的情況下，如何恢復
　　4:rman是什麼，有何特點
　　5:standby的特點
　　6:對於一個要求恢復時間比較短的系統(資料庫50G,每天歸檔5G)，你如何設計備份策略
　　四:系統管理類
　　1:對於一個存在系統性能的系統，說出你的診斷處理思路
　　2:列舉幾種診斷IO、CPU、性能狀況的方法
　　3:對statspack有何認識
　　4:如果系統現在需要在一個很大的表上創建一個索引，你會考慮那些因素，如何做以儘量減小對應用的影響
　　5:對raid10 和raid5有何認識
　　五:綜合隨意類
　　1:你最擅長的是oracle哪部分?
　　2:喜歡oracle嗎?喜歡上論壇嗎?或者偏好oracle的哪一部分?
　　3:隨意說說你覺得oracle最有意思的部分或者最困難的部分
　　4:為何要選擇做DBA呢?
參考解答：
　　一：SQL tuning 類
　　1:列舉幾種表連接方式
　　hash join/merge join/nest loop(cluster join)/index join

　　2:不借助第三方工具，怎樣查看sql的執行計畫
　　set autot on
　　explain plan set statement_id = &item_id for &sql;
　　select * from table(dbms_xplan.display);
　　http://download-west.oracle.com/ ... /b10752/ex_plan.htm

　　3:如何使用CBO,CBO與RULE的區別
　　在optimizer_mode=choose時,如果表有統計資訊(分區表外),優化器將選擇CBO,否則選RBO。RBO遵循簡單的分級方法學,使用15種級別要點，當接收到查詢，優化器將評估使用到的要點數目, 然後選擇最佳級別(最少的數量)的執行路徑來運行查詢。
　　CBO嘗試找到最低成本的訪問資料的方法,為了最大的吞吐量或最快的初始回應時間,計算使用不同 的執行計畫的成本，並選擇成本最低的一個,關於表的資料內容的統計被用於確定執行計畫。
　　4:如何定位重要(消耗資源多)的SQL
select sql_text
from v$sql
where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);

　　5:如何跟蹤某個session的SQL
exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);
select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1);
exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');

　　6:SQL調整最關注的是什麼
　　查看該SQL的response time(db block gets/consistent gets/physical reads/sorts (disk))
　　7:說說你對索引的認識(索引的結構、對dml影響、為什麼提高查詢性能)
　　b-tree index/bitmap index/function index/patitional index(local/global) 索引通常能提高select/update/delete的性能,會降低insert的速度,
　　8:使用索引查詢一定能提高查詢的性能嗎?為什麼
　　索引就是為了提高查詢性能而存在的, 如果在查詢中索引沒有提高性能, 只能說是用錯了索引,或者講是場合不同
　　9:綁定變數是什麼?綁定變數有什麼優缺點?
　　綁定變數是相對文本變數來講的,所謂文本變數是指在SQL直接書寫查詢條件，這樣的SQL在不同條件下需要反復解析,綁定變數是指使用變數來代替直接書寫條件，查詢bind value在運行時傳遞，然後綁定執行。優點是減少硬解析,降低CPU的爭用,節省shared_pool ;缺點是不能使用histogram,sql優化比較困難
　　10:如何穩定(固定)執行計畫
　　query_rewrite_enabled = true
　　star_transformation_enabled = true
　　optimizer_features_enable = 9.2.0

　　創建並使用stored outline
　　http://download-west.oracle.com/ ... /outlines.htm#26854

　　11:和排序相關的記憶體在8i和9i分別怎樣調整，臨時表空間的作用是什麼
　　8i中sort_area_size/sort_area_retained_size決定了排序所需要的記憶體
　　如果排序操作不能在sort_area_size中完成,就會用到temp表空間
　　9i中如果workarea_size_policy=auto時,
　　排序在pga內進行,通常pga_aggregate_target的1/20可以用來進行disk sort;
　　如果workarea_size_policy=manual時,排序需要的記憶體由sort_area_size決定
　　在執行order by/group by/distinct/union/create index/index rebuild/minus等操作時,
　　如果在pga或sort_area_size中不能完成,排序將在臨時表空間進行(disk sort),
　　臨時表空間主要作用就是完成系統中的disk sort.
　　12:存在表T(a,b,c,d),要根據欄位c排序後取第21—30條記錄顯示，請給出sql
　　create table t(a number(,b number(,c number(,d number();
　　/
　　begin
　　for i in 1 .. 300 loop
　　insert into t values(mod(i,2),i/2,dbms_random.value(1,300),i/4);
　　end loop;
　　end;
　　/
　　select * from (select c.*,rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30;
　　/
　　select * from (select * from test order by c desc) x where rownum < 30
　　minus
　　select * from (select * from test order by c desc) y where rownum < 20 order by 3 desc

　　相比之 minus性能較差
資料庫基本概念類
　　1:pctused and pctfree 表示什麼含義有什麼作用
　　pctused與pctfree控制資料塊是否出現在freelist中,
　　pctfree控制資料塊中保留用於update的空間,當資料塊中的free space小於pctfree設置的空間時,
　　該資料塊從freelist中去掉,當塊由於dml操作free space大於pct_used設置的空間時,該資料庫塊將
　　被添加在freelist鏈表中。
　　2:簡單描述table / segment / extent / block之間的關係
　　table創建時,默認創建了一個data segment,
　　每個data segment含有min extents指定的extents數,
　　每個extent據據表空間的存儲參數分配一定數量的blocks
　　3:描述tablespace和datafile之間的關係
　　一個tablespace可以有一個或多個datafile,每個datafile只能在一個tablespace內,
　　table中的資料,通過hash演算法分佈在tablespace中的各個datafile中,
　　tablespace是邏輯上的概念,datafile則在物理上儲存了資料庫的種種物件。
　　4:本地管理表空間和字典管理表空間的特點，ASSM有什麼特點
　　本地管理表空間(Locally Managed Tablespace簡稱LMT)
　　8i以後出現的一種新的表空間的管理模式，通過點陣圖來管理表空間的空間使用。
　　字典管理表空間(Dictionary-Managed Tablespace簡稱DMT)
　　8i以前包括以後都還可以使用的一種表空間管理模式，通過資料字典管理表空間的空間使用。
　　動段空間管理(ASSM)，
　　它首次出現在Oracle920裏有了ASSM，鏈結列表freelist被點陣圖所取代，它是一個二進位的陣列，
　　能夠迅速有效地管理存儲擴展和剩餘區塊(free block)，因此能夠改善分段存儲本質，
　　ASSM表空間上創建的段還有另外一個稱呼叫Bitmap Managed Segments(BMB 段)。
　　5:回滾段的作用是什麼
　　事務回滾：當事務修改表中資料的時候，該資料修改前的值(即前影像)會存放在回滾段中，
　　當用戶回滾事務(ROLLBACK)時，ORACLE將會利用回滾段中的資料前影像來將修改的資料恢復到原來的值。
　　事務恢復：當事務正在處理的時候，常式失敗，回滾段的資訊保存在undo表空間中，
　　ORACLE將在下次打開資料庫時利用回滾來恢復未提交的資料。
　　讀一致性：當一個會話正在修改資料時，其他的會話將看不到該會話未提交的修改。
　　當一個語句正在執行時，該語句將看不到從該語句開始執行後的未提交的修改(語句級讀一致性)
　　當ORACLE執行SELECT語句時，ORACLE依照當前的系統改變號(SYSTEM CHANGE NUMBER-SCN)
　　來保證任何前於當前SCN的未提交的改變不被該語句處理。可以想像：當一個長時間的查詢正在執行時，
　　若其他會話改變了該查詢要查詢的某個資料塊，ORACLE將利用回滾段的資料前影像來構造一個讀一致性視圖。
　　6:日誌的作用是什麼
　　記錄資料庫事務,最大限度地保證資料的一致性與安全性
　　重做日誌檔：含對資料庫所做的更改記錄，這樣萬一出現故障可以啟用資料恢復,一個資料庫至少需要兩個重做日誌檔
　　歸檔日誌檔：是重做日誌檔的脫機副本，這些副本可能對於從介質失敗中進行恢復很必要。
　　7:SGA主要有那些部分，主要作用是什麼
　　SGA：db_cache/shared_pool/large_pool/java_pool
　　db_cache:

　　資料庫緩存(Block Buffer)對於Oracle資料庫的運轉和性能起著非常關鍵的作用，
　　它佔據Oracle資料庫SGA(系統共用記憶體區)的主要部分。Oracle資料庫通過使用LRU
　　演算法，將最近訪問的資料塊存放到緩存中，從而優化對磁片資料的訪問.
　　shared_pool:
　　共用池的大小對於Oracle 性能來說都是很重要的。
　　共用池中保存資料字典高速緩衝和完全解析或編譯的的PL/SQL 塊和SQL 語句及控制結構
　　large_pool:
　　使用MTS配置時，因為要在SGA中分配UGA來保持用戶的會話，就是用Large_pool來保持這個會話記憶體
　　使用RMAN做備份的時候，要使用Large_pool這個記憶體結構來做磁片I/O暫存器
　　java_pool:
　　為java procedure預備的記憶體區域,如果沒有使用java proc,java_pool不是必須的
　　8 Oracle系統進程主要有哪些，作用是什麼
　　資料寫進程(dbwr)：負責將更改的資料從資料庫緩衝區快取記憶體寫入資料檔案
　　日誌寫進程(lgwr)：將重做日誌緩衝區中的更改寫入線上重做日誌檔
　　系統監控(smon) ：檢查資料庫的一致性如有必要還會在資料庫打開時啟動資料庫的恢復
　　進程監控(pmon) ：負責在一個Oracle 進程失敗時清理資源
　　檢查點進程(chpt)：負責在每當緩衝區快取記憶體中的更改永久地記錄在資料庫中時,更新控制檔和資料檔案中的資料庫狀態資訊。
　　歸檔進程(arcn) ：在每次日誌切換時把已滿的日誌組進行備份或歸檔
　　作業調度器(cjq) :負責將調度與執行系統中已定義好的job,完成一些預定義的工作.
　　恢復進程(reco) :保證分散式事務的一致性,在分散式事務中,要麼同時commit,要麼同時rollback;
備份恢復類
　　1:備份如何分類
　　邏輯備份：exp/imp
　　物理備份：
　　RMAN備份
　　full backup/incremental backup(累積/差異)
　　熱備份:alter tablespace begin/end backup;
　　冷備份:脫機備份(database shutdown)
　　2:歸檔是什麼含義
　　關於歸檔日誌：Oracle要將填滿的線上日誌檔組歸檔時,則要建立歸檔日誌(archived redo log)。
　　其對資料庫備份和恢復有下列用處：
　　資料庫後備以及線上和歸檔日誌檔，在作業系統和磁片故障中可保證全部提交的事物可被恢復。
　　在資料庫打開和正常系統使用下，如果歸檔日誌是永久保存，線上後備可以進行和使用。
　　資料庫可運行在兩種不同方式下：
　　NOARCHIVELOG方式或ARCHIVELOG 方式資料庫在NOARCHIVELOG方式下使用時，不能進行線上日誌的歸檔,如果資料庫在ARCHIVELOG方式下運行，可實施線上日誌的歸檔。3:如果一個表在2004-08-04 10:30:00 被drop，在有完善的歸檔和備份的情況下，如何恢復?
　　手工拷貝回所有備份的資料檔案
　　startup mount;
　　sql alter database recover automatic until time '2004-08-04:10:30:00';
　　alter database open resetlogs;

　　4:rman是什麼,有何特點?
　　RMAN(Recovery Manager)是DBA的一個重要工具，用於備份、還原和恢復oracle資料庫, RMAN 可以用來備份和恢復資料庫檔、歸檔日誌、控制檔、系統參數檔,也可以用來執行完全或不完全的資料庫恢復。RMAN有三種不同的用戶介面：
　　COMMAND LINE方式、GUI 方式(集成在OEM 中的備份管理器)、API 方式(用於集成到第三方的備份軟體中)。
　　具有如下特點：
　　1)功能類似物理備份，但比物理備份強大N倍;
　　2)可以壓縮空塊;
　　3)可以在塊水準上實現增量;
　　4)可以把備份的輸出打包成備份集，也可以按固定大小分割備份集;
　　5)備份與恢復的過程可以自動管理;
　　6)可以使用腳本(存在Recovery catalog 中)
　　7)可以做壞塊監測
　　5:standby的特點
　　備用資料庫(standby database)：ORACLE推出的一種高可用性(HIGH AVAILABLE)資料庫方案，
　　在主節點與備用節點間通過日誌同步來保證資料的同步，備用節點作為主節點的備份
　　可以實現快速切換與災難性恢復,從920開始，還開始支援物理與邏輯備用伺服器。
　　9i中的三種資料保護模式分別是：
　　1)、MAXIMIZE PROTECTION ：最大資料保護與無數據分歧，LGWR將同時傳送到備用節點，
　　在主節點事務確認之前，備用節點也必須完全收到日誌資料。如果網路不好，引起LGWR不能傳送資料，將引起嚴重的性能問題，導致主節點DOWN機。
　　2)、MAXIMIZE AVAILABILITY ：無數據丟失模式，允許資料分歧，允許非同步傳送。
　　正常情況下運行在最大保護模式，在主節點與備用節點的網路斷開或連接不正常時，自動切換到最大性能模式，
　　主節點的操作還是可以繼續的。在網路不好的情況下有較大的性能影響。
　　3)、MAXIMIZE PERFORMANCE：這種模式應當可以說是從8i繼承過來的備用伺服器模式，非同步傳送，
　　無數據同步檢查，可能丟失資料，但是能獲得主節點的最大性能。9i在配置DATA GUARD的時候默認就是MAXIMIZE PERFORMANCE
　　6:對於一個要求恢復時間比較短的系統(資料庫50G,每天歸檔5G)，你如何設計備份策略
　　rman/每月一號 level 0 每週末/週三 level 1 其他每天level 2
四：系統管理類
　　對於一個存在系統性能的系統，說出你的診斷處理思路
　　1 做statspack收集系統相關資訊
　　瞭解系統大致情況/確定是否存在參數設置不合適的地方/查看top 5 event/查看top sql等
　　2 查v$system_event/v$session_event/v$session_wait
　　從v$system_event開始,確定需要什麼資源(db file sequential read)等
　　深入研究v$session_event,確定等待事件涉及的會話
　　從v$session_wait確定詳細的資源爭用情況(p1-p3的值:file_id/block_id/blocks等)
　　3 通過v$sql/v$sqltext/v$sqlarea表確定disk_reads、(buffer_gets/executions)值較大的SQL
　　2:列舉幾種診斷IO、CPU、性能狀況的方法
　　top/vmstat
　　statspack
　　sql_trace/tkprof
　　查v$system_event/v$session_event/v$session_wait
　　查v$sqlarea(disk_reads或buffer_gets/executions較大的SQL)
　　3:對statspack有何認識
　　StapSpack是Oracle公司提供的一個收集資料庫運行性能指標的套裝軟體，該套裝軟體從8i起，在9i、10g都有顯著的增強 。該套裝軟體的輔助表(存儲相關參數與收集的性能指標的表)由最初的25個增長到43個。收集級別參數由原來的3個(0、5、10)增加到5個(0、5、6、7、10)通過分析收集的性能指標，資料庫管理員可以詳細地瞭解資料庫目前的運行情況，對資料庫實例、等待事件、SQL等進行優化調整。利用statspack收集的snapshot,可以統計製作資料庫的各種性能指標的統計趨勢圖表。
　　4:如果系統現在需要在一個很大的表上創建一個索引，你會考慮那些因素，如何做以儘量減小對應用的影響
　　在系統比較空閒時;nologging選項(如果有dataguard則不可以使用nologging)大的sort_ared_size或pga_aggregate_target較大
　　5:對raid1+0 和raid5有何認識
　　RAID 10(或稱RAID 1+0)與RAID 0+1不同，它是用硬碟驅動器先組成RAID 1陣列，然後在RAID 1陣列之間再組成RAID 0陣列。RAID 10模式同RAID 0+1模式一樣具有良好的資料傳輸性能，但卻比RAID 0+1具有更高的可靠性。RAID 10陣列的實際容量為M×n/2，磁片利用率為50%。RAID 10也需要至少4個硬碟驅動器構成，因而價格昂貴。 RAID 10的可靠性同RAID 1一樣，但由於RAID 10硬碟驅動器之間有資料分割，因而資料傳輸性能優良。RAID 5與RAID 3很相似，不同之處在於RAID 5的奇偶校驗資訊也同資料一樣被分割保存到所有的硬碟驅動器，而不是寫入一個指定的硬碟驅動器，從而消除了單個奇偶校驗硬碟驅動器的瓶頸問題。RAID 5磁碟陣列的性能比RAID 3有所提高，但仍然需要至少3塊硬碟驅動器。其實際容量為M×(n-1)，磁片利用率為(n-1)/n 。

with admin option 和 with grant option

在賦予user 許可權或者role 時，常常會用到with admin option 和with grant option,
而在使用中，可能會很容易出現混淆的情況，現把他們的相同點和不同點總結如下：
相同點：
- 兩個都可以既可以賦予user 許可權時使用，也可以在賦予role 時用
GRANT CREATE SESSION TO emi WITH ADMIN OPTION;
GRANT CREATE SESSION TO role WITH ADMIN OPTION;
GRANT role1 to role2 WITH ADMIN OPTION;
GRANT select ON customers1 TO bob WITH GRANT OPTION;
GRANT select ON customers1 TO hr_manager(role) WITH GRANT OPTION;

- 兩個受賦予者，都可以把許可權或者role 再賦予other users
- 兩個option 都可以對DBA 和APP ADMIN 管理帶來方便性，但同時，都帶來不安全的因素
不同點：
- with admin option 只能在賦予 system privilege 的時使用
- with grant option 只能在賦予 object privilege 的時使用
- 移除帶有admin option 的system privileges 時，連帶的許可權將保留
例如：
1. DBA 給了CREATE TABLE 系統許可權給JEFF WITH ADMIN OPTION
2. JEFF CREATES TABLE
3. JEFF grants the CREATE TABLE 系統許可權給EMI
4. EMI CREATES A table
5. DBA 撤銷CREATE TABLE 系統許可權從JEFF
結果：
JEFF'S TABLE 依然存在，但不能建立新的TABLE 了
EMI'S TABLE 依然存在，他還保留著CREATE TABLE 系統許可權。
- 移除帶有grant option 的object privileges 時，連帶的許可權也將移除
例如：
1. JEFF 給了SELECT object privileges 在EMP 上 WITH ADMIN OPTION
2. JEFF 給了SELECT 許可權在EMP 上 TO EMI
3. 後來，移除JEFF的SELECT 許可權

Oracle 10gR2 with Ultra serach option

Environment:
1. 10gR2 software installed.
2. Patch p6810189_10204_Win32 applied.
3. Database created.
4. 10201_companion_win32 installed.
Using DBCA to add database option "Ultra Search" , but failed with "ORA-06575: 套裝程式或函數 WK$IS_ADMIN 處於無效的狀態".When try to recompile WKSYS.WK$USER function , the error message "PLS-00920: parameter plsql_native_library_dir is not set" return.

Cause:
Database parameter :
plsql_code_type = NATIVE
plsql_compiler_flags = NATIVE, NON_DEBUG

Solution:
Must changing the database parameter as:
plsql_code_type = INTERPRETED
plsql_compiler_flags = INTERPRETED, NON_DEBUG

oracle 10gR2 connect role

Oracle 10gR2 版本的 Connect role , 除了"Create Session", 其於已不包含於 Connect role 中, 需另外grant 權限. 如原有的 Alter Session Create Cluster Create Synonym Create Database Link Create Table Create Sequence Create View

v$session status column display with "SNIPED"

From v$session , found status column "SNIPED".
What happened ?

> A status of SNIPED in v$session for a session indicates that the session has been terminated by Oracle, because it exceeded the IDLE_TIME set in the profile assigned to the use.

1. Find the user_name in v$session that status is SNIPED
2. select * from dba_users , check "SNIPED" status user's profile column .
3. select * from dba_profiles , check the "IDLE_TIME" limit value.
4. > show parameter resource_limit , if the value is true , then resource limit will use.

Set the IDLE_TIME in profile can release session holding resource (DB object lock), but OS resource will not release (memory resource).

Oracle.exe CPU utilization 100% in Windows

You may find oracle.exe process consume 100% CPU in Windows platform.
1.
Download qslice.exe from here
Find oracle.exe detail threads and who use 100% CPU , frind TID ffrom qslice.exe console.
2. TID is a HEX number , need to translate it as DEC , you can translate through this web site. http://www.parkenet.com/apl/HexDecConverter.html
3. Using following sql statement to find thread information.
select p.spid "Thread ID", b.name "Background Process", s.username "User Name", s.osuser "OS User", s.status "STATUS", s.sid "Session ID", s.serial# "Serial No.", s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr
and b.paddr(+) = p.addr
and p.spid= -- TID number

4. Using following command to find thread doing what.
select s2.sql_text
from v$process p,v$session s1,v$sqlarea s2
where p.addr = s1.paddr
and s1.SQL_HASH_VALUE = s2.HASH_VALUE
and p.spid= -- TID number
5. Terminate problematic sesion.
alter system kill session 'SID,#Serial NUM';

SQL Server Replication Data Type & Oracle Data Type

Oracle Data Type Definitions

1. The following table lists the Oracle data type definitions.

Oracle data type	Definition
CHAR	<=2000
DATE	Jan 1, 4712 B.C. to Dec 31, 4712 A.D.
DECIMAL	Same as Number
FLOAT	Same as Number
INTEGER	Same as Number
LONG	<=2GB
LONG RAW	Raw data; Same as Long
LONG VARCHAR	Same as Long
NUMBER	1.0E-130 to 9.99..E125
SMALLINT	Same as Number
RAW	Raw Binary Data <=255 bytes
ROWID	Unique Value
VARCHAR2	<=4000 bytes
VARCHAR	Same as Varchar2
BLOB	Binary Large Object <=4GB
COB	Char Large Object <=4GB
NCLOB	Same as Clob (for multibyte)
BFILE	Pointer to binary operating file

2. The following table maps data types for replication to Oracle Subscribers.

SQL Server 2000 data type	Oracle data type
bigint	NUMBER
binary	LONG RAW NOT NULL
bit	NUMBER (1, 0)
char	VARCHAR2 (900) NOT NULL
datetime	DATE
decimal	NUMBER (255, 3) NOT NULL
float	FLOAT NOT NULL
image	LONG RAW
int	NUMBER (255, 3) NOT NULL
money	NUMBER (255, 3) NOT NULL
nchar	VARCHAR2 (2000) NOT NULL
ntext	LONG
numeric	NUMBER (255, 3) NOT NULL
nvarchar	VARCHAR2 (2000) NOT NULL
real	FLOAT NOT NULL
smallint	NUMBER (255, 3) NOT NULL
smalldatetime	DATE NOT NULL
smallmoney	NUMBER (255, 3) NOT NULL
sql_variant	LONG
sysname	CHAR(255)
text	LONG
timestamp	RAW (255)
tinyint	NUMBER (255, 3) NOT NULL

3. The following table shows the data type mappings that are used when data is replicated to a Subscriber running Oracle.

SQL Server 2005 Data Type	Oracle Data Type
BIGINT	NUMBER(19,0)
BINARY(1-2000)	RAW(1-2000)
BINARY(2001-8000)	BLOB
BIT	NUMBER(1)
CHAR(1-2000)	CHAR(1-2000)
CHAR(2001-4000)	VARCHAR2(2001-4000)
CHAR(4001-8000)	CLOB
DATETIME	DATE
DECIMAL(1-38, 0-38)	NUMBER(1-38, 0-38)
DOUBLE PRECISION	FLOAT
FLOAT	FLOAT
IMAGE	BLOB
INT	NUMBER(10,0)
MONEY	NUMBER(19,4)
NCHAR(1-1000)	CHAR(1-1000)
NCHAR(1001-4000)	NCLOB
NTEXT	NCLOB
NUMERIC(1-38, 0-38)	NUMBER(1-38, 0-38)
NVARCHAR(1-1000)	VARCHAR2(1-2000)
NVARCHAR(1001-4000)	NCLOB
NVARCHAR(MAX)	NCLOB
REAL	REAL
SMALLDATETIME	DATE
SMALLINT	NUMBER(5,0)
SMALLMONEY	NUMBER(10,4)
SQL_VARIANT	N/A
SYSNAME	VARCHAR2(128)
TEXT	CLOB
TIMESTAMP	RAW(8)
TINYINT	NUMBER(3,0)
UNIQUEIDENTIFIER	CHAR(38)
VARBINARY(1-2000)	RAW(1-2000)
VARBINARY(2001-8000)	BLOB
VARCHAR(1-4000)	VARCHAR2(1-4000)
VARCHAR(4001-8000)	CLOB
VARBINARY(MAX)	BLOB
VARCHAR(MAX)	CLOB
XML	NCLOB

Substr 與 Instr

Example : String "one two three/four/five/"
Need to get before first "/" word->"three"

substr : get part of string by your parameter.
instr : return the position that match you input parameter.
instr('abcb','b',-1) -> search the 'b' location from the end to start , return from start to end position.

select trim(substr(substr('one two three/four/five/',1,instr('one two three/four/five/','/')-1),instr(substr('one two three/four/five/',1,instr('one two three/four/five/','/')-1),' ',-1))) from dual;

TEMP tablspace and tempfile

When backup,restore,revocer database , tempfile can be excluded .
TEMP tablespace alway in no-logging mode and no checkpoints.

When move a database from one A machine to B machine with difference disk label and file directory, tempfile can be bypass to copy from A machine to B machine.

The solution is to drop the tempfile and recreate it, while keeping the TEMP tablespace:

SQL> alter database tempfile 'D:\oracle\oradata\Amar\temp01.dbf' drop including datafiles;

Database altered.

Physically delete that file from the path.

SQL> alter tablespace temp add tempfile 'D:\oracle\oradata\Amar\temp01.dbf' size 100 m;

Online hot backup

Using OS Level copy command to backup oracle database
1. Make sure database in archive log mode.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ora/ora920/archlog/PROD
Oldest online log sequence 41288
Next log sequence to archive 41294
Current log sequence 41294
SQL>

If not archive log mode , modify init parameter file , add following parameter
log_archive_start = true
log_archive_dest_1 = "location=/ora/ora920/archlog/PROD"
log_archive_format = PROD_%t_%s.arc

2. Bring tablespace one by one to backup mode(Exclude temp tablespace).
alter tablespace xxx begin backup;

or
bring whole database to backup mode.
alter database begin backup;

3. Copy all datafile to backup disk.

4. Bring tablespace one by one to end-backup(Exclude temp tablespace).
alter tablespace xxx end backup;

or
bring whole database to end-backup mode.
alter database end backup;

5. Generating after end backup redo log.
alter system archive log current;
or
alter system switch logfile;

6. Copy all archived logs that generated during backup period to backup disk.

Windows : CONNECT AS SYSDBA Without a Password

Windows 上遇到 ORA-01031 的錯誤, 無法使用 sqlplus " / as sysdba" 登入 ,
WIN: OS Authentication - CONNECT AS SYSDBA Without a Password
檢查
1. sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NTS) , 確認有這一行

2.Windows local 群組, 建立ㄧ個 group ORA_DBA , 把你現在登入的使用者加到此一群組,即可

Physical Standby DB

1. Primary DB 參數:
#DB_NAME : Primary 與 Standby 都設為相同

#DB_UNIQUE_NAME : Primary 與 Standby 各不同名稱

#LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRI,PRIBK),SEND,RECEIVE' : DG_CONFIG 中的值須為 Primary 與 Standby DB 所設定的 DB_UNIQUE_NAME, SEND 表示傳 Redo log 到遠端 , RECEIVE 表示收遠端的 Archived log.

#LOG_ARCHIVE_DEST_1='LOCATION=d:\ARCH\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRI' : 代表 Archived log 產生到d:\ARCH 目錄

#LOG_ARCHIVE_DEST_2='SERVICE=PRIBK ARCH VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIBK OPTIONAL REOPEN=300' : 代表 Archived log 產生到 tnsnames.ora 的 PRIBK, 利用 Archiver, 只限定 Valid 為 Archiving redo log 時, 且產生的 DB 是 PRIMARY_ROLE 的狀況下 , REOPEN 表示 300秒後 archiver processes (ARCn) or the log writer process (LGWR) should try again to access a previously failed destination , OPTIONAL 表示不需等到傳完redo data 即將 online redo log 變成可reuse 狀態.

#LOG_ARCHIVE_DEST_STATE_1=ENABLE : ENABLE 代表允許redo transport services 傳送 redo data 到指定目的地

#LOG_ARCHIVE_DEST_STATE_2=ENABLE : 若為 DEFER 表示不傳送到此.

#REMOTE_LOGIN_PASSWORDFILE=SHARED : NONE 表示只能用 OS 認證方式登入 , SHARED 可表示利用 orapwxxx.ora password 來記錄與允許遠端登入成 SYSDBA

#LOG_ARCHIVE_FORMAT=%t_%s_%r.arc : %s log sequence number ,%t thread number , %r resetlogs ID

#LOG_ARCHIVE_MAX_PROCESSES : 預設為 4 個

#FAL_SERVER='PRIBK' : 指定Standby , FAL (fetch archive log) server for a standby database

#FAL_CLIENT='PRI'

#DB_FILE_NAME_CONVERT='c:\pri\t1\','d:\pribk\t1' : 表示PRI 的 datafile 路徑為/pri/t1/ , 於PRIBK 的路徑為 /pribk/t1/

#LOG_FILE_NAME_CONVERT='c:\pri\t1\','d:\pribk\t1' : 表示PRI 的 log file 路徑為/pri/t1/ , 於PRIBK 的路徑為 /pribk/t1

#STANDBY_ARCHIVE_DEST='d:\ARCH\'

#STANDBY_FILE_MANAGEMENT=AUTO : AUTO so when datafiles are added to or dropped from the primary database, corresponding changes are made automatically to the standby database.



2. Standby DB 參數:
#CONTROL_FILES='c:\PIRBK\bak.ctl' : 於 Primary DB 所產生出來的 Standby control file

#DB_NAME : 與 Primary DB 設為相同

#DB_UNIQUE_NAME=PRIBK : 與 Primary DB 設為不相同

#FAL_SERVER='PRI' : 指定 Primary DB , FAL (fetch archive log) server for a standby database

#FAL_CLIENT='PRIBK'

#STANDBY_ARCHIVE_DEST='d:\ARCH\'

#STANDBY_FILE_MANAGEMENT=AUTO

#LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

#REMOTE_LOGIN_PASSWORDFILE=SHARED

#LOG_ARCHIVE_DEST_1='LOCATION=d:\ARCH\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIBK'

#LOG_ARCHIVE_DEST_2='SERVICE=PRI ARCH VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIBK OPTIONAL REOPEN=300'

#LOG_ARCHIVE_DEST_STATE_1=ENABLE

#LOG_ARCHIVE_DEST_STATE_2=ENABLE


3. 建立
Primary 上
# Primary 資料庫上的 tnsnames.ora ,將Primary 與 Standby 加入

# 確認 Archive log mode 有啟動

# 確認 password file 有產生

# alter database force logging;

# backup databse

# shutdown database , startup mount
alter databse create standby controlfile as 'd:\bk\pribk.ctl'

Standby 上
#確認 Listener (Standby) , tnsnames.ora (Primary 與Standby 皆需有) , Control file, password file

# 確認 standby db 的 init parameter file 位置

# startup Standby DB

# Start apply log
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

4. Active Standby DB 步驟

# Stop Primary DB , 登入 Standby DB

# sqlplus " / as sysdba"

# alter database recover managed standby database finish skip standby logfile; (若是採用 LGWR 方式則skip standby logfile 可去掉)

# alter database commit to switchover to primary;

# shutdown immediate

4.1 確認準備好的 initPRI.ora parameter file存在 , 內容重點的
INSTANCE_NAME=PRI
SERVICE_NAMES=PRI

# 確認password file 存在(ORAPWDPRI.ora)., listener.ora 有 listen 此 Instance, tnsnames.ora 需調整

4.2 增加 Windows 上 Oracle Service .
oradim -new -sid PRI -startmode Auto -SHUTMODE immediate

4.3 set ORACLE_SID=PRI

sqlplus " / as sysdba"

shutdown immediate

startup pfile='D:\oracle\as\database\INITPRI.ORA'