Oracle DBA遇到頻次最高的五十個問題

随着資料價值在企業地位越來越高，DBA這一職位更是受到重視和支援，如何幫助各位資料達人維護好資料，也是ITPUB社群一直秉承的理念，本期我們将社群DBA關注最多的50個問題，已經各位大師對問題的解決方式進行了彙總和整理，期待大家更多給與問題和回報，以下是問題和解決方案。

問題1：如何收集統計資訊不影響資料庫？

解決方案

大多數情況下，表的統計資訊不準導緻了優化器對于執行計劃的錯誤計算，是以需要對表的統計資訊進行更正，

以便讓優化器重新選擇準确的 執行計劃。

但是在生産情況下，随意的收集統計資訊，則會給資料庫帶來隐患：

1、對重新收集統計資訊的表，對應的一些SQL可能需要重新硬解析生成執行計劃。

2、對于重新收集統計資訊的表的部分SQL來說，可能會出現收集完統計資訊了，但是執行計劃更差的情況。

3、在業務高峰期收集統計資訊，會需要額外的資源開銷，影響資料庫的性能。

在進行SQL優化時，通過檢視執行計劃，表的統計資訊以及表的具體情況，去分析是否是由于統計資訊不準導緻執行計劃有

問題，當确定了是統計資訊 的問題時，不能盲目的去收集統計資訊，需要進一步驗證“重新收集統計資訊可以提升SQL性能”。

是以在針對“重新收集統計資訊可以提升SQL性能”時， 主要介紹一下如何去重新收集統計資訊而不影響資料庫中

正在運作的SQL。

在Oracle中，統計資訊的收集，都是存儲在對應的資料字典裡，是以正常收集完統計資訊， 就會被對應的SQL去用來生成

執行計劃。 但是，Oracle也提供了一種收集完統計資訊卻不會被記錄在資料字典裡，是以也不會被對應的SQL使用， 隻有

在需要使用這些統計資訊的時候， 通過設定一些參數，才可以正常的使用這些統計資訊。

文章詳情連結：

http://blog.itpub.net/31536355/viewspace-2691584/

問題2：Oracle安裝完成後的初始密碼?

internal/oracle

sys/change_on_install

system/manager

sysman/oem_temp

問題3：怎樣計算一個表占用的空間的大小？

select owner,table_name,

NUM_ROWS,

BLOCKS*AAA/1024/1024 "Size M",

EMPTY_BLOCKS,

LAST_ANALYZED

from dba_tables

where table_name='XXX';

Here: AAA is the value of db_block_size ;

XXX is the table name you want to check

問題4：如何檢視最大會話數？

SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%%%%';

　　SQL>

　　SQL> show parameter processes

　　NAME TYPE VALUE

　　------------------------------------ ------- -----------------　aq_tm_processes integer 1

　　db_writer_processes integer 1

　　job_queue_processes integer 4

　　log_archive_max_processes integer 1

　　processes integer 200

　　這裡為200個使用者。

　　select * from v$license;

　　其中sessions_highwater紀錄曾經到達的最大會話數

問題5：通過PL/SQL連接配接資料庫，出現錯誤ORA-12154:TNS：無法解析指定的連接配接辨別符

1、使用sqlplus登陸。是否能登陸上，若能登陸上，說明tns和監聽檔案沒有問題，那就是路徑的問題了，檢查PATH和TNS_ADMIN，是否正确，若不正确，修改正确，再檢視pl/sql加載，工具-選項-OCI庫裡面的路徑是否正确。

2、使用sqlplus不能登陸，檢查服務是否開啟，然後再檢查tns和監聽裡面的host是否正确。

問題6：PL/SQL查詢出來中文亂碼

PL/SQL查詢出來中文亂碼，一把是需要修改PL/SQL Developer 編碼格式：在windows中創 建一個名為“NLS_LANG”的系統環境變量，設定其值為“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”，

然後重新啟動PL/SQL Developer，這樣檢索出來的中文内容就不會是亂碼了。如果想轉換為UTF8字元集，可以賦予“NLS_LANG”為 “AMERICAN_AMERICA.UTF8”，然後重新啟動 pl/sql developer。

問題7：GoldenGate目标端檢查發現錯誤ogg-01296

view report RORA_001找到錯誤ogg-01296對應的map表名,可以使用如下方式快速檢查:

Tail -3000 RORA_001.rpt >> /tmp/ RORA_001.rpt

Cat /tmp/ RORA_001.rpt |grep -i OGG- 會出現ogg-01296錯誤

edit report RORA_001在對應的map語句加--注釋掉，啟動複制程序，一般沒有問題,如果修改的

QQ号買賣平台

表比較多，可以使用腳本檢查

問題8：用什麼語句查詢字段

desc table_name 可以查詢表的結構

select field_name,... from ... 可以查詢字段的值

select * from all_tables where table_name like '%%%%'

select * from all_tab_columns where table_name='??'

問題9：如何在Oracle伺服器上通過SQLPLUS檢視本機IP位址

select sys_context('userenv','ip_address') from dual;

如果是登陸本機資料庫，隻能傳回127.0.0.1

問題10：如何檢視各個表空間占用磁盤情況

SQL> col tablespace format a20 SQL> select b.file_id 檔案ID号, b.tablespace_name 表空間名, b.bytes 位元組數, (b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩餘空間, sum(nvl(a.bytes,0))/(b.bytes)*100 剩餘百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id

問題11：怎樣檢視哪些使用者擁有SYSDBA、SYSOPER權限？

SQL>conn sys/change_on_install

SQL>select * from V_$PWFILE_USERS;

問題12：如何檢視現有復原段及其狀态

SQL> col segment format a30

SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS

問題13：如何改變一個字段初始定義的Check範圍

SQL> alter table xxx drop constraint constraint_name;

之後再建立新限制:

SQL> alter table xxx add constraint constraint_name check();

問題14：Oracle常用系統檔案有哪些

通過以下視圖顯示這些檔案資訊：v$database,v$datafile,v$logfile v$controlfile v$parameter;

問題15：表和索引收集資訊如何立馬生效：

請問大家有什麼辦法讓手工進行表和索引收集的資訊立馬生效，讓正在執行的SQL根據最新的統計資訊 走正确的執行計劃？

已經在執行SQL的沒法使用剛采集到的統計資訊，隻能等下一次執行，

用DBMS_stats包采集時，添加參數 NO_INVALIDATE=>false 即可，

問題16：如何測硬碟IO

一般如何粗略檢視硬碟IO的，有個存儲盤，這樣測試：

dd if=/dev/zero of=/dev/sdxx bs=8k count=250000 oflag=direct

傳回用時200多秒，速度還不到 10 MB/s

但bs換成2M,速度可以達到700 MB/s，按這個算，iops應該是1000左右吧，考慮資料庫的塊大小是8k，一開始就用8k去測

這樣測合理麼，這種iops做生産庫适合麼

一般I/O類型是混合型的。

你這個存儲是多個磁盤組成的嗎、raid5 還是别的。樣看不出來。

運作oracle程式，一般可以考慮使用orion測試

問題17：不使用索引消除排序為什麼性能更好

這種屬于稀疏結果的場景，不走這個避免排序的索引會更好些。

同樣是掃描全表資料，一般FTS比FS更快。

fts=full table scan;

fs=full index scan;

雖然fts免不了order操作，但沒結果或結果很少的話，這不會成為性能問題，顯然，這個案例系統生成計劃跑偏了。

問題18：ORA--00604怎麼解決

節點Alert日志爆出如下錯誤

ORA-00604: 遞歸 SQL 級别 1 出現錯誤

ORA-20099: Loginclosed,please connect to dba

ORA-06512:在 line

兩個問題：

1、什麼是遞歸SQL級别；

2，怎麼消除這種錯誤，怎麼排查？

這明顯是人為在觸發器裡定義的錯誤，先看看觸發器腳本怎麼寫！

問題19：如何做到讓scanip開始不自動啟動

$GI_HOME/bin/srvctl disable scan -i 1 $GI_HOME/bin/srvctl disable scan -i 2 $GI_HOME/bin/srvctl disable scan -i 3

問題20：12C中能否單獨給PDB做DG

可以針對某幾個PDB做dataguard，有一個參數 enabled_pdbs_on_standby 來控制.

問題21：DataGuard是否需要配置靜态監聽

不是一定需要配,但是最好配

duplicate 方式建立standby，如果主庫執行需要靜态監聽

broker管理，低版本需要靜态監聽

問題22：Oracle在生産環境如何劃分表空間

1.索引與資料分開沒有必要.

2.你可以根據業務來分.比如:财務1個表空間 銷售另外1個表空間.

3.另外一些大表也可以單獨配置設定一個表空間.

4.基礎表,業務字典 一個表空間等等.

5.合理的是使用分區技術.

問題23：正式環境asm要加盤，需要重新開機udev服務，需要申請停機嗎

這個不需要重新開機，找到新添加的盤，添加到ASM就可以。

問題24：OGG使用add extract exta, SourceisTable初始化後，如何接着做增量同步

如果源端是Oracle init的時候可以指定SCN，table user.*, SQLPREDICATE 'AS OF SCN 1909670000'，初始化之後再從這個SCN開始應用隊列檔案.

問題25：如何知道開發前台使用update語句更新的值

SQL TRACE 現在可能叫做 SQL monitor 或者直接10046event， 追蹤 Session 。事後分析，可以用logminer挖redo，也可以針對特定SQL類型和表啟用審計。

SQL TRACE 現在可能叫做 SQL monitor 或者直接10046event， 追蹤 Session 。

問題26：物化視圖可以基于scn開始同步麼

1、通過物化視圖進行增量同步，平時有用，效果不錯；

2、自帶的初始化方式效率，這個看你自己的環境，這個不好說，關鍵看網絡；

3、物化視圖增量重新整理一般結合主鍵或者rowid實作的，scn 估計不太好弄.

問題27：表空間的檔案設定的自動擴大，檔案32G無法自動新增資料檔案

可以給表空間多加幾個資料檔案，讓這幾個資料檔案都自動增長。

問題28：關于多節點RAC中VIP漂移的疑問

VIP漂移的規律對于連接配接資料庫，沒有關系。節點故障，導緻VIP漂移後，你連接配接哪個節點和VIP漂移到哪個節點沒有任何關系

問題29：關于B樹索引的問題

1、B*Tree的頂層節點稱為root節點，即根節點；

2、B*Tree的層數最大不是3，而是看實際需要，root節點在索引變大時會發生split，有時不止一次；

3、B*Tree的leaf節點間有雙向指針，而branch節點間沒有

問題30：控制檔案丢失如何恢複

隻是控制檔案壞了,不代表資料丢失,直接重建就可以了.

使用”recover database using backup controlfile until cancel;“進行恢複資料庫，告訴Oracle把資料庫恢複到資料檔案頭記錄的最大SCN，同時也會把控制檔案恢複到最新，最後用resetlogs打開資料庫。

問題31：Oracle的自動收集計劃是什麼量級的收集

收集是全庫範圍内的，但并不是讀取庫中所有的資料，也不是對所有修改過的表都收集，是對修改資料量達到某個門檻值的表進行收集，而且也不都是全表讀，有時是按照一定的比例收集。

問題32：expdp怎麼導出分區表的某些分區

–Excluding Table Partitions’ Data

EXCLUDE=TABLE_DATA:”IN (select partition_name from dba_tab_partitions where table_name in (‘TABLE_NAME1′,’TABLE_NAME2′…) and partition_name like ‘%%%%_P%%%%’ and substr(partition_name,-4,4) < to_char(sysdate,’YYYY’) and partition_name not like ‘%%%%P2013%%%%’)” –P20XX

–Excluding Table

EXCLUDE=TABLE:”IN (‘TABLE_NAME1′,’TABLE_NAME2′…)”

問題33：impdp remap_datafile和 remap_tablespace差別

remap_*的作用不是幫你建立表空間和資料檔案，而是在源和目标庫之間可以改變表存儲的資料檔案和表空間，比如：表在源庫中存儲在表空間tbs1上，而目标庫中沒tbs1，隻有tbs2，那麼可以用remap_tablespace将表的存儲表空間由tbs1改為目标庫中的tbs2。

問題34：怎麼知道DB用的是CBO還是RBO

一般沒有修改的話，預設優化模式是choose，如果有分析過表則會走CBO，否則RBO,可以通過強制指定單個SQL的優化類型。

問題35：RAC安裝時安裝軟體如何确定哪台伺服器為節點1

GI和資料庫都先在節點1上跑，我記得是安裝GI到運作ROOT.SH這個腳本時決定，也就是，誰先跑ROOT.SH，ASM1就給誰，後跑的就用ASM2。

問題36：oracle adg主庫故障怎樣啟用備庫

這個就是failover了。

alter database recover managed standby database finish;

alter database commit to switchover to primary;

shutdown immediate;

startup;

不過，這個要謹慎，會毀掉DG配置。

問題37：ocr和voting disk 存放在asm中的問題

ocr是叢集的注冊資訊,隻要資訊不變更可以有很多信鏡像,可以同時放在多個磁盤組中；voting disk是用來做權重vote的當然隻能是一個.

問題38：oracle dataguard 主備庫可以是不同作業系統麼

不行，必須位元組順序一緻。但是OGG可以跨OS，跨DB，支援不同版本資料庫（oracle/DB2/SQLServer等），不同硬體平台OS（linux/AIX/windows）

問題39：拉連結清單如何分區

拉連結清單資料量很大，請問怎麼按日期分區，主要查詢最近一段時間的資料

table_t1(

card_no 賬戶,

product_no 産品,

product_num 數量,

start_dt 開始日期,

end_dt 結束日期

)；

start_dt和end_dt 如果在查詢條件中都用到，你就標明一個字段，做時間範圍分區; 如果還不夠，另一個時間字段，還可以考慮劃分子分區

問題40：oracle資料庫可以暴力備份嗎？

實際上os拷貝大小與oracle塊大小不一緻.

如果你拷貝這個塊時正好有寫入這個塊,這樣問題就來了.

oracle通過尾部的tailchk記錄前面的一部分scn_base(低2位)的資訊,這樣就可能出現不一緻的情況,這樣拷貝這塊就有問題.

這也就是為什麼熱備份模式要在日志中記錄1次整個資料塊的資訊,這樣熱備份時要選擇空閑時進行.

而rman之類的工具自動修複這個錯誤.

如果拷貝時沒有寫入,也許沒有問題.

實際上還是不建議這樣的備份方式.

問題41：如何監控資料庫表增長趨勢

在巡檢的時候發現很多資料庫每天整體表空間占用率增長較快

想要通過user_segments 查詢哪些表比較大，但是一直查不出結果，可能是由于資料檔案比較多的原因。

那如何監控segment級别的資料增長呢，有沒有其他有效手段可以抓出每天增長比較多的segment對象？

找到一個視圖

DBA_HIST_SEG_STAT

問題42：oracle是通過什麼方法來控制執行個體名

先有執行個體，再有資料庫：通過ORACLE_SID找到參數檔案，參照檔案裡記錄了要啟動的資料庫；

根據目前作業系統環境變量的ORACLE_SID去ORACLE_HOME/dbs尋找對應ORACLE_SID的spfile或pfile來啟動資料庫。

問題43：Grid安裝時候搜尋不到磁盤怎麼解決

把字元裝置更改成塊裝置即可。

問題44：oracle自增字段做主鍵是否會比較快

自增主鍵本身就是增序索引，索引塊中存儲的記錄也都是按順序存在資料塊中，此時排序效率肯定要高。

問題45：請問如何修改一張表的主鍵

alter table aaa

drop constraint aaa_key ;

add constraint aaa_key primary key(a1,b1) ;

問題46：如何建立SPFILE

SQL> connect / as sysdba

SQL> select * from v$version;

SQL> create pfile from spfile;

SQL> CREATE SPFILE FROM PFILE='Era9iadmineyglepfileinit.ora';

檔案已建立。

SQL> CREATE SPFILE='Era9idatabaseSPFILEEYGLE.ORA' FROM PFILE='Era9iad

mineyglepfileinit.ora';

問題47：如何測試SQL語句執行所用的時間

SQL>set timing on ;

SQL>select * from tablename;

問題48：SQL語句如何插入全年日期

create table BSYEAR (d date);

insert into BSYEAR

select to_date('20030101','yyyymmdd')+rownum-1

from all_objects

where rownum <= to_char(to_date('20031231','yyyymmdd'),'ddd');

問題49：怎樣估算SQL執行的I/O數

SQL>SET AUTOTRACE ON ;

SQL>SELECT * FROM TABLE;

OR

SQL>SELECT * FROM v$filestat ;

問題50：如何檢視資料檔案放置的路徑

col file_name format a50

SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_fi

les order by file_id;

問題51：如何用正規表達式取出指定的方括号的值

可以嘗試用純PL/SQL來實作你這個函數，包括按逗号解析、排序、拼接都用PLSQL完成。

排序部分沒有SQL的ORDER BY那麼友善，可以試試用關聯數組，即 TABLE OF ... INDEX BY VARCHAR2(100) 這樣的，把你要排序的字元串用作數組下标。然後用 FIRST, NEXT來周遊這個稀疏數組，間接完成了排序的動作。