面試題目

 

一、 技術問題部分(資料庫部分)

1、 表空間的管理方式有哪幾種？
資料字典管理方式  本地檔案管理方式
fet$ , uet$ 

2、 談談你對索引的理解？索引是若干資料行的關鍵字的列表，查詢資料時，
透過索引中的關鍵字可以快速定位到要訪問的記錄所在的資料塊，從而大大
減少讀取資料塊的I/O次數，因此可以顯著提高效能。

3、 說說索引的組成?
索引列、rowid

4、 分割槽表用過麼？談談分割槽表的應用？
分割槽表在對錶的DML的並行處理上有極大得優勢，而且可以一部分設為只讀，
用在銷售記錄，醫院處方等地方！！

5、 你對分割槽表的理解?怎樣截斷分割槽表一個分割槽的記錄?

一個分割槽表有一個或多個分割槽，每個分割槽透過使用範圍分割槽、雜湊分割槽、或組合分割槽分割槽
的行分割槽表中的每一個分割槽為一個段，可各自位於不同的表空間中對於同時能夠使用幾個
程式進行查詢或操作的大型表分割槽非常有用

alter table table_name truncate partition partition_name;

6、 物理檔案有哪幾種?控制檔案包含了什麼資訊?
1） 資料檔案 2）控制檔案 3）日誌檔案 包含維護和驗證有選舉權據庫完整
性的必要資訊、例如，控制檔案用於識別資料檔案和重做日誌檔案，一個有選
舉權據庫至少需要一個控制檔案

 

7、 表空間用完了如何增加? 三種種擴充套件方式：
1 增加資料檔案 alter tablespace name add datafile ‘路徑’ size 4M;
2 擴充套件資料檔案大小alter database datafile ‘路徑’ resize 4M;
3 把資料檔案設為自動增長 alter database datafile ‘路徑’ autoextend on next 1M maxsize 20M;

 

8、 SGA包含幾部分?
Shared pool(共享池)，DataBase Buffer Cache(資料緩衝區) Redo Log Buffer
(重做日誌緩衝區), Large Pool,大池，JAVA池。

 

9、 DECODE函式的用法?

DECODE的語法：
DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等於if1時，
DECODE函式的結果返回then1,...,如果不等於任何一個if值，則返回else。初看一下，
DECODE 只能做等於測試，但剛才也看到了，我們透過一些函式或計算替代value，
是可以使DECODE函式具備大於、小於或等於功能。還可以轉化表結構！

 

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

 

11、 談談你對事務的理解?
ORACLE事務從COMMIT、ROLLBACK、連線到資料庫或開始第一 條可執行的SQL語句時開始，
到一條COMMIT、ROLLBACK語句或退出 資料庫時結束。如果在一個事務中包含DDL語句，則
在DDL語句的 前後都會隱含地執行COMMIT語句，從而開始或結束一個事務。 如果一個事務
由於某些故障或者由於使用者改變主意而必須在 提交前取消它，則資料庫被恢復到這些語句
和過程執行之前的狀 態。 利用ROLLBACK語句可以在COMMIT命令前隨時撤消或回退一個 事務。
可以回退整個事務，也可以會退部分事務，但是不能回退 一個已經被提交的事務。回退部分
事務的ROLLBACK命令為： ROLLBACK to savepoint 儲存點名 儲存點是使用者放入事務中的標記，
用來表示一個可被回退的 位置。儲存點透過在事務中放入一個SAVEPOINT命令而被插入。
該 命令的語法是： SAVEPOINT 儲存點名 如果在ROLLBACK語句中沒有給出儲存點名，
則整個事務被回 退。

 

12、 給表A加一列abc口述?

資料庫正在應用時應先使資料庫處於靜默狀態再
alter table a add abc varchar2(10)，如果不是就直接用！！

 

13、 客戶端連線伺服器需要修改什麼檔案?

tnsname.ora，listener.ora

 

 

14、 索引重建的概念? 說說索引重建的意義?

當我們建立索引時，oracle會為索引建立索引樹，表和索引樹透過rowid(偽列)來定位資料。
當表裡的資料發生更新時，oracle會自動維護索引樹。但是在索引樹中沒有更新操作，只有
刪除和插入操作。 例如在某表id列上建立索引，某表id列上有值“101”，當我將“101”更
新為“110”時，oracle同時會來更新索引樹，但是oracle先將索引樹中的“101”標示為刪除
（實際並未刪除，只是標示一下），然後再將“110”寫到索引樹中。 如果表更新比較頻繁，
那麼在索引中刪除標示會越來越多，這時索引的查詢效率必然降低，所以我們應該定期重建索引。
來消除索引中這些刪除標記。 一般不會選擇先刪除索引，然後再重新建立索引，而是rebuild索引。
在rebuild期間，使用者還可以使用原來的索引，並且rebuild新的索引時也會利用原來的索引資訊，
這樣重建索引會塊一些。

 

15、 你對遊標的理解, 遊標的分類,使用方法?

遊標是結果集資料中的指標,作用是為遍歷結果集時,儲存每條記錄的結果,
1.宣告遊標,
2.開啟遊標,
3.提取遊標,
4.關閉遊標

遊標for迴圈

顯式遊標，隱式遊標，遊標變數！

 

16、 儲存過程的用法?  在儲存過程中異常的處理,分類?  舉兩個預定義異常的例子?

儲存過程：是預編譯資料庫SQL的集合儲存過程是預編譯過的，
執行時勿須編譯，執行速度更快，儲存過程封裝了一批SQL語句，便於維護資料的完整性
與一致性。並且可以多次呼叫。異常：自定義，預定義，
非預定義 No_Data_Found    Too_Many_Rows    CASE_NOT_FOUND

 

17、 談談你對分割槽索引的理解,說說分類和區別?

分割槽表和一般表一樣可以建立索引，分割槽表可以建立區域性索引和全域性索引。當分割槽中出現許多
事務並且要保證所有分割槽中的資料記錄的唯一性時採用全域性索引。

1.3.1. 區域性索引分割槽的建立：
SQL> create index dinya_idx_t on dinya_test(item_id)
2 local
3 (
4 partition idx_1 tablespace dinya_space01,
5 partition idx_2 tablespace dinya_space02,
6 partition idx_3 tablespace dinya_space03
7 );

 

看查詢的執行計劃，從下面的執行計劃可以看出，
系統已經使用了索引：

SQL> select * from dinya_test partition(part_01) t where t.item_id=12;
Execution Plan ----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=187) 1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF ’DINYA_TEST’ (Cost= 2 Card=1 Bytes=187)
2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1 Card=1) Statistics ----------------------------------------------------------
0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 334 bytes sent via SQL*Net to client 309 bytes
received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed SQL> 　　

1.3.2. 全域性索引分割槽的建立　　
全域性索引建立時global 子句允許指定索引的範圍值，這個範圍值為索引欄位的範圍值：
SQL> create index dinya_idx_t on dinya_test(item_id)
2 global partition by range(item_id)
3 (
4 partition idx_1 values less than (1000) tablespace dinya_space01,
5 partition idx_2 values less than (10000) tablespace dinya_space02,
6 partition idx_3 values less than (maxvalue) tablespace dinya_space03
7 );
Index created.

本例中對錶的item_id欄位建立索引分割槽，當然也可以不指定索引分割槽名直接對整個表建立索引，
如： SQL> create index dinya_idx_t on dinya_test(item_id);

同樣的，對全域性索引根據執行計劃可以看出索引已經可以使用： SQL> select * from dinya_test
t where t.item_id=12; Execution Plan ----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=3 Bytes=561) 1 0 TABLE ACCESS
 (BY GLOBAL INDEX ROWID) OF ’DINYA_TEST’ (Cost =2 Card=3 Bytes=561)
2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1 Card=3) Statistics
----------------------------------------------------------
5 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 420
bytes sent via SQL*Net to client 309 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 5 rows processed

 

18、 建立使用者的語句?

Create user user_name identified by password
default tablespace tablespacename;
grant create session to username;

19、 談談你對例項的理解?

Oracle是用例項來進行資料庫管理的，例項在使用者和orale資料庫之間充當中間層的角色。
每當在伺服器上啟動資料庫時，就在記憶體中建立一個oracle例項，即oracle為資料庫分配
記憶體和建立並啟動一個或多個oracle程式，然後由例項載入並開啟資料庫，最後由這個實
例來訪問和控制硬碟中的資料檔案。

20、 觸發器中能用COMMIT,為什麼?

在觸發器中不能使用COMMIT；等事務控制語句。因為觸發器是事務觸發的如果有事務控制
語句就會影響到觸發它的事務。即連帶觸發它的語句之前的已經完成的沒有提交的語句都
要受到影響。這是會影響到資料的一致性的。

 

21、 有幾種表空間?
資料表空間（永久表空間）、臨時表空間、撤銷表空間

 

22、 歸檔模式與非歸檔模式?為什麼歸檔模式可以恢復到任意時刻? 

歸檔是在重做日誌檔案被覆蓋之前，將該重做日誌檔案透過複製作業系統檔案的 方式，
儲存到指定的位置。儲存下來的重做日誌檔案的集合被稱為歸檔重做日誌檔案，複製
的過程被稱為歸檔。在歸檔日誌（ARCHIVELOG）模式下，ORACLE對重做日誌檔案進行
歸檔操作。非歸檔日誌（NOARCHIVELOG）模式下，不會對重做日誌檔案進行歸檔操作。
由於非歸檔模式不會在覆蓋之前儲存，這樣就造成了資料庫在一定時間之前的重做日
志檔案丟失，也就不能恢復到被覆蓋之前。而歸檔模式在任意時間上都有重做日誌檔案，
故可以恢復到任意時刻。

 

 

 

 

 

ORACLE 面試問題-技術篇

1. 解釋冷備份和熱備份的不同點以及各自的優點
解答：熱備份針對歸檔模式的資料庫，在資料庫仍舊處於工作狀態時進行備份。而冷
備份指在資料庫關閉後，進行備份，適用於所有模式的資料庫。熱備份的優點在於當
備份時，資料庫仍舊可以被使用並且可以將資料庫恢復到任意一個時間點。冷備份的
優點在於它的備份和恢復操作相當簡單，並且由於冷備份的資料庫可以工作在非歸檔
模式下,資料庫效能會比歸檔模式稍好。（因為不必將archive log寫入硬碟）

 

2. 你必須利用備份恢復資料庫，但是你沒有控制檔案，該如何解決問題呢？
解答：重建控制檔案，用帶backup control file 子句的recover 命令恢復
資料庫。

   

3. 如何轉換init.ora到spfile?

 

解答：使用create spfile from pfile 命令

.

4. 解釋data block , extent 和 segment的區別（這裡建議用英文術語）  

 

解答：data block是資料庫中最小的邏輯儲存單元。當資料庫的物件需要更多的物
理儲存空間時，連續的data block就組成了extent . 一個資料庫物件

擁有的所有extents被稱為該物件的segment.

 

5. 給出兩個檢查表結構的方法 
解答：1。DESCRIBE命令
  2． DBMS_METADATA.GET_DDL 包

 

6. 怎樣檢視資料庫引擎的報錯

 

解答：alert log.

 

7. 比較truncate和delete 命令 

 

解答：兩者都可以用來刪除表中所有的記錄。區別在於：truncate是DDL操作，
它移動HWK，不需要 rollback segment .而Delete是DML操作, 需要rollback
segment 且花費較長時間.

 

8. 使用索引的理由

解答：快速訪問表中的data block

 

9. 給出在STAR SCHEMA中的兩種表及它們分別含有的資料

解答：Fact tables 和dimension tables. fact table 包含大量的主要的資訊而 dimension tables 存放對fact table 某些屬性描述的資訊

 

10. FACT Table上需要建立何種索引？

解答：點陣圖索引 （bitmap index）

 

11. 給出兩種相關約束?

 

解答：主鍵和外來鍵

 

12. 如何在不影響子表的前提下，重建一個母表

 

解答：子表的外來鍵強制實效，重建母表，啟用外來鍵

 

13. 解釋歸檔和非歸檔模式之間的不同和它們各自的優缺點

 

解答：歸檔模式是指你可以備份所有的資料庫 transactions並恢復到任意一個時間點。非歸檔模式則相反，不能恢復到任意一個時間點。但是非歸檔模式可以帶來資料庫效能上的少許提高

.

14. 如何建立一個備份控制檔案？

 

解答：Alter database backup control file to trace.

 

15. 給出資料庫正常啟動所經歷的幾種狀態 ?

 

解答：

 

STARTUP NOMOUNT – 資料庫例項啟動

STARTUP MOUNT - 資料庫裝載

STARTUP OPEN – 資料庫開啟

 

16. 哪個column可以用來區別V$檢視和GV$檢視?

 

解答： INST_ID 指明叢集環境中具體的 某個instance 。

 

17. 如何生成explain plan? 

 

解答：

  執行utlxplan.sql. 建立plan 表 

  針對特定SQL語句，使用 explain plan set statement_id = 'tst1' into plan_table 

  執行utlxplp.sql 或 utlxpls.sql察看explain plan

 

18. 如何增加buffer cache的命中率？

 

解答：在資料庫較繁忙時，適用buffer cache advisory 工具，查詢v$db_cache_advice . 如果有必要更改，可以使用 alter system set db_cache_size 命令

 

19. ORA-01555的應對方法？ 

 

解答：具體的出錯資訊是snapshot too old within rollback seg , 通常可以透過

增大rollback seg來解決問題。當然也需要察看一下具體造成錯誤的SQL文字

 

20. 解釋$ORACLE_HOME和$ORACLE_BASE的區別？

解答：ORACLE_BASE是oracle的根目錄，ORACLE_HOME是oracle產品

的目錄。 

21. 如何判斷資料庫的時區？
解答：SELECT DBTIMEZONE FROM DUAL;

22. 解釋GLOBAL_NAMES設為TRUE的用途
解答：GLOBAL_NAMES指明聯接資料庫的方式。如果這個引數設定為TRUE,
在建立資料庫連結時就必須用相同的名字連結遠端資料庫

23。如何加密PL/SQL程式？
解答：WRAP

24. 解釋FUNCTION,PROCEDURE和PACKAGE區別
解答：function 和procedure是PL/SQL程式碼的集合，通常為了完成
一個任務。procedure 不需要返回任何值而function將返回一個值
在另一方面，Package是為了完成一個商業功能的一組function和proceudre
的集合

25. 解釋TABLE Function的用途
解答：TABLE Function是透過PL/SQL邏輯返回一組紀錄，用於
普通的表/檢視。他們也用於pipeline和ETL過程。

26. 舉出3種可以收集three advisory statistics
解答：Buffer Cache Advice, Segment Level Statistics, Timed Statistics

27. Audit trace 存放在哪個oracle目錄結構中? 
解答：unix $ORACLE_HOME/rdbms/audit
  Windows the event viewer

28. 解釋materialized views的作用 
解答：Materialized views 用於減少那些彙總，集合和分組的
資訊的集合數量。它們通常適合於資料倉儲和DSS系統。

29. 當使用者程式出錯，哪個後臺程式負責清理它
解答： PMON

30. 哪個後臺程式重新整理materialized views? 
解答：The Job Queue Processes.

31. 如何判斷哪個session正在連結以及它們等待的資源？ 
解答：V$SESSION / V$SESSION_WAIT

32. 描述什麼是 redo logs
解答：Redo Logs 是用於存放資料庫資料改動狀況的物理和邏輯結構。
可以用來修復資料庫.

33. 如何進行強制LOG SWITCH?
解答：ALTER SYSTEM SWITCH LOGFILE;

34. 舉出兩個判斷DDL改動的方法？ 
解答：你可以使用 Logminer 或 Streams

35. Coalescing做了什麼？
解答：Coalescing針對於字典管理的tablespace進行碎片整理，將
臨近的小extents合併成單個的大extent.

36. TEMPORARY tablespace和PERMANENT tablespace 的區別是？
解答：A temporary tablespace 用於臨時物件例如排序結構而 permanent tablespaces
用來儲存那些'真實'的物件(例如表，回滾段等）

37. 建立資料庫時自動建立的tablespace名稱？ 
解答：SYSTEM tablespace.

38. 建立使用者時，需要賦予新使用者什麼許可權才能使它聯上資料庫。
解答：CONNECT

39. 如何在tablespace裡增加資料檔案？
解答：ALTER TABLESPACE ADD DATAFILE SIZE

40. 如何變動資料檔案的大小？
解答：ALTER DATABASE DATAFILE RESIZE ;

41. 哪個VIEW用來檢查資料檔案的大小？
解答： DBA_DATA_FILES

42. 哪個VIEW用來判斷tablespace的剩餘空間
解答：DBA_FREE_SPACE

43. 如何判斷誰往表裡增加了一條紀錄？
解答：auditing 

44. 如何重構索引？
解答： ALTER INDEX REBUILD;

45. 解釋什麼是Partitioning（分割槽） 以及它的優點。
解答：Partition將大表和索引分割成更小，易於管理的分割槽。

46. 你剛剛編譯了一個PL/SQL Package但是有錯誤報導，如何顯示出錯資訊？
解答：SHOW ERRORS

47. 如何蒐集表的各種狀態資料？
解答： ANALYZE
The ANALYZE command.

48. 如何啟動SESSION級別的TRACE
解答: DBMS_SESSION.SET_SQL_TRACE 
  ALTER SESSION SET SQL_TRACE = TRUE;

49. IMPORT和SQL*LOADER 這2個工具的不同點
解答：這兩個ORACLE工具都是用來將資料匯入資料庫的。
區別是：IMPORT工具只能處理由另一個ORACLE工具EXPORT生成
的資料。而SQL*LOADER可以匯入不同的ASCII格式的資料來源

50。 用於網路連線的2個檔案？
解答： TNSNAMES.ORA and SQLNET.ORA

SYS使用者預設密碼是多少 change_on_install

EXP/IMP ， EXPDP/IMPDP 使用什麼pool，如何加快速度。

Oracle I/O , OS I/O  ?

 

硬解析，軟解析，軟軟解析 ：

Oracle在執行SQL語句時，普遍存在以下幾個步驟：

當SQL語句首次執行，Oracle將確認該句語句的語法是否正確（語法解析Syntax parse）
並進一步確認語句相關表和列的存在性等因素(語義解析semantic parse)以及最佳化器決
定執行計劃等步驟。整個過程稱之為硬解析，硬解析消耗大量的CPU時間和系統資源。
硬解析過多會有效降低系統效能。

若之前已進行過硬解析，且解析後的分析樹和執行計劃仍存在於共享池中，則同樣的
SQL僅需要軟解析。軟解析將輸入的SQL語句轉換為雜湊程式碼，同共享池內雜湊連結串列上
的已有記錄進行對比，找出對應的遊標資訊，使用已有的執行計劃執行。

sql在執行之後oracle會試圖關閉cursor，關閉之前會檢查是否設定了引數session_cached_cursors，如果設定了，那麼還會判斷即
將要關閉的cursor對用的sql的parse_calls次數，如果達到3次(注意包括3次)，那麼此時oracle不會關閉cursor了，而是把cursor相
關的資訊放到該session的uga中儲存起來，以便該session下次執行相同的sql而不需要重新產生soft parse，因為該sql的cursor資訊
直接可以從uga中找到，避免soft parse最終節約的資源當然是cpu同時也減少了library cache latch事件的等待...，oracle把這種
parse稱為soft soft parse(softer parse)

淘寶網的問題

資料庫切換日誌的時候，為什麼一定要發生檢查點？這個檢查點有什麼意義？

表空間管理方式有哪幾種，各有什麼優劣。

本地索引與全域性索引的差別與適用情況。

一個表a varchar2(1),b number(1),c char(2)，有100000條記錄，建立B-Tree
索引在欄位a上，那麼表與索引誰大？為什麼？

9i的data guard有幾種模式，各有什麼差別。 最大效能，最大可用性，最大保護

執行計劃是什麼，檢視執行計劃一般有哪幾種方式。

簡單描述一下nest loop與hash join的差別。

db file sequential read與db file scattered read等待的差別，如果以上等待比較多，證明了什麼問題？

library cache pin與library cache lock是什麼地方的等待事件，一般說明什麼問題？

在一個24*7的應用上，需要把一個訪問量很大的1000萬以上資料級別的表的普通索引(a,b)修改成唯一約束(a,b,c)，你一般會選擇怎麼做，請說出具體的操作步驟與語句。

如果一個linux上的oracle資料庫系統突然變慢，你一般從哪裡去查詢原因。

說明一下對raid5與raid01/10的認識。

列舉5個10g的新特性
 

使用篇

　　1：使用索引查詢一定能提高查詢的效能嗎?舉出例項

　　2：資料庫設計的一、二、三正規化、BCNF是什麼?

　　3：StoreProcedure和Function有何區別?

　　4：如何跟蹤某個session的SQL?

　　5：如何使用CBO,CBO與RULE的區別?

　　6：描述tablespace和datafile之間的關係

　　7：truncate和delete有何區別?哪一種操作效能更快?

　　8：什麼是Snapshot?與View有何區別?

　　9：pctused and pctfree 表示什麼含義有什麼作用

　　10：cursor一般用在什麼地方?有何弱點?

　　管理篇

　　1：SQL調整最關注的是什麼?

　　2：Oracle的系統程式有哪些?作用是什麼?

　　3：本地管理表空間和字典管理表空間的特點，ASSM有什麼特點?

　　4：備份如何分類?

　　5：如果一個表被drop，在有完善的歸檔和備份的情況下，如何恢復

　　6：rman是什麼，有何特點?

　　7：standby的特點

　　8：對於一個要求恢復時間比較短的系統(資料庫50G,每天歸檔5G)，你如何設計備份策略

　　9：對於一個存在系統效能的系統，說出你的診斷處理思路

　　10：列舉幾種診斷IO、CPU、效能狀況的方法

　　11：對statspack有何認識

　　12：如果系統現在需要在一個很大的表上建立一個索引，你會考慮那些因素，如何做以儘量減小對應用的影響

　　13：對raid10 和raid5有何認識

　　14：SGA主要有那些部分，主要作用是什麼

　　15：簡單描述table / segment / extent / block之間的關係

　　16：說說你對索引的認識(索引的結構、對dml影響、對查詢影響、為什麼提高查詢效能)

 

例項恢復的簡單描述：

datafile :     end scn ,
controlfile :  system ckpt scn, datafile scn, start scn .

buffer cache中的checkpoint queue (checkpoint position) .

控制檔案中的checkpoint position ,  在redo log中找到對應的checkpoint position ，
將此點後面的重做條目寫入buffer, 進行前滾。open後根據redo log中記錄的commit marker
進行回滾。

DBWR負責寫檢查點佇列上的髒資料塊，而CKPT負責記錄當前檢查點佇列的第一個
資料塊對應的重做條目在日誌檔案中的位置。

large pool :  分擔shared pool，MTS時UGA使用， 並行, MTS, 備份恢復使用I/O Slave .