oracle數(shù)據(jù)庫(kù)面試題

1、oracle數(shù)據(jù)庫(kù)面試題1. Oracle跟SQL Server 2005的區(qū)別?宏觀上：1). 最大的區(qū)別在于平臺(tái)，oracle可以運(yùn)行在不同的平臺(tái)上，sql server只能運(yùn)行在windows平臺(tái)上，由于windows平臺(tái)的穩(wěn)定性和安全性影響了sql server的穩(wěn)定性和安全性2). oracle使用的腳本語(yǔ)言為PL-SQL，而sql server使用的腳本為T(mén)-SQL微觀上： 從數(shù)據(jù)類(lèi)型,數(shù)據(jù)庫(kù)的結(jié)構(gòu)等等回答2. 如何使用Oracle的游標(biāo)?1). oracle中的游標(biāo)分為顯示游標(biāo)和隱式游標(biāo)2). 顯示游標(biāo)是用cursor.is命令定義的游標(biāo)，它可以對(duì)查詢(xún)語(yǔ)句(select)返回的多

2、條記錄進(jìn)行處理;隱式游標(biāo)是在執(zhí)行插入 (insert)、刪除(delete)、修改(update)和返回單條記錄的查詢(xún)(select)語(yǔ)句時(shí)由PL/SQL自動(dòng)定義的。3). 顯式游標(biāo)的操作：打開(kāi)游標(biāo)、操作游標(biāo)、關(guān)閉游標(biāo);PL/SQL隱式地打開(kāi)SQL游標(biāo)，并在它內(nèi)部處理SQL語(yǔ)句，然后關(guān)閉它3. Oracle中function和procedure的區(qū)別?1). 可以理解函數(shù)是存儲(chǔ)過(guò)程的一種2). 函數(shù)可以沒(méi)有參數(shù),但是一定需要一個(gè)返回值，存儲(chǔ)過(guò)程可以沒(méi)有參數(shù),不需要返回值3). 函數(shù)return返回值沒(méi)有返回參數(shù)模式，存儲(chǔ)過(guò)程通過(guò)out參數(shù)返回值, 如果需要返回多個(gè)參數(shù)則建議使用存儲(chǔ)過(guò)程4).

3、在sql數(shù)據(jù)操縱語(yǔ)句中只能調(diào)用函數(shù)而不能調(diào)用存儲(chǔ)過(guò)程4. Oracle的導(dǎo)入導(dǎo)出有幾種方式，有何區(qū)別?1). 使用oracle工具 exp/imp2). 使用plsql相關(guān)工具方法1. 導(dǎo)入/導(dǎo)出的是二進(jìn)制的數(shù)據(jù)， 2.plsql導(dǎo)入/導(dǎo)出的是sql語(yǔ)句的文本文件5. Oracle中有哪幾種文件?數(shù)據(jù)文件(一般后綴為.dbf或者.ora)，日志文件(后綴名.log)，控制文件(后綴名為.ctl)6. 怎樣優(yōu)化Oracle數(shù)據(jù)庫(kù)，有幾種方式?個(gè)人理解，數(shù)據(jù)庫(kù)性能最關(guān)鍵的因素在于IO，因?yàn)椴僮鲀?nèi)存是快速的，但是讀寫(xiě)磁盤(pán)是速度很慢的，優(yōu)化數(shù)據(jù)庫(kù)最關(guān)鍵的問(wèn)題在于減少磁盤(pán)的IO，就個(gè)人理解應(yīng)該分為物理的

4、和邏輯的優(yōu)化， 物理的是指oracle產(chǎn)品本身的一些優(yōu)化，邏輯優(yōu)化是指應(yīng)用程序級(jí)別的優(yōu)化物理優(yōu)化的一些原則：1). Oracle的運(yùn)行環(huán)境(網(wǎng)絡(luò)，硬件等)2). 使用合適的優(yōu)化器3). 合理配置oracle實(shí)例參數(shù)4). 建立合適的索引(減少I(mǎi)O)5). 將索引數(shù)據(jù)和表數(shù)據(jù)分開(kāi)在不同的表空間上(降低IO沖突)6). 建立表分區(qū)，將數(shù)據(jù)分別存儲(chǔ)在不同的分區(qū)上(以空間換取時(shí)間，減少I(mǎi)O)邏輯上優(yōu)化：1). 可以對(duì)表進(jìn)行邏輯分割，如中國(guó)移動(dòng)用戶(hù)表，可以根據(jù)手機(jī)尾數(shù)分成10個(gè)表，這樣對(duì)性能會(huì)有一定的作用2). Sql語(yǔ)句使用占位符語(yǔ)句，并且開(kāi)發(fā)時(shí)候必須按照規(guī)定編寫(xiě)sql語(yǔ)句(如全部大寫(xiě)，全部小寫(xiě)等)o

5、racle解析語(yǔ)句后會(huì)放置到共享池中如： select * from Emp where name=? 這個(gè)語(yǔ)句只會(huì)在共享池中有一條，而如果是字符串的話(huà)，那就根據(jù)不同名字存在不同的語(yǔ)句，所以占位符效率較好3). 數(shù)據(jù)庫(kù)不僅僅是一個(gè)存儲(chǔ)數(shù)據(jù)的地方，同樣是一個(gè)編程的地方，一些耗時(shí)的操作，可以通過(guò)存儲(chǔ)過(guò)程等在用戶(hù)較少的情況下執(zhí)行，從而錯(cuò)開(kāi)系統(tǒng)使用的高峰時(shí)間，提高數(shù)據(jù)庫(kù)性能4). 盡量不使用*號(hào)，如select * from Emp，因?yàn)橐D(zhuǎn)化為具體的列名是要查數(shù)據(jù)字典，比較耗時(shí)5). 選擇有效的表名對(duì)于多表連接查詢(xún)，可能oracle的優(yōu)化器并不會(huì)優(yōu)化到這個(gè)程度， oracle 中多表查詢(xún)是根據(jù)FRO

6、M字句從右到左的數(shù)據(jù)進(jìn)行的，那么最好右邊的'表(也就是基礎(chǔ)表)選擇數(shù)據(jù)較少的表，這樣排序更快速，如果有l(wèi)ink表(多對(duì)多中間表)，那么將link表放最右邊作為基礎(chǔ)表，在默認(rèn)情況下oracle會(huì)自動(dòng)優(yōu)化，但是如果配置了優(yōu)化器的情況下，可能不會(huì)自動(dòng)優(yōu)化，所以平時(shí)最好能按照這個(gè)方式編寫(xiě)sql6). Where字句 規(guī)則Oracle 中Where字句時(shí)從右往左處理的，表之間的連接寫(xiě)在其他條件之前，能過(guò)濾掉非常多的數(shù)據(jù)的條件，放在where的末尾， 另外!=符號(hào)比較的列將不使用索引，列經(jīng)過(guò)了計(jì)算(如變大寫(xiě)等)不會(huì)使用索引(需要建立起函數(shù))， is null、is not null等優(yōu)化器不會(huì)使用

7、索引7). 使用Exits Not Exits 替代 In Not in8). 合理使用事務(wù)，合理設(shè)置事務(wù)隔離性數(shù)據(jù)庫(kù)的數(shù)據(jù)操作比較消耗數(shù)據(jù)庫(kù)資源的，盡量使用批量處理，以降低事務(wù)操作次數(shù)7. Oracle中字符串用什么符號(hào)鏈接?Oracle中使用 | 這個(gè)符號(hào)連接字符串 如 abc | d8. Oracle分區(qū)是怎樣優(yōu)化數(shù)據(jù)庫(kù)的?Oracle的分區(qū)可以分為：列表分區(qū)、范圍分區(qū)、散列分區(qū)、復(fù)合分區(qū)。1). 增強(qiáng)可用性：如果表的一個(gè)分區(qū)由于系統(tǒng)故障而不能使用，表的其余好的分區(qū)仍可以使用;2). 減少關(guān)閉時(shí)間：如果系統(tǒng)故障只影響表的一部份分區(qū)，那么只有這部份分區(qū)需要修復(fù)，可能比整個(gè)大表修復(fù)花的時(shí)間更

8、少;3). 維護(hù)輕松：如果需要得建表，獨(dú)產(chǎn)管理每個(gè)公區(qū)比管理單個(gè)大表要輕松得多;4). 均衡I/O：可以把表的不同分區(qū)分配到不同的磁盤(pán)來(lái)平衡I/O改善性能;5). 改善性能：對(duì)大表的查詢(xún)、增加、修改等操作可以分解到表的不同分區(qū)來(lái)并行執(zhí)行，可使運(yùn)行速度更快6). 分區(qū)對(duì)用戶(hù)透明，最終用戶(hù)感覺(jué)不到分區(qū)的存在。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 <=

9、20 )where r > 1010. Oralce怎樣存儲(chǔ)文件，能夠存儲(chǔ)哪些文件?Oracle 能存儲(chǔ) clob、nclob、 blob、 bfileClob 可變長(zhǎng)度的字符型數(shù)據(jù)，也就是其他數(shù)據(jù)庫(kù)中提到的文本型數(shù)據(jù)類(lèi)型Nclob 可變字符類(lèi)型的數(shù)據(jù)，不過(guò)其存儲(chǔ)的是Unicode字符集的字符數(shù)據(jù)Blob 可變長(zhǎng)度的二進(jìn)制數(shù)據(jù)Bfile 數(shù)據(jù)庫(kù)外面存儲(chǔ)的可變二進(jìn)制數(shù)據(jù)11. Oracle中使用了索引的列，對(duì)該列進(jìn)行where條件查詢(xún)、分組、排序、使用聚集函數(shù)，哪些用到了索引?均會(huì)使用索引， 值得注意的是復(fù)合索引(如在列A和列B上建立的索引)可能會(huì)有不同情況12. 數(shù)據(jù)庫(kù)怎樣實(shí)現(xiàn)每隔30分

10、鐘備份一次?通過(guò)操作系統(tǒng)的定時(shí)任務(wù)調(diào)用腳本導(dǎo)出數(shù)據(jù)庫(kù)13. Oracle中where條件查詢(xún)和排序的性能比較?Order by使用索引的條件極為嚴(yán)格，只有滿(mǎn)足如下情況才可以使用索引，1). order by中的列必須包含相同的索引并且索引順序和排序順序一致2). 不能有null值的列所以排序的性能往往并不高，所以建議盡量避免order by14. 解釋冷備份和熱備份的不同點(diǎn)以及各自的優(yōu)點(diǎn)?冷備份發(fā)生在數(shù)據(jù)庫(kù)已經(jīng)正常關(guān)閉的情況下，將關(guān)鍵性文件拷貝到另外位置的一種說(shuō)法熱備份是在數(shù)據(jù)庫(kù)運(yùn)行的情況下，采用歸檔方式備份數(shù)據(jù)的方法冷備的優(yōu)缺點(diǎn)：1).是非?？焖俚膫浞莘椒?只需拷貝文件)2).容易歸檔(簡(jiǎn)單

11、拷貝即可)3).容易恢復(fù)到某個(gè)時(shí)間點(diǎn)上(只需將文件再拷貝回去)4).能與歸檔方法相結(jié)合，作數(shù)據(jù)庫(kù)“最新?tīng)顟B(tài)”的恢復(fù)。5).低度維護(hù)，高度安全。冷備份不足：1).單獨(dú)使用時(shí)，只能提供到“某一時(shí)間點(diǎn)上”的恢復(fù)。2).在實(shí)施備份的全過(guò)程中，數(shù)據(jù)庫(kù)必須要作備份而不能作其它工作。也就是說(shuō)，在冷備份過(guò)程中，數(shù)據(jù)庫(kù)必須是關(guān)閉狀態(tài)。3).若磁盤(pán)空間有限，只能拷貝到磁帶等其它外部存儲(chǔ)設(shè)備上，速度會(huì)很慢。4).不能按表或按用戶(hù)恢復(fù)。熱備的優(yōu)缺點(diǎn)1).可在表空間或數(shù)據(jù)文件級(jí)備份，備份時(shí)間短。2).備份時(shí)數(shù)據(jù)庫(kù)仍可使用。3).可達(dá)到秒級(jí)恢復(fù)(恢復(fù)到某一時(shí)間點(diǎn)上)。4).可對(duì)幾乎所有數(shù)據(jù)庫(kù)實(shí)體作恢復(fù)。5).恢復(fù)是快速的

12、，在大多數(shù)情況下在數(shù)據(jù)庫(kù)仍工作時(shí)恢復(fù)。熱備份的不足是：1).不能出錯(cuò)，否則后果嚴(yán)重。2).若熱備份不成功，所得結(jié)果不可用于時(shí)間點(diǎn)的恢復(fù)。3).因難于維護(hù)，所以要特別仔細(xì)小心，不允許“以失敗而告終”。15. 解釋data block , extent 和 segment的區(qū)別?data block 數(shù)據(jù)塊，是oracle最小的邏輯單位，通常oracle從磁盤(pán)讀寫(xiě)的就是塊extent 區(qū)，是由若干個(gè)相鄰的block組成segment段，是有一組區(qū)組成tablespace表空間，數(shù)據(jù)庫(kù)中數(shù)據(jù)邏輯存儲(chǔ)的地方，一個(gè)tablespace可以包含多個(gè)數(shù)據(jù)文件16. 比較truncate和delete命令 ?

13、1). Truncate 和delete都可以將數(shù)據(jù)實(shí)體刪掉，truncate 的操作并不記錄到 rollback日志，所以操作速度較快，但同時(shí)這個(gè)數(shù)據(jù)不能恢復(fù)2). Delete操作不騰出表空間的空間3). Truncate 不能對(duì)視圖等進(jìn)行刪除4). Truncate是數(shù)據(jù)定義語(yǔ)言(DDL)，而delete是數(shù)據(jù)操縱語(yǔ)言(DML)17. 解釋什么是死鎖，如何解決Oracle中的死鎖?簡(jiǎn)言之就是存在加了鎖而沒(méi)有解鎖，可能是使用鎖沒(méi)有提交或者回滾事務(wù)，如果是表級(jí)鎖則不能操作表，客戶(hù)端處于等在狀態(tài)，如果是行級(jí)鎖則不能操作鎖定行解決辦法：1). 查找出被鎖的表select b.owner,b.ob

14、ject_name,a.session_id,a.locked_modefrom v$locked_object a,dba_objects bwhere b.object_id = a.object_id;select b.username,b.sid,b.serial#,logon_timefrom v$locked_object a,v$session bwhere a.session_id = b.sid order by b.logon_time;2). 殺進(jìn)程中的會(huì)話(huà)alter system kill session "sid,serial#"18. 簡(jiǎn)述ora

15、cle中 dml、ddl、dcl的使用Dml 數(shù)據(jù)操縱語(yǔ)言，如select、update、delete，insertDdl 數(shù)據(jù)定義語(yǔ)言，如create table 、drop table 等等Dcl 數(shù)據(jù)控制語(yǔ)言， 如 commit、 rollback、grant、 invoke等19. 說(shuō)說(shuō)oracle中的經(jīng)常使用到得函數(shù)Length 長(zhǎng)度、 lower 小寫(xiě)、upper 大寫(xiě)， to_date 轉(zhuǎn)化日期， to_char轉(zhuǎn)化字符Ltrim 去左邊空格、 rtrim去右邊空格，substr取字串，add_month增加或者減掉月份、to_number轉(zhuǎn)變?yōu)閿?shù)字20. 怎樣創(chuàng)建一個(gè)存儲(chǔ)過(guò)程,

16、 游標(biāo)在存儲(chǔ)過(guò)程怎么使用, 有什么好處?附：存儲(chǔ)過(guò)程的一般格式，游標(biāo)使用參考問(wèn)題1 .使用游標(biāo)可以執(zhí)行多個(gè)不相關(guān)的操作.如果希望當(dāng)產(chǎn)生了結(jié)果集后,對(duì)結(jié)果集中的數(shù)據(jù)進(jìn)行多種不相關(guān)的數(shù)據(jù)操作2. 使用游標(biāo)可以提供腳本的可讀性3. 使用游標(biāo)可以建立命令字符串,使用游標(biāo)可以傳送表名,或者把變量傳送到參數(shù)中,以便建立可以執(zhí)行的命令字符串.但是個(gè)人認(rèn)為游標(biāo)操作效率不太高，并且使用時(shí)要特別小心，使用完后要及時(shí)關(guān)閉存儲(chǔ)過(guò)程優(yōu)缺點(diǎn)：優(yōu)點(diǎn)：1. 存儲(chǔ)過(guò)程增強(qiáng)了SQL語(yǔ)言的功能和靈活性。存儲(chǔ)過(guò)程可以用流控制語(yǔ)句編寫(xiě)，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運(yùn)算。2. 可保證數(shù)據(jù)的安全性和完整性。3. 通過(guò)存儲(chǔ)過(guò)

17、程可以使沒(méi)有權(quán)限的用戶(hù)在控制之下間接地存取數(shù)據(jù)庫(kù)，從而保證數(shù)據(jù)的安全。通過(guò)存儲(chǔ)過(guò)程可以使相關(guān)的動(dòng)作在一起發(fā)生，從而可以維護(hù)數(shù)據(jù)庫(kù)的完整性。3. 再運(yùn)行存儲(chǔ)過(guò)程前，數(shù)據(jù)庫(kù)已對(duì)其進(jìn)行了語(yǔ)法和句法分析，并給出了優(yōu)化執(zhí)行方案。這種已經(jīng)編譯好的過(guò)程可極大地改善SQL語(yǔ)句的性能。 由于執(zhí)行SQL語(yǔ)句的大部分工作已經(jīng)完成，所以存儲(chǔ)過(guò)程能以極快的速度執(zhí)行。4. 可以降低網(wǎng)絡(luò)的通信量, 不需要通過(guò)網(wǎng)絡(luò)來(lái)傳送很多sql語(yǔ)句到數(shù)據(jù)庫(kù)服務(wù)器了5. 使體現(xiàn)企業(yè)規(guī)則的運(yùn)算程序放入數(shù)據(jù)庫(kù)服務(wù)器中，以便集中控制當(dāng)企業(yè)規(guī)則發(fā)生變化時(shí)在服務(wù)器中改變存儲(chǔ)過(guò)程即可，無(wú)須修改任何應(yīng)用程序。企業(yè)規(guī)則的特點(diǎn)是要經(jīng)常變化，如果把體現(xiàn)企業(yè)規(guī)則

18、的運(yùn)算程序放入應(yīng)用程序中，則當(dāng)企業(yè)規(guī)則發(fā)生變化時(shí)，就需要修改應(yīng)用程序工作量非常之大(修改、發(fā)行和安裝應(yīng)用程序)。如果把體現(xiàn)企業(yè)規(guī)則的 運(yùn)算放入存儲(chǔ)過(guò)程中，則當(dāng)企業(yè)規(guī)則發(fā)生變化時(shí)，只要修改存儲(chǔ)過(guò)程就可以了，應(yīng)用程序無(wú)須任何變化。缺點(diǎn)：1. 可移植性差2. 占用服務(wù)器端多的資源，對(duì)服務(wù)器造成很大的壓力3. 可讀性和可維護(hù)性不好Create or replace procedure 過(guò)程名字(參數(shù) )asvs_ym_sn_end CHAR(6); -同期終止月份CURSOR cur_1 IS -定義游標(biāo)(簡(jiǎn)單的說(shuō)就是一個(gè)可以遍歷的結(jié)果集)SELECT area_code,CMCODE,SUM(rmb

19、_amt)/10000 rmb_amt_sn,SUM(usd_amt)/10000 usd_amt_snFROM BGD_AREA_CM_M_BASE_TWHERE ym >= vs_ym_sn_begAND ym <= vs_ym_sn_endGROUP BY area_code,CMCODE;BEGIN-用輸入?yún)?shù)給變量賦初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS TO_DATE 等很常用的函數(shù)。vs_ym_beg := SUBSTR(is_ym,1,6);vs_ym_end := SUBSTR(is_ym,7,6);vs_ym_sn_beg

20、:= 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");-先刪除表中特定條件的數(shù)據(jù)。DELETE FROM xxxxxxxxxxx_T WHERE ym = is_ym;-然后用內(nèi)置的DBMS_OUTPUT對(duì)象的put_line方法打印出影響的記錄行數(shù)，其中用到一個(gè)系統(tǒng)變量SQL%ro

21、wcountDBMS_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)/10000FROM BGD_AREA_CM_M_BASE_TWHERE ym >= vs_ym_begAND ym <= vs_ym_endGROUP BY area_code,CMCOD

22、E;DBMS_OUTPUT.put_line("ins當(dāng)月記錄="|SQL%rowcount|"條");-遍歷游標(biāo)處理后更新到表。遍歷游標(biāo)有幾種方法，用for語(yǔ)句是其中比較直觀的一種。FOR rec IN cur_1 LOOPUPDATE xxxxxxxxxxx_TSET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_snWHERE area_code = rec.area_codeAND CMCODE = rec.CMCODEAND ym = is_ym;END LOOP;COMMIT;-錯(cuò)誤

23、處理部分。OTHERS表示除了聲明外的任意錯(cuò)誤。SQLERRM是系統(tǒng)內(nèi)置變量保存了當(dāng)前錯(cuò)誤的詳細(xì)信息。EXCEPTIONWHEN OTHERS THENvs_msg := "ERROR IN xxxxxxxxxxx_p("|is_ym|"):"|SUBSTR(SQLERRM,1,500);ROLLBACK;-把當(dāng)前錯(cuò)誤記錄進(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)建標(biāo)準(zhǔn)索引：CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空間名;創(chuàng)建唯一索引:CREATE unique INDEX 索引名 ON 表名 (列名) TABLESPACE 表空間名;創(chuàng)建組合索引:CREATE INDEX 索引