Oracle面試題彙總

1，delete 與Truncate差別？

1）Truncate 是DDL 語句，DELETE 是DML語句。

2) Truncate 的速度遠快於DELETE；

原因是：當執行DELETE操作時所有表格資料先被COPY到回滾表空間，資料量不同花費時間長短不一。而TRUNCATE 是直接刪除資料不進回滾表空間。

3） delete 資料可以運行Rollback 進行資料回滾。而Truncate 則是永久刪除不能回滾。

4) Truncate 操作不會觸發表上的delete觸發器，而delete 會正常觸發。

5） Truncate 語句不能帶where 條件表示只能全部資料刪除，而DELETE可帶where 條件刪除資料。

6） Truncate 操作會重置表的高水位線（High Water Mark）,而delete 不會。

【專題推薦】：2020年oracle面試題彙整（最新）

2，集合運算子

Union ：不包含重複值，預設依第一個查詢的第一列升序排列。

Union All : 完全並集包含重複值。不排序。

Minus 不包含重複值，不排序。

3，資料庫的三大範式是什麼？

1）第一範式：原子件，要求每一列的值不能再拆分了。

2）第二範式：一張表只描述一個實體（若列中有冗餘數據，則不滿足）

3）第三範式：所有列與主鍵值直接相關。

4 事務的特性（ACID）是指什麼？

1）原子性（Atomic）：事務中的各項操作，要麼全做要麼全不做，任何一項操作的失敗都會導致整個事務的失敗。

2) 一致性（Consistent）：交易結束後系統狀態是一樣的。

3）隔離性（Isolated）: 並發執行的交易彼此無法看到對方的中間狀態。

4）持久性（Durable）:交易完成後，即使發生災難性故障，透過日誌和同步備份可以在故障發生後重建資料。

5 Mysql資料庫與Oracle 資料庫有什麼不同？

1，應用程式方面，Mysql 是中小型應用的資料庫。一般用於個人和中小型企業。 Oracle 屬於大型資料庫，一般用於具有相當規模的企業應用。

2，自動成長的資料類型面向： MySQL有自動成長的資料型態。 Oracle 沒有自動成長的資料型態。需要建立一個自增序列。

3，group by 用法： Mysql 中group by 在SELECT 語句中可以隨意使用，但在ORACLE 中如果查詢語句中有群組函數，那麼其他欄位必須是群組函數處理過的或是group by子句中的列，否則會報錯。

4,引導方面： MySQL中可以用單引號、雙引號包起字串，Oracle 中只可以用單引號包起字串

6 Oracle跟SQL Server 2005的差別？

宏觀：
1). 最大的區別在於平台，oracle可以運行在不同的平台上，sql server只能運行在windows平台上，由於windows平台的穩定性和安全性影響了sql server的穩定性和安全性
2). oracle使用的腳本語言為PL-SQL，而sql server使用的腳本為T-SQL
微觀上：從資料類型,資料庫的結構等等回答

7 . 如何使用Oracle的遊標？

1). oracle中的遊標分為顯示遊標和隱式遊標
2). 顯示遊標是用cursor...is指令定義的遊標，它可以對查詢語句(select)傳回的多筆記錄進行處理；隱含遊標是在執行插入(insert)、刪除(delete)、修改(update)和傳回單一記錄的查詢(select)語句時由PL/SQL自動定義的。
3). 明確遊標的操作：開啟遊標、操作遊標、關閉遊標；PL/SQL隱含地開啟SQL遊標，並在它內部處理SQL語句，然後關閉它

#8 Oracle中function和procedure的差別？

1). 可以理解函數是預存程序的一種
2). 函數可以沒有參數,但是一定需要一個回傳值，預存程序可以沒有參數,不需要回傳值
3). 函數return回傳值沒有回傳參數模式，預存程序透過out參數傳回值, 如果需要傳回多個參數則建議使用預存程序
4). 在sql資料操縱語句中只能呼叫函數而不能呼叫預存程序

9 Oracle的導入匯出有幾種方式，有何不同？

1). 使用oracle工具exp/imp
2). 使用plsql相關工具
方法1. 導入/導出的是二進位的數據， 2.plsql導入/導出的是sql語句的文字檔

3） sqlloader

4) dblink

10 . 解釋冷備份和熱備份的不同點以及各自的優點？

冷備份發生在資料庫已經正常關閉的情況下，將關鍵性檔案拷貝到另一個位置的一種說法
熱備份是在資料庫運作的情況下，採用歸檔方式備份資料的方法

冷備的優點和缺點：
#1)．是非常快速的備份方法（只需拷貝文件）
2)．容易歸檔（簡單拷貝即可）
3)．容易恢復到某個時間點上（只要將文件再拷貝回去）
#4)．能與歸檔方法結合，作資料庫「最新狀態」的恢復。
5)．低度維護，高度安全。
冷備份不足：
1)．單獨使用時，只能提供到「某一時間點上」的恢復。
2)．在實作備份的整個過程中，資料庫必須要作備份而不能作其它工作。也就是說，在冷備份過程中，資料庫必須是關閉狀態。
3)．若磁碟空間有限，只能拷貝到磁帶等其它外部儲存裝置上，速度會很慢。
4)．不能按表或依使用者恢復。

熱備的優缺點
1)．可在表空間或資料檔案層級備份，備份時間短。
2)．備份時資料庫仍可使用。
3)．可達到秒級恢復（恢復到某一時間點）。
4)．可對幾乎所有資料庫實體作復原。
5)．恢復是快速的，在大多數情況下在資料庫仍工作時恢復。
熱備份的缺點是：
  1)．不能出錯，否則後果嚴重。
  2)．若熱備份不成功，所得結果不可用於時間點的復原。
  3)．因難於維護，所以要特別仔細小心，不允許「以失敗而告終」。

11 解釋data block , extent 與 segment的差異？

data block 資料塊，是oracle最小的邏輯單位，通常oracle從磁碟讀寫的就是塊
extent 區，是由若干個相鄰的block組成
segment段，是有一組區組成
tablespace表空間，資料庫中資料邏輯儲存的地方，一個tablespace可以包含多個資料檔

12. 解釋什麼是死鎖，如何解決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;
,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#";

簡述oracle中dml、ddl、dcl的使用

### ##Dml 資料操縱語言，如select、update、delete，insert ###Ddl 資料定義語言，如create table 、drop table 等等 ###Dcl 資料控制語言，如commit、rollback、grant、 invoke等 ## #

14 如何建立一個索引,索引使用的原則,有什麼優點和缺點

建立標準索引：
CREATE INDEX 索引名ON表名(列名) TABLESPACE 表空間名;
建立唯一索引:
CREATE unique INDEX 索引名ON 表名(列名) TABLESPACE 表空間名;

###################建立組合索引: ######CREATE INDEX 索引名ON 表名(列名1,列名2) TABLESPACE 表空間名稱; ######建立反向鍵索引: #######CRE 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. 索引需要佔實體空間，除了資料表佔資料空間之外，每個索引還要佔一定的實體空間，如果要建立叢集索引，那麼需要的空間就會更大 ###4. 當表格中的資料增加、刪除和修改的時候，索引也要動態的維護，降低了資料的維護速度 ####

15 oracle的鎖定又幾種,定義分別是什麼;

1.  行共享鎖定(ROW SHARE)
#2.  行排他鎖(ROW EXCLUSIVE)
3 . 共享鎖(SHARE)
4.  共享行排他鎖(SHARE ROW EXCLUSIVE)
5.  排他鎖(EXCLUSIVE)
使用方法：
SELECT * FROM order_master WHERE vencode="V002"
FOR UPDATE WAIT 5;
LOCK TABLE order_master IN SHARE MODE;
LOCK TABLE itemfile# IN EXCLUSIVE MODE #IT; ##ORACLE鎖定具體分為以下幾類：
1.依使用者與系統劃分，可分為自動鎖定與顯示鎖定
自動鎖定：當進行一項資料庫操作時，預設情況下，系統會自動為此資料庫操作取得所有必要的鎖定。
顯示鎖定：某些情況下，需要使用者顯示的鎖定資料庫操作要用到的數據，才能使資料庫操作執行得更好，顯示鎖定是使用者為資料庫物件設定的。
2 . 以鎖定層級劃分，可分為共享鎖定與排它鎖定
共享鎖定：共享鎖定使一個事務對特定資料庫資源進行共享存取— —另一事務也可對此資源進行存取或取得相同共用鎖定。共享鎖為交易提供高並發性，但如拙劣的事務設計共享鎖容易造成死鎖或資料更新遺失。
排它鎖定：事務設定排它鎖定後，該事務單獨取得此資源，另一事務不能在此事務提交之前獲得相同物件的共享鎖定或排它鎖定。
3.依作業劃分，可分為DML鎖定、DDL鎖定
#DML鎖定又可分為，行鎖定、表鎖定、死鎖
行鎖定：當交易執行資料庫插入、更新、刪除操作時，該交易會自動取得操作表中操作行的排它鎖定。
表格層級鎖定：當交易取得行鎖定後，此交易也會自動取得該行的表鎖定(共用鎖定),以防止其它交易進行DDL語句影響記錄行的更新。事務也可以在進行過程中獲得共享鎖或排它鎖，只有當事務顯示使用LOCK TABLE語句顯示的定義一個排它鎖時，事務才會獲得表上的排它鎖,也可使用LOCK TABLE顯示的定義一個表級的共享鎖(LOCK TABLE具體用法請參考相關文件)。
死鎖：當兩個事務需要一組有衝突的鎖，而不能將事務繼續下去的話，就出現死鎖。
如事務1在表A行記錄#3中有一排它鎖，並等待事務2在表A中記錄#4中排它鎖的釋放，而事務2在表A記錄行#4中有一排它鎖，並等待事務; 1在表A中記錄#3中排它鎖的釋放，事務1與事務2彼此等待，因此就造成了死鎖。死鎖一般是因拙劣的事務設計而產生。死鎖只能使用SQL下:alter system kill session "sid,serial#"；或使用相關作業系統kill進程的指令，如UNIX下kill -9 sid,或使用其它工具殺掉死鎖程序。 DDL鎖定又可以分為：排它DDL鎖定、共享DDL鎖定、分析鎖定

排它DDL鎖定：建立、修改、刪除一個資料庫物件的DDL語句獲得操作物件的排它鎖。如使用alter table語句時，為了維護資料的完成性、一致性、合法性，該事務獲得一排它DDL鎖定。
共享DDL鎖定：需在資料庫物件之間建立相互依賴關係的DDL語句通常需共享取得DDL鎖定。
如建立一個包，該包中的過程與函數引用了不同的資料庫表，當編譯此包時，該事務就獲得了引用表的共享DDL鎖定。分析鎖定：
ORACLE使用共享池儲存分析與最佳化的SQL語句及PL/SQL程序，讓執行相同語句的應用速度更快。一個在共享池中緩存的物件獲得它所引用資料庫物件的分析鎖。分析鎖是一種獨特的DDL鎖類型，ORACLE使用它追蹤共享池物件及它所引用資料庫物件之間的依賴關係。當一個事務修改或刪除了共享池持有分析鎖的資料庫物件時，ORACLE使共享池中的物件作廢，下次在引用這條SQL/PLSQL語句時，ORACLE重新分析編譯此語句。
4.內部閂鎖
#內部閂鎖：這是ORACLE中的一種特殊鎖，用於順序存取內部系統結構。當事務需向緩衝區寫入資訊時，為了使用此區塊記憶體區域，ORACLE首先必須取得這塊記憶體區域的閂鎖，才能向此區塊記憶體寫入資訊。