因為第一次翻譯的質量不好,出版社決定“殺掉重練"進行第二次翻譯.導致Oracle管理藝術這本書的簡體版拖了將近一年才出版.
可也總算完成了,所以在2010/12/01在大陸正式開賣.終於解決了一件懸在心頭的事情.
不過我還沒有看到成品,不知道到底翻譯的品質如何!必須等書到以後才知道
這本書交由清華大學出版社出書,因為我個人的一點點小虛榮.....
看樣子,應該開始下一本書了......
2010年12月6日 星期一
Oracle管理藝術-簡體中文版終於出版了
因為第一次翻譯的質量不好,出版社決定“殺掉重練"進行第二次翻譯.導致Oracle管理藝術這本書的簡體版拖了將近一年才出版.
可也總算完成了,所以在2010/12/01在大陸正式開賣.終於解決了一件懸在心頭的事情.
不過我還沒有看到成品,不知道到底翻譯的品質如何!必須等書到以後才知道
這本書交由清華大學出版社出書,因為我個人的一點點小虛榮.....
看樣子,應該開始下一本書了......
2010年11月1日 星期一
Oracle Database 11g New Features - Virtual Column (虛擬欄位)
在資料庫設計的相關技巧中，正規化是DBA耳熟能詳的基本技巧，而基本的正規化至少要達到第三正規化，才有正規化的效果。正規化可以帶來以下的一些好處：1.刪除重複資料。2.節省儲存空間。當然某些情況下，正規化可能導致效能上的問題。這時可以適時地使用反正規化來增加整體的效能，不過在進行反正規化之前，當然還是要先正規化，因為不是每個表格都需要反正規化。以下對正規化的規則做一些簡單的說明：
第一正規化：每個表格中都要有主鍵(Primary Key)用以辨識資料列，而且表格中不能有重複的欄位，同時每個欄位中只能有一筆值。
學號為主鍵，沒有重複欄位以及每個欄位只有一個值。因此範例表格滿足第一正規化。
第二正規化：當滿足第一正規化的要求後，表格中非主鍵的欄位，還必須跟主鍵有完全相依性(即可以由主鍵決定其他非主鍵的欄位)。
國文、數學、英文都與學號有著完全的相依性。所以此表格也滿足第二正規化的要求。
第三正規化：當滿足第二正規化後，非主鍵欄位之間不能存在相依性。 國文、數學、英文這些非主鍵欄位之間並沒有相依性。所以這個範例表格可以說符合第三正規化的表格。
也許有人會建議再加上一個欄位：總分，用來存放三科的總成績。可是總分除與學號(主鍵)相依外，還與國文、數學、英文的成績相依，為達到第三正規化的要求，所以不能加上總分的欄位。
因此當需要計算學生的總成績時，可以由下列方式得到總成績。
1.由AP人員必須將計算公式(國文+數學+英文)寫到程式中，這種方法的好處是，表格依然維持正規化的設計，但是若計算公式一但有所變動，則需要修改程式內容，以及必須將新的程式部署到各個客戶端。
2.然而DBA也可以透過建立一個視圖(VIEW)，將計算公式產生總分的虛擬欄位(create view student_view as select 國文,數學,英文,(國文+數學+英文) as 總分 from student)，這樣便可以透過視圖存取總分的資料。這種方式的缺點是必須多花一些維護成本在視圖的管理上。（
3.DBA直接在Student表格上，多加一個總分的欄位，其內容值為國文、數學、英文的總和。並利用觸發器(Trigger)在相關分數(國文、數學、英文)變動時，自動維護總分的內容，以保持資料的一致性。此方式即所謂的反正規化，可以增加查詢的效能，但也造成儲存的浪費，也增加了資料不一致的風險。所以反正規化在使用前必須仔細設計，避免相關的問題發生。
虛擬欄位(Virtual Column)介紹
由Oracle Database 11g開始，DBA可以宣告一種欄位形態：虛擬欄位，這種欄位並沒有真正的消耗儲存空間，只有在表格定義中存在。當此欄位被查詢時，才會使用欄位定義的公式計算欄位值。欄位定義的公式可以包括任何合法的運算以及函數。
SQL> create table t1
2 (a number,b number,c number generated always as (a+b) virtual);
/*generated always是為語法清晰而存在，virtual也是相同用途。所以可以直接使用c number as (a+b)即可*/
SQL> desc t1 /*這裡看不出來，是否有使用虛擬欄位*/
Name Null? Type
--------------------------------------------------------- ------ ---------
A NUMBER
B NUMBER
C NUMBER
SQL> select column_name,data_type,data_default from user_tab_columns
2> where table_name='T1';
COLUMN_NAM DATA_TYPE DATA_DEFAULT
---------- ---------- --------------------
A NUMBER
B NUMBER
C NUMBER "A"+"B" /*欄位公式*/
SQL> insert into t1 values(1,2,3); /*不能直接Insert/Update虛擬欄位的內容*/
insert into t1 values(1,2,3)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
SQL> insert into t1 values(1,2);
insert into t1 values(1,2)
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into t1(a,b) values(1,2); /*必須如此才能新增成功*/
或 insert into t1 values(1,2,default);
1 row created.
SQL> select * from t1;
A B C
---------- ---------- ----------
1 2 3
/*找出row真正存放的位置*/
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
2 dbms_rowid.rowid_block_number(rowid) block#
3 from t1 where a=1 and b=2 and c=3;
FILE# BLOCK#
---------- ----------
1 40378
/*將Data Block內容dump到user tracefile*/
SQL> alter system dump datafile 1 block 40378;
SQL> show parameter user_dump_dest /*確認User tracefile的位置*/
NAME TYPE VALUE
----------------------------- --------- ----------------------------
user_dump_dest string /u03/diag/diag/rdbms/ora11g
/ora11g/trace
/*找到Server Process的OS Process ID*/
SQL> select spid from v$process
2 where addr=(select paddr from v$session where
3 sid=(select distinct sid from v$mystat));
SPID
------------------------
5000
SQL> !cat /u03/diag/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5000.trc
從Data Block的內容來看，可以證明虛擬欄位-C，並未佔有任何空間。
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02 /*A欄位*/
col 1: [ 2] c1 03 /*B欄位*/
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 40378 maxblk 40378
使用User Defined Function的注意事項
當虛擬欄位用到User Defined Function時，必須確定函數是Deterministic，即只要輸入相同的參數值，函數就一定回傳相同的結果。
SQL> create or replace function minus_10(x number) return number
2 is
3 begin
4 return x-10;
5 end;
6 /
Function created.
SQL> create table t2 (a number,b number,c number as (minus_10(a+b)) virtual);
create table t2 (a number,b number,c number as (minus_10(a+b)) virtual)
*
ERROR at line 1:
ORA-30553: The function is not deterministic
SQL> create or replace function minus_10(x number) return number deterministic
2 is
3 begin
4 return x-10;
5 end;
6 /
Function created.
SQL> create table t2 (a number,b number,c number as (minus_10(a+b) virtual);
create table t2 (a number,b number,c number as (minus_10(a+b) virtual)
*
ERROR at line 1:
ORA-02000: missing ) keyword
SQL> create table t2 (a number,b number,c number as (minus_10(a+b)) virtual);
Table created.
虛擬欄位的操作
虛擬欄位在Oracle Database 11g中，被當作一般的欄位，所以除了不能直接新增、異動其內容外，其餘的操作如：當做主鍵、建立索引等都可以正常運行。
SQL> create index t1_c_idx on t1(c);
Index created.
SQL> select index_name,index_type from user_indexes where table_name='T1';
INDEX_NAME INDEX_TYPE
------------------------------- ----------------------
T1_C_IDX FUNCTION-BASED NORMAL
/*從INDEX_TYPE可以得知，當建立索引在虛擬欄位上時，所建立的索引為Function-Based形態*/
SQL> alter table t1 add constraints t1_c_pk primary key(c);
Table altered.
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='T1';
CONSTRAINT_NAME C
------------------------ --
T1_C_PK P /*P表示為Primary Key*/
SQL> execute dbms_stats.gather_table_stats('SYS','T1'); /*收集統計值*/
PL/SQL procedure successfully completed.
/*虛擬欄位也可以當作分區欄位，這種分區方式稱為Virtual Column-Based Partitioning*/
SQL> create table t3
2 (a number,b number,c number as (a+b+100) virtual)
3 partition by range (c)
4 (partition p1 values less than(150),
5 partition p2 values less than(250),
6 partition p3 values less than(350));
虛擬欄位的使用限制
目前虛擬欄位在使用上，還存在著一些限制：
1.僅適用在Heap Table上，還不能用在Index-Organized、Cluster、External、Temporary以及Object等表格上。
2.虛擬欄位不能參考其他的虛擬欄位，只能參考同一個表格中的真實欄位。
3.當使用函數時，此函數必須宣告為Deterministic以及回傳的資料型態必須為Scalar。
4.虛擬欄位的資料型態不能為PL/SQL的資料形態、User Defined Type、LOB以及Long Raw等型態。
了解以上這些限制，可以幫助DBA在使用虛擬欄位時，更加的得心應手。
結論
資料庫表格設計的正規化是DBA的基本功，也可以減少資料庫的資料量，避免資料不一致的情況，同時更可以增加資料庫整體的效能。然而某一情況下，DBA可能採用反正規化的技巧，來解決某些效能上的問題，不過重複資料與內容不一致的風險也同時增加。如果DBA能夠正確地使用虛擬欄位的功能，便可以一方面擁有正規化的好處，同時也可以達到原本需要反正規化才能得到的效能提昇。
第一正規化：每個表格中都要有主鍵(Primary Key)用以辨識資料列，而且表格中不能有重複的欄位，同時每個欄位中只能有一筆值。
學號為主鍵，沒有重複欄位以及每個欄位只有一個值。因此範例表格滿足第一正規化。
第二正規化：當滿足第一正規化的要求後，表格中非主鍵的欄位，還必須跟主鍵有完全相依性(即可以由主鍵決定其他非主鍵的欄位)。
國文、數學、英文都與學號有著完全的相依性。所以此表格也滿足第二正規化的要求。
第三正規化：當滿足第二正規化後，非主鍵欄位之間不能存在相依性。 國文、數學、英文這些非主鍵欄位之間並沒有相依性。所以這個範例表格可以說符合第三正規化的表格。
也許有人會建議再加上一個欄位：總分，用來存放三科的總成績。可是總分除與學號(主鍵)相依外，還與國文、數學、英文的成績相依，為達到第三正規化的要求，所以不能加上總分的欄位。
因此當需要計算學生的總成績時，可以由下列方式得到總成績。
1.由AP人員必須將計算公式(國文+數學+英文)寫到程式中，這種方法的好處是，表格依然維持正規化的設計，但是若計算公式一但有所變動，則需要修改程式內容，以及必須將新的程式部署到各個客戶端。
2.然而DBA也可以透過建立一個視圖(VIEW)，將計算公式產生總分的虛擬欄位(create view student_view as select 國文,數學,英文,(國文+數學+英文) as 總分 from student)，這樣便可以透過視圖存取總分的資料。這種方式的缺點是必須多花一些維護成本在視圖的管理上。（
3.DBA直接在Student表格上，多加一個總分的欄位，其內容值為國文、數學、英文的總和。並利用觸發器(Trigger)在相關分數(國文、數學、英文)變動時，自動維護總分的內容，以保持資料的一致性。此方式即所謂的反正規化，可以增加查詢的效能，但也造成儲存的浪費，也增加了資料不一致的風險。所以反正規化在使用前必須仔細設計，避免相關的問題發生。
虛擬欄位(Virtual Column)介紹
由Oracle Database 11g開始，DBA可以宣告一種欄位形態：虛擬欄位，這種欄位並沒有真正的消耗儲存空間，只有在表格定義中存在。當此欄位被查詢時，才會使用欄位定義的公式計算欄位值。欄位定義的公式可以包括任何合法的運算以及函數。
SQL> create table t1
2 (a number,b number,c number generated always as (a+b) virtual);
/*generated always是為語法清晰而存在，virtual也是相同用途。所以可以直接使用c number as (a+b)即可*/
SQL> desc t1 /*這裡看不出來，是否有使用虛擬欄位*/
Name Null? Type
--------------------------------------------------------- ------ ---------
A NUMBER
B NUMBER
C NUMBER
SQL> select column_name,data_type,data_default from user_tab_columns
2> where table_name='T1';
COLUMN_NAM DATA_TYPE DATA_DEFAULT
---------- ---------- --------------------
A NUMBER
B NUMBER
C NUMBER "A"+"B" /*欄位公式*/
SQL> insert into t1 values(1,2,3); /*不能直接Insert/Update虛擬欄位的內容*/
insert into t1 values(1,2,3)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
SQL> insert into t1 values(1,2);
insert into t1 values(1,2)
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into t1(a,b) values(1,2); /*必須如此才能新增成功*/
或 insert into t1 values(1,2,default);
1 row created.
SQL> select * from t1;
A B C
---------- ---------- ----------
1 2 3
/*找出row真正存放的位置*/
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
2 dbms_rowid.rowid_block_number(rowid) block#
3 from t1 where a=1 and b=2 and c=3;
FILE# BLOCK#
---------- ----------
1 40378
/*將Data Block內容dump到user tracefile*/
SQL> alter system dump datafile 1 block 40378;
SQL> show parameter user_dump_dest /*確認User tracefile的位置*/
NAME TYPE VALUE
----------------------------- --------- ----------------------------
user_dump_dest string /u03/diag/diag/rdbms/ora11g
/ora11g/trace
/*找到Server Process的OS Process ID*/
SQL> select spid from v$process
2 where addr=(select paddr from v$session where
3 sid=(select distinct sid from v$mystat));
SPID
------------------------
5000
SQL> !cat /u03/diag/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5000.trc
從Data Block的內容來看，可以證明虛擬欄位-C，並未佔有任何空間。
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02 /*A欄位*/
col 1: [ 2] c1 03 /*B欄位*/
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 40378 maxblk 40378
使用User Defined Function的注意事項
當虛擬欄位用到User Defined Function時，必須確定函數是Deterministic，即只要輸入相同的參數值，函數就一定回傳相同的結果。
SQL> create or replace function minus_10(x number) return number
2 is
3 begin
4 return x-10;
5 end;
6 /
Function created.
SQL> create table t2 (a number,b number,c number as (minus_10(a+b)) virtual);
create table t2 (a number,b number,c number as (minus_10(a+b)) virtual)
*
ERROR at line 1:
ORA-30553: The function is not deterministic
SQL> create or replace function minus_10(x number) return number deterministic
2 is
3 begin
4 return x-10;
5 end;
6 /
Function created.
SQL> create table t2 (a number,b number,c number as (minus_10(a+b) virtual);
create table t2 (a number,b number,c number as (minus_10(a+b) virtual)
*
ERROR at line 1:
ORA-02000: missing ) keyword
SQL> create table t2 (a number,b number,c number as (minus_10(a+b)) virtual);
Table created.
虛擬欄位的操作
虛擬欄位在Oracle Database 11g中，被當作一般的欄位，所以除了不能直接新增、異動其內容外，其餘的操作如：當做主鍵、建立索引等都可以正常運行。
SQL> create index t1_c_idx on t1(c);
Index created.
SQL> select index_name,index_type from user_indexes where table_name='T1';
INDEX_NAME INDEX_TYPE
------------------------------- ----------------------
T1_C_IDX FUNCTION-BASED NORMAL
/*從INDEX_TYPE可以得知，當建立索引在虛擬欄位上時，所建立的索引為Function-Based形態*/
SQL> alter table t1 add constraints t1_c_pk primary key(c);
Table altered.
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='T1';
CONSTRAINT_NAME C
------------------------ --
T1_C_PK P /*P表示為Primary Key*/
SQL> execute dbms_stats.gather_table_stats('SYS','T1'); /*收集統計值*/
PL/SQL procedure successfully completed.
/*虛擬欄位也可以當作分區欄位，這種分區方式稱為Virtual Column-Based Partitioning*/
SQL> create table t3
2 (a number,b number,c number as (a+b+100) virtual)
3 partition by range (c)
4 (partition p1 values less than(150),
5 partition p2 values less than(250),
6 partition p3 values less than(350));
虛擬欄位的使用限制
目前虛擬欄位在使用上，還存在著一些限制：
1.僅適用在Heap Table上，還不能用在Index-Organized、Cluster、External、Temporary以及Object等表格上。
2.虛擬欄位不能參考其他的虛擬欄位，只能參考同一個表格中的真實欄位。
3.當使用函數時，此函數必須宣告為Deterministic以及回傳的資料型態必須為Scalar。
4.虛擬欄位的資料型態不能為PL/SQL的資料形態、User Defined Type、LOB以及Long Raw等型態。
了解以上這些限制，可以幫助DBA在使用虛擬欄位時，更加的得心應手。
結論
資料庫表格設計的正規化是DBA的基本功，也可以減少資料庫的資料量，避免資料不一致的情況，同時更可以增加資料庫整體的效能。然而某一情況下，DBA可能採用反正規化的技巧，來解決某些效能上的問題，不過重複資料與內容不一致的風險也同時增加。如果DBA能夠正確地使用虛擬欄位的功能，便可以一方面擁有正規化的好處，同時也可以達到原本需要反正規化才能得到的效能提昇。
Oracle 11g New Features - Result Cache
當一支SQL指令由客戶端發送到伺服器端時，Server Process首先必須先將其解析為執行計畫，將執行計畫存放在Shared Pool後，才使用該執行計畫進行SQL操作，這樣當下次使用者執行相同的SQL指令時，便可以直接由Library Cache中取得執行計畫，而不需要再次解析一次。這樣在重複地執行相同的SQL指令的環境下，可以減少硬解析(Hard Parse)的次數，這樣對整體Oracle Server效能有很大的提昇。
同時當Server Process執行該執行計畫時，利用Buffer Cache將由資料檔讀入的資料區塊(data block)快取起來，往後如果有需要相同的資料區塊，便可以直接由Buffer Cache中取得，利用記憶體的存取速度遠快過於磁碟機的優勢，將原本的Disk I/O轉換成Memory I/O。因此即便是I/O的總數量不變，但是整體I/O的時間卻可以大幅下降，如此便可以減少SQL指令的反應時間。
Oracle11g更進一步將SQL指令的結果，直接快取起來，存放在Result Cache。因此當使用者執行相同的SQL指令時，不需再次執行該SQL指令，直接將存放在Result Cache中的結果集，直接回傳給使用者即可，如此可以得到更短的反應時間。同時Oracle11g會自動偵測結果集的正確性，如果相關資料表的內容有變動，將會將該結果集設定為失效，下次執行SQL指令時，會重新執行SQL指令產生最新的結果集並將其快取在Result Cache中，而Result Cache存放在Shared Pool中。
Result Cache的相關參數
SQL> show parameter result_cache
NAME TYPE VALUE
------------------------------- ------------- -------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5 --最多可以快取幾個Result
result_cache_max_size big integer 1536K --Result Cache所佔的最大值
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
其中result_cache_mode決定是否自動使用RESULT CACHE功能。
如果result_cache_mode設定為MANUAL，查詢指令必須加上RESULT_CACHE的HINT才會使用RESULT CACHE。
反之result_cache_mode設定為FORCE，則會自動使用RESULT CACHE，除非加上NO_RESULT_CACHE才會不使用RESULT CACHE。
NOTE︰有些人發現可以將result_cache_mode設定為auto，並顧名思義地認為可以讓Oracle Server自動判斷是否要使用RESULT CACHE，但是根據Oracle內部的研發人員說明，AUTO現在不會有任何效果。
建立相關測試環境
建立一個測試表格
Connected.
SQL> create user frank
2 identified by oracle
3 default tablespace users
4 quota unlimited on users;
User created.
SQL> grant connect,resource to frank;
Grant succeeded.
SQL> create table frank.big1 as select * from dba_objects;
Table created.
SQL> insert into frank.big1 select * from frank.big1;
11654 rows created.
SQL> /
23308 rows created.
SQL> /
46616 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from frank.big1;
COUNT(*)
----------
93232
SQL> execute dbms_stats.gather_table_stats('FRANK','BIG1');
PL/SQL procedure successfully completed.
Result Cache的功能示範
SQL> show parameter result_cache_mode
NAME TYPE VALUE
----------------------------- --------- ------------------------------
result_cache_mode string MANUAL
SQL> set autotrace traceonly
/*底下的SQL指令將不會產生Result Cache，因為result_cache_mode為MANUAL*/
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 2 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1241 consistent gets /*需要讀取1241個data blocks，才能完成*/
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
/*使用result_cahce的HINT，要求將此SQL的結果快取起來*/
SQL> select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | RESULT CACHE | 86cxjfsndphbsd664abz33z4bt | | | | |
| 2 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(FRANK.BIG1); parameters=(nls); name="select /*+ result_cache */ owner,cou
nt(*) from frank.big1 group by owner order by count(*)"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1241 consistent gets
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
/*以下的SQL使用result_cache的HINT，可以由Result Cache取得結果*/
SQL> select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | RESULT CACHE | 86cxjfsndphbsd664abz33z4bt | | | | |
| 2 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(FRANK.BIG1); parameters=(nls); name="select /*+ result_cache */ owner,cou
nt(*) from frank.big1 group by owner order by count(*)
"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets /*沒有任何data block被讀取*/
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
/*查詢目前已被快取的結果集*/
SQL> select name,id,status from v$result_cache_objects;
NAME
-----------------------------------------------------------------------------
ID STATUS
---------- ---------
FRANK.BIG1
1 Published
select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*)
2 Published
SELECT DECODE('A','A','1','2') FROM DUAL
0 Published
/*將Result Cache的內容請空*/
SQL> execute dbms_result_cache.flush;
PL/SQL procedure successfully completed.
SQL> select name,id,status from v$result_cache_objects;
no rows selected
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
SQL> select name,id,status from v$result_cache_objects;
no rows selected
SQL> alter session set result_cache_mode=FORCE;
Session altered.
/*自動將執行結果快取到Result Cache中*/
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
SQL> select name,id,status from v$result_cache_objects;
NAME
-----------------------------------------------------------------------------
ID STATUS
---------- ---------
FRANK.BIG1
0 Published
select owner,count(*) from frank.big1 group by owner order by count(*)
1 Published
SQL> set autotrace on statistics
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets /*完全沒有任何I/O*/
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
可以使用V$RESULT_CACHE_DEPENDENCY的內容，查知與哪些表格有被快取。
SQL> select * from v$result_cache_dependency;
RESULT_ID DEPEND_ID OBJECT_NO
---------- ---------- ----------
3 0 12082
SQL> col object_name format a30
SQL> select owner,object_name,object_type from dba_objects where object_id=12082;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
FRANK BIG1 TABLE
如果需要察看目前的Result Cache的狀態，可以透過V$RESULT_CACHE_STATISTICS或DBMS_RESULT_CACHE.MEMORY_REPORT得知。
SQL> col name format a30
SQL> select * from v$result_cache_statistics;
ID NAME VALUE
------- ------------------------------ ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1536
3 Block Count Current 32
4 Result Size Maximum (Blocks) 76
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
10 rows selected.
SQL> set serveroutput on
SQL> execute dbms_result_cache.memory_report;
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 1536K bytes (1536 blocks)
Maximum Result Size = 76K bytes (76 blocks)
[Memory]
Total Memory = 116008 bytes [0.132% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 110876 bytes [0.126% of the Shared Pool]
....... Overhead = 78108 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
Result Cache的內容會自動由Oracle Server維護，例如當Result Set中的相關表格內容被異動後，已快取的Result Set將會被設定為Invalid，同時相關的相依關係也會被消除。
SQL> select id,name,status from v$result_cache_objects;
ID NAME STATUS
------- ------------------------------ ---------
0 FRANK.BIG1 Published
2 select owner,count(*) from fra Published
nk.big1 group by owner order b
y count(*)
1 SELECT DECODE('A','A','1','2') Published
FROM DUAL
SQL> update frank.big1 set object_id=object_id where object_id=10000;
8 rows updated.
SQL> commit;
Commit complete.
SQL> select id,name,status from v$result_cache_objects;
ID NAME STATUS
------- ------------------------------ ---------
0 FRANK.BIG1 Published
1 SELECT DECODE('A','A','1','2') Published
FROM DUAL
2 select owner,count(*) from fra Invalid
nk.big1 group by owner order b
y count(*)
SQL> select * from v$result_cache_dependency;
no rows selected
結論
Result Set Cache的功能可以大幅降低SQL查詢指令的反應時間，尤其當記憶體的成本越來越低的現在，未來有更多的功能，會利用記憶體的空間，來節省整體的反應時間。不過這項功能不適合用在OLTP系統，因為即便已經將結果集快取後，但是表格內容一但被異動後，便失去其意義。因此這項功能應該用在DW/BATCH的資料庫才對。
同時當Server Process執行該執行計畫時，利用Buffer Cache將由資料檔讀入的資料區塊(data block)快取起來，往後如果有需要相同的資料區塊，便可以直接由Buffer Cache中取得，利用記憶體的存取速度遠快過於磁碟機的優勢，將原本的Disk I/O轉換成Memory I/O。因此即便是I/O的總數量不變，但是整體I/O的時間卻可以大幅下降，如此便可以減少SQL指令的反應時間。
Oracle11g更進一步將SQL指令的結果，直接快取起來，存放在Result Cache。因此當使用者執行相同的SQL指令時，不需再次執行該SQL指令，直接將存放在Result Cache中的結果集，直接回傳給使用者即可，如此可以得到更短的反應時間。同時Oracle11g會自動偵測結果集的正確性，如果相關資料表的內容有變動，將會將該結果集設定為失效，下次執行SQL指令時，會重新執行SQL指令產生最新的結果集並將其快取在Result Cache中，而Result Cache存放在Shared Pool中。
Result Cache的相關參數
SQL> show parameter result_cache
NAME TYPE VALUE
------------------------------- ------------- -------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5 --最多可以快取幾個Result
result_cache_max_size big integer 1536K --Result Cache所佔的最大值
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
其中result_cache_mode決定是否自動使用RESULT CACHE功能。
如果result_cache_mode設定為MANUAL，查詢指令必須加上RESULT_CACHE的HINT才會使用RESULT CACHE。
反之result_cache_mode設定為FORCE，則會自動使用RESULT CACHE，除非加上NO_RESULT_CACHE才會不使用RESULT CACHE。
NOTE︰有些人發現可以將result_cache_mode設定為auto，並顧名思義地認為可以讓Oracle Server自動判斷是否要使用RESULT CACHE，但是根據Oracle內部的研發人員說明，AUTO現在不會有任何效果。
建立相關測試環境
建立一個測試表格
Connected.
SQL> create user frank
2 identified by oracle
3 default tablespace users
4 quota unlimited on users;
User created.
SQL> grant connect,resource to frank;
Grant succeeded.
SQL> create table frank.big1 as select * from dba_objects;
Table created.
SQL> insert into frank.big1 select * from frank.big1;
11654 rows created.
SQL> /
23308 rows created.
SQL> /
46616 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from frank.big1;
COUNT(*)
----------
93232
SQL> execute dbms_stats.gather_table_stats('FRANK','BIG1');
PL/SQL procedure successfully completed.
Result Cache的功能示範
SQL> show parameter result_cache_mode
NAME TYPE VALUE
----------------------------- --------- ------------------------------
result_cache_mode string MANUAL
SQL> set autotrace traceonly
/*底下的SQL指令將不會產生Result Cache，因為result_cache_mode為MANUAL*/
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 2 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1241 consistent gets /*需要讀取1241個data blocks，才能完成*/
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
/*使用result_cahce的HINT，要求將此SQL的結果快取起來*/
SQL> select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | RESULT CACHE | 86cxjfsndphbsd664abz33z4bt | | | | |
| 2 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(FRANK.BIG1); parameters=(nls); name="select /*+ result_cache */ owner,cou
nt(*) from frank.big1 group by owner order by count(*)"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1241 consistent gets
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
/*以下的SQL使用result_cache的HINT，可以由Result Cache取得結果*/
SQL> select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | RESULT CACHE | 86cxjfsndphbsd664abz33z4bt | | | | |
| 2 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(FRANK.BIG1); parameters=(nls); name="select /*+ result_cache */ owner,cou
nt(*) from frank.big1 group by owner order by count(*)
"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets /*沒有任何data block被讀取*/
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
/*查詢目前已被快取的結果集*/
SQL> select name,id,status from v$result_cache_objects;
NAME
-----------------------------------------------------------------------------
ID STATUS
---------- ---------
FRANK.BIG1
1 Published
select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*)
2 Published
SELECT DECODE('A','A','1','2') FROM DUAL
0 Published
/*將Result Cache的內容請空*/
SQL> execute dbms_result_cache.flush;
PL/SQL procedure successfully completed.
SQL> select name,id,status from v$result_cache_objects;
no rows selected
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
SQL> select name,id,status from v$result_cache_objects;
no rows selected
SQL> alter session set result_cache_mode=FORCE;
Session altered.
/*自動將執行結果快取到Result Cache中*/
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
SQL> select name,id,status from v$result_cache_objects;
NAME
-----------------------------------------------------------------------------
ID STATUS
---------- ---------
FRANK.BIG1
0 Published
select owner,count(*) from frank.big1 group by owner order by count(*)
1 Published
SQL> set autotrace on statistics
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets /*完全沒有任何I/O*/
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
可以使用V$RESULT_CACHE_DEPENDENCY的內容，查知與哪些表格有被快取。
SQL> select * from v$result_cache_dependency;
RESULT_ID DEPEND_ID OBJECT_NO
---------- ---------- ----------
3 0 12082
SQL> col object_name format a30
SQL> select owner,object_name,object_type from dba_objects where object_id=12082;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
FRANK BIG1 TABLE
如果需要察看目前的Result Cache的狀態，可以透過V$RESULT_CACHE_STATISTICS或DBMS_RESULT_CACHE.MEMORY_REPORT得知。
SQL> col name format a30
SQL> select * from v$result_cache_statistics;
ID NAME VALUE
------- ------------------------------ ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1536
3 Block Count Current 32
4 Result Size Maximum (Blocks) 76
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
10 rows selected.
SQL> set serveroutput on
SQL> execute dbms_result_cache.memory_report;
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 1536K bytes (1536 blocks)
Maximum Result Size = 76K bytes (76 blocks)
[Memory]
Total Memory = 116008 bytes [0.132% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 110876 bytes [0.126% of the Shared Pool]
....... Overhead = 78108 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
Result Cache的內容會自動由Oracle Server維護，例如當Result Set中的相關表格內容被異動後，已快取的Result Set將會被設定為Invalid，同時相關的相依關係也會被消除。
SQL> select id,name,status from v$result_cache_objects;
ID NAME STATUS
------- ------------------------------ ---------
0 FRANK.BIG1 Published
2 select owner,count(*) from fra Published
nk.big1 group by owner order b
y count(*)
1 SELECT DECODE('A','A','1','2') Published
FROM DUAL
SQL> update frank.big1 set object_id=object_id where object_id=10000;
8 rows updated.
SQL> commit;
Commit complete.
SQL> select id,name,status from v$result_cache_objects;
ID NAME STATUS
------- ------------------------------ ---------
0 FRANK.BIG1 Published
1 SELECT DECODE('A','A','1','2') Published
FROM DUAL
2 select owner,count(*) from fra Invalid
nk.big1 group by owner order b
y count(*)
SQL> select * from v$result_cache_dependency;
no rows selected
結論
Result Set Cache的功能可以大幅降低SQL查詢指令的反應時間，尤其當記憶體的成本越來越低的現在，未來有更多的功能，會利用記憶體的空間，來節省整體的反應時間。不過這項功能不適合用在OLTP系統，因為即便已經將結果集快取後，但是表格內容一但被異動後，便失去其意義。因此這項功能應該用在DW/BATCH的資料庫才對。
2010年10月21日 星期四
3M大師
今天我終於收到通過Oracle 11g OCM升級考試的通知
正式晉升為3M大師：9i/10g/11g OCM
考試範圍如下,但是其實並不是每個項目都會考.考試內容相對簡單多了,只要Oracle 10g OCM稍加準備就應該可以順利通過
我個人在第3單元Performance Management遇到一些阻礙,幾乎將所有時間用完
其他單元大約只花費限制時間的2/3就完成所有的項目
Database, RMAN, EM and Network Configuration
[ ] Configure server-side network
[ ] Configure client-side network
[ ] Create and Manage encrypted tablespaces
[ ] Create and Manage a tablespace that uses NFS mounted file system file
[ ] Set up ADR file based repository
[ ] Perform cold database backup
[ ] Manage user accounts and use case sensitive passwords
[ ] Use OPatch to install a patch
[ ] Install and configure EM Agent
[ ] Create Base Recovery Catalog
[ ] Configure RMAN
[ ] Perform multisection backup of a datafile
[ ] Create an Archival Backup
Data Guard
[ ] Create Physical Standby Database with real-time apply.
[ ] Configure the data guard environment to reduce overheads of fast incremental backups on the primary database
[ ] Configure the Observer
[ ] Switchover and switch back
[ ] Convert the standby to a snapshot standby
[ ] Configure archivelog deletion policy for the dataguard configuration
Data and Data Warehouse Management
[ ] Troubleshoot fast materialized views to fast refresh and query rewrite
[ ] Add a tablespace by using Transportable Tablespace Feature of Data Pump Import (cross platform transportable tablespace)
[ ] Configure a schema to support a star transformation query
[ ] Configure and use parallel execution for queries
[ ] Use and access SecureFile LOBS
[ ] Create partitioned tables (includes reference and interval partitioning)
[ ] Configure Flashback Data Archive
[ ] Use Oracle Streams to capture and propagate changes in a table
Performance Management
[ ] Set up and configure Resource Manager to control active sessions, number of I/Os, execution time..etc
[ ] Use Result Cache
[ ] Use multi column statistics
[ ] Gather statistics on a specific table without invalidating cursors
[ ] Use partitioned indexes
[ ] Use SQL Tuning Advisor
[ ] Use SQL Access Advisor
[ ] Configure baseline templates
[ ] Use SQL Plan Management feature
[ ] Replay a captured workload
正式晉升為3M大師：9i/10g/11g OCM
考試範圍如下,但是其實並不是每個項目都會考.考試內容相對簡單多了,只要Oracle 10g OCM稍加準備就應該可以順利通過
我個人在第3單元Performance Management遇到一些阻礙,幾乎將所有時間用完
其他單元大約只花費限制時間的2/3就完成所有的項目
Database, RMAN, EM and Network Configuration
[ ] Configure server-side network
[ ] Configure client-side network
[ ] Create and Manage encrypted tablespaces
[ ] Create and Manage a tablespace that uses NFS mounted file system file
[ ] Set up ADR file based repository
[ ] Perform cold database backup
[ ] Manage user accounts and use case sensitive passwords
[ ] Use OPatch to install a patch
[ ] Install and configure EM Agent
[ ] Create Base Recovery Catalog
[ ] Configure RMAN
[ ] Perform multisection backup of a datafile
[ ] Create an Archival Backup
Data Guard
[ ] Create Physical Standby Database with real-time apply.
[ ] Configure the data guard environment to reduce overheads of fast incremental backups on the primary database
[ ] Configure the Observer
[ ] Switchover and switch back
[ ] Convert the standby to a snapshot standby
[ ] Configure archivelog deletion policy for the dataguard configuration
Data and Data Warehouse Management
[ ] Troubleshoot fast materialized views to fast refresh and query rewrite
[ ] Add a tablespace by using Transportable Tablespace Feature of Data Pump Import (cross platform transportable tablespace)
[ ] Configure a schema to support a star transformation query
[ ] Configure and use parallel execution for queries
[ ] Use and access SecureFile LOBS
[ ] Create partitioned tables (includes reference and interval partitioning)
[ ] Configure Flashback Data Archive
[ ] Use Oracle Streams to capture and propagate changes in a table
Performance Management
[ ] Set up and configure Resource Manager to control active sessions, number of I/Os, execution time..etc
[ ] Use Result Cache
[ ] Use multi column statistics
[ ] Gather statistics on a specific table without invalidating cursors
[ ] Use partitioned indexes
[ ] Use SQL Tuning Advisor
[ ] Use SQL Access Advisor
[ ] Configure baseline templates
[ ] Use SQL Plan Management feature
[ ] Replay a captured workload
2010年6月29日 星期二
使用Logmnr挖掘logfile
1.從Oracle10g開始,必須先啓用supplemental log,才能確保可以挖掘到所有的異動.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 16 INACTIVE
2 17 CURRENT
3 15 INACTIVE
SQL> alter system archive log current;
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 16 INACTIVE
2 17 ACTIVE
3 18 CURRENT
2.進行DDL/DML範例操作
SQL> create table hr.d1 as select * from hr.employees;
SQL> select rowid,employee_id,department_id from hr.d1 where department_id=10;
ROWID EMPLOYEE_ID DEPARTMENT_ID
------------------ ----------- -------------
AAAM9eAAEAAAASMAAC 200 10
SQL> select rowid,employee_id,department_id from hr.d1 where employee_id in (100,206);
ROWID EMPLOYEE_ID DEPARTMENT_ID
------------------ ----------- -------------
AAAM9eAAEAAAASMAAI 206 110
AAAM9eAAEAAAASMAAJ 100 90
SQL> alter table hr.d1 add constraint d1_id_pk primary key(employee_id);
SQL> delete hr.d1 where department_id=10;
已刪除 1 個資料列.
SQL> commit;
SQL> update hr.d1 set salary=10000 where employee_id=206;
已更新 1 個資料列.
SQL> update hr.d1 set salary=12345 where employee_id=100;
已更新 1 個資料列.
SQL> commit;
SQL> alter system switch logfile ;
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 19 CURRENT
2 17 ACTIVE
3 18 ACTIVE
3.開始進行日誌挖掘
SQL> select name from v$archived_log where sequence#=18; --archived logfile
NAME
--------------------------------------------------------------------------------
C:\ORCL_ARCHIVE1\ARC00018_0720742289.001
C:\FRA_DIR\ORCL\ARCHIVELOG\2010_06_29\O1_MF_1_18_62MS4CC1_.ARC
SQL> select member from v$logfile where group#=1; --current online logfile
MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
3.1 加入準備被挖掘的logfile,可以為online logfile或archived logfile
SQL> execute dbms_logmnr.add_logfile('C:\ORCL_ARCHIVE1\ARC00018_0720742289.001',dbms_logmnr.addfile);
SQL> execute dbms_logmnr.add_logfile('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG',dbms_logmnr.addfile);
3.2 開始挖掘日誌,挖掘結果將顯示在v$logmnr_contents
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
--因為redo entry所記錄的內容是object id,所以必須透過data dictionary將object id與user id轉換為object name與username
--dbms_logmnr.dict_from_online_catalog表示直接使用目前的dictionary進行轉換
3.3 查詢v$logmnr_contents內容
SQL> select count(*) from v$logmnr_contents; --資料來源就是剛剛加入的online/archived logfile
COUNT(*)
----------
1645
SQL> desc v$logmnr_contents
名稱 空值? 類型
----------------------------------------- -------- ----------------------------
SCN NUMBER
CSCN NUMBER
TIMESTAMP DATE
COMMIT_TIMESTAMP DATE
THREAD# NUMBER
LOG_ID NUMBER
XIDUSN NUMBER
XIDSLT NUMBER
XIDSQN NUMBER
PXIDUSN NUMBER
PXIDSLT NUMBER
PXIDSQN NUMBER
RBASQN NUMBER
RBABLK NUMBER
RBABYTE NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBAREC NUMBER
UBASQN NUMBER
ABS_FILE# NUMBER
REL_FILE# NUMBER
DATA_BLK# NUMBER
DATA_OBJ# NUMBER
DATA_OBJD# NUMBER
SEG_OWNER VARCHAR2(32)
SEG_NAME VARCHAR2(256)
TABLE_NAME VARCHAR2(32)
SEG_TYPE NUMBER
SEG_TYPE_NAME VARCHAR2(32)
TABLE_SPACE VARCHAR2(32)
ROW_ID VARCHAR2(18)
SESSION# NUMBER
SERIAL# NUMBER
USERNAME VARCHAR2(30)
SESSION_INFO VARCHAR2(4000)
TX_NAME VARCHAR2(256)
ROLLBACK NUMBER
OPERATION VARCHAR2(32)
OPERATION_CODE NUMBER
SQL_REDO VARCHAR2(4000)
SQL_UNDO VARCHAR2(4000)
RS_ID VARCHAR2(32)
SEQUENCE# NUMBER
SSN NUMBER
CSF NUMBER
INFO VARCHAR2(32)
STATUS NUMBER
REDO_VALUE NUMBER
UNDO_VALUE NUMBER
SQL_COLUMN_TYPE VARCHAR2(30)
SQL_COLUMN_NAME VARCHAR2(30)
REDO_LENGTH NUMBER
REDO_OFFSET NUMBER
UNDO_LENGTH NUMBER
UNDO_OFFSET NUMBER
DATA_OBJV# NUMBER
SAFE_RESUME_SCN NUMBER
XID RAW(8)
PXID RAW(8)
AUDIT_SESSIONID NUMBER
SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';
已更改階段作業.
SQL> select timestamp,operation,sql_redo,sql_undo from v$logmnr_contents
2 where seg_owner='HR' and table_name='D1' and operation != 'DIRECT INSERT';
--將create table as select所產生的INSERT操作過濾掉(operation != 'DIRECT INSERT')
TIMESTAMP OPERATION
------------------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
2010-06-29:20:16:01 DDL
create table hr.d1 as select * from hr.employees;
2010-06-29:20:17:15 DDL
alter table hr.d1 add constraint d1_id_pk primary key(employee_id);
TIMESTAMP OPERATION
------------------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
2010-06-29:20:17:23 DELETE
delete from "HR"."D1" where "EMPLOYEE_ID" = '200' and "FIRST_NAME" = 'Jennifer'
and "LAST_NAME" = 'Whalen' and "EMAIL" = 'JWHALEN' and "PHONE_NUMBER" = '515.123
.4444' and "HIRE_DATE" = TO_DATE('1987-09-17:00:00:00', 'YYYY-MM-DD:HH24:MI:SS')
and "JOB_ID" = 'AD_ASST' and "SALARY" = '4400' and "COMMISSION_PCT" IS NULL and
"MANAGER_ID" = '101' and "DEPARTMENT_ID" = '10' and ROWID = 'AAAM9eAAEAAAASMAAC
';
insert into "HR"."D1"("EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMB
ER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID")
values ('200','Jennifer','Whalen','JWHALEN','515.123.4444',TO_DATE('1987-09-17:
00:00:00', 'YYYY-MM-DD:HH24:MI:SS'),'AD_ASST','4400',NULL,'101','10');
2010-06-29:20:17:31 UPDATE
update "HR"."D1" set "SALARY" = '10000' where "EMPLOYEE_ID" = '206' and "SALARY"
= '8300' and ROWID = 'AAAM9eAAEAAAASMAAI';
update "HR"."D1" set "SALARY" = '8300' where "EMPLOYEE_ID" = '206' and "SALARY"
= '10000' and ROWID = 'AAAM9eAAEAAAASMAAI';
2010-06-29:20:17:38 UPDATE
update "HR"."D1" set "SALARY" = '12345' where "EMPLOYEE_ID" = '100' and "SALARY"
= '24000' and ROWID = 'AAAM9eAAEAAAASMAAJ';
update "HR"."D1" set "SALARY" = '24000' where "EMPLOYEE_ID" = '100' and "SALARY"
= '12345' and ROWID = 'AAAM9eAAEAAAASMAAJ';
3.4 結束日誌挖掘
SQL> execute dbms_logmnr.end_logmnr;
已順利完成 PL/SQL 程序.
SQL> select timestamp,operation,sql_redo,sql_undo from v$logmnr_contents
2 where seg_owner='HR' and table_name='D1' and operation != 'DIRECT INSERT';
select timestamp,operation,sql_redo,sql_undo from v$logmnr_contents where seg_owner='HR' and table_name='D1' a
nd operation != 'DIRECT INSERT'
*
ERROR 在行 1:
ORA-01306: 必須在 v$logmnr_contents 選取之前啟動 dbms_logmnr.start_logmnr()
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 16 INACTIVE
2 17 CURRENT
3 15 INACTIVE
SQL> alter system archive log current;
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 16 INACTIVE
2 17 ACTIVE
3 18 CURRENT
2.進行DDL/DML範例操作
SQL> create table hr.d1 as select * from hr.employees;
SQL> select rowid,employee_id,department_id from hr.d1 where department_id=10;
ROWID EMPLOYEE_ID DEPARTMENT_ID
------------------ ----------- -------------
AAAM9eAAEAAAASMAAC 200 10
SQL> select rowid,employee_id,department_id from hr.d1 where employee_id in (100,206);
ROWID EMPLOYEE_ID DEPARTMENT_ID
------------------ ----------- -------------
AAAM9eAAEAAAASMAAI 206 110
AAAM9eAAEAAAASMAAJ 100 90
SQL> alter table hr.d1 add constraint d1_id_pk primary key(employee_id);
SQL> delete hr.d1 where department_id=10;
已刪除 1 個資料列.
SQL> commit;
SQL> update hr.d1 set salary=10000 where employee_id=206;
已更新 1 個資料列.
SQL> update hr.d1 set salary=12345 where employee_id=100;
已更新 1 個資料列.
SQL> commit;
SQL> alter system switch logfile ;
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 19 CURRENT
2 17 ACTIVE
3 18 ACTIVE
3.開始進行日誌挖掘
SQL> select name from v$archived_log where sequence#=18; --archived logfile
NAME
--------------------------------------------------------------------------------
C:\ORCL_ARCHIVE1\ARC00018_0720742289.001
C:\FRA_DIR\ORCL\ARCHIVELOG\2010_06_29\O1_MF_1_18_62MS4CC1_.ARC
SQL> select member from v$logfile where group#=1; --current online logfile
MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
3.1 加入準備被挖掘的logfile,可以為online logfile或archived logfile
SQL> execute dbms_logmnr.add_logfile('C:\ORCL_ARCHIVE1\ARC00018_0720742289.001',dbms_logmnr.addfile);
SQL> execute dbms_logmnr.add_logfile('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG',dbms_logmnr.addfile);
3.2 開始挖掘日誌,挖掘結果將顯示在v$logmnr_contents
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
--因為redo entry所記錄的內容是object id,所以必須透過data dictionary將object id與user id轉換為object name與username
--dbms_logmnr.dict_from_online_catalog表示直接使用目前的dictionary進行轉換
3.3 查詢v$logmnr_contents內容
SQL> select count(*) from v$logmnr_contents; --資料來源就是剛剛加入的online/archived logfile
COUNT(*)
----------
1645
SQL> desc v$logmnr_contents
名稱 空值? 類型
----------------------------------------- -------- ----------------------------
SCN NUMBER
CSCN NUMBER
TIMESTAMP DATE
COMMIT_TIMESTAMP DATE
THREAD# NUMBER
LOG_ID NUMBER
XIDUSN NUMBER
XIDSLT NUMBER
XIDSQN NUMBER
PXIDUSN NUMBER
PXIDSLT NUMBER
PXIDSQN NUMBER
RBASQN NUMBER
RBABLK NUMBER
RBABYTE NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBAREC NUMBER
UBASQN NUMBER
ABS_FILE# NUMBER
REL_FILE# NUMBER
DATA_BLK# NUMBER
DATA_OBJ# NUMBER
DATA_OBJD# NUMBER
SEG_OWNER VARCHAR2(32)
SEG_NAME VARCHAR2(256)
TABLE_NAME VARCHAR2(32)
SEG_TYPE NUMBER
SEG_TYPE_NAME VARCHAR2(32)
TABLE_SPACE VARCHAR2(32)
ROW_ID VARCHAR2(18)
SESSION# NUMBER
SERIAL# NUMBER
USERNAME VARCHAR2(30)
SESSION_INFO VARCHAR2(4000)
TX_NAME VARCHAR2(256)
ROLLBACK NUMBER
OPERATION VARCHAR2(32)
OPERATION_CODE NUMBER
SQL_REDO VARCHAR2(4000)
SQL_UNDO VARCHAR2(4000)
RS_ID VARCHAR2(32)
SEQUENCE# NUMBER
SSN NUMBER
CSF NUMBER
INFO VARCHAR2(32)
STATUS NUMBER
REDO_VALUE NUMBER
UNDO_VALUE NUMBER
SQL_COLUMN_TYPE VARCHAR2(30)
SQL_COLUMN_NAME VARCHAR2(30)
REDO_LENGTH NUMBER
REDO_OFFSET NUMBER
UNDO_LENGTH NUMBER
UNDO_OFFSET NUMBER
DATA_OBJV# NUMBER
SAFE_RESUME_SCN NUMBER
XID RAW(8)
PXID RAW(8)
AUDIT_SESSIONID NUMBER
SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';
已更改階段作業.
SQL> select timestamp,operation,sql_redo,sql_undo from v$logmnr_contents
2 where seg_owner='HR' and table_name='D1' and operation != 'DIRECT INSERT';
--將create table as select所產生的INSERT操作過濾掉(operation != 'DIRECT INSERT')
TIMESTAMP OPERATION
------------------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
2010-06-29:20:16:01 DDL
create table hr.d1 as select * from hr.employees;
2010-06-29:20:17:15 DDL
alter table hr.d1 add constraint d1_id_pk primary key(employee_id);
TIMESTAMP OPERATION
------------------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
2010-06-29:20:17:23 DELETE
delete from "HR"."D1" where "EMPLOYEE_ID" = '200' and "FIRST_NAME" = 'Jennifer'
and "LAST_NAME" = 'Whalen' and "EMAIL" = 'JWHALEN' and "PHONE_NUMBER" = '515.123
.4444' and "HIRE_DATE" = TO_DATE('1987-09-17:00:00:00', 'YYYY-MM-DD:HH24:MI:SS')
and "JOB_ID" = 'AD_ASST' and "SALARY" = '4400' and "COMMISSION_PCT" IS NULL and
"MANAGER_ID" = '101' and "DEPARTMENT_ID" = '10' and ROWID = 'AAAM9eAAEAAAASMAAC
';
insert into "HR"."D1"("EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMB
ER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID")
values ('200','Jennifer','Whalen','JWHALEN','515.123.4444',TO_DATE('1987-09-17:
00:00:00', 'YYYY-MM-DD:HH24:MI:SS'),'AD_ASST','4400',NULL,'101','10');
2010-06-29:20:17:31 UPDATE
update "HR"."D1" set "SALARY" = '10000' where "EMPLOYEE_ID" = '206' and "SALARY"
= '8300' and ROWID = 'AAAM9eAAEAAAASMAAI';
update "HR"."D1" set "SALARY" = '8300' where "EMPLOYEE_ID" = '206' and "SALARY"
= '10000' and ROWID = 'AAAM9eAAEAAAASMAAI';
2010-06-29:20:17:38 UPDATE
update "HR"."D1" set "SALARY" = '12345' where "EMPLOYEE_ID" = '100' and "SALARY"
= '24000' and ROWID = 'AAAM9eAAEAAAASMAAJ';
update "HR"."D1" set "SALARY" = '24000' where "EMPLOYEE_ID" = '100' and "SALARY"
= '12345' and ROWID = 'AAAM9eAAEAAAASMAAJ';
3.4 結束日誌挖掘
SQL> execute dbms_logmnr.end_logmnr;
已順利完成 PL/SQL 程序.
SQL> select timestamp,operation,sql_redo,sql_undo from v$logmnr_contents
2 where seg_owner='HR' and table_name='D1' and operation != 'DIRECT INSERT';
select timestamp,operation,sql_redo,sql_undo from v$logmnr_contents where seg_owner='HR' and table_name='D1' a
nd operation != 'DIRECT INSERT'
*
ERROR 在行 1:
ORA-01306: 必須在 v$logmnr_contents 選取之前啟動 dbms_logmnr.start_logmnr()
2010年3月22日 星期一
deferred segment creation(延緩的區段建立)
Oracle 11g R2新增加一種功能:deferred segment creation(延緩的區段建立)
也就是在建立新區段時,並不會立刻配置區段.而是延緩到區段第一次被新增資料列時,才真正配置區段
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> create user frank identified by oracle;
User created.
SQL> grant create session,create table to frank;
Grant succeeded.
SQL> create table frank.t1 (a number,b varchar2(10)) tablespace users;
Table created.
frank並沒有被設定配額(Quota)在users表格空間上,但是還是可以成功地建立t1表格
SQL> insert into frank.t1 values(1,'A');
insert into frank.t1 values(1,'A')
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
因為延緩的區段建立的機制,所以現在才試圖建立區段,所以才會出現上面的錯誤訊息
SQL> select object_id,data_object_id from dba_objects
2 where object_name='T1' and object_type='TABLE' and owner='FRANK';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
74695 74695
SQL> select segment_name,segment_type from dba_segments
2 where segment_name='T1' and owner='FRANK';
no rows selected
也就是在建立新區段時,並不會立刻配置區段.而是延緩到區段第一次被新增資料列時,才真正配置區段
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> create user frank identified by oracle;
User created.
SQL> grant create session,create table to frank;
Grant succeeded.
SQL> create table frank.t1 (a number,b varchar2(10)) tablespace users;
Table created.
frank並沒有被設定配額(Quota)在users表格空間上,但是還是可以成功地建立t1表格
SQL> insert into frank.t1 values(1,'A');
insert into frank.t1 values(1,'A')
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
因為延緩的區段建立的機制,所以現在才試圖建立區段,所以才會出現上面的錯誤訊息
SQL> select object_id,data_object_id from dba_objects
2 where object_name='T1' and object_type='TABLE' and owner='FRANK';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
74695 74695
SQL> select segment_name,segment_type from dba_segments
2 where segment_name='T1' and owner='FRANK';
no rows selected
2010年2月11日 星期四
log_archive_dest_31 ?
--從Oracle 11g R2後,archivelog destination可以多達31
--log_archive_dest_n可以將archived log存放在本地端:location=/path/或遠端standby database:service=standbydb_service_name
--可以log_archive_dest_1到10可以設定為location或service
--但是log_archive_dest_11到31只能設定為service
SQL> show parameter log_archive_dest_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
--Archiver的個數也多達30個
SQL> select count(*) from v$bgprocess where substr(name,1,3)='ARC';
COUNT(*)
----------
30
--log_archive_dest_1 到 log_archive_dest_10
--可以將archived logfile傳到local disk或遠端的standby database(使用net service name)
SQL> alter system set log_archived_dest_1='location=/home/oracle/orcl_archive1/';
--orcl_standby為net service name,可以連到standby database
SQL> alter system set log_archived_dest_5='serivce=orcl_standby';
--log_archive_dest_11到log_archive_dest_31
--只能用來將archived logfile傳到遠端的standby database
SQL> alter system set log_archive_dest_11='location=/home/oracle/orcl_archive1';
alter system set log_archive_dest_11='location=/home/oracle/orcl_archive1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16025: parameter LOG_ARCHIVE_DEST_11 contains repeated or conflicting
attributes
SQL> alter system set log_archive_dest_11='service=orcl_standby';
System altered.
--log_archive_dest_n可以將archived log存放在本地端:location=/path/或遠端standby database:service=standbydb_service_name
--可以log_archive_dest_1到10可以設定為location或service
--但是log_archive_dest_11到31只能設定為service
SQL> show parameter log_archive_dest_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
--Archiver的個數也多達30個
SQL> select count(*) from v$bgprocess where substr(name,1,3)='ARC';
COUNT(*)
----------
30
--log_archive_dest_1 到 log_archive_dest_10
--可以將archived logfile傳到local disk或遠端的standby database(使用net service name)
SQL> alter system set log_archived_dest_1='location=/home/oracle/orcl_archive1/';
--orcl_standby為net service name,可以連到standby database
SQL> alter system set log_archived_dest_5='serivce=orcl_standby';
--log_archive_dest_11到log_archive_dest_31
--只能用來將archived logfile傳到遠端的standby database
SQL> alter system set log_archive_dest_11='location=/home/oracle/orcl_archive1';
alter system set log_archive_dest_11='location=/home/oracle/orcl_archive1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16025: parameter LOG_ARCHIVE_DEST_11 contains repeated or conflicting
attributes
SQL> alter system set log_archive_dest_11='service=orcl_standby';
System altered.
訂閱：
文章 (Atom)
