- oracle數據庫開(kāi)發(fā)面試題 推薦度：
- 相關(guān)推薦
2017年oracle數據庫面試題精選
Oracle公司針對職業(yè)教育市場(chǎng)在全球推廣的項目，其以低廉的成本給這部分人群提供Oracle技術(shù)培訓，經(jīng)過(guò)系統化的實(shí)訓，讓這部分人群能夠迅速掌握Oracle最新的核心技術(shù)，并能勝任企業(yè)大型數據庫管理、維護、開(kāi)發(fā)工作。下面是小編收集的oracle數據庫面試題，希望大家認真閱讀!
1. Oracle跟SQL Server 2005的區別?
宏觀(guān)上：
1). 最大的區別在于平臺，oracle可以運行在不同的平臺上，sql server只能運行在windows平臺上，由于windows平臺的穩定性和安全性影響了sql server的穩定性和安全性
2). oracle使用的腳本語(yǔ)言為PL-SQL，而sql server使用的腳本為T(mén)-SQL
微觀(guān)上： 從數據類(lèi)型,數據庫的結構等等回答
2. 如何使用Oracle的游標?
1). oracle中的游標分為顯示游標和隱式游標
2). 顯示游標是用cursor...is命令定義的游標，它可以對查詢(xún)語(yǔ)句(select)返回的多條記錄進(jìn)行處理;隱式游標是在執行插入 (insert)、刪除(delete)、修改(update)和返回單條記錄的查詢(xún)(select)語(yǔ)句時(shí)由PL/SQL自動(dòng)定義的。
3). 顯式游標的操作：打開(kāi)游標、操作游標、關(guān)閉游標;PL/SQL隱式地打開(kāi)SQL游標，并在它內部處理SQL語(yǔ)句，然后關(guān)閉它
3. Oracle中function和procedure的區別?
1). 可以理解函數是存儲過(guò)程的一種
2). 函數可以沒(méi)有參數,但是一定需要一個(gè)返回值，存儲過(guò)程可以沒(méi)有參數,不需要返回值
3). 函數return返回值沒(méi)有返回參數模式，存儲過(guò)程通過(guò)out參數返回值, 如果需要返回多個(gè)參數則建議使用存儲過(guò)程
4). 在sql數據操縱語(yǔ)句中只能調用函數而不能調用存儲過(guò)程
4. Oracle的導入導出有幾種方式，有何區別?
1). 使用oracle工具 exp/imp
2). 使用plsql相關(guān)工具
方法1. 導入/導出的是二進(jìn)制的數據， 2.plsql導入/導出的是sql語(yǔ)句的文本文件
5. Oracle中有哪幾種文件?
數據文件(一般后綴為.dbf或者.ora)，日志文件(后綴名.log)，控制文件(后綴名為.ctl)
6. 怎樣優(yōu)化Oracle數據庫，有幾種方式?
個(gè)人理解，數據庫性能最關(guān)鍵的因素在于IO，因為操作內存是快速的，但是讀寫(xiě)磁盤(pán)是速度很慢的，優(yōu)化數據庫最關(guān)鍵的問(wèn)題在于減少磁盤(pán)的IO，就個(gè)人理解應該分為物理的和邏輯的優(yōu)化， 物理的是指oracle產(chǎn)品本身的一些優(yōu)化，邏輯優(yōu)化是指應用程序級別的優(yōu)化
物理優(yōu)化的一些原則：
1). Oracle的運行環(huán)境(網(wǎng)絡(luò )，硬件等)
2). 使用合適的優(yōu)化器
3). 合理配置oracle實(shí)例參數
4). 建立合適的索引(減少I(mǎi)O)
5). 將索引數據和表數據分開(kāi)在不同的表空間上(降低IO沖突)
6). 建立表分區，將數據分別存儲在不同的分區上(以空間換取時(shí)間，減少I(mǎi)O)
邏輯上優(yōu)化：
1). 可以對表進(jìn)行邏輯分割，如中國移動(dòng)用戶(hù)表，可以根據手機尾數分成10個(gè)表，這樣對性能會(huì )有一定的作用
2). Sql語(yǔ)句使用占位符語(yǔ)句，并且開(kāi)發(fā)時(shí)候必須按照規定編寫(xiě)sql語(yǔ)句(如全部大寫(xiě)，全部小寫(xiě)等)oracle解析語(yǔ)句后會(huì )放置到共享池中
如： select * from Emp where name=? 這個(gè)語(yǔ)句只會(huì )在共享池中有一條，而如果是字符串的話(huà)，那就根據不同名字存在不同的語(yǔ)句，所以占位符效率較好
3). 數據庫不僅僅是一個(gè)存儲數據的地方，同樣是一個(gè)編程的地方，一些耗時(shí)的操作，可以通過(guò)存儲過(guò)程等在用戶(hù)較少的情況下執行，從而錯開(kāi)系統使用的高峰時(shí)間，提高數據庫性能
4). 盡量不使用*號，如select * from Emp，因為要轉化為具體的列名是要查數據字典，比較耗時(shí)
5). 選擇有效的表名
對于多表連接查詢(xún)，可能oracle的優(yōu)化器并不會(huì )優(yōu)化到這個(gè)程度， oracle 中多表查詢(xún)是根據FROM字句從右到左的數據進(jìn)行的，那么最好右邊的表(也就是基礎表)選擇數據較少的表，這樣排序更快速，如果有link表(多對多中間表)，那么將link表放最右邊作為基礎表，在默認情況下oracle會(huì )自動(dòng)優(yōu)化，但是如果配置了優(yōu)化器的情況下，可能不會(huì )自動(dòng)優(yōu)化，所以平時(shí)最好能按照這個(gè)方式編寫(xiě)sql
6). Where字句 規則
Oracle 中Where字句時(shí)從右往左處理的，表之間的連接寫(xiě)在其他條件之前，能過(guò)濾掉非常多的數據的條件，放在where的末尾， 另外!=符號比較的列將不使用索引，列經(jīng)過(guò)了計算(如變大寫(xiě)等)不會(huì )使用索引(需要建立起函數)， is null、is not null等優(yōu)化器不會(huì )使用索引
7). 使用Exits Not Exits 替代 In Not in
8). 合理使用事務(wù)，合理設置事務(wù)隔離性
數據庫的數據操作比較消耗數據庫資源的，盡量使用批量處理，以降低事務(wù)操作次數
7. Oracle中字符串用什么符號鏈接?
Oracle中使用 || 這個(gè)符號連接字符串 如 ‘abc’ || ‘d’
8. Oracle分區是怎樣優(yōu)化數據庫的?
Oracle的分區可以分為：列表分區、范圍分區、散列分區、復合分區。
1). 增強可用性：如果表的一個(gè)分區由于系統故障而不能使用，表的其余好的分區仍可以使用;
2). 減少關(guān)閉時(shí)間：如果系統故障只影響表的一部份分區，那么只有這部份分區需要修復，可能比整個(gè)大表修復花的時(shí)間更少;
3). 維護輕松：如果需要得建表，獨產(chǎn)管理每個(gè)公區比管理單個(gè)大表要輕松得多;
4). 均衡I/O：可以把表的不同分區分配到不同的磁盤(pán)來(lái)平衡I/O改善性能;
5). 改善性能：對大表的查詢(xún)、增加、修改等操作可以分解到表的不同分區來(lái)并行執行，可使運行速度更快
6). 分區對用戶(hù)透明，最終用戶(hù)感覺(jué)不到分區的存在。
9. Oracle是怎樣分頁(yè)的?
Oracle中使用rownum來(lái)進(jìn)行分頁(yè), 這個(gè)是效率最好的分頁(yè)方法，hibernate也是使用rownum來(lái)進(jìn)行oralce分頁(yè)的
select * from
( select rownum r,a from tabName where rownum <= 20 )
where r > 10
10. Oralce怎樣存儲文件，能夠存儲哪些文件?
Oracle 能存儲 clob、nclob、 blob、 bfile
Clob 可變長(cháng)度的字符型數據，也就是其他數據庫中提到的文本型數據類(lèi)型
Nclob 可變字符類(lèi)型的數據，不過(guò)其存儲的是Unicode字符集的字符數據
Blob 可變長(cháng)度的二進(jìn)制數據
Bfile 數據庫外面存儲的可變二進(jìn)制數據
11. Oracle中使用了索引的列，對該列進(jìn)行where條件查詢(xún)、分組、排序、使用聚集函數，哪些用到了索引?
均會(huì )使用索引， 值得注意的是復合索引(如在列A和列B上建立的索引)可能會(huì )有不同情況
12. 數據庫怎樣實(shí)現每隔30分鐘備份一次?
通過(guò)操作系統的定時(shí)任務(wù)調用腳本導出數據庫
13. Oracle中where條件查詢(xún)和排序的性能比較?
Order by使用索引的條件極為嚴格，只有滿(mǎn)足如下情況才可以使用索引，
1). order by中的列必須包含相同的索引并且索引順序和排序順序一致
2). 不能有null值的列
所以排序的性能往往并不高，所以建議盡量避免order by
14. 解釋冷備份和熱備份的不同點(diǎn)以及各自的優(yōu)點(diǎn)?
冷備份發(fā)生在數據庫已經(jīng)正常關(guān)閉的情況下，將關(guān)鍵性文件拷貝到另外位置的一種說(shuō)法
熱備份是在數據庫運行的情況下，采用歸檔方式備份數據的方法
冷備的優(yōu)缺點(diǎn)：
1).是非�？焖俚膫浞莘椒�(只需拷貝文件)
2).容易歸檔(簡(jiǎn)單拷貝即可)
3).容易恢復到某個(gè)時(shí)間點(diǎn)上(只需將文件再拷貝回去)
4).能與歸檔方法相結合，作數據庫“最新?tīng)顟B(tài)”的恢復。
5).低度維護，高度安全。
冷備份不足：
1).單獨使用時(shí)，只能提供到“某一時(shí)間點(diǎn)上”的恢復。
2).在實(shí)施備份的全過(guò)程中，數據庫必須要作備份而不能作其它工作。也就是說(shuō)，在冷備份過(guò)程中，數據庫必須是關(guān)閉狀態(tài)。
3).若磁盤(pán)空間有限，只能拷貝到磁帶等其它外部存儲設備上，速度會(huì )很慢。
4).不能按表或按用戶(hù)恢復。
熱備的優(yōu)缺點(diǎn)
1).可在表空間或數據文件級備份，備份時(shí)間短。
2).備份時(shí)數據庫仍可使用。
3).可達到秒級恢復(恢復到某一時(shí)間點(diǎn)上)。
4).可對幾乎所有數據庫實(shí)體作恢復。
5).恢復是快速的，在大多數情況下在數據庫仍工作時(shí)恢復。
熱備份的不足是：
1).不能出錯，否則后果嚴重。
2).若熱備份不成功，所得結果不可用于時(shí)間點(diǎn)的恢復。
3).因難于維護，所以要特別仔細小心，不允許“以失敗而告終”。
15. 解釋data block , extent 和 segment的區別?
data block 數據塊，是oracle最小的邏輯單位，通常oracle從磁盤(pán)讀寫(xiě)的就是塊
extent 區，是由若干個(gè)相鄰的block組成
segment段，是有一組區組成
tablespace表空間，數據庫中數據邏輯存儲的地方，一個(gè)tablespace可以包含多個(gè)數據文件
16. 比較truncate和delete命令 ?
1). Truncate 和delete都可以將數據實(shí)體刪掉，truncate 的操作并不記錄到 rollback日志，所以操作速度較快，但同時(shí)這個(gè)數據不能恢復
2). Delete操作不騰出表空間的空間
3). Truncate 不能對視圖等進(jìn)行刪除
4). Truncate是數據定義語(yǔ)言(DDL)，而delete是數據操縱語(yǔ)言(DML)
17. 解釋什么是死鎖，如何解決Oracle中的死鎖?
簡(jiǎn)言之就是存在加了鎖而沒(méi)有解鎖，可能是使用鎖沒(méi)有提交或者回滾事務(wù)，如果是表級鎖則不能操作表，客戶(hù)端處于等在狀態(tài)，如果是行級鎖則不能操作鎖定行
解決辦法：
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). 殺進(jìn)程中的會(huì )話(huà)
alter system kill session "sid,serial#";
18. 簡(jiǎn)述oracle中 dml、ddl、dcl的使用
Dml 數據操縱語(yǔ)言，如select、update、delete，insert
Ddl 數據定義語(yǔ)言，如create table 、drop table 等等
Dcl 數據控制語(yǔ)言， 如 commit、 rollback、grant、 invoke等
19. 說(shuō)說(shuō)oracle中的經(jīng)常使用到得函數
Length 長(cháng)度、 lower 小寫(xiě)、upper 大寫(xiě)， to_date 轉化日期， to_char轉化字符
Ltrim 去左邊空格、 rtrim去右邊空格，substr取字串，add_month增加或者減掉月份、to_number轉變?yōu)閿底?/p>
20. 怎樣創(chuàng )建一個(gè)存儲過(guò)程, 游標在存儲過(guò)程怎么使用, 有什么好處?
附：存儲過(guò)程的一般格式，游標使用參考問(wèn)題
1 .使用游標可以執行多個(gè)不相關(guān)的操作.如果希望當產(chǎn)生了結果集后,對結果集中的數據進(jìn)行多種不相關(guān)的數據操作
2. 使用游標可以提供腳本的可讀性
3. 使用游標可以建立命令字符串,使用游標可以傳送表名,或者把變量傳送到參數中,以便建立可以執行的命令字符串.
但是個(gè)人認為游標操作效率不太高，并且使用時(shí)要特別小心，使用完后要及時(shí)關(guān)閉
存儲過(guò)程優(yōu)缺點(diǎn)：
優(yōu)點(diǎn)：
1. 存儲過(guò)程增強了SQL語(yǔ)言的功能和靈活性。存儲過(guò)程可以用流控制語(yǔ)句編寫(xiě)，有很強的靈活性，可以完成復雜的判斷和較復雜的運算。
2. 可保證數據的安全性和完整性。
3. 通過(guò)存儲過(guò)程可以使沒(méi)有權限的用戶(hù)在控制之下間接地存取數據庫，從而保證數據的安全。
通過(guò)存儲過(guò)程可以使相關(guān)的動(dòng)作在一起發(fā)生，從而可以維護數據庫的完整性。
3. 再運行存儲過(guò)程前，數據庫已對其進(jìn)行了語(yǔ)法和句法分析，并給出了優(yōu)化執行方案。這種已經(jīng)編譯好的過(guò)程可極大地改善SQL語(yǔ)句的性能。 由于執行SQL語(yǔ)句的大部分工作已經(jīng)完成，所以存儲過(guò)程能以極快的速度執行。
4. 可以降低網(wǎng)絡(luò )的通信量, 不需要通過(guò)網(wǎng)絡(luò )來(lái)傳送很多sql語(yǔ)句到數據庫服務(wù)器了
5. 使體現企業(yè)規則的運算程序放入數據庫服務(wù)器中，以便集中控制
當企業(yè)規則發(fā)生變化時(shí)在服務(wù)器中改變存儲過(guò)程即可，無(wú)須修改任何應用程序。企業(yè)規則的特點(diǎn)是要經(jīng)常變化，如果把體現企業(yè)規則的運算程序放入應用程序中，則當企業(yè)規則發(fā)生變化時(shí)，就需要修改應用程序工作量非常之大(修改、發(fā)行和安裝應用程序)。如果把體現企業(yè)規則的 運算放入存儲過(guò)程中，則當企業(yè)規則發(fā)生變化時(shí)，只要修改存儲過(guò)程就可以了，應用程序無(wú)須任何變化。
缺點(diǎn)：
1. 可移植性差
2. 占用服務(wù)器端多的資源，對服務(wù)器造成很大的壓力
3. 可讀性和可維護性不好
Create [or replace] procedure 過(guò)程名字(參數 …)as
vs_ym_sn_end CHAR(6); --同期終止月份
CURSOR cur_1 IS --定義游標(簡(jiǎn)單的說(shuō)就是一個(gè)可以遍歷的結果集)
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方法打印出影響的記錄行數，其中用到一個(gè)系統變量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語(yǔ)句是其中比較直觀(guān)的一種。
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;
--把當前錯誤記錄進(jìn)日志表。
INSERT INTO LOG_INFO(proc_name,error_info,op_date)
VALUES("xxxxxxxxxxx_p",vs_msg,SYSDATE);
COMMIT;
RETURN;
END;
21. 怎樣創(chuàng )建一個(gè)一個(gè)索引,索引使用的原則,有什么優(yōu)點(diǎn)和缺點(diǎn)
創(chuàng )建標準索引：
CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空間名;
創(chuàng )建唯一索引:
CREATE unique INDEX 索引名 ON 表名 (列名) TABLESPACE 表空間名;
創(chuàng )建組合索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2) TABLESPACE 表空間名;
創(chuàng )建反向鍵索引:
CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空間名;
索引使用原則：
索引字段建議建立NOT NULL約束
經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應該建立索引;
經(jīng)常出現在Where子句中的字段且過(guò)濾性很強的，特別是大表的字段，應該建立索引;
可選擇性高的關(guān)鍵字 ，應該建立索引;
可選擇性低的關(guān)鍵字，但數據的值分布差異很大時(shí)，選擇性數據比較少時(shí)仍然可以利用索引提高效率
復合索引的建立需要進(jìn)行仔細分析;盡量考慮用單字段索引代替：
A、正確選擇復合索引中的第一個(gè)字段，一般是選擇性較好的且在where子句中常用的字段上;
B、復合索引的幾個(gè)字段經(jīng)常同時(shí)以AND方式出現在Where子句中可以建立復合索引;否則單字段索引;
C、如果復合索引中包含的字段經(jīng)常單獨出現在Where子句中，則分解為多個(gè)單字段索引;
D、如果復合索引所包含的字段超過(guò)3個(gè)，那么仔細考慮其必要性，考慮減少復合的字段;
E、如果既有單字段索引，又有這幾個(gè)字段上的復合索引，一般可以刪除復合索引;
頻繁DML的表，不要建立太多的索引;
不要將那些頻繁修改的列作為索引列;
索引的優(yōu)缺點(diǎn)：
有點(diǎn)：
1. 創(chuàng )建唯一性索引，保證數據庫表中每一行數據的唯一性
2. 大大加快數據的檢索速度，這也是創(chuàng )建索引的最主要的原因
3. 加速表和表之間的連接，特別是在實(shí)現數據的參考完整性方面特別有意義。
4. 在使用分組和排序子句進(jìn)行數據檢索時(shí)，同樣可以顯著(zhù)減少查詢(xún)中分組和排序的時(shí)間。
缺點(diǎn)：
1. 索引創(chuàng )建在表上，不能創(chuàng )建在視圖上
2. 創(chuàng )建索引和維護索引要耗費時(shí)間，這種時(shí)間隨著(zhù)數據量的增加而增加
3. 索引需要占物理空間，除了數據表占數據空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì )更大。
【oracle數據庫面試題】相關(guān)文章：
oracle數據庫開(kāi)發(fā)面試題201708-07
Oracle數據庫SQLPLUS介紹10-09
ORACLE數據庫碎片的整理08-18
Oracle數據庫語(yǔ)句大全10-08
oracle數據庫基本語(yǔ)句08-21
Oracle數據庫認證層次08-29
Oracle數據庫的基本介紹09-04
最強的oracle面試題積累06-11