[Oracle] フラッシュバック・データ・アーカイブ(Oracle Total Recall)

Sep 16th, 2012

目的

フラッシュバック・データ・アーカイブ(Oracle Total Recall) を使用して、表への更新履歴を長期間保存する。

環境

OS: Oracle Enterprise Linux 5.8
DB: Oracle Database 11g Release 2 (11.2.0.3)

マニュアル

アドバンスト・アプリケーション開発者ガイド
–> 12 Oracle Flashback Technologyの使用
–> フラッシュバック・データ・アーカイブの使用(Oracle Total Recall)

フラッシュバック・データ・アーカイブの使用

フラッシュバック・データ・アーカイブ(Oracle Total Recall) を使用して、表への更新履歴を長期間保存できる。例えば、コンプライアンス上変更履歴を年単位で長時間保存しておく必要がある場合などに有効。

フラッシュバック・データ・アーカイブの作成

CREATE FLASHBACK ARCHIVE 文を使用してフラッシュバック・データ・アーカイブを作成する。データを1年間保持するデフォルトのフラッシュバック・データ・アーカイブ fla1 を USERS 表領域に作成。作成するには FLASHBACK ARCHIVE ADMINISTER システム権限を持つユーザか、SYSDBA として接続する必要がある。

SQL> connect /as sysdba
Connected.
SQL> CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE users
     RETENTION 1 YEAR;

Flashback archive created.

表のフラッシュバック・アーカイブの有効化

scott.emp 表のフラッシュバック・アーカイブを有効にして、フラッシュバック・データ・アーカイブ fla1 に履歴データを格納するようにする。 scott スキーマに fla1 に対する FLASHBACK ARCHIVE オブジェクト権限を与えて、ALTER TABLE 文を発行する。

SQL> connect /as sysdba
Connected.
SQL> GRANT FLASHBACK ARCHIVE ON fla1 TO scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> ALTER TABLE scott.emp FLASHBACK ARCHIVE fla1;

Table altered.

フラッシュバック・データ・アーカイブが有効になっていることの確認

scott スキーマで emp 表のフラッシュバック・データ・アーカイブが有効になっていることの確認

SQL> connect scott/tiger
Connected.
SQL> col table_name for a30
     col owner_name for a30
     col flashback_archive_name for a30
     col archive_table_name for a30
     col status for a10
     set lines 200
SQL> SELECT * FROM USER_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME             STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------
EMP                            SCOTT                          FLA1                           SYS_FBA_HIST_75335             ENABLED

[Oracle] 時間隔パーティション表

Sep 16th, 2012

目的

時間隔パーティション表を作成する。

環境

OS: Oracle Enterprise Linux 5.8
DB: Oracle Database 11g Release 2 (11.2.0.3)

マニュアル

VLDBおよびパーティショニング・ガイド
–> 4 パーティションの管理
–> 時間隔パーティション表の作成

時間隔パーティション表の作成

時間隔パーティション表はレンジ・パーティションの一種で、例えば月単位の時間隔パーティション表を作成すると、パーティションを明示的に作成しなくても自動で必要な月のパーティションをデータ insert 時に作成してくれる。

DATE 型の time_id をパーティション・キーとして、2009年までは年単位でパーティション化し、2010年1月1日以降は月単位でパーティションを自動作成するような時間隔パーティション表を作成してみる。

SQL> CREATE TABLE interval_sales
         ( prod_id        NUMBER(6)
         , cust_id        NUMBER
         , time_id        DATE
         , channel_id     CHAR(1)
         , promo_id       NUMBER(6)
         , quantity_sold  NUMBER(3)
         , amount_sold    NUMBER(10,2)
         ) 
       PARTITION BY RANGE (time_id) 
       INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
         ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
           PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
           PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

Table created.

ちなみに追加されるパーティションを日単位にする場合は、NUMTODSINTERVAL(1, 'DAY') 関数を利用する。

パーティション情報の確認。

SQL> col table_name for a30
     col partitioning_type for a20
     set lines 200
SQL> SELECT table_name, partitioning_type FROM user_part_tables;

TABLE_NAME                     PARTITIONING_TYPE   
------------------------------ --------------------
INTERVAL_SALES                 RANGE

SQL> col partition_name for a30
SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions ORDER BY table_name, partition_position;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
INTERVAL_SALES                 P0                             TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES                 P1                             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES                 P2                             TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

最初はパーティションは作られていないが、上記の範囲を超える行を insert するとパーティションが自動で作成される。

SQL> INSERT INTO interval_sales(prod_id, time_id) VALUES(1, TO_DATE('2012-09-16', 'YYYY-MM-DD'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions ORDER BY table_name, partition_position;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
INTERVAL_SALES                 P0                             TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES                 P1                             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES                 P2                             TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES                 SYS_P41                        TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P41 というパーティションが自動で作成された。

[Oracle] 参照パーティション表

Sep 16th, 2012

目的

参照パーティション表を作成する。

環境

OS: Oracle Enterprise Linux 5.8
DB: Oracle Database 11g Release 2 (11.2.0.3)

マニュアル

VLDBおよびパーティショニング・ガイド
–> 4 パーティションの管理
–> 参照パーティション表の作成

参照パーティション表の作成

参照パーティション表は、例えば次のようなケースで役立つ。注文を管理する orders 表に注文日が格納される order_date 列がある。また、注文に含まれる品目を管理する order_items 表があり、orders 表を order_id 列で外部参照しているとする。orders 表が order_date 列をキー値としてパーティション化されている場合、order_items 表も同じように注文日によりパーティション化したかったら、従来ならば order_date 列を order_itmes 表にも加える必要があった。参照パーティション表を使えば、order_items 列に余計な order_date 列を加えることなく、外部参照している orders と同じ単位でパーティション化することができる。

実際に上記シナリオで参照パーティション表を作成してみる。

SQL> CREATE TABLE orders
         ( order_id           NUMBER(12),
           order_date         DATE,
           order_mode         VARCHAR2(8),
           customer_id        NUMBER(6),
           order_status       NUMBER(2),
           order_total        NUMBER(8,2),
           sales_rep_id       NUMBER(6),
           promotion_id       NUMBER(6),
           CONSTRAINT orders_pk PRIMARY KEY(order_id)
         )
       PARTITION BY RANGE(order_date)
         ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
           PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
           PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
           PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
         );

Table created.

SQL> CREATE TABLE order_items
         ( order_id           NUMBER(12) NOT NULL,
           line_item_id       NUMBER(3)  NOT NULL,
           product_id         NUMBER(6)  NOT NULL,
           unit_price         NUMBER(8,2),
           quantity           NUMBER(8),
           CONSTRAINT order_items_fk
           FOREIGN KEY(order_id) REFERENCES orders(order_id)
         )
         PARTITION BY REFERENCE(order_items_fk);

Table created.

パーティション情報の確認。

SQL> col table_name for a30
     col partitioning_type for a20
     col ref_ptn_constraint_name for a30
     set lines 200
SQL> SELECT table_name, partitioning_type, ref_ptn_constraint_name FROM user_part_tables;

TABLE_NAME                     PARTITIONING_TYPE    REF_PTN_CONSTRAINT_NAME
------------------------------ -------------------- ------------------------------
ORDERS                         RANGE
ORDER_ITEMS                    REFERENCE            ORDER_ITEMS_FK

SQL> col partition_name for a30
SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions ORDER BY table_name, partition_position;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
ORDERS                         Q1_2005                        TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         Q2_2005                        TO_DATE(' 2005-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         Q3_2005                        TO_DATE(' 2005-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         Q4_2005                        TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDER_ITEMS                    Q1_2005
ORDER_ITEMS                    Q2_2005
ORDER_ITEMS                    Q3_2005
ORDER_ITEMS                    Q4_2005

← Older Blog Archives Newer →