【面試】【Oracle常見問題總結】【01】____Oracle

【常見面試問題總結目錄>>>】 001 資料庫分頁查詢

　　select * from (
　　　　select t.*,rownum from table1 t where condition )
　　where rownum>(pangeNow-1)*pageSize and rownum<=(pageNow)*pageSize 002 事務概念

　　答案：事務是這樣一種機制，它確保多個SQL語句被當作單個工作單元來處理。事務具有以下的作用：
　　一致性：同時進行的查詢和更新彼此不會發生衝突，其他使用者不會看到發生了變化但尚未提交的資料。
　　可恢複性：一旦系統故障，資料庫會自動地完全恢複未完成的事務。 003 觸發器的作用，什麼時候用觸發器,建立觸發器的步驟，觸發器裡是否可以有commit, 為什麼。

　　答案：觸發器是可以由事件來啟動啟動並執行，存在於資料庫伺服器中的一個過程。他的作用：可以實現一般的約束無法完成的複雜約束，從而實現更為複雜的完整性要求。使用觸發器並不存在嚴格的限定，只要使用者想在無人工參與的情況下完成一般的定義約束不可以完成的約束，來保證資料庫完整性，那麼就可以使用觸發器。
　　由於觸發器主要是用來保證資料庫的完整性的，所以要建立一個觸發器，首先要明確該觸發器應該屬於那一種（DML，INSTEAD OF，SYSTEM）因為他們各有個的用途；其次就是要確定觸發器被觸發以後所設計到的資料。 觸發器中不可以使用COMMIT。 004 關聯式資料庫系統與檔案資料庫系統的區別在那裡?關聯式資料庫系統一般適用那些方面?

　　答案： 關聯式資料庫系統檔案系統的區別在於：
　　首先，關係性資料庫的整體資料是結構化的，採用關係資料模型來描述，這是它與檔案系統的根本區別。（資料模型包括：資料結構，資料操作以及完整性條件約束條件）
　　其次，關聯式資料庫系統的共用性高，冗餘低可以面向整個系統，而檔案系統則具有應用範圍的局限性，不易擴充。
　　第三，關聯式資料庫系統採用兩級映射機制保證了資料的高獨立性，從而使得程式的編寫和資料都存在很高的獨立性。這方面是檔案系統無法達到的，它只能針對於某一個具體的應用。（兩級映射：保證邏輯獨立性的外模式/模式映射和保證物理獨立性的內模式/模式映射。外模式：使用者模式，是資料庫使用者的局部資料的邏輯結構特徵的描述。模式：資料庫全體資料的邏輯結構特徵的描述。內模式：也就是資料最終的實體儲存體結構的描述。）
　　第四，就是關係性資料庫系統由統一的DBMS進行管理，從而為資料提供了如安全性保護，並發控制，完整性檢查和資料庫恢複服務。 005 觸發器的概念,預存程序的概念.

　　答案： 觸發器： 是儲存在資料庫中的過程，當表被修改（增、刪、改）時它隱式地被激發。 預存程序：是資料庫語言SQL的集合，同樣也儲存在資料庫中，但是他是由其他應用程式來啟動運行或者也可以直接運行。 006 什麼是事務一致性 ? 選擇熟悉的資料庫實現一個交易處理,如信用卡提款. 答案：

　　事務的一致性：是事務原子性的體現，事務所對應的資料庫操作要麼成功要麼失敗沒有第三種情況。事務不管是提交成功與否都不能影響資料庫資料的一致性狀態。
　　事務：使用者定義的一個資料庫操作序列，這些操作要麼全部成功完成要麼全部不做，是一個不可分割的整體。定義事務的SQL語句有：BEGIN TRANSACTION，COMMIT，ROLLBACK。
　　事務的原子性：就是事務所自主資料庫操作要麼都做,要麼都不做.
　　事務的隔離性：事務對資料的操作不能夠受到其他事務的影響。
　　事務的持久性：也就是說事務對資料的影響是永久的。
　　對信用卡提款這一事務而言就是要保證’提取到現金’和’卡帳號餘額’的修改要同時成功或失敗.
　　BEGIN TRANSACTION
　　　　讀取A的帳戶餘額BALANCE
　　　　BALANCE=BALANCE-AMOUNT轉帳金額
　　　　IF（BALANCE<0）
　　　　　　THEN ROLLBACK
　　　　ELSE BEGIN
　　　　　　將A的新喻額寫回
　　　　　　讀取B的帳戶餘額BALANCEB
　　　　　　BALANCEB=BALANCEB+AMOUNT轉帳金額
　　　　　　將B的新喻額寫回
　　　　　　COMMIT
　　　　END IF
　　END 007 實現索引的方式? 索引的原理? 索引的代價? 索引的類型。

　　答案： 實現索引的方式有兩種：針對一張表的某些欄位建立具體的索引,如對oracle: create index 索引名稱 on 表名(欄位名)；在建立表時為欄位建立主鍵約束或者唯一約束，系統將自動為其建立索引。
　　索引的原理：根據建立索引的欄位建立索引表，存放欄位值以及對應記錄的物理地址，從而在搜尋的時候根據欄位值搜尋索引表的到物理地址直接存取記錄。
　　引入索引雖然提高了查詢速度,但本身佔用一定的系統儲存容量和系統處理時間,需要根據實際情況進行具體的分析.
　　（oracle）索引的類型有：B樹索引，位元影像索引，函數索引等。 008 view 的概念 ? 何時應用?

答案： view 是對錶級資料的多角度的透視,適用於對查詢安全性、靈活性有一定要求的環境. 009 使用預存程序訪問資料庫比直接用SQL語句訪問有哪些優點。

　　答案：預存程序是先行編譯過的，執行時無須編譯，執行速度更快；預存程序封裝了一批SQL語句，便於維護資料的完整性與一致性；可以實現代碼的複用。 010 Oracle中復原的概念。復原段有什麼作用。

　　復原就是在事務提交之前將資料庫資料恢複到事務修改之前資料庫資料狀態。
　　復原段就是為復原提供依據，記錄的是事務操作資料庫之前的資料或者對應於以前操作的操作，這個內容要根據以前的操作而定。比如說以前事務操作如果是UPDATE那麼復原段則儲存UPDATE以前的資料，如果事務是DELETE操作那麼儲存的則是與之相對應的INSERT動作陳述式，相反如果事務操作是INSERT那麼記錄相應的則是DELETE操作了。 011 介紹一下oracle的體繫結構。

　　邏輯體繫結構：塊，區，段，資料表空間
　　物理體繫結構：資料表空間，三大檔案
　　軟體體繫結構：SGA,後台進程. 012 簡述ORACLE中SGA的組成部分。

　　答：系統全域區包括：共用池、重做日誌緩衝區、資料快取區，大池，JAVA池。 013 簡述ORACLE的啟動和關閉各有多少步驟。

　　啟動：啟動執行個體、裝載資料庫資料、開啟資料庫。 關閉：關閉資料庫、卸載資料庫資料、關閉執行個體。 014 ORACLE資料庫都有哪些類型的檔案。

　　資料檔案，控制檔案，記錄檔，參數檔案 015 在Oracle中，你所建立的資料表空間資訊放在哪裡。

　　存放在資料字典中，資料字典內容對應於系統資料表空間SYSTEM資料表空間。 016 ORACLE的基礎資料型別 (Elementary Data Type)有哪些。

　　Char()儲存定長字元，定義的時候可以不為他指定長度但是如若往裡插入值則會出錯；varchar2()儲存變長字元定義的時候必須指定長度，date儲存時間日期；Number()數字類型，包括整型，浮點型等；clob()大容量字串；blob()大二進位對象 017 資料庫的幾種物理檔案。

　　1）資料檔案
　　2）控制檔案
　　3）記錄檔 018 控制檔案都含有哪些資訊。

　　控制檔案存放有執行個體資訊（執行個體名稱建立時間等），資料檔案和記錄檔資訊，還有系統運行時記錄的系統變更碼(SCN)，檢查點資訊和歸檔的目前狀態資訊等。資料庫在載入資料庫的時候首先要讀取控制檔案獲得和資料庫有關的物理結構資訊之後才能夠正確載入資料檔案和記錄檔並開啟資料庫。 019 truncate和delete的區別。

　　1. TRUNCATE在各種表上無論是大的還是小的都非常快。如果有ROLLBACK命令DELETE將被撤銷，而TRUNCATE則不會被撤銷。
　　2. TRUNCATE是一個DDL語言而DELETE是DML語句，向其他所有的DDL語言一樣，他將被隱式提交，不能對TRUNCATE使用ROLLBACK命令。
　　3. TRUNCATE將重新設定高水平線和所有的索引。在對整個表和索引進行完全瀏覽時，經過TRUNCATE操作後的表比DELETE操作後的表要快得多。
　　4. TRUNCATE不能觸發觸發器，DELETE會觸發觸發器。
　　5. 不能授予任何人清空他人的表的許可權。
　　6. 當表被清空後表和表的索引講重新設定成初始大小，而delete則不能。
　　7. 不能清空父表。 020 資料表空間如何擴充。並用語句寫出。

　　兩種擴充方式：
　　1. 增加資料檔案 alter tablespace tablespace_name add datafile ‘’ xxMB
　　2. 擴充資料檔案大小 alter database datafile ‘’ resize newMB 021 資料表空間區管理方式。哪種方式現在是推薦使用的。

　　1. 字典管理方式 extent management dictionary;預設
　　2. 本地管理方式 extent management local[autoallocate/uniform xxmb]<推薦>; 022 分區表的應用。

　　1. 一個分區表有一個或多個分區，每個分區通過使用定界分割、散列分區、或組合分區分區的行
　　2. 分區表中的每一個分區為一個段，可各自位於不同的資料表空間中
　　3. 對於同時能夠使用幾個進程進行查詢或操作的大型表分區非常有用 023 談談索引的用法及原理。

　　索引是若干資料行的關鍵字的列表，查詢資料時，通過索引中的關鍵字可以快速定位到要訪問的記錄所在的資料區塊，從而大大減少讀取資料區塊的I/O次數，因此可以顯著提高效能。 024 預存程序的應用，如何既有輸入又有輸出。

　　Create procedure pro_name (xxxx in/out type; yyyy in/out/inout type; )
　　is/as zzzz type;
　　begin
　　　　sqlpro;
　　　　exception exceptionxxxxx;
　　　　commit;
　　end; 025 Oracle中的異常有哪幾類。

　　oracle中有三種類型的異常。預定義的異常 非預定義的異常 使用者定義的異常 第二種非預定義的異常是與特定的oracle錯誤關聯。 026 Oracle的最佳化策略（對MySQL同樣適用）

　　最佳化的策略一般包括: 記憶體最佳化、作業系統最佳化、資料存放區的最佳化、網路最佳化等方法
　　具體到不同的資料庫涉及到要調整不同的資料庫設定檔、不同的作業系統參數、網路參數等等, 不同的資料庫不同。 027 使用索引查詢一定能提高查詢的效能嗎。為什麼。

　　通常，通過索引查詢資料比全表掃描要快.但是我們也必須注意到它的代價. 索引需要空間來儲存,也需要定期維護, 每當有記錄在表中增減或索引列被修改時,索引本身也會被修改. 這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5 次的磁碟I/O. 因為索引需要額外的儲存空間和處理,那些不必要的索引反而會使查詢反應時間變慢.使用索引查詢不一定能提高查詢效能,索引範圍查詢(INDEX RANGE SCAN)適用於兩種情況:
　　基於一個範圍的檢索,一般查詢返回結果集小於表中記錄數的30%宜採用;
　　基於非唯一性索引的檢索，索引就是為了提高查詢效能而存在的,如果在查詢中索引沒有提高效能,只能說是用錯了索引,或者講是場合不同 028 綁定變數是什麼。綁定變數有什麼優缺點。

　　綁定變數是指在SQL語句中使用變數，改變變數的值來改變SQL語句的執行結果。
　　優點：使用綁定變數，可以減少SQL語句的解析，能減少資料庫引擎消耗在SQL語句解析上的資源。提高了編程效率和可靠性。減少訪問資料庫的次數, 就能實際上減少ORACLE的工作量。
　　缺點：經常需要使用動態SQL的寫法，由於參數的不同，可能SQL的執行效率不同；
　　綁定變數是相對文本變數來講的,所謂文本變數是指在SQL直接書寫查詢條件， 這樣的SQL在不同條件下需要反覆解析,綁定變數是指使用變數來代替直接書寫條件，查詢bind value在運行時傳遞，然後綁定執行。
　　優點是減少硬解析,降低CPU的爭用,節省shared_pool
　　缺點是不能使用histogram,sql最佳化比較困難 029 簡單描述tablespace / segment / extent / block之間的關係

　　tablespace: 一個資料庫劃分為一個或多個邏輯單位，該邏輯單位成為資料表空間;每一個資料表空間可能包含一個或多個 Segment;
　　Segments: Segment指在tablespace中為特定邏輯儲存結構分配的空間。每一個段是由一個或多個extent組成。包括資料區段、索引段、復原段和臨時段。
　　Extents: 一個 extent 由一系列連續的 Oracle blocks組成.ORACLE為通過extent 來給segment分配空間。
　　Data Blocks：Oracle 資料庫最小的I/O儲存單位，一個data block對應一個或多個分配給data file的作業系統塊。
　　table建立時,預設建立了一個data segment,每個data segment含有min extents指定的extents數,每個extent據據資料表空間的儲存參數分配一定數量的blocks 030 復原段的作用是什麼。

　　復原段用於儲存資料修改前的映象，這些資訊用於產生讀一致性資料庫資訊、在資料庫恢複和Rollback時使用。一個事務只能使用一個復原段。
　　交易回復：當事務修改表中資料的時候，該資料修改前的值（即前影像）會存放在復原段中，當使用者復原事務（ROLLBACK）時，ORACLE將會利用復原段中的資料前影像來將修改的資料恢複到原來的值。
　　事務恢複：當事務正在處理的時候，常式失敗，復原段的資訊儲存在undo資料表空間中，ORACLE將在下次開啟資料庫時利用復原來恢複未提交的資料。
　　讀一致性：當一個會話正在修改資料時，其他的會話將看不到該會話未提交的修改。 當一個語句正在執行時，該語句將看不到從該語句開始執行後的未提交的修改（語句級讀一致性） 當ORACLE執行SELECT語句時，ORACLE依照當前的系統改變號（SYSTEM CHANGE NUMBER-SCN） 來保證任何前於當前SCN的未提交的改變不被該語句處理。可以想象：當一個長時間的查詢正在執行時， 若其他會話改變了該查詢要查詢的某個資料區塊，ORACLE將利用復原段的資料前影像來構造一個讀一致性視圖 031 日誌的作用是什麼

　　記錄檔（Log File）記錄所有對資料庫資料的修改，主要是保護資料庫以防止故障,以及恢複資料時使用。其特點如下：
　　1. 每一個資料庫至少包含兩個記錄檔組。每個記錄檔組至少包含兩個記錄檔成員。
　　2. 記錄檔組以迴圈方式進行寫操作。
　　3. 每一個記錄檔成員對應一個物理檔案。
　　記錄資料庫事務,最大限度地保證資料的一致性與安全性
　　重做記錄檔：含對資料庫所做的更改記錄，這樣萬一出現故障可以啟用資料恢複,一個資料庫至少需要兩個重做記錄檔
　　歸檔記錄檔：是重做記錄檔的離線副本，這些副本可能對於從介質失敗中進行恢複很必要。 031 SGA主要有那些部分，主要作用是什麼

　　系統全域區（SGA）:是ORACLE為執行個體分配的一組共用緩衝儲存區，用於存放資料庫資料和控制資訊，以實現對資料庫資料的管理和操作。 SGA主要包括:
　　1. 共用池(shared pool) ：用來儲存最近執行的SQL語句和最近使用的資料字典的資料。
　　2. 資料緩衝區 (database buffer cache)：用來儲存最近從資料檔案中讀寫過的資料。
　　3. 重作日誌緩衝區（redo log buffer）：用來記錄服務或後台進程對資料庫的操作。 另外在SGA中還有兩個可選的記憶體結構：
　　4. Java pool: 用來儲存Java代碼。
　　5. Large pool: 用來儲存不與SQL直接相關的大型記憶體結構。備份、恢複使用。 033 Oracle系統進程主要有哪些，作用是什麼

　　資料寫進程(DBWR)：負責將更改的資料從資料庫緩衝區快取寫入資料檔案
　　日誌寫進程(LGWR)：將重做日誌緩衝區中的更改寫入線上重做記錄檔
　　系統監控 (SMON): 檢查資料庫的一致性如有必要還會在資料庫開啟時啟動資料庫的恢複
　　進程監控 (PMON): 負責在一個Oracle 進程失敗時清理資源
　　檢查點進程(CKPT)：負責在每當緩衝區快取中的更改永久地記錄在資料庫中時,更新控制檔案和資料檔案中的資料庫狀態資訊。
　　歸檔進程 (ARCH)：在每次日誌切換時把已滿的日誌組進行備份或歸檔
　　恢複進程 (RECO): 保證分散式交易的一致性,在分散式交易中,要麼同時commit,要麼同時rollback 034 備份如何分類

　　邏輯備份：exp/imp 指定表的邏輯備份 物理備份：
　　熱備份:alter tablespace begin/end backup;
　　冷備份:離線備份(database shutdown)
　　RMAN備份
　　full backup/incremental backup(累積/差異) 物理備份
　　物理備份是最主要的備份方式。用於保證資料庫在最小的資料庫丟失或沒有資料丟失的情況下得到恢複。
　　冷物理
　　　　冷物理備份提供了最簡單和最直接的方法保護資料庫因物理損壞丟失。建議在以下幾種情況中使用。
　　　　對一個已經存在大最資料量的資料庫，在晚間資料庫可以關閉，此時應用冷物理備份。 對需對資料庫伺服器進行升級，（如更換硬碟），此時需要備份資料庫資訊，並在新的硬碟中恢複這些資料資訊，建議採用冷物理備份。
　　熱物理
　　　　主要是指備份過程在資料庫開啟並且使用者可以使用的情況下進行。需要執行熱物理備份的情況有：
由於資料庫性質要求不間斷工作，因而此時只能採用熱物理備份。 由於備份的要求的時間過長，而資料庫只能短時間關閉時。
　　邏輯備份 (EXP/IMP)
　　　　邏輯備份用於實現資料庫物件的恢複。但不是基於時間點可完全恢複的備份策略。只能作為聯機備份和離線備份的一種補充。
　　完全邏輯備份
　　　　完全邏輯備份是將整個資料庫匯出到一個資料庫的格式檔案中，該檔案可以在不同的資料庫版本、作業系統和硬體平台之間進行移植。
指定表的邏輯備份
　　通過備份工具，可以將指定的資料庫表備份出來，這可以避免完全邏輯備份所帶來的時間和財力上的浪費。 035 事物務是什麼?

　　事務是作為一個邏輯單元執行的一系列操作，一個邏輯工作單元必須有四個屬性，稱為 ACID(原子性、一致性、隔離性和持久性)屬性，只有這樣才能成為一個事務:
　　原子性：事務必須是原子工作單元;對於其資料修改，要麼全都執行，要麼全都不執行。
　　一致性：事務在完成時，必須使所有的資料都保持一致狀態。在相關資料庫中，所有規則都必須應用於事務的修改，以保持所有資料的完整性。事務結束時，所有的內部資料結構(如 B 樹索引或雙向鏈表)都必須是正確的。
　　隔離性：由並發事務所作的修改必須與任何其它並發事務所作的修改隔離。事務查看資料時資料所處的狀態，要麼是另一併發事務修改它之前的狀態，要麼是另一事務修改它之後的狀態，事務不會查看中間狀態的資料。這稱為可串列性，因為它能夠重新裝載起始資料，並且重播一系列事務，以使資料結束時的狀態與原始事務執行的狀態相同。
　　持久性：事務完成之後，它對於系統的影響是永久性的。該修改即使出現系統故障也將一直保持。 036 資料庫死結的預防與解除

　　死結發生的條件：
　　　　1、資源不能共用，需要只能由一個進程或者線程使用
　　　　2、請求且保持，已經鎖定資源自已保持著不釋放
　　　　3、不剝奪，自給申請到的資源不能被別人剝奪
　　　　4、迴圈等待
　　想預防死結，把上面四個條件破壞一個就可以了。
　　防止死結的途徑就是避免滿足死結條件的情況發生，為此使用者需要遵循以下原則。
　　　　（1）盡量避免並發地執行涉及到修改資料的語句。
　　　　（2）要求每個事務一次就將所有要使用的資料全部加鎖，否則就不予執行。
　　　　（3）預先規定一個封鎖順序，所有的事務都必須按這個順序對資料執行封鎖。如不同的過程在事務內部對對象的更新執行順序應盡量保持一致。
　　　　（4）每個事務的執行時間不可太長，在業務允許的情況下可以考慮將事務分割成為幾個小事務來執行。【比如說把複雜的多表查詢分散成多次單表查詢】
　　　　（5）資料存放區空間離散法。資料存放區空間離散法是指採取各種手段，將邏輯上在一個表中的資料分散到若干離散的空間上去，以便改善對錶的訪問效能。主要通過將大表按行或列分解為若干小表，或者按不同的使用者群分解兩種方法實現。這種方法類似分散“資料熱點”，但是確實，如果資料不是太經常被訪問，那麼死結就不會太經常發生。
　　　　（6）還是類似（1）的，比如有一個修改上百條記錄的update語句，我們可以修改成每10條一個update語句，或者乾脆就每條記錄一個update語句。
　　　　（7）將經常更新的資料庫和查詢資料庫分開 037 ORACLE中的控制檔案什麼時候讀取。

　　ORACLE伺服器啟動時，先啟動執行個體然後再讀取資料庫的各個檔案當然也包括控制檔案。也就是說在資料庫伺服器啟動的第二步時讀取。 038 Oracle索引分為哪幾類，說出唯一索引和位元影像索引的概念。

　　Oracle索引有B樹索引，位元影像索引，函數索引，簇索引等。
　　唯一索引也是B樹索引的一種，它要求被索引的欄位值不可以重複。在建立的時候使用B樹演算法建立。
　　位元影像索引並不是採用像唯一索引那樣儲存（索引欄位值，記錄ROWID）來建立索引段的，而是為每一個唯一的欄位值建立一個位元影像，位元影像中使用位元來對應一個記錄的ROWID。位元到ROWID是通過映射的到的。 039 描述tablespace和datafile之間的關係

　　一個資料表空間可包含一個或多個資料檔案。資料表空間利用增加或擴充資料檔案擴大資料表空間，資料表空間的大小為組成該資料表空間的資料檔案大小的和。一個datafile只能屬於一個資料表空間; 一個tablespace可以有一個或多個datafile,每個datafile只能在一個tablespace內, table中的資料,通過hash演算法分布在tablespace中的各個datafile中,tablespace是邏輯上的概念,datafile則在物理上儲存了資料庫的種種對象