老爹寫程式

 
CREATE MATERIALIZED VIEW Material_View_Name
[TABLESPACE TBS_XXX]
REFRESH [ON DEMAND (預設)/ COMMIT] [COMPLETE / FORCE (預設) / FAST] [WITH Primary Key (預設)/ ROWID / ROWID / OBJECT ID]
[START WITH SYSDATE NEXT (SYSDATE+1)]
AS
SELECT SQL
--以上 SQL 的順序是有意義的, 必須注意.
Refresh Interval
DEMAND: 分為自動跟手動，手動就是當需要refrest mview時執行 dbms_mview.refresh; 自動就是在create mview 時指定排程時間，會增加一個job在 dba_jobs定時refresh
COMMIT: master table 資料有異動的時候，transaction commit時 refresh mview。 (即時同步)
Refresh Type
COMPLETE: 每次refresh 時重新執行 mview Query 且重新建置 mview (費時)(文件中提到如果是 complete，mview 的 pctfree 會是0，pctused 會是 99，十分合理，因為這mview 每次都會重新建置，不需要留空間update使用)
FAST: refresh mview 只更新 master 異動的資料 (較快但要建 mview log)
FORCE: refresh mview 時，會嘗試以 FAST 方式 refresh，如果無法完成則以 COMPLETE 方式 refresh
Replication situations
# Primary Key Materialized Views
# Object Materialized Views
# ROWID Materialized Views
# Complex Materialized Views
手動更新
BEGIN
DBMS_MVIEW.REFRESH( 'Material_View_Name' );
END;
mview 心得: 建立 mview 的時候,oracle會建立一個table,而這個table的ddl就是取自建立時的ddl
所以考慮以下問題:
1. 比較建立mv時 "指定欄位" 和 "欄位用*", 當src增加欄位會有什麼問題?
=> 做mv refresh時指定欄位不會有問題, 不過oracle也不可能幫你為mv增加欄位, 因為對應於mv的table ddl在mv建立時就建好了;
欄位用*的做mv refresh會發生error, 因為它會撈到src新增的欄位, 但因為對應於mv的table ddl在mv建立時就建好了, 所以該table沒有此新增的欄位, 因此error
2. src欄位名稱改變或長度改變
=> 因為對應於mv的table ddl在mv建立時就建好了, 因此做mv refresh都會有問題, 必須一起修改對應於mv的table ddl
P.S. 做mv refresh當發生error的時候, 若refresh complete的mv資料會全部被清掉; 而refresh fast的mv資料仍會保留在上一次的狀態
CREATE MATERIALIZED VIEW

 
ORA-01950: no privileges on tablespace XXX
今天在建立一個 mview 時發生以上的 ora 錯誤, 查一下原因解法如下說明.
Cause: User does not have privileges to allocate an extent in the specified tablespace.
Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.
On the ITtoolbox Forum, a confrontation concerning ORA-01950 has been posted.   A replier offers information, by asking the user to solve ORA-01950 by either:
* ALTER USER <username> QUOTA 100M ON <tablespace name>
 
* GRANT UNLIMITED TABLESPACE TO <username>
And to also make sure the user has been granted Connect, Resources roles incase the user was not given Create table privileges.

 
Query Locked Objects from v$locked_object
 
SELECT SUBSTR(b.owner, 1, 8) owner, b.object_type, SUBSTR(b.object_name, 1, 18) object_name
 , DECODE(a.locked_mode
         , 0, 'None'
         , 1, 'Null'
         , 2, 'Row-S'
         , 3, 'Row-X'
         , 4, 'Share'
         , 5, 'S/Row-X'
         , 6, 'Exclusive')   locked_mode
 , a.session_id,a.oracle_username, a.os_user_name, TO_CHAR(c.logon_time,'YYYY/MM/DD HH24:MI:SS') logon_time, c.PROGRAM,c.saddr
FROM
  v$locked_object a
 , dba_objects b
 , v$session c
WHERE
  a.object_id  = b.object_id  AND a.session_id = c.sid
ORDER BY  b.object_name, b.owner, b.object_type

 
Unix DB Instance Startup / Stop
1. Login OS with oracle account
2. Using sqlplus to connect to db as sysdba : sqlplus "/ as sysdba"
3. Startup the instance : startup
4. Check alert file to know if there is error during the startup
5. Shutdown the instance using "immediate" option : shutdown immediate
Listener Startup / Stop
1. Login OS with oracle account
2. Startup listener : lsnrctl start [listenerA]
3. Check listener status : lsnrctl status [listenerA]
4. Check listener.log to know if there is error during the startup
5. Stop listener : lsnrctl stop [listenerA]
[NOTE]
除了以上的正常步驟, 當 DB 下不下來時, 就必須 kill oracle process以加快速度, 所以可以使用下 unix 指定列出 ora 的 process.
ps -ef | grep ora
或是可以使用以下 SQL 自系統的 v$session, v$process 取得指定要刪除的帳號.
select  s.sid || ',' || s.serial# "SID/SERIAL",  s.username,  s.osuser,  p.spid "OS PID", s.program,   'kill -9 '||p.spid as kill
from    v$session s
,    v$process p
Where    s.paddr = p.addr
and s.username in ('ERP',’BPM’)

 
[ID 744125.1]  Connections Fail with ORA-12640 or ORA-21561 due to Windows Desktop heap sizing issue.
最近 DB 常常出現 ORA-12640, 查了 alert log 沒有異常，查了一些文章說要改 sqlnet.ora
AUTHENTICATION_SERVICES= (NONE), 原本是設定 NTS , DB 也正常的運作好幾年了, 該不會是一個 Bug 吧? 查了 Metalink [ID 750457.1] 說明如下 :
Cause
This problem is due to unpublished Bug 5114330 AUTHENTICATION ADAPTER INITIALIZATION FAILED IS OS-USER IS NOT ADMIN
Solution
The unpublished Bug 5114330 is fixed in 10.1.0.6, 10.2.0.3. 9.2.0.8 database versions
所以就照著改好了, 改完沒多久變成出現 ORA-21561. 查詢 Metalink [ID 744125.1]如下, 在照改就 OK 了.
Connections Fail with ORA-12640 or ORA-21561 [ID 744125.1]
Modified 12-MAY-2010     Type PROBLEM     Status MODERATED
In this Document
 Symptoms
 Cause
 Solution

---

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:
Oracle Net Services - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Generic Windows
Microsoft Windows
Symptoms
Connections to database fail, reporting the error code ORA-12640 Authentication adapter initialization failed. SQLNET.AUTHENTICATION_SERVICES is set to NTS in the SQLNET.ORA file. Changing SQLNET.AUTHENTICATION_SERVICES=NONE and connections the fail with the error code ORA-21561 OID generation failed.
Seen for connections using third party applications and oracle tools such as recovery manager (RMAN). Connections have also been reported to be restricted to 64.
A typical Oracle Net level 16 client trace of RMAN failure shows following:
Cause
Windows Desktop heap sizing issue.
Solution
Desktop heap size is required to be increased in the registry for the database server. It can be found in the following location.
\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems\
At this location the following set of values can be seen 1024,3072,512.The third value (512) is the size of the desktop heap for each desktop that is associated with a "noninteractive" window station.
Increase of the third value to 1024, so the values are now listed as 1024, 3072, 1024 resolved the problem.
If this value is not present, the size of the desktop heap for noninteractive window stations will be same as the size specified for interactive window stations (the second SharedSection value).
Changing registry values is critical and and backup of the registry should be taken prior to amending these values.Oracle also recommends any such changes should be checked with Windows System administrator before attempting such a change.
 

 
Bug 4433936 Queries with FULL / LEFT OUTER join are involved with remote tables and these fail can fail with ORA-907 or ORA-933
select * from
(
select * from a@dblink a
full/left/right outer join
select * from b@dblink b
on a.col1 = b.col1
)
Error: ORA-00933: SQL command not properly ended ORA-02063: preceding line from <dblink>
單獨執行紅色的sql不會有問題, 但變成 sub-sql 時問題就出現了.
Solution
Rewrite the related queries using Oracle join operators.
倘若有這樣的需求,須改寫成Oracle提供的join寫法,
如假設上例為 right outer join,改寫成
select * from
(
select * from a@dblink a, b@dblink b
where a.col1 = b.col1(+)
)

 
C# function 執行失敗時, 重複執行.
一般當我寫一個 function, 當這個 function 執行失敗, 我們希望可以再執行幾次, 看是否可以成功, 這是蠻常見的情況, 舉一個 ftp  List() function程式的例子就有這樣的需求, 因為 List() 其實是下一個 LIST 的 command, 如下 :
 


public ArrayList List()
{
 Connect();
 OpenDataSocket();
 try
 {
     SendCommand("LIST");
 }
 catch (Exception ex)
 {
     log.Warn(MethodBase.GetCurrentMethod() + ex.Message);
     throw ex;
 }
 ReadResponse();
 return list;
}

在 oracle 的 analyse  說明中清楚的說明, 於 8i 後建議不要用 analyse command 了, 而建議使用 dbms_stat. 以下是摘錄部份的說明.
Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The cost-based optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS. See PL/SQL Packages and Types Reference for more information on the DBMS_STATS package.

一、 概念：
為強化資料庫大表格的管理，ORACLE推出了partition talbe。partition將表分離在若干不同的表空間上，用限制每個partition的空間，不只達到空間管理也增進了效能。更方便維護、備份、恢復、事務及查詢性能。
二、優點：
1 、增強可用性：如果表的一個 partition 由於系統故障而不能使用，表的其餘好的 partition 仍然可以使用, 減少系統故障只影響表的一部分 partition 
2 、維護輕鬆：如果需要重建表，獨立管理每個 partition 比管理單個 table 要輕鬆得多
3 、均衡I/O:可以把 table 的不同 partition 分配到不同的磁片來平衡I/O改善性能
4 、改善性能：對大 table 的查詢、增加、修改等操作可以分解到各 partition 來平行執行，可使運行速度更快
5 、 partition 對用戶透明，最終用戶感覺不到 partition 的存在。
三、Partition Management：
1 、Create partition：
以老爺公司為例, 有個 table 每個月要增加的資料約 200G, 沒有誇張, 而且者已經是有做過分區儲存在不同的 Oracle Instance 了, 如果整個公司只有一台 Oracle 那更是倍增.
STEP1、Create tablestapce and assign data file location：
--假設我以一個時間欄位當作切割, 且每個月用 2 個 tablespace/partition, 每個 tablespace 內含 2 個 datafile 儲存資料.(實際更多, 只為了範例說明方便)
CREATE TABLESPACE TBS_PHONE_LOG_DAT_2010_01_1
    LOGGING DATAFILE 
    '/data_1/tbs_phone_log_dat_2010_01_1_1.dbf' SIZE  200M REUSE AUTOEXTEND ON NEXT  200M MAXSIZE  30000M,
    '/data_2/tbs_phone_log_dat_2010_01_1_2.dbf' SIZE  200M REUSE AUTOEXTEND ON NEXT  200M MAXSIZE  30000M,
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2048K SEGMENT SPACE MANAGEMENT  AUTO ;
CREATE TABLESPACE TBS_PHONE_LOG_DAT_2010_01_2
    LOGGING DATAFILE 
    '/data_1/tbs_phone_log_dat_2010_01_2_1.dbf' SIZE  200M REUSE AUTOEXTEND ON NEXT  200M MAXSIZE  30000M,
    '/data_2/tbs_phone_log_dat_2010_01_2_2.dbf' SIZE  200M REUSE AUTOEXTEND ON NEXT  200M MAXSIZE  30000M,
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2048K SEGMENT SPACE MANAGEMENT  AUTO ;
STEP2、建立基於分區的表：
CREATE TABLE phone_log
(no VARCHAR2(20) NOT NULLl,
communicate_date DATE NOT NULL 
)
PARTITION BY RANGE (communicate_date)
(
PARTITION phone_log_2010_01_1 VALUES LESS THAN (TO_DATE(' 2010-01-16 ','YYYY-MM-DD')) TABLESPACE TBS_PHONE_LOG_DAT_2010_01_1,
PARTITION phone_log_2010_01_2 VALUES LESS THAN (TO_DATE(' 2010-02-01 ','YYYY-MM-DD')) TABLESPACE TBS_PHONE_LOG_DAT_2010_01_2
);
對於有 partition 的 table 可以指定要查詢的 partition
select * from phone_log
select * from phone_log partition (phone_log_2010_01_1);
2 、Partition Extension ：
STEP1、Create tablespace：
--假設原本只有建立 2010/01 的 partition, 現在要增加 2010/02 的 partition.
CREATE TABLESPACE TBS_PHONE_LOG_DAT_2010_02_1
    LOGGING DATAFILE 
    '/data_1/tbs_phone_log_dat_2010_02_1_1.dbf' SIZE  200M REUSE AUTOEXTEND ON NEXT  200M MAXSIZE  30000M,
    '/data_2/tbs_phone_log_dat_2010_02_1_2.dbf' SIZE  200M REUSE AUTOEXTEND ON NEXT  200M MAXSIZE  30000M,
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2048K SEGMENT SPACE MANAGEMENT  AUTO ;
CREATE TABLESPACE TBS_PHONE_LOG_DAT_2010_02_2
    LOGGING DATAFILE 
    '/data_1/tbs_phone_log_dat_2010_02_2_1.dbf' SIZE  200M REUSE AUTOEXTEND ON NEXT  200M MAXSIZE  30000M,
    '/data_2/tbs_phone_log_dat_2010_02_2_2.dbf' SIZE  200M REUSE AUTOEXTEND ON NEXT  200M MAXSIZE  30000M,
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2048K SEGMENT SPACE MANAGEMENT  AUTO ;
STEP2、Add partition to table：
ALTER TABLE phone_log
ADD PARTITION phone_log_2010_02_1 VALUES LESS THAN (TO_DATE(' 2010-02-16 ','YYYY-MM-DD')) TABLESPACE TBS_PHONE_LOG_DAT_2010_02_1;
ADD PARTITION phone_log_2010_02_2 VALUES LESS THAN (TO_DATE(' 2010-02-16 ','YYYY-MM-DD')) TABLESPACE TBS_PHONE_LOG_DAT_2010_02_2;
3 、Drop partition：
--對於不要的資料或超出保存期限的資料, 可用刪除 partition 刪除的方式處理.
STEP1、Drop partition：
ALTER TABLE phone_log DROP PARTITION phone_log_2010_01_1;
SETP2、Drop tablespace
drop tablespace TBS_PHONE_LOG_DAT_2010_01_1 including contents and datafiles;
--刪除 tablespace 時可以同時刪除 datafile, 但有些 oracle 版本有 bug, 會無法刪除 datafile.
STEP3、rm data file
--UNIX系統為例：
rm /data_1/tbs_phone_log_dat_2010_01_1_1.dbf
rm /data_2/tbs_phone_log_dat_2010_01_1_2.dbf

Update data in one table with data from another table.
假設 History table 只有 Product_id, 現在新增 Product_Name column, 但資料再另一個 table 有, 要怎樣更新.
以往使用 MS SQL 做這件事非常簡單, 語法也很易懂.




update history 
set product_name=product.product_name
from history, product
where history.product_id=product.product_id