# 主題
CSSCAN (Character Set Scanner)
# 適用版本
Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Oracle Database - Standard Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.
# 現象/目的
在從前建置Oracle DB時，我們會將字元集設定成BIG5，但是網路促成的無國界情境使得種種軟體包含資料庫本身都必須容納多國語系，所以公司內部的資料庫大多早早已轉換成UTF8，但是受限於商業軟體，或者是其他考量，少部分資料庫還是停留在BIG5，倘若有朝一日當資料庫需要做轉換時，如果來源端跟目的端的資料庫字元集不一致時，必須先使用csscan 工具來確認對資料的影響性。
# 解決方式/內容
1. 準備動作
1.1 確認目前字元集:
SQL> select value
from NLS_DATABASE_PARAMETERS
where parameter='NLS_CHARACTERSET';
NLS_CHARACTERSET 定義存放在資料庫的是何種字元，而非由 NLS_LANGUAGE 或是 NLS_TERRITORY 決定的
1.2 DB Version小於10.2.0.4或是11.1.0.6，impdp會有資料毀損狀況，expdp不受影響，apply patch 5874989可解決此bug。這問題在10.2.0.4 and 11.1.0.7 patch set，或是11.2.0.1 以上版本被解決。
1.3 清除recyclebin
$ sqlplus / as sysdba
SQL> SELECT OWNER, ORIGINAL_NAME, OBJECT_NAME, TYPE
FROM dba_recyclebin ORDER BY 1,2;
SQL> purge dba_recyclebin;
1.4 compile invalid objects
$ sqlplus / as sysdba
SQL> SELECT owner,object_name,object_type,status
FROM dba_objects
WHERE status ='INVALID';
SQL> @?/rdbms/admin/utlrp.sql
1.5 可以移除sample schema：'HR', 'OE', 'SH', 'PM', 'IX', 'BI' and 'SCOTT'，如果沒有用到APEX / HTML DB也可移除FLOWS_XXX 以及APEX_XXX Users。
2. 參考 Note 745809.1 安裝必要物件 (10g / 11g)
$ export ORACLE_SID=ORCL
$ sqlplus /nolog
SQL> conn / as sysdba
SQL> set termout on
SQL> set echo on
SQL> spool csminst.log
SQL> -- note the drop user
SQL> drop user csmig cascade;
SQL> @?/rdbms/admin/csminst.sql
3. 執行 csscan
$ csscan \"system/password as sysdba\" USER=schema TOCHAR=UTF8 ARRAY=1024000 PROCESS=3 LOG=csscan_log
4. 檢查產出的報表並修正
4.1 csscan_log.out ==> 執行csscan的過程
4.2 csscan_log.txt ==> tables / column 有無異常的總表
4.3 csscan_log.err ==> tables / column 有異常的資料明細
4.4 通常BIG5轉UTF8欄位大小是除以2乘以3：column_size / 2 * 3 (因為一個中文字在BIG5中佔2個bytes，在UTF8中佔3個bytes)
# 參考文件
Note 225912.1
Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET )
Note 458122.1
Installing and Configuring Csscan in 8i and 9i (Database Character Set Scanner)
Note 745809.1
Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner)
Note 444701.1
Csscan output explained
(Doc ID 260192.1)
Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g
2014年11月4日 星期二
2014年10月30日 星期四
執行Procedure 對Remote Database 做DML 動作時發生ORA-2069 錯誤訊息
Ora-02069 When Using a Local Function While Updating a Remote Table (Doc ID 342320.1) 這篇文章有提到：無法使用 Local Function 針對 Remote Database做 DML ( insert / update / delete) 動作，否則就會發生 ORA-2069 錯誤訊息。
暫時解決方式如下：
1. 將 session level 的 global_name 設成 true：alter session set global_names=true
2. 將 function 建在 Remote Database 上面
3. PL/SQL Code 中，盡可能的不使用function
4. 先將要處理的資料放在Local Table / Local Temp Table 中，當對 Remove Database 做DML 時，再拿出來引用
PS：
Doc ID 342320.1：
Information in this document applies to any platform.
where pkg_date.Fn_GetDate is a function of a local package that returns a sysdate,
and t1 is a synonym that points to a remote table, defined through a database link.
returns
暫時解決方式如下：
1. 將 session level 的 global_name 設成 true：alter session set global_names=true
2. 將 function 建在 Remote Database 上面
3. PL/SQL Code 中，盡可能的不使用function
4. 先將要處理的資料放在Local Table / Local Temp Table 中，當對 Remove Database 做DML 時，再拿出來引用
PS：
Doc ID 342320.1：
APPLIES TO:
Oracle Server - Enterprise Edition - Version 9.2.0.1 to 11.2.0.2 [Release 9.2 to 11.2]Information in this document applies to any platform.
SYMPTOMS
In Oracle Server executing the following update:
update t1
set c1=c1
where c2=pkg_date.Fn_GetDate;
where pkg_date.Fn_GetDate is a function of a local package that returns a sysdate,
and t1 is a synonym that points to a remote table, defined through a database link.
returns
"ORA-02069: global_names parameter must be set to TRUE for this operation"
on the function pkg_date.Fn_GetDate on the where condition.
CHANGES
CAUSE
Because of a limitation, it is not possible to use a local function when doing a dml operation on a remote table .When this is attempted, the ora-2069 is raised.
SOLUTION
These are possible workaround to avoid the ora-2069 error in the described scenario
1. Use global_names=true. This can be done on a session basis: "alter
session set global_names=true". If you are having problems getting this
to work, it is no doubt a configuration problem. Please open a separate
TAR for this if you can't figure it out.
session set global_names=true". If you are having problems getting this
to work, it is no doubt a configuration problem. Please open a separate
TAR for this if you can't figure it out.
2. Put the function to be used at the remote site.
3. Put a wrapper function at the remote site which calls the actual function
over a database link back to the local site.
over a database link back to the local site.
4. Include in the "from" the "dual" table. You'll have a Cartesian product (with dual) and the functions will be applied in the calling side, hence some performance issues can be raised.
REFERENCES
BUG:671775 - GET A ORA-2069 OR ORA-2019 WHEN TRYING TO USE A FUNCTION WITH A DB_LINK
2014年9月16日 星期二
定期刪除table過期資料
1. 建立執行清除資料的 Table 候選清單
create table "zdba_del_tab"
2. 建立 Procedure 只處理 delete 動作
create procedure "zdba_delete_commit"
3. 建立 Procedure 呼叫 "zdba_delete_commit" 處理 Table清單 "zdba_del_tab" 中的 Table
create procedure "zdba_del_tab_p""zdba_delete_commit" 做刪除動作
4. 建立 Job 定期自動執行
1.
CREATE TABLE CMO.ZDBA_DEL_TAB
(
OWNER VARCHAR2(30 BYTE),
SEGMENT_NAME VARCHAR2(81 BYTE),
KEYCOL VARCHAR2(30 BYTE),
CONDITION VARCHAR2(10 BYTE),
KEYCOLVAL VARCHAR2(100 BYTE),
ENABLED VARCHAR2(1 BYTE),
STIME DATE,
ETIME DATE,
CONDITION2 VARCHAR2(500 BYTE),
SHRINK VARCHAR2(1 BYTE),
STATUS VARCHAR2(20 BYTE)
)
TABLESPACE <tablespace_name>;
COMMENT ON TABLE ZDBA_DEL_TAB IS
'Purpose: Table List for Delete Expired Table Data
Created by: DBA
Keep days: Always
Purge key: NA
Desc:
enabled=A :簡單型, 每日1:00執 delete by crontab
enabled=B :簡單型, 每日5:00執 delete by DB job
enabled=C :複雜型, 每日1:00執 delete by crontab in
enabled=X :不執行 delete';
COMMENT ON COLUMN ZDBA_DEL_TAB.SHRINK IS '是否做shrink動作';
CREATE UNIQUE INDEX ZDBA_DEL_TAB_U01 ON ZDBA_DEL_TAB
(OWNER, SEGMENT_NAME)
LOGGING
TABLESPACE <tablespace_name>;
ALTER TABLE ZDBA_DEL_TAB ADD (
CONSTRAINT ZDBA_DEL_TAB_U01
UNIQUE (OWNER, SEGMENT_NAME)
USING INDEX
TABLESPACE <tablespace_name>);
2.
CREATE OR REPLACE PROCEDURE zdba_delete_commit (
p_statement IN VARCHAR2,
p_commit_batch_size IN NUMBER DEFAULT 10000
)
IS
/* ----------------------------------
Purpose: Delete Table in Batch Mode
Created by: DBA
Date:
structure:
1. define sql statement
2. open cursor
3. execute cursor
4. close cursor
updated:
---------------------------------- */
cid INTEGER;
changed_statement VARCHAR2 (2000);
finished BOOLEAN;
nofrows INTEGER;
lrowid ROWID;
rowcnt INTEGER;
errpsn INTEGER;
sqlfcd INTEGER;
errc INTEGER;
errm VARCHAR2 (2000);
BEGIN
-- If the actual statement contains a WHERE clause, then append a
-- rownum < n clause after that using AND, else use WHERE rownum < n clause
IF (UPPER (p_statement) LIKE '% WHERE %')
THEN
changed_statement :=
p_statement || ' AND rownum < ' || TO_CHAR (p_commit_batch_size + 1);
ELSE
changed_statement :=
p_statement || ' WHERE rownum < '
|| TO_CHAR (p_commit_batch_size + 1);
END IF;
BEGIN
cid := DBMS_SQL.open_cursor; -- Open a cursor for the task
DBMS_SQL.parse (cid, changed_statement, DBMS_SQL.native);
-- parse the cursor.
rowcnt := DBMS_SQL.last_row_count;
-- store for some future reporting
EXCEPTION
WHEN OTHERS
THEN
errpsn := DBMS_SQL.last_error_position;
-- gives the error position in the changed sql
-- delete statement if anything happens
sqlfcd := DBMS_SQL.last_sql_function_code;
-- function code can be found in the OCI
-- manual
lrowid := DBMS_SQL.last_row_id;
-- store all these values for error reporting. However
-- all these are really useful in a stand-alone proc
-- execution for DBMS_OUTPUT to be successful, not
-- possible when called from a form or front-end tool.
errc := SQLCODE;
errm := SQLERRM;
DBMS_OUTPUT.put_line ( 'Error:'
|| TO_CHAR (errc)
|| ' Posn:'
|| TO_CHAR (errpsn)
|| 'SQL fCode '
|| TO_CHAR (sqlfcd)
|| ' rowid'
|| ROWIDTOCHAR (lrowid)
);
raise_application_error (-20000, errm);
-- it'll ensure the display of at least the error
-- message if something happens.
END;
finished := FALSE;
WHILE NOT (finished)
LOOP -- keep on executing the cursor till there is no more to process.
BEGIN
nofrows := DBMS_SQL.EXECUTE (cid);
rowcnt := DBMS_SQL.last_row_count;
EXCEPTION
WHEN OTHERS
THEN
errpsn := DBMS_SQL.last_error_position;
sqlfcd := DBMS_SQL.last_sql_function_code;
lrowid := DBMS_SQL.last_row_id;
errc := SQLCODE;
errm := SQLERRM;
DBMS_OUTPUT.put_line ( 'Error:'
|| TO_CHAR (errc)
|| 'Posn:'
|| TO_CHAR (errpsn)
|| 'SQL fCode '
|| TO_CHAR (sqlfcd)
|| ' rowid'
|| ROWIDTOCHAR (lrowid)
);
raise_application_error (-20000, errm);
END;
IF nofrows = 0
THEN
finished := TRUE;
ELSE
finished := FALSE;
END IF;
COMMIT;
END LOOP;
BEGIN
DBMS_SQL.close_cursor (cid); -- close the cursor for a clean finish
EXCEPTION
WHEN OTHERS
THEN
errpsn := DBMS_SQL.last_error_position;
sqlfcd := DBMS_SQL.last_sql_function_code;
lrowid := DBMS_SQL.last_row_id;
errc := SQLCODE;
errm := SQLERRM;
DBMS_OUTPUT.put_line ( 'Error:'
|| TO_CHAR (errc)
|| ' Posn:'
|| TO_CHAR (errpsn)
|| 'SQL fCode '
|| TO_CHAR (sqlfcd)
|| ' rowid'
|| ROWIDTOCHAR (lrowid)
);
raise_application_error (-20000, errm);
END;
END;
/
3.
CREATE OR REPLACE PROCEDURE zdba_del_tab_p (p_enabled IN VARCHAR2)
IS
/*
Purpose: Delete Expired Data
Created by: DBA
Desc: 排crontab 每日 1:00執行
Update:
*/
CURSOR c1
IS
SELECT owner, segment_name, keycol, condition, keycolval, condition2
FROM zdba_del_tab
WHERE enabled = p_enabled;
r1 c1%ROWTYPE;
str VARCHAR2 (500);
v_sqlcode VARCHAR2 (200);
v_sqlerrm VARCHAR2 (200);
v_stime DATE;
v_etime DATE;
v_difftime NUMBER;
v_sender VARCHAR2 (200) := 'XX System';
v_maillist VARCHAR2 (200)
:= 'dba@domain,dba_backup@domain';
BEGIN
OPEN c1;
IF p_enabled = 'A' or p_enabled = 'B'
THEN
SELECT SYSDATE
INTO v_stime
FROM DUAL;
LOOP
FETCH c1
INTO r1;
EXIT WHEN c1%NOTFOUND;
BEGIN
UPDATE zdba_del_tab
SET stime = SYSDATE
WHERE owner = r1.owner AND segment_name = r1.segment_name;
COMMIT;
str :=
'delete from '
|| r1.owner
|| '.'
|| r1.segment_name
|| ' where '
|| r1.keycol
|| ' '
|| r1.condition
|| ' '
|| r1.keycolval;
zdba_delete_commit (str);
UPDATE zdba_del_tab
SET etime = SYSDATE
WHERE owner = r1.owner AND segment_name = r1.segment_name;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001,
'A loop error was encountered '
|| SQLCODE
|| ' -ERROR- '
|| SQLERRM
);
END;
END LOOP;
SELECT SYSDATE
INTO v_etime
FROM DUAL;
v_difftime := trunc((v_etime - v_stime) * 24 * 60,2);
ELSIF p_enabled = 'C'
THEN
SELECT SYSDATE
INTO v_stime
FROM DUAL;
LOOP
FETCH c1
INTO r1;
EXIT WHEN c1%NOTFOUND;
BEGIN
UPDATE zdba_del_tab
SET stime = SYSDATE
WHERE owner = r1.owner AND segment_name = r1.segment_name;
COMMIT;
str :=
'delete from '
|| r1.owner
|| '.'
|| r1.segment_name
|| ' where '
|| r1.condition2;
zdba_delete_commit (str);
UPDATE zdba_del_tab
SET etime = SYSDATE
WHERE owner = r1.owner AND segment_name = r1.segment_name;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001,
'A loop error was encountered '
|| SQLCODE
|| ' -ERROR- '
|| SQLERRM
);
END;
END LOOP;
SELECT SYSDATE
INTO v_etime
FROM DUAL;
v_difftime := trunc((v_etime - v_stime) * 24 * 60,2);
ELSE
NULL;
END IF;
CLOSE c1;
UTL_MAIL.send (sender => v_sender,
recipients => v_maillist,
cc => NULL,
subject => 'OK...!! Delete Expired Data Completely. Type='||p_enabled||' (apps.zdba_del_tab_p)',
MESSAGE => 'Total consume '
|| v_difftime
|| ' min, from '
|| TO_CHAR (v_stime,
'yyyy-mm-dd hh24:mi:ss'
)
|| ' to '
|| TO_CHAR (v_etime,
'yyyy-mm-dd hh24:mi:ss'
),
mime_type => 'text/plain; charset=UTF-8'
);
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001,
'A main error was encountered '
|| SQLCODE
|| ' -ERROR- '
|| SQLERRM
);
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
UTL_MAIL.send (sender => v_sender,
recipients => v_maillist,
cc => NULL,
subject => 'ERROR...!! Delete Expired Data Failed. Type='||p_enabled||' (apps.zdba_del_tab_p)',
MESSAGE => 'SQLCODE: '
|| v_sqlcode
|| ' ,SQLERRM: '
|| v_sqlerrm,
mime_type => 'text/plain; charset=UTF-8'
);
END;
/
create table "zdba_del_tab"
2. 建立 Procedure 只處理 delete 動作
create procedure "zdba_delete_commit"
3. 建立 Procedure 呼叫 "zdba_delete_commit" 處理 Table清單 "zdba_del_tab" 中的 Table
create procedure "zdba_del_tab_p""zdba_delete_commit" 做刪除動作
4. 建立 Job 定期自動執行
1.
CREATE TABLE CMO.ZDBA_DEL_TAB
(
OWNER VARCHAR2(30 BYTE),
SEGMENT_NAME VARCHAR2(81 BYTE),
KEYCOL VARCHAR2(30 BYTE),
CONDITION VARCHAR2(10 BYTE),
KEYCOLVAL VARCHAR2(100 BYTE),
ENABLED VARCHAR2(1 BYTE),
STIME DATE,
ETIME DATE,
CONDITION2 VARCHAR2(500 BYTE),
SHRINK VARCHAR2(1 BYTE),
STATUS VARCHAR2(20 BYTE)
)
TABLESPACE <tablespace_name>;
COMMENT ON TABLE ZDBA_DEL_TAB IS
'Purpose: Table List for Delete Expired Table Data
Created by: DBA
Keep days: Always
Purge key: NA
Desc:
enabled=A :簡單型, 每日1:00執 delete by crontab
enabled=B :簡單型, 每日5:00執 delete by DB job
enabled=C :複雜型, 每日1:00執 delete by crontab in
enabled=X :不執行 delete';
COMMENT ON COLUMN ZDBA_DEL_TAB.SHRINK IS '是否做shrink動作';
CREATE UNIQUE INDEX ZDBA_DEL_TAB_U01 ON ZDBA_DEL_TAB
(OWNER, SEGMENT_NAME)
LOGGING
TABLESPACE <tablespace_name>;
ALTER TABLE ZDBA_DEL_TAB ADD (
CONSTRAINT ZDBA_DEL_TAB_U01
UNIQUE (OWNER, SEGMENT_NAME)
USING INDEX
TABLESPACE <tablespace_name>);
2.
CREATE OR REPLACE PROCEDURE zdba_delete_commit (
p_statement IN VARCHAR2,
p_commit_batch_size IN NUMBER DEFAULT 10000
)
IS
/* ----------------------------------
Purpose: Delete Table in Batch Mode
Created by: DBA
Date:
structure:
1. define sql statement
2. open cursor
3. execute cursor
4. close cursor
updated:
---------------------------------- */
cid INTEGER;
changed_statement VARCHAR2 (2000);
finished BOOLEAN;
nofrows INTEGER;
lrowid ROWID;
rowcnt INTEGER;
errpsn INTEGER;
sqlfcd INTEGER;
errc INTEGER;
errm VARCHAR2 (2000);
BEGIN
-- If the actual statement contains a WHERE clause, then append a
-- rownum < n clause after that using AND, else use WHERE rownum < n clause
IF (UPPER (p_statement) LIKE '% WHERE %')
THEN
changed_statement :=
p_statement || ' AND rownum < ' || TO_CHAR (p_commit_batch_size + 1);
ELSE
changed_statement :=
p_statement || ' WHERE rownum < '
|| TO_CHAR (p_commit_batch_size + 1);
END IF;
BEGIN
cid := DBMS_SQL.open_cursor; -- Open a cursor for the task
DBMS_SQL.parse (cid, changed_statement, DBMS_SQL.native);
-- parse the cursor.
rowcnt := DBMS_SQL.last_row_count;
-- store for some future reporting
EXCEPTION
WHEN OTHERS
THEN
errpsn := DBMS_SQL.last_error_position;
-- gives the error position in the changed sql
-- delete statement if anything happens
sqlfcd := DBMS_SQL.last_sql_function_code;
-- function code can be found in the OCI
-- manual
lrowid := DBMS_SQL.last_row_id;
-- store all these values for error reporting. However
-- all these are really useful in a stand-alone proc
-- execution for DBMS_OUTPUT to be successful, not
-- possible when called from a form or front-end tool.
errc := SQLCODE;
errm := SQLERRM;
DBMS_OUTPUT.put_line ( 'Error:'
|| TO_CHAR (errc)
|| ' Posn:'
|| TO_CHAR (errpsn)
|| 'SQL fCode '
|| TO_CHAR (sqlfcd)
|| ' rowid'
|| ROWIDTOCHAR (lrowid)
);
raise_application_error (-20000, errm);
-- it'll ensure the display of at least the error
-- message if something happens.
END;
finished := FALSE;
WHILE NOT (finished)
LOOP -- keep on executing the cursor till there is no more to process.
BEGIN
nofrows := DBMS_SQL.EXECUTE (cid);
rowcnt := DBMS_SQL.last_row_count;
EXCEPTION
WHEN OTHERS
THEN
errpsn := DBMS_SQL.last_error_position;
sqlfcd := DBMS_SQL.last_sql_function_code;
lrowid := DBMS_SQL.last_row_id;
errc := SQLCODE;
errm := SQLERRM;
DBMS_OUTPUT.put_line ( 'Error:'
|| TO_CHAR (errc)
|| 'Posn:'
|| TO_CHAR (errpsn)
|| 'SQL fCode '
|| TO_CHAR (sqlfcd)
|| ' rowid'
|| ROWIDTOCHAR (lrowid)
);
raise_application_error (-20000, errm);
END;
IF nofrows = 0
THEN
finished := TRUE;
ELSE
finished := FALSE;
END IF;
COMMIT;
END LOOP;
BEGIN
DBMS_SQL.close_cursor (cid); -- close the cursor for a clean finish
EXCEPTION
WHEN OTHERS
THEN
errpsn := DBMS_SQL.last_error_position;
sqlfcd := DBMS_SQL.last_sql_function_code;
lrowid := DBMS_SQL.last_row_id;
errc := SQLCODE;
errm := SQLERRM;
DBMS_OUTPUT.put_line ( 'Error:'
|| TO_CHAR (errc)
|| ' Posn:'
|| TO_CHAR (errpsn)
|| 'SQL fCode '
|| TO_CHAR (sqlfcd)
|| ' rowid'
|| ROWIDTOCHAR (lrowid)
);
raise_application_error (-20000, errm);
END;
END;
/
3.
CREATE OR REPLACE PROCEDURE zdba_del_tab_p (p_enabled IN VARCHAR2)
IS
/*
Purpose: Delete Expired Data
Created by: DBA
Desc: 排crontab 每日 1:00執行
Update:
*/
CURSOR c1
IS
SELECT owner, segment_name, keycol, condition, keycolval, condition2
FROM zdba_del_tab
WHERE enabled = p_enabled;
r1 c1%ROWTYPE;
str VARCHAR2 (500);
v_sqlcode VARCHAR2 (200);
v_sqlerrm VARCHAR2 (200);
v_stime DATE;
v_etime DATE;
v_difftime NUMBER;
v_sender VARCHAR2 (200) := 'XX System';
v_maillist VARCHAR2 (200)
:= 'dba@domain,dba_backup@domain';
BEGIN
OPEN c1;
IF p_enabled = 'A' or p_enabled = 'B'
THEN
SELECT SYSDATE
INTO v_stime
FROM DUAL;
LOOP
FETCH c1
INTO r1;
EXIT WHEN c1%NOTFOUND;
BEGIN
UPDATE zdba_del_tab
SET stime = SYSDATE
WHERE owner = r1.owner AND segment_name = r1.segment_name;
COMMIT;
str :=
'delete from '
|| r1.owner
|| '.'
|| r1.segment_name
|| ' where '
|| r1.keycol
|| ' '
|| r1.condition
|| ' '
|| r1.keycolval;
zdba_delete_commit (str);
UPDATE zdba_del_tab
SET etime = SYSDATE
WHERE owner = r1.owner AND segment_name = r1.segment_name;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001,
'A loop error was encountered '
|| SQLCODE
|| ' -ERROR- '
|| SQLERRM
);
END;
END LOOP;
SELECT SYSDATE
INTO v_etime
FROM DUAL;
v_difftime := trunc((v_etime - v_stime) * 24 * 60,2);
ELSIF p_enabled = 'C'
THEN
SELECT SYSDATE
INTO v_stime
FROM DUAL;
LOOP
FETCH c1
INTO r1;
EXIT WHEN c1%NOTFOUND;
BEGIN
UPDATE zdba_del_tab
SET stime = SYSDATE
WHERE owner = r1.owner AND segment_name = r1.segment_name;
COMMIT;
str :=
'delete from '
|| r1.owner
|| '.'
|| r1.segment_name
|| ' where '
|| r1.condition2;
zdba_delete_commit (str);
UPDATE zdba_del_tab
SET etime = SYSDATE
WHERE owner = r1.owner AND segment_name = r1.segment_name;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001,
'A loop error was encountered '
|| SQLCODE
|| ' -ERROR- '
|| SQLERRM
);
END;
END LOOP;
SELECT SYSDATE
INTO v_etime
FROM DUAL;
v_difftime := trunc((v_etime - v_stime) * 24 * 60,2);
ELSE
NULL;
END IF;
CLOSE c1;
UTL_MAIL.send (sender => v_sender,
recipients => v_maillist,
cc => NULL,
subject => 'OK...!! Delete Expired Data Completely. Type='||p_enabled||' (apps.zdba_del_tab_p)',
MESSAGE => 'Total consume '
|| v_difftime
|| ' min, from '
|| TO_CHAR (v_stime,
'yyyy-mm-dd hh24:mi:ss'
)
|| ' to '
|| TO_CHAR (v_etime,
'yyyy-mm-dd hh24:mi:ss'
),
mime_type => 'text/plain; charset=UTF-8'
);
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001,
'A main error was encountered '
|| SQLCODE
|| ' -ERROR- '
|| SQLERRM
);
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
UTL_MAIL.send (sender => v_sender,
recipients => v_maillist,
cc => NULL,
subject => 'ERROR...!! Delete Expired Data Failed. Type='||p_enabled||' (apps.zdba_del_tab_p)',
MESSAGE => 'SQLCODE: '
|| v_sqlcode
|| ' ,SQLERRM: '
|| v_sqlerrm,
mime_type => 'text/plain; charset=UTF-8'
);
END;
/
2014年8月21日 星期四
SAP WF_LOG__ files in /sapmnt//global too much
Symptom
A large file is written in the global directory (DIR_GLOBAL).
The file name is WF_LOG_000000000000_ccc where ccc is the client.
The file name is WF_LOG_000000000000_ccc where ccc is the client.
Other Terms
RSWWWIDE, workflow, technical trace
Reason and Prerequisites
This file is created or appended to when work items are archived or deleted.
Solution
To prevent the trace from being written implement the source code correction described below.
The file may be deleted manually without any side effects.
The file may be deleted manually without any side effects.
Note 117718 - Large file WF_LOG_000000000000_... created
2014年7月28日 星期一
ORATOP
前言：
隨著PC Server的規格及速度愈來愈快，大多數的公司摒棄大型主機，進而選擇PC Server的趨勢愈來愈盛，雖然在可靠度上仍然是大型主機占優勢，但是大型主機的維護費用高昂，這也是讓一般公司望之卻步的主要因素。
在目前PC Server的可靠度尚待提升的當下，其實，Virtual Machine的選擇可以彌補PC Server可靠度的不足，目前三大虛擬平台逐漸成形，分別是Vmware、Hyper-V以及Oracle VM。
如果各位使用PC Server，將Oracle Database安裝在PC Server上，大概就只有Linux可以選擇了。Linux上面要即時監控系統狀況，"top" 指令是系統管理員常用的，但是我們使用 "top" 找到了 Top Process之後，往往還需要將Process ID轉換成Database SID，才能找出關鍵性的Session，進而解決效能問題，不過，Oracle最近有一項工具叫做 "oratop"，可以及時監控Linux上的Database Process狀況，讓系統管理員省去不少時間，找出 Top Session。
目的：
oratop是類似 top 的工具，可以針對Oracle Database Performance做全面性的檢視，如果搭配 top 使用，會得到更完整的系統效能資訊。
適用版本：
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Linux x86-64
Linux x86
使用方式：
1. 使用oracle 使用者將下載的oratop.RDBMS_11.2_LINUX_X64 ftp 到資料庫主機上，如果是RAC環境，選定其中一個node上傳即可。
2. cd 到 oratop 程式所在目錄
3. 更名oratop程式
按下 "q"，或是 CTRL-C
參考畫面：
指令介紹：
1. 語法
a) Help，Displays usage or output information.
預設: N/A
預設: 累計
選項: 即時呈現
預設: Event/Latch
選項: File#:Block#
預設: 是Username/Program
選項: 是Module/Action
預設: Process mode
選項: SQL display
預設: Connection mode
選項: N/A
預設: short (80 columns)
選項: long format for header & process section.
預設: Process mode
選項: process display
預設: Text-based user interface
選項: N/A
預設: infinite
選項: the maximum number of iterations, or frames
預設: N/A
選項: tablespace information
預設: N/A
選項: ASM diskgroup information
預設: 5 seconds
選項: the delay between update refresh
預設: Connection mode
選項: N/A
參考文件：
oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)
隨著PC Server的規格及速度愈來愈快，大多數的公司摒棄大型主機，進而選擇PC Server的趨勢愈來愈盛，雖然在可靠度上仍然是大型主機占優勢，但是大型主機的維護費用高昂，這也是讓一般公司望之卻步的主要因素。
在目前PC Server的可靠度尚待提升的當下，其實，Virtual Machine的選擇可以彌補PC Server可靠度的不足，目前三大虛擬平台逐漸成形，分別是Vmware、Hyper-V以及Oracle VM。
如果各位使用PC Server，將Oracle Database安裝在PC Server上，大概就只有Linux可以選擇了。Linux上面要即時監控系統狀況，"top" 指令是系統管理員常用的，但是我們使用 "top" 找到了 Top Process之後，往往還需要將Process ID轉換成Database SID，才能找出關鍵性的Session，進而解決效能問題，不過，Oracle最近有一項工具叫做 "oratop"，可以及時監控Linux上的Database Process狀況，讓系統管理員省去不少時間，找出 Top Session。
目的：
oratop是類似 top 的工具，可以針對Oracle Database Performance做全面性的檢視，如果搭配 top 使用，會得到更完整的系統效能資訊。
適用版本：
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Linux x86-64
Linux x86
使用方式：
1. 使用oracle 使用者將下載的oratop.RDBMS_11.2_LINUX_X64 ftp 到資料庫主機上，如果是RAC環境，選定其中一個node上傳即可。
2. cd 到 oratop 程式所在目錄
3. 更名oratop程式
- $ mv oratop* oratop
- $ chmod 755 oratop
- $ export TERM=xterm #or vt100
$ export ORACLE_HOME=<11.2 database home>$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib$ export PATH=$ORACLE_HOME/bin:$PATH$ export ORACLE_SID=<local 11.2 database SID to be monitored> #only needed if connecting to a local database
- $ ./oratop -i 10 / as sysdba
- $ ./oratop -i 10 system/manager@tns_alias
按下 "q"，或是 CTRL-C
參考畫面：
指令介紹：
1. 語法
- $ oratop [Options] [Logon]
a) Help，Displays usage or output information.
預設: N/A
- $ oratop -h[elp] # runtime mode 按下h
預設: 累計
選項: 即時呈現
- $ oratop -d # runtime mode 按下d
預設: Event/Latch
選項: File#:Block#
- $ oratop -k # runtime mode 按下k
預設: 是Username/Program
選項: 是Module/Action
- $ oratop -m # runtime mode 按下m
預設: Process mode
選項: SQL display
- $ oratop -s # runtime mode 按下s
預設: Connection mode
選項: N/A
- $ oratop -c # runtime mode:N/A
預設: short (80 columns)
選項: long format for header & process section.
- $ oratop -f # runtime mode: 按下f
預設: Process mode
選項: process display
- $ oratop -p # runtime mode: 按下p
預設: Text-based user interface
選項: N/A
- $ oratop -b # runtime mode: N/A
預設: infinite
選項: the maximum number of iterations, or frames
- $ oratop -n # runtime mode: N/A
預設: N/A
選項: tablespace information
- # runtime mode: 按下t
預設: N/A
選項: ASM diskgroup information
- # runtime mode: 按下a
預設: 5 seconds
選項: the delay between update refresh
- $ oratop -c # runtime mode: 按下
預設: Connection mode
選項: N/A
- $ oratop -v # runtime mode: N/A
參考文件：
oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)
2014年7月23日 星期三
SQLHC
介紹：
SQLHC (SQL Health Check) 是Database診斷工具之一，目的是快速取得SQL效能診斷資訊，你可以將它視為SQLT (SQLTXPLAIN) 的精簡版，不過和SQLT不同的是，SQLHC不需要安裝，所以也不會異動到Database，但是精簡版的工具當然有所限制，例如它無法在Data Guard的環境之下使用，也無法針對PL/SQL Procedure進行分析等等。
適用環境：
Oracle Database 10.2.0.1以後的版本
設定：
無須任何設定
執行條件：
需要在SQL*Plus執行sqlhc.sql，並且使用SYS，DBA，或是對Data Dictionary views有權限存取的User
執行步驟：
首先，先登入到database Server
# sqlplus / as sysdba
SQL> @sqlhc.sql
接著會出現參數1詢問：
Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
請輸入T
接著會出現參數2詢問：
A valid SQL_ID for the SQL to be analyzed (required)
請輸入SQL_ID
或是直接輸入參數也可以
SQL> @sqlhc.sql T djkbyr8vkc64h
執行結果：
執行完之後會在Database Server上產生sqlhc_{timestamp}_{sql_id}.zip，解壓縮之後會產出以下檔案：
1_health_check.html
2_diagnostics.html
3_execution_plans.html
4_sql_detail.html
5_sql_monitor.zip
6_10053_trace_from_cursor.trc
8_sqldx.zip
9_log.zip
各位是不是覺得 "7" 怎麼不見了?? 但是產出結果就是如此，不用再去鑽牛角尖，畢竟這不是重點。
第一份的health check會給出 Oracle建議項目，各位可以自身經驗參考，如下圖所示：
第二份的diagnostics會直接從AWR and ASH Reprot 抓出和該SQL相關的數值。
第三份execution plan對各位來說比較有感覺，裡頭會詳述該 SQL 的執行計畫，有經驗的Programer看了執行計畫應該就知道哪個地方該被tuning。
第四份sql detail是以圖形化的方式直接呈現該SQL對各項資源的效能指標。
如果沒有太多時間，前4份文件給的資訊就足以tuning SQL statement，如果可以的話，其他文件會讓你發現對於該SQL相關的其他更微小的細節，
參考文件：
SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
SQLHC (SQL Health Check) 是Database診斷工具之一，目的是快速取得SQL效能診斷資訊，你可以將它視為SQLT (SQLTXPLAIN) 的精簡版，不過和SQLT不同的是，SQLHC不需要安裝，所以也不會異動到Database，但是精簡版的工具當然有所限制，例如它無法在Data Guard的環境之下使用，也無法針對PL/SQL Procedure進行分析等等。
適用環境：
Oracle Database 10.2.0.1以後的版本
設定：
無須任何設定
執行條件：
需要在SQL*Plus執行sqlhc.sql，並且使用SYS，DBA，或是對Data Dictionary views有權限存取的User
執行步驟：
首先，先登入到database Server
# sqlplus / as sysdba
SQL> @sqlhc.sql
接著會出現參數1詢問：
Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
請輸入T
接著會出現參數2詢問：
A valid SQL_ID for the SQL to be analyzed (required)
請輸入SQL_ID
或是直接輸入參數也可以
SQL> @sqlhc.sql T djkbyr8vkc64h
執行結果：
執行完之後會在Database Server上產生sqlhc_{timestamp}_{sql_id}.zip，解壓縮之後會產出以下檔案：
1_health_check.html
2_diagnostics.html
3_execution_plans.html
4_sql_detail.html
5_sql_monitor.zip
6_10053_trace_from_cursor.trc
8_sqldx.zip
9_log.zip
各位是不是覺得 "7" 怎麼不見了?? 但是產出結果就是如此，不用再去鑽牛角尖，畢竟這不是重點。
第一份的health check會給出 Oracle建議項目，各位可以自身經驗參考，如下圖所示：
第二份的diagnostics會直接從AWR and ASH Reprot 抓出和該SQL相關的數值。
第三份execution plan對各位來說比較有感覺，裡頭會詳述該 SQL 的執行計畫，有經驗的Programer看了執行計畫應該就知道哪個地方該被tuning。
第四份sql detail是以圖形化的方式直接呈現該SQL對各項資源的效能指標。
如果沒有太多時間，前4份文件給的資訊就足以tuning SQL statement，如果可以的話，其他文件會讓你發現對於該SQL相關的其他更微小的細節，
參考文件：
SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
2014年7月14日 星期一
2.3 Space Management
1. 在Oracle ERP中，沒有針對空間做歷史紀錄，建議自己做空間的歷史紀錄，才能得知長時間的空間變化趨勢，以當作資料生命週期，或是年度採買Storage使用。
2. 因為Oracle ERP物件過多，所以我不會所有物件都納入紀錄範疇。我鎖定超過10 MB的物件每日作記錄，但是如此一來，Tablespace 空間匯總資訊就會失真，所以Tablespace 建議額外處理。
3. 資料收集好之後，排 JOB 每日檢查，例如單一 Tablespace 空間增幅超過100 MB就發警告信，單一 Table 空間增幅超過50 MB就發警告信。
4. 如果收集統計值的工作都有定期執行的話 (至少一星期一次)，那麼就可以列出 High Water Mark (HWM) 和實際 Size 差異過大的 Table List，接著擬定 Table Reorganization 計畫，排定每一季或是每半年做一次 Table Reorganization。
未完待續.................
2. 因為Oracle ERP物件過多，所以我不會所有物件都納入紀錄範疇。我鎖定超過10 MB的物件每日作記錄，但是如此一來，Tablespace 空間匯總資訊就會失真，所以Tablespace 建議額外處理。
3. 資料收集好之後，排 JOB 每日檢查，例如單一 Tablespace 空間增幅超過100 MB就發警告信，單一 Table 空間增幅超過50 MB就發警告信。
4. 如果收集統計值的工作都有定期執行的話 (至少一星期一次)，那麼就可以列出 High Water Mark (HWM) 和實際 Size 差異過大的 Table List，接著擬定 Table Reorganization 計畫，排定每一季或是每半年做一次 Table Reorganization。
未完待續.................
訂閱：
文章 (Atom)