Oracle的技術廣泛應用於各行各業，其中電信、電力、金融、政府及大量製造業都需要Oracle技術人才。下面是小編整理的關於oracle資料庫面試題，希望大家認真閱讀!
1. Oracle跟SQL Server 2005的區別?
巨集觀上：
1). 最大的區別在於平臺，oracle可以執行在不同的平臺上，sql server只能執行在windows平臺上，由於windows平臺的穩定性和安全性影響了sql server的穩定性和安全性
2). oracle使用的指令碼語言為PL-SQL，而sql server使用的指令碼為T-SQL
微觀上： 從資料型別,資料庫的結構等等回答
2. 如何使用Oracle的遊標?
1). oracle中的遊標分為顯示遊標和隱式遊標
2). 顯示遊標是用命令定義的遊標，它可以對查詢語句(select)返回的多條記錄進行處理;隱式遊標是在執行插入 (insert)、刪除(delete)、修改(update)和返回單條記錄的查詢(select)語句時由PL/SQL自動定義的。
3). 顯式遊標的操作：開啟遊標、操作遊標、關閉遊標;PL/SQL隱式地開啟SQL遊標，並在它內部處理SQL語句，然後關閉它
3. Oracle中function和procedure的區別?
1). 可以理解函式是儲存過程的一種
2). 函式可以沒有引數,但是一定需要一個返回值，儲存過程可以沒有引數,不需要返回值
3). 函式return返回值沒有返回引數模式，儲存過程通過out引數返回值, 如果需要返回多個引數則建議使用儲存過程
4). 在sql資料操縱語句中只能呼叫函式而不能呼叫儲存過程
4. Oracle的匯入匯出有幾種方式，有何區別?
1). 使用oracle工具 exp/imp
2). 使用plsql相關工具
方法1. 匯入/匯出的是二進位制的資料， l匯入/匯出的是sql語句的文字檔案
5. Oracle中有哪幾種檔案?
資料檔案(一般字尾為或者)，日誌檔案(字尾名)，控制檔案(字尾名為)
6. 怎樣優化Oracle資料庫，有幾種方式?
個人理解，資料庫效能最關鍵的因素在於IO，因為操作記憶體是快速的，但是讀寫磁碟是速度很慢的，優化資料庫最關鍵的問題在於減少磁碟的IO，就個人理解應該分為物理的和邏輯的優化， 物理的是指oracle產品本身的一些優化，邏輯優化是指應用程式級別的優化
物理優化的一些原則：
2). 使用合適的優化器
3). 合理配置oracle例項引數
4). 建立合適的索引(減少IO)
5). 將索引資料和表資料分開在不同的表空間上(降低IO衝突)
6). 建立表分割槽，將資料分別儲存在不同的分割槽上(以空間換取時間，減少IO)
邏輯上優化：
1). 可以對錶進行邏輯分割，如中國移動使用者表，可以根據手機尾數分成10個表，這樣對效能會有一定的作用
2). Sql語句使用佔位符語句，並且開發時候必須按照規定編寫sql語句(如全部大寫，全部小寫等)oracle解析語句後會放置到共享池中
如： select * from Emp where name=? 這個語句只會在共享池中有一條，而如果是字串的話，那就根據不同名字存在不同的語句，所以佔位符效率較好
3). 資料庫不僅僅是一個儲存資料的地方，同樣是一個程式設計的地方，一些耗時的操作，可以通過儲存過程等在使用者較少的情況下執行，從而錯開系統使用的高峰時間，提高資料庫效能
4). 儘量不使用*號，如select * from Emp，因為要轉化為具體的列名是要查資料字典，比較耗時
5). 選擇有效的表名
對於多表連線查詢，可能oracle的優化器並不會優化到這個程度， oracle 中多表查詢是根據FROM字句從右到左的資料進行的，那麼最好右邊的表(也就是基礎表)選擇資料較少的表，這樣排序更快速，如果有link表(多對多中間表)，那麼將link表放最右邊作為基礎表，在預設情況下oracle會自動優化，但是如果配置了優化器的情況下，可能不會自動優化，所以平時最好能按照這個方式編寫sql
6). Where字句 規則
Oracle 中Where字句時從右往左處理的，表之間的連線寫在其他條件之前，能過濾掉非常多的資料的條件，放在where的末尾， 另外!=符號比較的列將不使用索引，列經過了計算(如變大寫等)不會使用索引(需要建立起函式)， is null、is not null等優化器不會使用索引
7). 使用Exits Not Exits 替代 In Not in
8). 合理使用事務，合理設定事務隔離性
資料庫的資料操作比較消耗資料庫資源的，儘量使用批量處理，以降低事務操作次數
7. Oracle中字串用什麼符號連結?
Oracle中使用 || 這個符號連線字串 如 ‘abc’ || ‘d’
8. Oracle分割槽是怎樣優化資料庫的?
Oracle的分割槽可以分為：列表分割槽、範圍分割槽、雜湊分割槽、複合分割槽。
1). 增強可用性：如果表的一個分割槽由於系統故障而不能使用，表的其餘好的分割槽仍可以使用;
2). 減少關閉時間：如果系統故障隻影響表的一部份分割槽，那麼只有這部份分割槽需要修復，可能比整個大表修復花的時間更少;
3). 維護輕鬆：如果需要得建表，獨產管理每個公區比管理單個大表要輕鬆得多;
4). 均衡I/O：可以把表的不同分割槽分配到不同的磁碟來平衡I/O改善效能;
5). 改善效能：對大表的查詢、增加、修改等操作可以分解到表的不同分割槽來並行執行，可使執行速度更快
6). 分割槽對使用者透明，終端使用者感覺不到分割槽的存在。
9. Oracle是怎樣分頁的?
Oracle中使用rownum來進行分頁, 這個是效率最好的分頁方法，hibernate也是使用rownum來進行oralce分頁的
select * from
( select rownum r,a from tabName where rownum <= 20 )
where r > 10
10. Oralce怎樣儲存檔案，能夠儲存哪些檔案?
Oracle 能儲存 clob、nclob、 blob、 bfile
Clob 可變長度的字元型資料，也就是其他資料庫中提到的文字型資料型別
Nclob 可變字元型別的資料，不過其儲存的是Unicode字符集的字元資料
Blob 可變長度的二進位制資料
Bfile 資料庫外面儲存的可變二進位制資料
11. Oracle中使用了索引的列，對該列進行where條件查詢、分組、排序、使用聚集函式，哪些用到了索引?
均會使用索引， 值得注意的是複合索引(如在列A和列B上建立的索引)可能會有不同情況
12. 資料庫怎樣實現每隔30分鐘備份一次?
通過作業系統的定時任務呼叫指令碼匯出資料庫
13. Oracle中where條件查詢和排序的效能比較?
Order by使用索引的條件極為嚴格，只有滿足如下情況才可以使用索引，
1). order by中的列必須包含相同的索引並且索引順序和排序順序一致
2). 不能有null值的列
所以排序的效能往往並不高，所以建議儘量避免order by
14. 解釋冷備份和熱備份的不同點以及各自的優點?
冷備份發生在資料庫已經正常關閉的情況下，將關鍵性檔案拷貝到另外位置的一種說法
熱備份是在資料庫執行的情況下，採用歸檔方式備份資料的方法
冷備的優缺點：
1).是非常快速的.備份方法(只需拷貝檔案)
2).容易歸檔(簡單拷貝即可)
3).容易恢復到某個時間點上(只需將檔案再拷貝回去)
4).能與歸檔方法相結合，作資料庫“最新狀態”的恢復。
5).低度維護，高度安全。
冷備份不足：
1).單獨使用時，只能提供到“某一時間點上”的恢復。
2).在實施備份的全過程中，資料庫必須要作備份而不能作其它工作。也就是說，在冷備份過程中，資料庫必須是關閉狀態。
3).若磁碟空間有限，只能拷貝到磁帶等其它外部儲存裝置上，速度會很慢。
4).不能按表或按使用者恢復。
熱備的優缺點
1).可在表空間或資料檔案級備份，備份時間短。
2).備份時資料庫仍可使用。
3).可達到秒級恢復(恢復到某一時間點上)。
4).可對幾乎所有資料庫實體作恢復。
5).恢復是快速的，在大多數情況下在資料庫仍工作時恢復。
熱備份的不足是：
1).不能出錯，否則後果嚴重。
2).若熱備份不成功，所得結果不可用於時間點的恢復。
3).因難於維護，所以要特別仔細小心，不允許“以失敗而告終”。
15. 解釋data block , extent 和 segment的區別?
data block 資料塊，是oracle最小的邏輯單位，通常oracle從磁碟讀寫的就是塊
extent 區，是由若干個相鄰的block組成
segment段，是有一組區組成
tablespace表空間，資料庫中資料邏輯儲存的地方，一個tablespace可以包含多個數據檔案
16. 比較truncate和delete命令 ?
1). Truncate 和delete都可以將資料實體刪掉，truncate 的操作並不記錄到 rollback日誌，所以操作速度較快，但同時這個資料不能恢復
2). Delete操作不騰出表空間的空間
3). Truncate 不能對檢視等進行刪除
4). Truncate是資料定義語言(DDL)，而delete是資料操縱語言(DML)
17. 解釋什麼是死鎖，如何解決Oracle中的死鎖?
簡言之就是存在加了鎖而沒有解鎖，可能是使用鎖沒有提交或者回滾事務，如果是表級鎖則不能操作表，客戶端處於等在狀態，如果是行級鎖則不能操作鎖定行
解決辦法：
1). 查找出被鎖的表
select r,ct_name,ion_id,ed_mode
from v$locked_object a,dba_objects b
where ct_id = ct_id;
select name,,al#,logon_time
from v$locked_object a,v$session b
where ion_id = order by n_time;
2). 殺程序中的會話
alter system kill session "sid,serial#";
18. 簡述oracle中 dml、ddl、dcl的使用
Dml 資料操縱語言，如select、update、delete，insert
Ddl 資料定義語言，如create table 、drop table 等等
Dcl 資料控制語言， 如 commit、 rollback、grant、 invoke等
19. 說說oracle中的經常使用到得函式
Length 長度、 lower 小寫、upper 大寫， to_date 轉化日期， to_char轉化字元
Ltrim 去左邊空格、 rtrim去右邊空格，substr取字串，add_month增加或者減掉月份、to_number轉變為數字
20. 怎樣建立一個儲存過程, 遊標在儲存過程怎麼使用, 有什麼好處?
附：儲存過程的一般格式，遊標使用參考問題
1 .使用遊標可以執行多個不相關的操作.如果希望當產生了結果集後,對結果集中的資料進行多種不相關的資料操作
2. 使用遊標可以提供指令碼的可讀性
3. 使用遊標可以建立命令字串,使用遊標可以傳送表名,或者把變數傳送到引數中,以便建立可以執行的命令字串.
但是個人認為遊標操作效率不太高，並且使用時要特別小心，使用完後要及時關閉
儲存過程優缺點：
優點：
1. 儲存過程增強了SQL語言的功能和靈活性。儲存過程可以用流控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。
2. 可保證資料的安全性和完整性。
3. 通過儲存過程可以使沒有許可權的使用者在控制之下間接地存取資料庫，從而保證資料的安全。
通過儲存過程可以使相關的動作在一起發生，從而可以維護資料庫的完整性。
3. 再執行儲存過程前，資料庫已對其進行了語法和句法分析，並給出了優化執行方案。這種已經編譯好的過程可極大地改善SQL語句的效能。 由於執行SQL語句的大部分工作已經完成，所以儲存過程能以極快的速度執行。
4. 可以降低網路的通訊量, 不需要通過網路來傳送很多sql語句到資料庫伺服器了
5. 使體現企業規則的運算程式放入資料庫伺服器中，以便集中控制
當企業規則發生變化時在伺服器中改變儲存過程即可，無須修改任何應用程式。企業規則的特點是要經常變化，如果把體現企業規則的運算程式放入應用程式中，則當企業規則發生變化時，就需要修改應用程式工作量非常之大(修改、發行和安裝應用程式)。如果把體現企業規則的 運算放入儲存過程中，則當企業規則發生變化時，只要修改儲存過程就可以了，應用程式無須任何變化。
缺點：
1. 可移植性差
2. 佔用伺服器端多的資源，對伺服器造成很大的壓力
3. 可讀性和可維護性不好
Create [or replace] procedure 過程名字(引數 …)as
vs_ym_sn_end CHAR(6); --同期終止月份
CURSOR cur_1 IS --定義遊標(簡單的說就是一個可以遍歷的結果集)
SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,SUM(usd_amt)/10000 usd_amt_sn
FROM BGD_AREA_CM_M_BASE_T
WHERE ym >= vs_ym_sn_beg
AND ym <= vs_ym_sn_end
GROUP BY area_code,CMCODE;
BEGIN
--用輸入引數給變數賦初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS TO_DATE 等很常用的函式。
vs_ym_beg := SUBSTR(is_ym,1,6);
vs_ym_end := SUBSTR(is_ym,7,6);
vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,"yyyymm"), -12),"yyyymm");
vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,"yyyymm"), -12),"yyyymm");
--先刪除表中特定條件的資料。
DELETE FROM xxxxxxxxxxx_T WHERE ym = is_ym;
--然後用內建的DBMS_OUTPUT物件的put_line方法打印出影響的記錄行數，其中用到一個系統變數SQL%rowcount
DBMS__line("del上月記錄="||SQL%rowcount||"條");
INSERT INTO xxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt)
SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000
FROM BGD_AREA_CM_M_BASE_T
WHERE ym >= vs_ym_beg
AND ym <= vs_ym_end
GROUP BY area_code,CMCODE;
DBMS__line("ins當月記錄="||SQL%rowcount||"條");
--遍歷遊標處理後更新到表。遍歷遊標有幾種方法，用for語句是其中比較直觀的一種。
FOR rec IN cur_1 LOOP
UPDATE xxxxxxxxxxx_T
SET rmb_amt_sn = _amt_sn,usd_amt_sn = _amt_sn
WHERE area_code = _code
AND CMCODE = DE
AND ym = is_ym;
END LOOP;
COMMIT;
--錯誤處理部分。OTHERS表示除了宣告外的任意錯誤。SQLERRM是系統內建變數儲存了當前錯誤的詳細資訊。
EXCEPTION
WHEN OTHERS THEN
vs_msg := "ERROR IN xxxxxxxxxxx_p("||is_ym||"):"||SUBSTR(SQLERRM,1,500);
ROLLBACK;
--把當前錯誤記錄進日誌表。
INSERT INTO LOG_INFO(proc_name,error_info,op_date)
VALUES("xxxxxxxxxxx_p",vs_msg,SYSDATE);
COMMIT;
RETURN;
END;
