ひとり勉強会: 3月 2014

2014年3月16日日曜日

【Oracle】外部表

マニュアルを見ながら外部表の練習。

---empxt1.dat------

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus

361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper

362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr

363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

⇒これを素直に取り込む

---empxt2.dat------

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel

402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega

403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins

404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

⇒これを一度gzに固めて、preprocessor経由で取り込む。そのために解凍スクリプトを準備。

#解凍スクリプト

[oracle@single data]$ cat zcat

/bin/gunzip -c $1

その３として、datapumpと絡んだ外部表で遊びます。

ではでは、ここから外部表を作成します。

#ちょっと前準備。サンプルの日付パターンが英語なので。

SH@orcl > alter session set nls_language='AMERICAN';

#ディレクトリオブジェクトへ権限付与

SYS@orcl > grant all on directory data_dir to sh;

#外部表その１

CREATE TABLE ext_emp

(employee_id NUMBER(4),

first_name VARCHAR2(20),

last_name VARCHAR2(25),

job_id VARCHAR2(10),

manager_id NUMBER(4),

hire_date DATE,

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

department_id NUMBER(4),

email VARCHAR2(25)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY data_dir

ACCESS PARAMETERS

(

records delimited by newline

badfile data_dir:'empxt%a_%p.bad'

logfile data_dir:'empxt%a_%p.log'

fields terminated by ','

missing field values are null

( employee_id, first_name, last_name, job_id, manager_id,

hire_date char date_format date mask "dd-mon-yyyy",

salary, commission_pct, department_id, email

)

LOCATION ('empxt1.dat')

)

PARALLEL

REJECT LIMIT UNLIMITED;

#外部表その２

CREATE TABLE sales_transactions_ext

(

employee_id NUMBER(4),

first_name VARCHAR2(20),

last_name VARCHAR2(25),

job_id VARCHAR2(10),

manager_id NUMBER(4),

hire_date DATE,

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

department_id NUMBER(4),

email VARCHAR2(25)

)

ORGANIZATION external

(TYPE oracle_loader

DEFAULT DIRECTORY data_dir

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE★

CHARACTERSET AL32UTF8

PREPROCESSOR data_dir:'zcat'★

FIELDS TERMINATED BY ","

(

employee_id, first_name, last_name, job_id, manager_id,

hire_date char date_format date mask "dd-mon-yyyy",

salary, commission_pct, department_id, email

)

location ('empxt2.dat.gz')

)REJECT LIMIT UNLIMITED;

#外部表その３

SH@orcl > CREATE TABLE test_pump

2 ORGANIZATION EXTERNAL

3 (

4 TYPE ORACLE_DATAPUMP

5 DEFAULT DIRECTORY data_dir

6 LOCATION ('table2.dmp')

7 )

8 AS SELECT * FROM sales_transactions_ext where EMPLOYEE_ID=401;

Table created.

#確認

SH@orcl > select * from test_pump;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID

----------- -------------------- ------------------------- ---------- ----------

HIRE_DAT SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL

-------- ---------- -------------- ------------- -------------------------

401 Jesse Cromwell HR_REP 203

01-05-17 7000 0 40 jcromwel

#deleteとか出来ちゃうか確認。外部表なので、いじれません。

SH@orcl > delete from test_pump;

delete from test_pump

ERROR at line 1:

ORA-30657: operation not supported on external organized table

＜参考マニュアル＞

Oracle® Database管理者ガイド

20 表の管理

外部表の管理

http://docs.oracle.com/cd/E16338_01/server.112/b56301/tables.htm#i1007424

http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf

Oracle® Databaseユーティリティ

11gリリース2 (11.2)

15 ORACLE_DATAPUMPアクセス・ドライバ

http://docs.oracle.com/cd/E16338_01/server.112/b56303/et_dp_driver.htm#i1006773

2014年3月15日土曜日

インスタンスorcl⇒インスタンスPROD1へのDBリンクを作る。

ユーザ名は（めんどくさかったので）systemユーザで作った。本当はちゃんとしたユーザが良いのだろうけれども・・・。

#DBリンクをつくる
SYS@orcl > create database link testprodlink connect to system identified by oracle using 'PRODTNS';

#DBリンクを使って問い合わせる。PROD1につながっていることを確認。
SYS@orcl > select instance_name from v$instance@testprodlink;

INSTANCE_NAME
----------------
PROD1

＜参考＞
Oracle® Database管理者ガイド11g リリース2 (11.2)
データベース・リンク
http://docs.oracle.com/cd/E16338_01/server.112/b56301/ds_concepts.htm#i1007669

【Oracle】リスナー　動的登録と静的登録

動的登録なリスナー

[oracle@single admin]$ cat listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = single)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

静的登録リスナー

[oracle@single admin]$ cat listener.ora

LISTENER2 =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=single)(PORT=1561))))

SID_LIST_LISTENER2 =
(SID_LIST=
(SID_DESC=
(SID_NAME=PROD1
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)))

2014年3月10日月曜日

【Oracle】フラッシュデータアーカイブ

フラッシュバック・データ・アーカイブにより、その存続期間中、表に対するすべてのトランザクションによる変更を追跡および格納できます。
監査対応などで１年前のデータを出せと言われたときに出せる仕組み。

使うテーブルとか、とりあえずメモ。
（マニュアルより抜粋）
*_FLASHBACK_ARCHIVE
フラッシュバック・データ・アーカイブ・ファイルに関する情報
*_FLASHBACK_ARCHIVE_TS
フラッシュバック・データ・アーカイブ・ファイルの表領域
*_FLASHBACK_ARCHIVE_TABLES
データ・フラッシュバック・アーカイブ・ファイルが有効になっている表

#専用の表領域を作成する
create tablespace fla_da_tbs1 datafile '/u01/app/oracle/oradata/PROD1/fla_da.dbf' size 10M reuse autoextend on;

SYS@PROD1 > conn hr/hr
接続されました。
HR@PROD1 > select default_tablespace from user_users;

DEFAULT_TABLESPACE
------------------------------
USERS

HR@PROD1 > conn / as sysdba
接続されました。

SYS@PROD1 > alter user hr default tablespace fla_da_tbs1 quota unlimited on fla_da_tbs1;

ユーザーが変更されました。

#権限付与
#既存の表に対して使用するフラッシュバック・データ・アーカイブのFLASHBACK ARCHIVEオブジェクト権限が持っているとアーカイブ有効化が可能。

SYS@PROD1 > grant flashback archive on fla_1year to hr;

権限付与が成功しました。

#データアーカイブを作成する
SYS@PROD1 > CREATE FLASHBACK ARCHIVE DEFAULT fla_1year TABLESPACE fla_da_tbs1 QUOTA 10M RETENTION 1 YEAR;

フラッシュバック・アーカイブが作成されました。

HR@PROD1 > ALTER TABLE employees FLASHBACK ARCHIVE fla_1year;

#ここから実験データを入れる----------------------
・vishneyさんの給料は10500。これをupdate文によって12000までにあげる。

HR@PROD1 > select employee_id,last_name,salary from employees where last_name='Vishney';

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
162 Vishney 10500

HR@PROD1 > update employees set salary=salary+500 where last_name='Vishney';

1行が更新されました。

HR@PROD1 > r
1* update employees set salary=salary+500 where last_name='Vishney'

1行が更新されました。

HR@PROD1 > r
1* update employees set salary=salary+500 where last_name='Vishney'

1行が更新されました。

HR@PROD1 > select employee_id,last_name,salary from employees where last_name='Vishney';

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
162 Vishney 12000★

-------------------------------------------------

#内部表を確認。ちゃんとアーカイブされていることがわかった。
HR@PROD1 > r
1 select * from user_FLASHBACK_ARCHIVE_TABLES
2*

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------------------------ ------------------------------ -------------------------------------------------- ----------------------------------------------------- -------------
EMPLOYEES HR FLA_1YEAR SYS_FBA_HIST_76257★ ENABLED

#昔のデータアクセスする。
HR@PROD1 > SELECT SALARY, LAST_NAME FROM employees AS OF
2 TIMESTAMP ★TO_TIMESTAMP ('2014-03-09 23:40:00', 'YYYY-MM-DD HH24:MI:SS') where last_name='Vishney';

SALARY LAST_NAME
---------- -------------------------
12000 Vishney

#もとに戻せと言われたので、元に戻す。
HR@PROD1 > update employees set salary=(SELECT SALARY FROM employees AS OF TIMESTAMP TO_TIMESTAMP ('2014-03-09 23:35:00', 'YYYY-MM-DD HH24:MI:SS') where last_name='Vishney') where last_name='Vishney';

1行が更新されました。

HR@PROD1 > commit;

コミットが完了しました。

HR@PROD1 > SELECT SALARY, LAST_NAME FROM employees where last_name='Vishney';

SALARY LAST_NAME
---------- -------------------------
10500 Vishney

#データのアーカイブ期間を変更する
HR@PROD1 > select FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS from user_FLASHBACK_ARCHIVE;

FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS
-------------------------------------------------- -----------------
FLA_1YEAR 365

フラッシュバック・データ・アーカイブfla1の保有時間を2年に変更します。

HR@PROD1 > conn / as sysdba
接続されました。
SYS@PROD1 > ALTER FLASHBACK ARCHIVE FLA_1YEAR MODIFY RETENTION 2 YEAR;

フラッシュバック・アーカイブが変更されました。

#削除。

SYS@PROD1 > DROP FLASHBACK ARCHIVE FLA_1YEAR;

フラッシュバック・アーカイブが削除されました。

SYS@PROD1 > select FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS from user_FLASHBACK_ARCHIVE;

レコードが選択されませんでした。

＜参考手順＞

Oracle® Databaseアドバンスト・アプリケーション開発者ガイド
http://docs.oracle.com/cd/E16338_01/appdev.112/b56259/toc.htm

【Oracle】ファイングレイン監査

ファイングレイン監査をいじる。

権限付与→特定の表の特定列をselectしたときなどのみ、監査証跡が残るように設定。

#権限付与
GRANT EXECUTE ON DBMS_FGA TO sysadmin_fga;

#監査ポリシーの追加。ここでは、EMPLOYEES表のempolyee_id=100というselect文が投げられたときのみレコードを残す、という設定を加える

BEGIN
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA => 'HR',
OBJECT_NAME => 'EMPLOYEES',
POLICY_NAME => 'AUDIT_EMPS_JOBID',
ENABLE => True,
STATEMENT_TYPES => 'SELECT',
audit_column => 'EMPLOYEE_ID',
audit_condition => 'EMPLOYEE_ID=100');
END;
/

#テスト。実際にselectしてみる。（監査として結果が残っているかどうかは最後でみることにする）

select EMPLOYEE_ID from hr.employees where EMPLOYEE_ID=100;

#ポリシー削除（ごみがたまるのがいやなので、こまめに消す・・・）
begin
DBMS_FGA.DROP_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'AUDIT_EMPS_JOBID');
end;
/

#違うポリシーを作成する。今度はとにかくsalary列をselectされたら監査レコードを追加するようなポリシー。
BEGIN
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA => 'HR',
OBJECT_NAME => 'EMPLOYEES',
POLICY_NAME => 'AUDIT_EMPS_SALARY',
STATEMENT_TYPES => 'SELECT',
audit_column => 'SALARY');
END;
/

#テスト。
select salary from hr.employees;

#ポリシー削除
begin
DBMS_FGA.DROP_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'AUDIT_EMPS_SALARY');
end;
/

#監査の証拠が残っているかを確認

SYS@PROD1 > select SESSIONID,TIMESTAMP#,POLICYNAME,LSQLTEXT ,STATEMENT from SYS.FGA_LOG$;

SESSIONID TIMESTAM POLICYNAME LSQLTEXT STATEMENT
---------- -------- ------------------------------ -------------------------------------------------------------------------------- ----------
104650 AUDIT_EMPS_SALARY select salary from hr.employees 14
104732 AUDIT_EMPS_SALARY select SALARY,job_id from hr.employees 10
104732 AUDIT_EMPS_JOBID select EMPLOYEE_ID from hr.employees where EMPLOYEE_ID=100 28

→残っていることを確認!

＜参考手順＞
Oracle® Databaseセキュリティ・ガイド
http://docs.oracle.com/cd/E16338_01/network.112/b56285/auditing.htm#CEGBAFIA

【Oracle】パーティション

1.参照パーティション表をつくる

親子関係を持つ表は、親表のパーティションキーを継承して論理的にパーティション化してくれる。
キー列を複製しなくてもいい。

＜例＞
・（親）orders表
・（子）order_items表
・orders表にあるorder_date列をorder_items表が外部参照している。
→orders表がorder_date列をキーとしてパーティション化されている場合、参照パーティション表を使えばorder_items表も親と同じ単位でパーティション化できる。

#準備
SYS@PROD1 > show parameter NLS_DATE_LANGUAGE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_language string JAPANESE

#親表の作成

SH@PROD1 > conn hr/hr
接続されました。

HR@PROD1 > CREATE TABLE orders2
2 ( order_id NUMBER(12),
3 order_date date,
4 customer_id NUMBER(6),
5 order_total NUMBER(8,2),
6 sales_rep_id NUMBER(6),
7 CONSTRAINT orders_pk PRIMARY KEY(order_id)
8 )
9 PARTITION BY RANGE(order_date)
10 ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-4月-2013','dd-MON-yyyy')),
11 PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-7月-2013','dd-MON-yyyy')),
12 PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-10月-2013','dd-MON-yyyy')),
13 PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-1月-2014','dd-MON-yyyy'))
14 );

表が作成されました。

#子表の作成。参照パーティション化のため「PARTITION BY REFERENCE」が入る。
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),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);

#確認。

HR@PROD1 > set linesize 300
HR@PROD1 > SELECT table_name, partitioning_type, ref_ptn_constraint_name FROM user_part_tables;

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

set linesize 400
HR@PROD1 > r
1 SELECT table_name, partition_name, high_value,tablespace_name FROM user_tab_partitions;
2*

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

8行が選択されました。

#削除。

HR@PROD1 > drop table order_items purge;

表が削除されました。

#明示的に表領域が指定されない場合は同じ表領域。明示的に指定するとちゃんと変わることを確認する。
#また、パーティション名を明示的に指定しない場合は、親と同じパーティション名になる。明示的に指定した場合は、その指定したパーティション名になることを確認する。

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),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
tablespace par_tmp★
PARTITION BY REFERENCE(order_items_fk);

HR@PROD1 > SELECT table_name, partition_name, high_value,tablespace_name FROM user_tab_partitions;

TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------
ORDER_ITEMS Q4_2005 PAR_TMP★
ORDER_ITEMS Q3_2005 PAR_TMP★
ORDER_ITEMS Q2_2005 PAR_TMP★
ORDER_ITEMS Q1_2005 PAR_TMP★
ORDERS Q4_2005 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS◎
ORDERS Q3_2005 TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS◎
ORDERS Q2_2005 TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS◎
ORDERS Q1_2005 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS◎

8行が選択されました。

→元表はデフォルト表領域のUSERS,参照パーティションは指定したPAR_TMP表領域に乗っかっていることが確認できた。

HR@PROD1 > CREATE TABLE order_items
2 ( order_id NUMBER(12) NOT NULL,
3 line_item_id NUMBER(3) NOT NULL,
4 product_id NUMBER(6) NOT NULL,
5 unit_price NUMBER(8,2),
6 CONSTRAINT order_items_fk
7 FOREIGN KEY(order_id) REFERENCES orders(order_id)
8 )
9 PARTITION BY REFERENCE(order_items_fk)
10 (PARTITION hoge1,PARTITION hoge2,PARTITION hoge3,PARTITION hoge4);★

表が作成されました。

HR@PROD1 > SELECT table_name, partition_name, high_value,tablespace_name FROM user_tab_partitions;

TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------
ORDER_ITEMS HOGE4★ USERS
ORDER_ITEMS HOGE3★ USERS
ORDER_ITEMS HOGE2★ USERS
ORDER_ITEMS HOGE1★ USERS
ORDERS Q4_2005 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
ORDERS Q3_2005 TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
ORDERS Q2_2005 TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
ORDERS Q1_2005 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS

8行が選択されました。

→パーティション名が「HOGE」になっていることを確認できた。

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

レンジパーティションの一種。完全自動化でレンジパーティションを作ってくれる。操作の自動化という意味で楽ちん。
例えば月単位でパーティションします　という定義をした時間隔パーティション表を作成すると、
パーティションを明示的に作成しなくても自動で必要な月のパーティションをデータ insert 時に作成してくれる。

#作成

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-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

※参照パーティションのときは、親を PARTITION BY RANGE(order_date)でふつうにつくって子でPARTITION BY REFERENCE(order_items_fk);だった。
今度は、親とか子とかそういうのではないので、
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))でOK。

#確認

SH@PROD1 > SELECT table_name, partitioning_type FROM user_part_tables;

TABLE_NAME PARTITION
------------------------------ ---------
COSTS RANGE
INTERVAL_SALES RANGE
SALES RANGE★

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(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

#データを1件入れて、パーティションが自動で追加されるかを確認

SH@PROD1 > INSERT INTO interval_sales(prod_id, time_id) VALUES(1, TO_DATE('2013-10-07', 'YYYY-MM-DD'));

1行が作成されました。

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(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES SYS_P41★ TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

#先ほどは「MONTH」の単位でやったので、今度は「YEAR」でやる。

CREATE TABLE interval_sales2
( 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, 'YEAR'★))
( 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-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

#データを挿入し年単位でパーティションが自動作成されるかを確認する

INSERT INTO interval_sales2(prod_id, time_id) VALUES(1, TO_DATE('2013-10-07', 'YYYY-MM-DD'));
INSERT INTO interval_sales2(prod_id, time_id) VALUES(1, TO_DATE('2013-12-07', 'YYYY-MM-DD'));
INSERT INTO interval_sales2(prod_id, time_id) VALUES(1, TO_DATE('2014-03-07', 'YYYY-MM-DD'));

TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
INTERVAL_SALES2 P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES2 P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES2 P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES2 P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES2 SYS_P42 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES2 SYS_P43 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

＜参考マニュアル＞
Oracle® Database VLDBおよびパーティショニング・ガイド
11g リリース2 (11.2)
http://docs.oracle.com/cd/E16338_01/server.112/b56316/part_admin001.htm#BAJHFFBE

2014年3月9日日曜日

SQL*Plusをもっと便利に。

プロンプトの表示をもっと便利に。

$cd /u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin

$ ls
glogin.sql help libsqlplus.def plustrce.sql pupbld.sql

$ vi glogin.sql

以下を追記。

SET SQLPROMPT '_USER@&_CONNECT_IDENTIFIER > '

→上書き保存

接続してみる。
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 日 3月 9 18:38:27 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
に接続されました。
SYS@PROD1 >

ユーザを変えてみる。

SYS@PROD1 > conn scott/tiger

接続されました。

SCOTT@PROD1 >

【Oracle】SecureFiles LOB

SecureFiles LOBを含んだテーブルの作り方

create table sh.t01 (empid number,c01 clob) tablespace securefile_tbs
lob (c01) store as securefile(
compress high
DEDUPLICATE
);

#確認を行う
SH@PROD1 > select table_name,TABLESPACE_NAME,COMPRESSION,DEDUPLICATION from user_lobs;

TABLE_NAME TABLESPACE_NAME COMPRE DEDUPLICATION
------------------------------ ------------------------------ ------ ---------------
DR$SUP_TEXT_IDX$R EXAMPLE NONE NONE
DR$SUP_TEXT_IDX$I EXAMPLE NONE NONE
T01 SECUREFILE_TBS HIGH LOB

#圧縮率をhighからlowに変えてみる
ALTER TABLE t01 MODIFY
LOB(c01) (
COMPRESS LOW
);

SH@PROD1 > select table_name,TABLESPACE_NAME,COMPRESSION,DEDUPLICATION from user_lobs;

TABLE_NAME TABLESPACE_NAME COMPRE DEDUPLICATION
------------------------------ ------------------------------ ------ ---------------
DR$SUP_TEXT_IDX$R EXAMPLE NONE NONE
DR$SUP_TEXT_IDX$I EXAMPLE NONE NONE
T01 SECUREFILE_TBS LOW LOB

BasicFiles LOB から SecureFiles LOB への移行

オンライン再定義を行う必要を試す。
オンライン定義そのもののマニュアルは管理者ガイド。
http://docs.oracle.com/cd/E16338_01/server.112/b56301/tables.htm#i1006790

#接続
conn scott/tiger

#表作成
create table cust
(
c_id number primary key,
c_zip number,
c_name varchar(30) default null,
c_lob clob
);

#データを入れる
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');

#オンライン再定義するのに必要な権限をつける
#後半二つはDBMS_REDEFINITION.COPY_TABLE_DEPENDENTSを実行するのに必要
GRANT EXECUTE ON DBMS_REDEFINITION TO scott;
GRANT ALTER ANY TABLE TO scott;
GRANT DROP ANY TABLE TO scott;
GRANT LOCK ANY TABLE TO scott;
GRANT CREATE ANY TABLE TO scott;
GRANT SELECT ANY TABLE TO scott;
GRANT CREATE ANY TRIGGER TO scott;
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
('scott', 'cust', 'cust_int',
1, TRUE,TRUE,TRUE,FALSE, error_count);

DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

#オンライン再定義をするための仮表をつくる（仮表はSecureFilesのLOBを持った表）
conn scott/tiger
CREATE TABLE cust_int
(c_id NUMBER NOT NULL,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
)
LOB(c_lob)
STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING);

#元表と仮表の列名の紐づけを行う。
#col_mappingが長いので変数化して外だししているっぽい。

DECLARE
col_mapping VARCHAR2(1000);
BEGIN
col_mapping :=
'c_id c_id , '||
'c_zip c_zip , '||
'c_name c_name, '||
'c_lob c_lob';
DBMS_REDEFINITION.START_REDEF_TABLE('scott', 'cust', 'cust_int', col_mapping);
END;
/

#依存オブジェクト（索引や権限など）をコピーする。
マニュアルではCONS_ORIG_PARAMSという定数を使っているが、値=1（索引コピーしますという意味）なので
最初から1を入れておく。なので、

DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
('scott', 'cust', 'cust_int',
1, TRUE,TRUE,TRUE,FALSE, error_count);

DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

#エラーがあるか確認
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS where BASE_TABLE_NAME='SCOTT.CUST';
SYS@PROD1 > select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS where BASE_TABLE_NAME='CUST';

レコードが選択されませんでした。

#再定義を完了
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'cust', 'cust_int');

#変換できたか確認
select TABLE_NAME,SECUREFILE,COLUMN_NAME from user_lobs

TABLE_NAME SEC COLUMN_NAME
------------------------------ --- ------------------------------
CUST_INT NO C_LOB
CUST YES C_LOB

＜全体的な参考箇所＞
Oracle® Database SecureFilesおよびラージ・オブジェクト開発者ガイド
4 Oracle SecureFiles LOBの使用
http://docs.oracle.com/cd/E16338_01/appdev.112/b56263/adlob_smart.htm#BABDIEGE

登録: 投稿 (Atom)

ひとり勉強会