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;
/

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.

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.

Reference
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程式
  1. $ mv oratop* oratop
4. 更改oratop權限
  1. $ chmod 755 oratop
5. 在.bash_profile 環境變數檔中,設定環境變數(如果尚未設定的話)
  1. $ 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
6. 執行oratop (每10秒更新一次)
  1. $ ./oratop -i 10 / as sysdba
7. 針對遠端資料庫執行oratop (每10秒更新一次)
  1. $ ./oratop -i 10 system/manager@tns_alias
8. 結束oratop
    按下 "q",或是 CTRL-C

參考畫面:

指令介紹:
1. 語法
  1. $ oratop [Options] [Logon]
2. Options
    a) Help,Displays usage or output information.
        預設: N/A
  1. $ oratop -h[elp]  # runtime mode 按下h
    b) Top 5 Wait Events
        預設: 累計
        選項: 即時呈現
  1. $ oratop -d  # runtime mode 按下d
    c) 等待物件
        預設: Event/Latch
        選項: File#:Block#
  1. $ oratop -k  # runtime mode 按下k
    d) session行為
        預設: 是Username/Program
        選項: 是Module/Action
  1. $ oratop -m  # runtime mode 按下m
    e) SQL mode
        預設: Process mode
        選項: SQL display
  1. $ oratop -s  # runtime mode 按下s
    f) Database Service centric mode
        預設: Connection mode
        選項: N/A
  1. $ oratop -c  # runtime mode:N/A
    g) Long format, (132 columns)
        預設:  short (80 columns)
        選項:  long format for header & process section.
  1. $ oratop -f  # runtime mode: 按下f
    h) Database Process mode
        預設:  Process mode
        選項: process display
  1. $ oratop -p  # runtime mode: 按下p
    i) Batch mode operation
        預設: Text-based user interface
        選項: N/A
  1. $ oratop -b  # runtime mode: N/A
    j) Maximum Number of iterations.   (requires a number)
        預設: infinite
        選項: the maximum number of iterations, or frames
  1. $ oratop -n  # runtime mode: N/A
   k)  Displays tablespace information
        預設: N/A
        選項: tablespace information
  1. # runtime mode: 按下t
    l) Displays  ASM diskgroup information.  (no op for non-ASM)
        預設: N/A
        選項: ASM diskgroup information
  1. # runtime mode: 按下a
    l) Displays SQL execution plan table. Requires input of the active SQL sql_id value
        預設: 5 seconds
        選項: the delay between update refresh
  1. $ oratop -c  # runtime mode: 按下
    m) Interval delay time. ( requires value in seconds)
         預設: Connection mode
         選項: N/A
  1. $ 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)

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。

未完待續.................

使用 utl_mail 遇到 error:ORA-24247: network access denied by access control list (ACL)

從Oracle 10g開始,我們直接使用 UTL_MAIL 來作為發信的 Package,而非以往使用 UTL_SMTP 自行撰寫PL/SQL Code的方式,但是我們在 Oracle 11.2.0.3.0 (x86-64 Oracle Linux 6.2) 遇到了以下的錯誤:

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2

原因:
我們參考了Oracle Doc ID 1209644.1,從Oracle 11gR1 之後由於安全性的提升,我們想要使用 DBMS_NETWORK_ACL_ADMIN package 來操作 Access Control Lists (ACL),用以存取 External Network Services 就會發生這個錯誤訊息。

解決方式:
使用DBA權限執行下面PL/SQL,請記得要將 SCOTT 換成你想要賦予帳號的帳號,帳號有大小寫限制:

BEGIN

  -- Only uncomment the following line if ACL "network_services.xml" has already been created
  --DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'network_services.xml',
    description => 'NETWORK ACL',
    principal => 'SCOTT',
    is_grant => true,
    privilege => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'network_services.xml',
    principal => 'SCOTT',
    is_grant => true,
    privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'network_services.xml',
    host => '*');

  COMMIT;

END;
/

如果有其他使用者要加入權限,只要執行以下 PL/SQL 即可:

BEGIN

  -- For EACH additional user, execute the following code

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'network_services.xml',
    principal => 'ANOTHER_USER',
    is_grant => true,
    privilege => 'resolve');

  COMMIT;

END;
/

參考資料:
ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP And UTL_MAIL With 11gR1 Or Later 
(Doc ID 1209644.1)

2014年7月13日 星期日

SAP BRSPACE Online Reorganizations

1. Which situations a table reorganization be useful ?

  • Reduction of table size
  • Cleanup of table fragmentation
  • Change of table storage parameters (e.g. INITRANS)
  • Move of table to a different tablespace
  • Reduction of chained and migrated rows
  • Reduction of number of allocated extents
  • Transition from LONG to LOB columns (Oracle >= 10g)
  • Reduction of hot spots on disk level
  • Activation of Transparent Data Encryption
  • Change of table structure
2. Which situations  a tablespace reorganization be useful ?

  • Activation of LMTS
  • Activation of ASSM
  • Cleanup of freespace block corruption
  • Reduction of database size
  • Reduction of backup size
  • Reduction of filesystem size
  • Reduction of fragmentation
  • Reduction of number of datafiles
  • Increase of number of datafiles
  • Transition to new tablespace layout
  • Other change to tablespace layout
  • Optimization of filesystem layout
3. How does a BRSPACE online reorganization work?
  • Determination of CREATE commands for target table and indexes based on DBMS_METADATA.GET_DDL
  • Creation of target table with naming convention <source_table>#$
  • Export of source table CBO statistics based on DBMS_STATS.EXPORT_TABLE_STATS
  • Copy of source table data to target table based on DBMS_REDEFINITION.START_REDEF_TABLE. This package will implicitly create a materialized view log MLOG$_<source_table> to track all changes that are performed on the source table while the reorganization is running.
  • Creation of target indexes with naming convention <source_index>#$
  • Import of source table CBO statistics to target table based on DBMS_STATS.IMPORT_TABLE_STATS
  • Finalizing of the online reorganization using DBMS_REDEFINITION.FINISH_REDEF_TABLE. In this step all changes in the materialized log are applied to the target table and the names of source and target table are exchanged.
  • The source table is dropped.
  • The ?#$“ suffix of the target table indexes is removed.
4. What are the advantages of a BRSPACE Online Reorganization?
  • It uses the advantages of DBMS_REDEFINITION:
          a. The reorganization can be performed in parallel to production operation.
              This means the table is accessible and records can be inserted / updated / deleted.
„          b. If anything goes wrong during the reorganization the source table still 
              exists unchanged and no restore / recovery is necessary.
          c.„ Compared to an offline reorganization via export / import all data has to be 
              copied only once and not twice
  • „ It provides possibilities for client and server side parallelism in order to speed up the reorganization if sufficient system resources exist and the reorganization should finish within a specific time window.
  • „ It has already proven to be a reliable reorganization tool at many customers.
  • „ It performs a lot of useful activities implicitly that would require a lot of manual efforts and impose the risk of errors if DBMS_REDEFINITION is used directly.
5. What are the restrictions of a BRSPACE Online Reorganization?
  • It is only available as of Oracle 9i.
  • It cannot handle tables with LONG or LONG RAW columns. As of 10g it is possible to convert them during an online reorganization on the fly to LOB columns (if SAP >= 6.40 is used).
  • Temporarily twice the space is needed because both source and target table and indexes exist in parallel.
  • If a table has no primary key constraint (or no unique index with NOT NULL columns) a ROWID based online reorganization is necessary that requires overhead (ROWID index, ROWID column).
  • The setup of the materialized view is an overhead that can negatively impact the performance of the reorganization of many very small tables.
  • Structure changes on the source table and its indexes must not be performed during the reorganization (as they would be lost).
6. How is a normal BRSPACE Online Reorganization started?
  • Table reorganization:
          brspace -f tbreorg -t <table_name>
  • Tablespace reorganization:
          brspace -f tbreorg -s <source_tsp> -t "*" -n <target_tsp>
  • Additional useful options exist, e.g.:
         a) -p <degree>
             => BRSPACE parallelism degree
         b) -e <degree>
             => PX parallelism degree
         c) -l <category>
             => Initial extent size category
         d) -i <target_ind_tsp>
             => Definition of target index tablespace (if different from target table tablespace)
7. Reference:
    Note 646681 and the BRSPACE online documentation.