[ORACLE] Get size in BYTE of a column

LENGTHB(COLUMN_NAME)

謝謝有為的中年人<(_ _)>
ref: KB-在Oracle中直接取得中文字串長度

Oracle shutdown hangs: forgot the ‘immediate’ option

一個簡單的小問題，不過還滿常發生的=   =||

一般需要關閉資料庫時，都會下'shutdown immediate’指令，有了immediate選項，Oracle不需等待所有連線關閉。反之，若只有下'shutdown'指令，也就是'shutdown normal'，Oracle就會等到所有連線都結束後才關閉，通常都關不下來... 這時候就算又開了新的連線想重下指令，都會收到類似"Not connected to Oracle"這類的訊息.. 這時候!!

可以另外開一個新連線這麼做:

SQL> conn sys as sysdba
輸入密碼:
連線至閒置的執行處理.
SQL> startup force
已啟動 ORACLE 執行處理.
……(資料庫已掛載./資料庫已開啟.)
SQL> shutdown immediate

這樣就可以成功的把資料庫關下來囉~

另一邊原本正在shutdown的視窗可能會看見如下過程:

SQL> shutdown
ORA-03113: 通訊通道上出現 EOF

SQL> ^F^D
SP2-0042: 未知的命令 "" － 此行的剩餘部份被略過不予處理

完工!

(ORA-01031) Cannot create/edit view with the owner of the schema

environment: enterprise linux + oracle11gr2

前情提要:
userA先生在9i的時代運作得相當正常，當然包含本篇所要探討的小小新建一個view的動作。升級到11g之後某天，卻發現userA再也無法在自己的schema下create view... 這麼小小一件事情都辦不到!!! 檢查了權限也跟9i時代完全相同(就是CONNECT, RESOURCE, UNLIMITED TABLESPACE這麼簡單，所以也沒把腦筋動到權限上)，なんで??? 當時為了應急直接用dba帳號幫他處理掉了，今天又遇到一次，該是要面對的時候了..

一開始看到的都是在說"物件的權限需要直接grant給指定的人，不要透過角色(ROLE)來做"這件事情，因為在所有stroe procedures或PL/SQL block裡，角色權限是不會起作用的(All roles are disabled inside stored procedures.)等，不過這並不是我的情況..

無意間看到這篇文章在探討Oracle的Role包含了那些權限，才恍然大悟...

9ir2的CONNECT角色裡包含了"CREATE VIEW"的權限，但在11g裡，CONNECT的角色卻只剩下"CREATE SESSION"的權限... 就這麼簡單... 所以在11g要另外把CREATE VIEW的權限grant給userA先生，這樣就可以正常新建/修改view了... 就這樣orz

若要詳細列出角色所包含的權限，可使用以下查詢:

SQL> select * from ROLE_SYS_PRIVS where role='CONNECT';
(ROLE_SYS_PRIVS: 列出登入帳號所有擁的角色，及角色所包含的系統權限)

或者也可以這樣查:

SQL> select * from DBA_SYS_PRIVS where grantee='CONNECT';
(DBA_SYS_PRIVS: 列出所有user/role所擁有的系統權限)

Q: What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

[類別] PL/SQL
[問題] 什麼是SQLCODE與SQLERRM，為什麼他們對PL/SQL開發者而言很重要?

Answer:

SQLCODE與SQLERRM都是用在exception handler裡。

SQLCODE會回傳exception的號碼，SQLERRM則是錯誤訊息內容。
最常見的用法就是搭配WHER OTHERS EXCEPTION使用:

DECLARE

   v_code NUMBER;

   v_errm VARCHAR2(64);

BEGIN

   ………

EXCEPTION

   WHEN OTHERS THEN

        v_code := SQLCODE;

        v_errm := TO_CHAR(SQLERRM(v_code));

        DBMS_OUTPUT.PUTLINE(‘ERRCODE ’ || v_code);

        DBMS_OUTPUT.PUTLINE(‘ERRMSG ’ || v_errm);

END;

重要性? 當然是... 你可以處理exceptions，知道更詳細的訊息囉。

Q: When is a DECLARE statement needed?

[類別] PL/SQL
[問題] 什麼時候需用到DECLARE語法?

Answer:
DECLARE是用在PL/SQL區塊(block)內。一個PL/SQL block被DECLARE, BEGIN, EXCEPTION, END四個關鍵字分成三個部份:

DECLARE

  -- Declarative part(optional)

  -- for declartions of local types, variables, subprograms

BEGIN

  -- Executable part(required)

  -- statements

  EXCEPTION

    -- Exception-handling part(optional)

END;

所以，當你有需要宣告變數等，就會把它放在DECLARE的區塊內。

Q: Describe the use of PL/SQL tables.

混了一個禮拜... 該是回來面對這系列問題的時候了... 其實我也不是故意要跳過的=P。因為上禮拜的問題是: "What packages (if any) has Oracle provided for use by developers?" .... 這.. 就算我去讀了一些文件，還是認為自己無法具體的回答... 所以就放棄這題了.. (如果我在面試的時候被問到這個，大概也沒時間拖過一個星期XD)

[類別] PL/SQL
[問題] 請描述PL/SQL tables該如何使用

Answer:
先講講揪竟PL/SQL table(又叫index-by tables)是什麼? 這是它在9iR2以前的稱呼，9iR2起稱為"associative arrays”。他是一種:

• collection型別
• 有index
• 可用BINARY INTEGER或VARCHAR2做索引(indexed/associated)

他跟另一個collection型別: PL/SQL nested tables很相似:

• 是一維陣列(one-dimensional arrays)
• 無(大小)限制(unbounded, 理論上...只要記憶體夠大)
• 同質性(homogeneous, 就是array裡的每個element要一樣型別的意思..)

先來看一下宣告語法，先定義一個型別，接著才宣告變數:

TYPE 型別名稱 IS TABLE OF 一已知存在型別 INDEX BY [BINART_INTEGER | VARCHAR2(5)];

變數名稱 型別名稱;

ex:

TYPE position_table IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(10);

who_list position_table;

所以上面我們就定義了一個position_table(使用者定義)data type, 是以字串(string)作index, 然後宣告變數who_list是這個型別。該怎麼使用呢?

who_list('CEO') := ‘Fisher Liang’;

who_list('CDO') := ‘Aileen Wu';

DBMS_OUTPUT.PUT_LINE('The CEO is ' || who_list('CEO'));

這樣~ 'CEO', 'CDO'就是index，而且是unique的，若是重複指定，就是取代舊有值的意思。雖然是個array，用起來卻很像是有index的table對巴~?

PL/SQL Tables/associative arrays只做暫時存放資料用，也不是一個真正的table，並無法對其使用insert或select into等SQL statement。不過可以做到session level的life cycle, 就是把以上宣告及使用放在package裡。

完畢!

Ref:

1. PLSQL Tutorial – PLSQL Collections
2. PLSQL Tutorial – Oracle PLSQL Collection Types
3. PL/SQL User’s Guide and Reference – PL/SQL Collections and Records
4. [Oracle-Base] Associative Array in Oracle9i Release 2

Using binding vareable with “LIKE” condition

這感覺就是一個相當簡單又會很常用到的東西... 卻一時寫不出來OTZ

通常我們用到LIKE時，大多就是想做模糊搜尋吧...

像下面這個SQL是要找名字裡包含大寫SM的人:
SELECT * FROM EMPLOYEE WHERE NAME LIKE ‘%SM%’

像上面這樣寫，就變成傳說中的hard coding，有時候為了效能考量，我們要把值的地方以變數(:variable)取代，所以這兩種組合揪竟該怎麼兜在一起呢!?

這樣… LIKE '%:variable%'          (X, :variable直接被當成字串=   =)
這樣… LIKE ''%:variable%''        (X, :variable終於不被當成字串，還是失敗)
這樣… LIKE '%'||:variable||'%'     (O, 原來要自己組....)

BUG! “Environment variable ORACLE_UNQNAME not defined”

Environment:
DB version: Oracle 11gr2
Platform: Linux RedHat EL5

今天本來是在做"Automating Database Startup and Shutdown on Linux" setp by step。照著文件做，也處理了最後"Known Issues"的部分後，發現.... Oracle em(dbconsole)的服務並沒有起來...

也不是什麼大問題，就是dbstart這支script裡並沒有把他寫進去而已，於是自己把他補在"/etc/init.d/dbora"(依照上面文件做的一個檔案)裡面:

su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole” ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^上面這行

補了一行code之後又重機一次，發現竟然還是一樣... 失敗了=   =，如果以oracle的身分直接去跑"emctl start dbconsole"是可以成功的，但是如果一開始是以root的身分切換到oracle去下指令(就是補上的那一行做的事情)，會被抱怨:

Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

ORACLE_UNQNAME... 還真沒看過這個東西... 結果在oracle文件裡看到這是一個BUG(Bug 1716161)!! 不過他只跟你說，把ORACLE_UNQNAME這個環境變數的值設成db_unique_name這個初始化參數而已... how!?!?

再來看另一篇文章，他也是做一樣的事情，並且在最後一個步驟(6.)說明:
在"/etc/profile"裡補上一段code即可!!

if [ $USER = "oracle" ] ; then
if [ $SHELL = "/bin/ksh" ] ; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
export ORACLE_UNQNAME=db_unique_name
umask 022
fi

真的只是個bug!!!!

Ref:

1. [ORACLE-BASE] Automating Database Startup and Shutdown on Linux
2. [Oracle Database Readme] section38 “Open Bugs”
3. [JOBACLE] Automatic Start/stop in 11g release 2

Q: Describe the use of %ROWTYPE and %TYPE in PL/SQL

[類別] PL/SQL
[問題] 請描述%ROWTYPE跟%TYPE該如何使用

Answer:
%ROWTYPE可以用來宣告一個record跟
(1)資料庫內某個table/view  OR  (2)從cursor fetch出來  的資料列有相同結構。
宣告語法如下:

變數名稱 表格名稱(OR view名稱 OR cursor名稱)%ROWTYPE;

ex: tmpRow employee%ROWTYPE;

如上就會有一個tmpRow結構跟employee(這個表格)裡的列一樣，包括欄位名稱與資料型別。但並不繼承constraints!!

%TYPE可以用來宣告一個data item跟
(1)已宣告的變數  OR  (2)表格裡的某欄位  有相同的資料型別。
宣告語法如下:

變數名稱 表格名稱.欄位名稱%TYPE;

ex: tmpItem employee.salary%TYPE;

以上面的例子，tmpitem是所謂的referencing item，而employee.salary是referenced item。

使用%TYPE一定會繼承資料型別，但不一定會繼承contraints。當referenced item是資料庫表格的欄位時，就不會繼承。

Q: What is a mutating table error and how can you get around it?

相當慚愧的... 在看到這題以前，我也完全沒聽過mutating tabel這東西OTZ

[類別] PL/SQL
[問題] 什麼是mutating table error? 該如何處理這類錯誤?

Answer:
在回答什麼是mutating table error前，先來說說什麼是"mutating error"。當一個表格正在被UPDATE, DELETE或INSERT語法修改(也就是這個statement尚未commit, 表格正在"變更中")，或是一個table有可能是DELETE CASCADE的對象時，就是所謂的mutsting table。

只有row-level trigger(定義時使用FOR EACH ROW子句)嘗試要查詢或更新一個mutating table時，才會發生mutating table error! 主要是為了避免trigger存取不一致的資料。這時候trigger本身還有觸發trigger的語法(triggering statement)都會被rollback。

中文講的好爛請參閱英文講解 orz

A mutating table error (ORA-4091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).

Mutating table error的中文訊息長這樣:

ORA-04091: 表格 Schema.Table 正在變更中, 觸發程式/函數無法檢視它

一般避免mutating table error最常見的方法是:

使用compound triggers或temporary tables(or views)，讓同一session update/select不同表格

喋喋不休版
舉個會發生mutating table error的實例: 假如hr.employee的表格上有定義一個row-level的trigger: 每次有row被刪除時，會計算employee的總筆數。所以當你對hr.employee下一個SQL去刪除某列資料的同時(此時employee就是一個mutating table)，也會觸發該trigger，這個trigger嘗試要存取employee，以上條件(mutating table, row-level trigger)，即會發生mutating table error。

Materialized views: read-only, updatable, writeable

一直對oracle replication的機制不是相當了解，今天看了幾個章節，稍微有些心得，雖然還是不知道replication group存在的真正目的，不過到是對materialized view多了些認識。

Materialized view可以是read-only, updatable, writeable這三種模式。read-only相當好理解，不過updatable跟writeable兩個就... ?

Read-only
要做一個read-only的materialized view，就是不要使用"FOR UPDATE"這個子句(clause)即可。如其名，不可以對這類views執行任何DML語法。而且!!! 它也不需要屬於任何一個materialized view group。

Updatable
跟read-only相反，可以在這類views上執行DML語法，而這些變更也可以透過排程或其他方法更新回target master(可能是一個master table或master materialized view)；不過有一個前提: 就是updatable materialized view必須放在一個materialized view group裡。

Writeable
writeable的materialized view跟updatable一樣，會用到"FOR UPDATE"子句。唯一差別是，他不會放在materialized view group內，換句話說，即使可以對writeable的materialized view執行DML，但是變更並不會更新回target master。(那幹嘛不用read-only就好=   = 所以這類materialized view也比較少用)

整理如下:

	Read-only	Updatable	Writeable
created with “FOR UPDATE” clause?	N	Y	Y
perform DML operation?	N	Y	Y
placed in a materialized view group?	N	Y	N
changes be pushed back to master?	N	Y	N

Q: Difference between oracle procedure, function and anonymous pl/sql block?

耶!! 前陣子的"回答oracle interview questions計畫"終於要開始惹!!
先簡單說明一下，基本上這些問題會用中文來回答，但是專有名詞的部分... 還是維持它原本的面貌... 因為我根本不知道他中文是要翻成什麼鬼=  =… 而且這些東西翻成中文感覺反而會失去原本的意思或被誤解。

[類別] PL/SQL
[問題] procedure, function還有anonymous pl/sql block間有什麼不同?

Answer
procedure跟function最大的差別在於function會有一個回傳值(a single value)。而anonymous pl/sql block如他字面上定義，是一段沒有名字的pl/sql，通常直接用在像SQL*PLUS這樣的工具裡，來叫用procedure, function或package。

喋喋不休版
procedure跟function兩者可以合稱為"subprogram"，特色是具名、可以給予參數、可以有回傳值(only function)；存在schema層級的subprogram又叫做standalone stored subprogram，而定義在package內的就叫package subprograms。

Anonymous blocks由三個部分組成: 宣告(declarative part)、執行(executable part)、例外處理(exception handlers)，其中宣告與例外處理可有可無。特色是不具名(也就無法永久存於資料庫內)，只做暫時之用(不像procedure跟function可以重複被叫用)。

比較表:

	Anonymous Blocks	Subprograms
具名?	N	Y
每次使用時都進行編譯?	N	N
存於資料庫內?	N	Y
可被其他應用程式呼叫?	N	Y
可回傳bind variable值?	Y	Y
可回傳函數值?	N	Y
可接受參數?	N	Y

*bind variable就是"連結變數"... 就是我們sql語法裡為了效能考量寫成”column_value=:v”的這個:v

[Study] Oracle 9i Space Management Demystified (2)

(續) [Study] Oracle9i Space Management Demystified (1)
[原文] Original file here

9i提供給DBA的利器之二: Automatic Undo Management

這是原文的第一個part，旨在介紹undo tablespace/undo segment。在讀這篇文章以前，心裡一直有訊息就是: 9i以前存放undo data的叫做rollback segment，9i稱為undo segment。以為只是名稱上的不同，作用機制應該一樣。現在終於清楚了解是不同的!於是相當三八的畫了圖，就來看圖說故事吧XD

雖然名字不同、作用機制不同，不過目的是一樣的:

 

Rollback Segment Reviewed

Rollback segment由一個transaction table(存放在header)與兩個以上的extents(由undo blocks)組成。每個extent可以被多個transaction存取，但同一時間只能由單一transaction寫入。

Extent是循環使用的。每個transaction會嘗試使用下一個"有空"的extent，如果每個extent都已經被佔用，server會再配置新的extent。

Automatic Undo Management

9i的這個新功能是透過undo tablespace來實現，tablespace裡的segment就叫undo segment(header同樣有一個transaction table)，segment再往下的邏輯架構同rollback segment。

Undo tablespace啟用時，部份undo segments也同時online。每個transaction建立時，server會嘗試配給一個undo segment(transaction table)。當online的undo segments不夠用時，剩餘offline的undo segments 也會online來使用。若還是不夠，server會嘗試再配置新的undo segments，直到undo tablespace的空間不足了，才可能讓transaction共享同一個undo segment(最閒的那個)。

以上，就是rollback segment跟undo segment的差異。所以這樣的差異如何可以讓DBA的生命更美好呢XD?

1. 只要create一個夠大的undo tablespace，undo segment的數量與大小oracle server都會接手，自動依需求動態調整。若是9i以前，rollback segments的數量與大小都需要仔細考慮，才能降低因transaction佔用而導致效率下降的問題。
2. 跟auto undo management搭配的還有一個參數: UNDO_RETENTION。透過設定這個參數(秒數)，可以決定undo data需要被保留至少多久的時間。如果有long run query，可以降低ORA-1555(shapshot too old)的錯誤。不過要特別注意的是，這個參數要undo tablespace的大小配合。否則你希望保留較久的undo data，可是允許的空間不足的話... 只能說: 巧婦難為無米之炊XD
3. 承第一點，比起固定大小的rollback segment，因為undo segment大小動態調整，對於整個tablespace的空間利用也就較有效率。

所以如果是oracle 9i用戶，只要透過設定以下兩個初始化參數，就可以使用automatic undo management囉~

UNDO_MANAGEMENT=AUTO
COMPATIBLE=9.0.0

Sizing Undo Tablespace

所以，undo tablespace該給多大顯然是個重要的議題... 最簡單的方法是，如果我知道三件事情，那麼就可以決定"至少"要給多大的空間:

1. 資料庫平均每秒需要用到多少個undo blocks?
2. 每個undo block(data block)多大?
3. 政策上要保留多久時間(秒)以前的undo data?

以上，2跟3都很好解決，就是初始化參數的DB_BLOCK_SIZE跟上面提到的UNDO_RETENTION。1呢... 我們可以從V$UNDOSTAT這個view裡得到答案。

V$UNDOSTAT這個表每十分鐘就會多一筆資料，統計每十分鐘產生了多少個undo blocks以及最久的query時間。數學上是這樣: "每十分鐘吃掉X個糖果，每秒吃幾個?" XD，不過當然是全部數據拿來平均比較準。最久的query時間也有點用處，可以讓DBA們參考決定undo_retention要設多久。

所以得到以上三個數字後，相乘，可以得到一個byte數，那就是最小需要的undo tablespace的大小囉~

呼~ 大致上就這樣，最基本的概念介紹完畢。原文裡還有其他資訊，請自閱。

[ORA-00845]: MEMORY_TARGET not supported on this system

environment:

Oracle Database 11g Release 11.2.0.1.0 – 64bit
Red Hat Linux Server

人哪.. 如果閒著沒事幹就會替自己找麻煩=   =，今天把準備拿來當production的oracle shutdown下來，再重開。馬上就被賞了個大禮。說是大禮，因為要是今天沒發現... 等到真的上線了才手忙腳亂，也沒時間整理文件囉~ =P

先來說說11g的新變革:
Automatic Memory Management(AMM)是11g的新功能，需要額外的shared memory(/dev/shm)還有一些file descriptors來實現，透過MMAN這支process來管理動態管理SGA與PGA的大小。

比較一下11g跟10g以前的記憶體管理設定參數:

	11g	before 10g
memory size	MEMORY_TARGET	SGA_TARGET PGA_AGGREGATE_TARGET
limit	MEMORY_MAX_TARGET	SGA_MAX_TARGET

(當MEMORY_TARGET或MEMORY_MAX_TARGET其中一個有指定值時，SGA_MAX_TARGET會自動設成大的那個)

嗯... 簡單介紹到這，接著該來講正事: ORA-00845
在startup oracle時，若

1. /dev/shm not mounted
2. mounted with available size less then MEMORY_TARGET(系統內shared memory(/dev/shm)比設定的MEMORY_TARGET還要小)

就會丟出ORA-00845這個錯誤。同時去檢查alert log，也可以看見相關訊息，而且他也會建議適合的大小給你設定。

Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 2097152000 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 2071711744 and used is 0 bytes. Ensure that the mount point is /dev/shm for this directory.
memory_target needs larger /dev/shm

要判斷自己是哪種情況可以先下"df -k"這個指令來查看/dev/shm是否有mount，若正常，應該可以看到如下:

[...]$ df –k
檔案系統          1K-區段      已用        可用       已用%    掛載點
tmpfs             3145728   1062256   2083472    34%      /dev/shm

 

若確定有掛載，那就是大小問題，可以調大mountpoint size:

# mount -t tmpfs shmfs -o size=7g /dev/shm

 

為了讓每次server重開機時，可以自動分配同樣的大小，需要修改/etc/fstab這個檔案，請讓他看起來長的像這樣:
tmpfs     /dev/shm       tmpfs   size=3g    0 0

這樣就一切大功告成!! 祝startup愉快~ =D

Ref:

1. [Oracle Doc] Oracle Release Notes > Known issues
2. [Oracle Doc] Preinstallation Requirements > Hardware Requirements > Memory Requirements > Automatic Memory Management(AMM)
3. (need login) [Metalink] ORA-00845: MEMORY_TARGET not supported on this system - Linux Servers [ID 465048.1]
4. [Ask Tom] ORA-00845: …
5. [鳥哥] /etc/fstab

Oracle Role and Privileges

介紹一些跟角色(role)還有oracle系統權限(privileges)相關的view:

VIEW	Description
DBA_ROLES	All Roles which exist in the database
DBA_ROLE_PRIVS	Roles granted to users and roles
ROLE_ROLE_PRIVS	Roles which are granted to roles
ROLE_SYS_PRIVS	System privileges granted to roles
ROLE_TAB_PRIVS	Table privileges granted to roles

個人覺得最實用的是ROLE_SYS_PRIVS，在授予使用者角色時，用這個表可以看出ROLE包了哪些system privileges。

另外加贈兩個不錯的view: DBA_TAB_COMMENTS, DBA_COL_COMMENTS分別可以看表格或表格欄位的說明~

[Study] Oracle 9i Space Management Demystified (1)

[原文在此] Original file here

今天早上找到的好文章。版本老是老了點，不過有很多概念是一直延續的，個人認為這篇還有讀的價值。

開場白就說了database administrator花了他們大部分的時間在做space utilization, 而oracle 9i相當好心的提供了3個改革來改善DBA們的生命XD。

• Automatic Undo Management
• Locally Managed Tablespace
• Auto Segment Space Management

因為我是在找tablespace fragmentation相關議題的時候看到這篇文章的，所以會先從Locally managed tablespace這塊讀起。

Locally Managed Tablespace
首先介紹名字的由來，Locally Managed Tablespace是針對extents管理的一個改革。傳統的tablespace management方法是利用data dictionary (tables) 來追蹤extents的使用狀況(所以稱為dictionary-managed tablespace)，因為data dictionary屬於SYS, 是在不同的tablespace裡，所以整個資料庫內extents被分配或釋放時，都要集中在SYS tablespace裡記錄，還會有資源搶用(contention)的問題。

	Dictionary Managed	Locally Managed
space managed by	data dictionary (SYS)	bitmap (datafile header)
generate undo info.	yes	no
auto coalesce free space	no	yes

使用Locally managed tablespace還有分兩種決定extent size的方法: AUTO ALLOCATE跟UNIFORM EXTENT。

	AUTO ALLOCATE	UNIFORM EXTENT
size decision	by Oracle automatically	DBA
decision factor	corresponding segment	?: base on db utilization
initial extent	smaller (64K)	? (default 1M)
when enough free space not available	auto allocates smaller extents	fail
which mode to use	1. objects in tablespace vary drastivally in size 2. growth pattern is expected to be highly dynamic	only if all objects in the db are going to be of the same size and grow in a uniform manner

整體上看來，還是建議使用AUTO ALLOCATE模式即可。許多人可能認為使用UNIFORM EXTENT可以保證被釋放的extnets一定可以再被重新利用，因此也比較能減少fragment的現象。但其實不然，就像上面表格裡建議的，若是tablespace裡的物件經常性的建立還有刪除(像是暫時建了Table當作資料處理的中繼站)，那麼使用uniform就相當浪費空間，因為不管表格大或小，他就是會佔去一個固定大小的extent。反過來看auto allocate模式下的extents，雖然大小各不相同，但是他們基本上還是follow一個基本的模式在增長，通常是initial extent的倍數，所以再被重新利用並不難。

造成fragmentation的現象的主因並非extents size不同，而是因為tablespace裡的objects follow不同的extent sizing policy，而導致被釋放的extent很難再被重新利用。所以要用auto還是uniform mode? 基本上還是視資料庫的使用情形決定。

整體上看來，locally managed tablespace有兩大優點:

1. 效能較好: 因為少了data dictionary的搶用；還有他對space的管理方式，也是提升效能的關鍵之一(long running queries, add and drop objects)。
2. 較少fragmentation: 如上述。

OK!!! 這就是locally managed tablespace的部分，是這三個主題裡篇幅最少的一個惹~ XD

Interview Questions with Answers for Oracle, DBA, and developer candidates

Interview Questions with Answers for Oracle, DBA, and developer candidates
今天看資料的時候忽然找到這個網站，感覺起來滿有趣的，他把問題分為8大類:

1. PL/SQL
2. DBA
3. SQL/SQL PLUS
4. Tuning
5. Installation/Configuration
6. Data Modeler
7. UNIX
8. Oracle Troubleshooting

花了一些時間把他們整理起來。然後我決定... 要回答全部的問題!!! (好大的願望XD)，目前預計能將進度控制在每個禮拜回答一題XDD(沒辦法，我還有半本Oracle DBA Fundamentals還沒讀完Q_Q)。至於開始時間呢... 就看什麼時候開始回答第一題吧~ =P (最近好懶散..)
敬請期待!!!

[Study] Oracle Extended ROWID & Base-64 Decode

最近讀到ROWID這東西… 費了我不少時間啊 orz。

Introduction
ROWID顧名思義，可以透過他來識別/定位資料庫裡的任何一個row。他並非真正的欄位(column)，但是在每個表(table)裡，我們都可以下select ROWID來取得他的值。

Format
先簡單介紹一下Extended ROWID的構成 (在Oracle7以及更早以前的版本是所謂的Restricted ROWID，這裡不討論)，ROWID是經過base-64編碼後以18個字元呈現，需用到最大10 byte的儲存空間。

• Data object #: 6個字元。白話一點說，就是row所在的table。以oracle的邏輯架構來看，table算是一個segment，而透過segment我們可以知道這個row在哪個Tablespace內
  
• Relative file #: 3個字元。定位row實際上是在哪個datafile內
  
• Block #: 6個字元。定位row所在的data block
  
• Row #: 3個字元。定位row本身

ROWID
SQL> select rowed from hr.jobs where job_id='XXX';

實際上select出的ROWID，大概是長這樣 → AAAgwuAAKAAAl7hAAR
拆解各部份套到上面圖示，會是這樣:


原本以為到DBA_OBJECTS內找到相對應的'DATA_OBJECT_ID'欄位也會是"AAAgwu"，結果大錯特錯。

SQL> select data_object_id from dba.objects where owner='HR' and object_name='JOBS';

得到的值竟然是一個數字!!! "134190"!!!! what!?!?
嗯… 因為我上面有提到，ROWID的18個字元，是經過base-64編碼的… 必須再處理過，才會是134190這個data object number。

BASE64 to Decimal
其實sys下也有現成的package可用: DBMS_ROWID (This package provides procedures to create ROWIDs and to interpret their contents)，只要把rowid丟進DBMS_ROWID.ROWID_INFO內(外加其他幾個承接結果的變數)，就可以得到每一段轉換過後的數字。詳細用法可參考這兒。

可以得到現成的結果還不錯，不過還是想知道他事實上到底是怎麼換算的，在找了幾篇失敗的解說後，找到了這篇!! 透過他提供的SQL, 就可以了解其中過程。

Create Or Replace Package B64 Is
B64 Varchar2(64):='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
Function Base64_2Dec(Val Varchar2) Return Number;
Function Dec2_Base64(Val Number) Return Varchar2;
End B64;
/
Create Or Replace Package Body B64 Is
Function Base64_2Dec
(Val Varchar2)
Return Number Is
I Pls_Integer;
J Pls_Integer;
K Pls_Integer:=0;
N Pls_Integer;
V_Out Number(38):=0;
Begin
  N:=Length(B64);
  For I In Reverse 1..Length(Val) Loop
    J:=Instr(B64,Substr(Val,I,1))-1;
    If J <0 Then
      Raise_Application_Error(-20001,'Invalid Base 64 Number: '||Val);
    End If;
    V_Out:=V_Out+J*(N**K);
    K:=K+1;
  End Loop;
  Return V_Out;
End;

Function Dec2_Base64
(Val Number)
Return Varchar2 Is
V_In Number;
N Pls_Integer;
V_Out Varchar2(30):='';
Begin
  N:=Length(B64);
  V_In:=Trunc(Val);
  While (V_In>0) Loop
    V_Out:=Substr(B64,Mod(V_In,N)+1,1)||V_Out;
    V_In:=Trunc(V_In/N);
  End Loop;
  Return V_Out;
End;

End B64;
/

(有點懶得解釋= = 請自行閱讀，B64那串字就是base-64的64個字元，請看Ref #3)

所以當我下
SQL> select rowid ,
B64.Base64_2Dec(substr(rowid,1,6)) object_no ,
B64.Base64_2Dec(substr(rowid,7,3)) rel_file_id,
B64.Base64_2Dec(substr(rowid,10,6)) block_no,
B64.BASE64_2DEC(SUBSTR(ROWID,16,3)) ROW_NO
from hr.jobs where job_id='XXX';

可以得到
ROWID                          OBJECT_NO REL_FILE_ID   BLOCK_NO    ROW_NO
------------------                     ----------------     ----------------      --------------       -------------
AAAgwuAAKAAAl7hAAR       134190                10           155361               17

完畢!!

Ref:

1. 如何使用base64编码解构Oracle rowid信息
   
2. Oracle DBMS_ROWID
   
3. Base64

Length Semantics for Character Datatypes

How to determine the column length in different database character set and length semantics? For example, you need to define a VARCHAR2 column that can store up to 5 Chinese characters together with 5 English characters.

	Database Character Set
Length Semantics		Single byte	Multiple byte
	BYTE	10 BYTE	5*3+5*1=20 BYTE
	CHAR	10 CHAR	10 CHAR

SQL Developer Change UI Language to English

最近在處理資料庫搬家，越來越不喜歡看到工具的介面是中文的= =，中文翻譯反而看不懂在寫啥鬼，一直看英文文件.... 忽然看到中文的，還會懷疑他翻的是這個嘛?XDD

SQL Developer一裝起來就一副中文樣XD，用著也沒什麼不方便，可是每次有跳錯誤或警告，都看不太懂到底是哪裡出錯了= =a。稍微看了一下設定... 好像也沒有直接可以改的地方@_@

後來在Oracle Forums看到也有人問，所以就找到方法了：

1. 先在sql developer的安裝資料夾下找到
   (sqldeveloper install directory)\ide\bin\ide.conf
   
   
2. 加上這行:
   
   AddVMOption -Duser.language=en

完畢!!

Ref: [Oracle Forum] Thread: EA2 - SQL Developer default language