Install OEL 5.7 and Configure OEL for Oracle Database 10g

1.啓動VM,確定以OEL 5.7 ISO檔為起動裝置

2.直接按下"Enter"鍵

3.請選擇"Skip"跳過媒體檢查

4.直接按"Next"

5.選擇"English"當作此次安裝的畫面語言

6.選擇鍵盤模式"U.S English"

7.進行磁碟分區(Disk Partition)操作

8.初學者可以直接使用OEL的預設分區

9.按下"Yes"確認分區方式

10.正式使用時,建議不要使用DHCP,但是測試環境下可以使用以簡化OS設定

11.設定時區

12.設定root的密碼

13.選擇"Customize now"

14.選擇"Base System"->"System Tools"->"Optional packages"

15.選擇安裝"oracle-validated" RPM,

這個package將自動安裝所有Oracle Software所需的package.

並設定相關的kernel parameters與OS USER(oracle)

16.按下"Next",開始安裝

17.安裝進行中

18.安裝完成後,將自動卸載OEL ISO,讓下次啓動使用此次所安裝的OEL.

重新啓動Guest OS

19.安裝後的第一次啓動,需要進行一些安裝後設定.

20.同意OEL的版權宣告

21.設定防火牆,若在測試環境,可以直接選擇"Disabled"

22.設定安全強化功能,測試環境可以直接"Disabled"

23.直接按下"Forward"

24.設定OS時間,或使用NTP

25.如果已安裝oracle-validated package,則可以跳過此步驟

因為已經自動建立一個OS User:oracle與OS Group:oinstall/dba

26.直接按下"Forward"

27.按下"Finish"

28.第一次登入請使用root.進行後續設定

29.設定OS User(oracle)的密碼,由oracle-validated package或前面所建立的OS User

30.建立oracle software與database的相關目錄

31.修改/etc/sysctl.conf內容,讓kernel parameter滿足oracle database 10g software的要求

32.使用sysctl -p讓修改後的/etc/sysctl.conf內容發生效果

33.安裝Guest Additions

34.將VBoxLinuxAdditions.run複製到OS,並設定為可執行

35.安裝VBoxLinuxAdditions,安裝完成後,請再重新啓動一次.

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能夠正確地使用虛擬欄位的功能，便可以一方面擁有正規化的好處，同時也可以達到原本需要反正規化才能得到的效能提昇。

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的資料庫才對。

使用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()

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

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.

Show parameter的結果，是可以相信的嗎?

Oracle DBA常常會使用SQLPLUS指令:show parameter，來顯示目前的參數值
例如:
SQL> show parameter fast_start_mttr_target
NAME TYPE VALUE
------------------ ------- ------
fast_start_mttr_target integer 0

但是這個參數值的來源到底是Session Specific還是Instance Level的參數值
其實show parameter顯示的是目前這個Session的參數值，也就是v$parameter的內容

但是有些參數可以在Session Level與Instance Level有不同的參數值
所有想要知道Instance Level的參數，必須查詢v$system_parameter

可是有些參數值，使用v$parameter或v$system_parameter查詢時，都有顯示參數值
但是可能只是使用預設值或尤其它參數所衍生的參數值，必沒有真的被設定在參數檔中
所以可以使用v$spparameter來查詢某個參數到底是否有明確設定
例如剛剛使用show parameter顯示fast_start_mttr_target的值為0,可是這個參數真的有被明確設定為0嗎？
SQL>select value from v$spparameter where name='fast_start_mttr_target';

VALUE
-----

如果結果如上，表示在spfile中並沒有設定此參數，所以show parameter的結果只是使用此參數的預設值，而fast_start_mttr_target的預設值剛好為0

若結果為
VALUE
-----
0
則表示fast_start_mttr_target的參數值明確被設定為0

Function Based Index 與 Cursor_sharing 的搭配

今天有人問我，為何他所建立的Function Based Index並沒有被使用,即便使用Hint也依然使用Full Table Scan
我向他要了相關的資訊後,使用下列的範例來解釋這個問題
在使用function based index時要注意cursor_sharing的設定值,將可能造成無法使用索引的問題

1.首先建立測試環境
SQL> create table t1 as select * from all_objects;
SQL> create index t1_fidx on t1(substr(object_name,3,6);
SQL> execute dbms_stats.gather_table_stats('HR','T1',cascade=>true);

2.開始測試
SQL> select substr(object_name,3,6) from t1 where object_id=10000;

SUBSTR(OBJEC
------------
ER_ADV

SQL> select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV';
查詢結果省略

3.使用sql_id找出真正使用的執行計畫,如果使用explain plan或set autotrace有時候會與真正的執行計畫不同
SQL> select sql_id from v$sqlarea
2 where sql_text like 'select object_name,object_id from t1%';

SQL_ID
-------------
fyt5sd2rt1zv6

SQL> select * from table(dbms_xplan.display_cursor('fyt5sd2rt1zv6'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID fyt5sd2rt1zv6, child number 0
-------------------------------------
select object_name,object_id from t1 where
substr(object_name,3,6)='ER_ADV'

Plan hash value: 2441016827

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 114 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_FIDX | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T1"."SYS_NC00016$"='ER_ADV')


20 rows selected.
由以上的結果可以看出,Function based Index有被使用


4.現在將cursor_sharing設為force,在進行相關的測試
SQL> select substr(object_name,3,6) from t1 where object_id=20000;

SUBSTR(OBJEC
------------
565853

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select object_name,object_id from t1
2 where substr(object_name,3,6)='565853';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/25658537_StoreInstruction 20000
/25658537_StoreInstruction 39336

SQL> select substr(object_name,3,6) from t1 where object_id=30000;

SUBSTR(OBJECT_NAME,3,6)
------------------------------------------------------------
6d7f7a

SQL> select object_name,object_id from t1
2 where substr(object_name,3,6)='6d7f7a';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/86d7f7a5_ProfileErrorsText_el 30000
/86d7f7a5_ProfileErrorsText_el 49335

SQL> select sql_id,sql_text from v$sqlarea where
2 sql_text like 'select object_name,object_id from t1%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
fyt5sd2rt1zv6
select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV'

6w6hd3w7ju6v9
select object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
注意現在的where條件已經被cursor_sharing=force的功能修改為substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
與所執行的where substr(object_name,3,6)不同
所以才會造成下面的結果,使用full table scan,而不使用function based index

SQL> select * from table(dbms_xplan.display_cursor('6w6hd3w7ju6v9'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6w6hd3w7ju6v9, child number 0
-------------------------------------
select object_name,object_id from t1 where
substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 230 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 538 | 16678 | 230 (2)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(SUBSTR("OBJECT_NAME",:SYS_B_0,:SYS_B_1)=:SYS_B_2)


19 rows selected.

5.即便加上hint,強迫使用index,也無法使用
SQL> select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1
2 where substr(object_name,3,6)='6d7f7a';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/86d7f7a5_ProfileErrorsText_el 30000
/86d7f7a5_ProfileErrorsText_el 49335

SQL> select sql_id,sql_text from v$sqlarea where
2 sql_text like 'select % from t1 where substr%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
9hnjcn7h3t35d
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"
SYS_B_2"

fyt5sd2rt1zv6
select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV'

6w6hd3w7ju6v9
select object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------


SQL> select * from table(dbms_xplan.display_cursor('9hnjcn7h3t35d'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 9hnjcn7h3t35d, child number 0
-------------------------------------
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where
substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 230 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| T1 | 538 | 16678 | 230 (2)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(SUBSTR("OBJECT_NAME",:SYS_B_0,:SYS_B_1)=:SYS_B_2)


19 rows selected.

6.如果將cursor_sharing設為exact,則hint就可以發揮效果,當然若不使用hint在目前的環境,也是可以使用function based index
SQL> alter session set cursor_sharing=exact;

Session altered.

SQL> select substr(object_name,3,6) from t1 where object_id=40000;

SUBSTR(OBJEC
------------
b93801

SQL> select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1
2 where substr(object_name,3,6)='b93801';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/7b938016_ProtocolVersion 20664
/7b938016_ProtocolVersion 40000

SQL> select sql_id,sql_text from v$sqlarea where
2 sql_text like 'select % from t1 where substr%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
bbhcgkhrysz5s
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where substr(object_name,3,6)='b93801'

9hnjcn7h3t35d
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"
SYS_B_2"

fyt5sd2rt1zv6
select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV'

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------

0uywm521n21jj
select sql_id,sql_text from v$sqlarea where sql_text like 'select % from t1 where substr%'

6w6hd3w7ju6v9
select object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"


SQL> select * from table(dbms_xplan.display_cursor('bbhcgkhrysz5s'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID bbhcgkhrysz5s, child number 0
-------------------------------------
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where
substr(object_name,3,6)='b93801'

Plan hash value: 2441016827

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 114 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_FIDX | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T1"."SYS_NC00016$"='b93801')


20 rows selected.