oracle數據庫面試題

2017年oracle數據庫面試題精選

　　Oracle公司針對職業教育市場在全球推廣的項目，其以低廉的成本給這部分人群提供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). 顯示游標是用cursor...is命令定義的游標，它可以對查詢語句(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. 導入/導出的是二進制的數據， 2.plsql導入/導出的是sql語句的文本文件

　　5. Oracle中有哪幾種文件?

　　數據文件(一般后綴為.dbf或者.ora)，日志文件(后綴名.log)，控制文件(后綴名為.ctl)

　　6. 怎樣優化Oracle數據庫，有幾種方式?

　　個人理解，數據庫性能最關鍵的因素在于IO，因為操作內存是快速的，但是讀寫磁盤是速度很慢的，優化數據庫最關鍵的問題在于減少磁盤的IO，就個人理解應該分為物理的和邏輯的優化， 物理的是指oracle產品本身的一些優化，邏輯優化是指應用程序級別的優化

　　物理優化的一些原則：

　　1). 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 b.owner,b.object_name,a.session_id,a.locked_mode

　　from v$locked_object a,dba_objects b

　　where b.object_id = a.object_id;

　　select b.username,b.sid,b.serial#,logon_time

　　from v$locked_object a,v$session b

　　where a.session_id = b.sid order by b.logon_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_OUTPUT.put_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_OUTPUT.put_line("ins當月記錄="||SQL%rowcount||"條");

　　--遍歷游標處理后更新到表。遍歷游標有幾種方法，用for語句是其中比較直觀的一種。

　　FOR rec IN cur_1 LOOP

　　UPDATE xxxxxxxxxxx_T

　　SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn

　　WHERE area_code = rec.area_code

　　AND CMCODE = rec.CMCODE

　　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;

　　21. 怎樣創建一個一個索引,索引使用的原則,有什么優點和缺點

　　創建標準索引：

　　CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空間名;

　　創建唯一索引:

　　CREATE unique INDEX 索引名 ON 表名 (列名) TABLESPACE 表空間名;

　　創建組合索引:

　　CREATE INDEX 索引名 ON 表名 (列名1,列名2) TABLESPACE 表空間名;

　　創建反向鍵索引:

　　CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空間名;

　　索引使用原則：

　　索引字段建議建立NOT NULL約束

　　經常與其他表進行連接的表，在連接字段上應該建立索引;

　　經常出現在Where子句中的字段且過濾性很強的，特別是大表的字段，應該建立索引;

　　可選擇性高的關鍵字 ，應該建立索引;

　　可選擇性低的關鍵字，但數據的值分布差異很大時，選擇性數據比較少時仍然可以利用索引提高效率

　　復合索引的建立需要進行仔細分析;盡量考慮用單字段索引代替：

　　A、正確選擇復合索引中的第一個字段，一般是選擇性較好的且在where子句中常用的字段上;

　　B、復合索引的幾個字段經常同時以AND方式出現在Where子句中可以建立復合索引;否則單字段索引;

　　C、如果復合索引中包含的字段經常單獨出現在Where子句中，則分解為多個單字段索引;

　　D、如果復合索引所包含的字段超過3個，那么仔細考慮其必要性，考慮減少復合的字段;

　　E、如果既有單字段索引，又有這幾個字段上的復合索引，一般可以刪除復合索引;

　　頻繁DML的表，不要建立太多的索引;

　　不要將那些頻繁修改的列作為索引列;

　　索引的優缺點：

　　有點：

　　1. 創建唯一性索引，保證數據庫表中每一行數據的唯一性

　　2. 大大加快數據的檢索速度，這也是創建索引的最主要的原因

　　3. 加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

　　4. 在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。

　　缺點：

　　1. 索引創建在表上，不能創建在視圖上

　　2. 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加

　　3. 索引需要占物理空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。

【oracle數據庫面試題】相關文章：

oracle數據庫開發面試題201708-07

Oracle數據庫SQLPLUS介紹10-09

ORACLE數據庫碎片的整理08-18

Oracle數據庫語句大全10-08

oracle數據庫基本語句08-21

Oracle數據庫開發技巧10-04

Oracle數據庫認證層次08-29

Oracle數據庫的基本介紹09-04

Oracle數據庫SELECT語句10-25

最強的oracle面試題積累06-11