Oracle實用技巧

oracle|技巧     1.找出無用索引:
　　
　DML 效能低下，其中最嚴重的原因之一是無用索引的存在。所有SQL的插入，更新和刪除操作在它們需要在每一行資料被改變時修改大量索引的時候會變得更慢。許多Oracle 管理員只要看見在一個SQL 查詢的WHERE語句出現了一列的話就會為它分配索引。雖然這個方法能夠讓SQL運行得更快速，但是基於功能的Oracle 索引使得資料庫管理員有可能在資料表的行上資源過度分派索引。資源過度分派索引會嚴重影響關鍵Oracle 資料表的效能。
　　
　在Oracle9i出現以前，沒有辦法確定SQL查詢沒有使用的索引。Oracle9i有一個工具能夠讓你使用ALTER INDEX命令監視索引的使用。然後你可以尋找這些沒有使用的索引並從資料庫裡刪除它們。
　　
　下面是一段指令碼，它能夠開啟一個系統中所有索引的監視功能:
　　
　　spool run_monitor.sql
　　
　　select 'alter index '||owner||'.'||index_name||' monitoring usage;'
　　
　　from dba_indexes
　　
　　where owner not in ('SYS','SYSTEM');
　　
　　spool off;
　　
　　@run_monitor
　　
　你需要等待一段時間直到在資料庫上運行了足夠多的SQL語句以後，然後你就可以查詢新的V$OBJECT_USAGE視圖。
　　
　　select index_name,table_name,mon,used
　　
　　from v$object_usage;
　　
　在下面，我們可以看見V$OBJECT_USAGE有一列被稱作USED，它的值是YES或者NO。它不會告訴你Oracle使用了這個索引多少次，但是這個工具對於找出沒有使用的索引還是很有用的。
　　
　　SQL> select * from v$object_usage where rownum < 10;
　　
　　INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
　　
　　------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
　　
　　ASD DIM_ACCT_ITEM_TYPE_TEMP YES NO 01/15/2004 13:50:59
　　
　　IDX_ACCOUNT_ACCESSORY_TARIFF1 ACCOUNT_ACCESSORY_TARIFF YES NO 01/15/2004 13:50:59
　　
　　IDX_ACCOUNT_QUOTA_LOG1 ACCOUNT_QUOTA_LOG YES NO 01/15/2004 13:50:59
　　
　　IDX_ACCOUNT_SYSTEM_PARAMETERS1 ACCOUNT_SYSTEM_PARAMETERS YES NO 01/15/2004 13:50:59
　　
　　IDX_ACCT2 ACCT YES NO 01/15/2004 13:50:59
　　
　　IDX_ACCT3 ACCT YES NO 01/15/2004 13:51:00
　　
　　IDX_ACCT4 ACCT YES NO 01/15/2004 13:51:00
　　
　　IDX_ACCT_BIND_DISCT1 ACCT_BIND_DISCT YES NO 01/15/2004 13:51:00
　　
　　IDX_ACCT_BIND_DISCT2 ACCT_BIND_DISCT YES NO 01/15/2004 13:51:00
　　
　2.查看一個很長的操作已經做了多少:
　　
　v$session_longops視圖可以使Oracle專家減少已耗用時間很長的DDL和DML語句的已耗用時間。例如在資料倉儲環境中，即使使用並行索引建立技術，構建一個很多G位元組大的索引需要耗費很多個小時。這裡你就可以查詢v$session_longops視圖快速找出一個特定的DDL語句已經完成了多少。其實v$session_longops視圖也可以用於任何已耗用時間很長的操作，包括已耗用時間很長的更新操作。
　　
　下面的指令碼將顯示一個狀態資訊，說明了已耗用時間很長的DDL操作已經使用的時間。注意你必須從v$session中取得SID並將其插入到下面的SQL語句中:
　　
　　select sid,start_time,elapsed_seconds,message
　　
　　from v$session_longops
　　
　　where sid = 13
　　
　　order by start_time;
　　
　　這裡是一個輸出的例子，顯示了已耗用時間很長的CREATE INDEX語句的運行過程。
　　
　　SID MESSAGE
　　
　　--- ---------------------------------------------------------------
　　
　　11 Table Scan: CUST.PK_IDX: 732 out of 243260 Blocks done
　　
　3.用set transaction 命令解決ORA-01555錯誤
　　
　在執行大事務時,有時oracle會報出如下的錯誤:
　　
　　ORA-01555:snapshot too old (rollback segment too small)
　　
　　這說明oracle給此事務隨機分配的復原段太小了,這時可以為它指定一個足夠大的復原段,以確保這個事務的成功執行.例如
　　
　　set transaction use rollback segment roll_abc;
　　
　　delete from table_name where ... ;
　　
　　commit;
　　
　　提交結束後ORACLE會自動釋放對 roll_abc 的指定。
　　
　4.刪除表中重複記錄
　　
　方法原理:
　　
　1、Oracle中，每一條記錄都有一個rowid，rowid在整個資料庫中是唯一的，　 rowid確定了每條記錄是在ORACLE中的哪一個資料檔案、塊、行上。
　　
　2、在重複的記錄中，可能所有列的內容都相同，但rowid不會相同，所以只要確定出重複記錄中那些具有最大rowid的就可以了，其餘全部刪除。
　　
　實現方法:
　　
　　SQL> create table a(bm char(4),mc varchar2(20));
　　
　　Table created
　　
　　SQL> insert into a values('1111','aaaa');
　　
　　SQL> insert into a values('1112','aaaa');
　　
　　SQL> insert into a values('1113','aaaa');
　　
　　SQL> insert into a values('1114','aaaa');
　　
　　SQL> insert into a select * from a;
　　
　　4 rows inserted
　　
　　SQL> commit;
　　
　　Commit complete
　　
　　SQL> select rowid,bm,mc from a;
　　
　　ROWID BM MC
　　
　　------------------ ---- --------------------
　　
　　AAAIRIAAQAAAAJqAAA 1111 aaaa
　　
　　AAAIRIAAQAAAAJqAAB 1112 aaaa
　　
　　AAAIRIAAQAAAAJqAAC 1113 aaaa
　　
　　AAAIRIAAQAAAAJqAAD 1114 aaaa
　　
　　AAAIRIAAQAAAAJqAAE 1111 aaaa
　　
　　AAAIRIAAQAAAAJqAAF 1112 aaaa
　　
　　AAAIRIAAQAAAAJqAAG 1113 aaaa
　　
　　AAAIRIAAQAAAAJqAAH 1114 aaaa
　　
　　8 rows selected
　　
　查出重複記錄
　　
　　SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
　　
　　ROWID BM MC
　　
　　------------------ ---- --------------------
　　
　　AAAIRIAAQAAAAJqAAA 1111 aaaa
　　
　　AAAIRIAAQAAAAJqAAB 1112 aaaa
　　
　　AAAIRIAAQAAAAJqAAC 1113 aaaa
　　
　　AAAIRIAAQAAAAJqAAD 1114 aaaa
　　
　重複資料刪除記錄
　　
　　SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
　　
　刪除4個記錄.
　　
　　SQL> select rowid,bm,mc from a;
　　
　　ROWID BM MC
　　
　　------------------ ---- --------------------
　　
　　AAAIRIAAQAAAAJqAAE 1111 aaaa
　　
　　AAAIRIAAQAAAAJqAAF 1112 aaaa
　　
　　AAAIRIAAQAAAAJqAAG 1113 aaaa
　　
　　AAAIRIAAQAAAAJqAAH 1114 aaaa
　　
　5.控制檔案損壞時的恢複
　　
　根據如下錯誤資訊，我們探索資料庫只能啟動執行個體，讀控制檔案時發生錯誤。在資料庫設計的過程中，從安全的角度考慮，系統使用了三個鏡像的控制檔案，現在三個控制檔案version號不一致。
　　
　　SVRMGRL>startup
　　
　　oracle instance started
　　
　　total system global area 222323980 bytes
　　
　　fixed size 70924 bytes
　　
　　variable size 78667776 bytes
　　
　　database buffers 143507456 bytes
　　
　　redo buffers 77824 bytes
　　
　　ORA-00214: controlfile ‘d:\oracle\oradata\orcl\control01.ctl’ version 57460 inconsistent with file ‘d:\oracle\oradata\orcl\control02.ctl’ version 57452.
　　
　根據以上分析，我們試著修改參數檔案。將參數檔案中的control_file參數修改為一個控制檔案，分別使用control01、control02、control03。但資料庫都無法啟動，說明三個控制檔案都已損壞。
　　
　由於沒有控制檔案的備份，我們只能採取重建控制檔案的做法。
　　
　　D:\>svrmgrl
　　
　　Oracle Server Manager Release 3.1.6.0.0 - Production
　　
　　著作權 (c) 1997，1999，Oracle Corporation。著作權所有，並保留一切權利。
　　
　　Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
　　
　　With the Partitioning option
　　
　　JServer Release 8.1.6.0.0 - Production
　　
　　SVRMGR> connect internal
　　
　　串連成功。
　　
　　SVRMGR> shutdowm abort
　　
　　已關閉 ORACLE 執行個體。
　　
　　SVRMGR> startup nomount
　　
　　已啟動 ORACLE 執行個體。
　　
　　系統全域地區合計有 108475660個位元組
　　
　　Fixed Size 70924個位元組
　　
　　Variable Size 46116864個位元組
　　
　　Database Buffers 62210048個位元組
　　
　　Redo Buffers 77824個位元組
　　
　　SVRMGR>create controlfile reuse database orcl noresetlogs archivelog
　　
　　Logfile group 1 ‘d:\oracle\oradata\orcl\redo01.log’,
　　
　　group 2 ‘d:\oracle\oradata\orcl\redo02.log’,
　　
　　group 3 ‘d:\oracle\oradata\orcl\redo03.log’
　　
　　datafile ‘d:\oracle\oradata\orcl\system01.dbf’,
　　
　　‘d:\oracle\oradata\orcl\users01.dbf’,
　　
　　‘d:\oracle\oradata\orcl\temp01.dbf’,
　　
　　‘d:\oracle\oradata\orcl\tools01.dbf’,
　　
　　‘d:\oracle\oradata\orcl\indx01.dbf’,
　　
　　‘d:\oracle\oradata\orcl\dr01.dbf’,
　　
　　‘d:\oracle\oradata\orcl\rbs01.dbf’;
　　
　　語句已處理。
　　
　　成功地重建控制檔案後，我們嘗試著開啟資料庫，但系統報錯，提示需要進行介質恢複。
　　
　　SVRMGR>recover datafile ‘d:\oracle\oradata\orcl\system01.dbf’;
　　
　　介質已恢複。
　　
　　SVRMGR> recover datafile ‘d:\oracle\oradata\orcl\users0101.dbf’;
　　
　　介質已恢複。
　　
　　SVRMGR> recover datafile ‘d:\oracle\oradata\orcl\temp01.dbf’;
　　
　　介質已恢複。
　　
　　SVRMGR> recover datafile ‘d:\oracle\oradata\orcl\tools01.dbf’;
　　
　　介質已恢複。
　　
　　SVRMGR> recover datafile ‘d:\oracle\oradata\orcl\indx01.dbf’;
　　
　　介質已恢複。
　　
　　SVRMGR> recover datafile ‘d:\oracle\oradata\orcl\dr01.dbf’;
　　
　　介質已恢複。
　　
　　SVRMGR> recover datafile ‘d:\oracle\oradata\orcl\rbs01.dbf’;
　　
　　介質已恢複。
　　
　　介質恢複後，重新開啟資料庫，提示記錄檔也需恢複。
　　
　　SVRMGR> recover database until cancel;
　　
　　日誌已恢複。
　　
　控制檔案、資料檔案、記錄檔全部恢複後，將三種檔案同步，並開啟資料庫，成功地完成了資料庫的恢複工作。
　　
　　SVRMGR> alter database open resetlogs;
　　
　資料庫已更改。
　　
　立即關閉資料庫，並進行資料庫的冷備份，將資料庫的資料完整地儲存下來。