表領域作成

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
---AERS
CREATE SMALLFILE TABLESPACE AERS
    DATAFILE 'D:\ORADATA\AERS\AERS.DBF'    SIZE 20G AUTOEXTEND
    ON NEXT 10M MAXSIZE UNLIMITED
    NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
---AERSIND
CREATE SMALLFILE TABLESPACE AERSIND
    DATAFILE 'D:\ORADATA\AERS\AERSIND.DBF' SIZE 20G AUTOEXTEND
    ON NEXT 10M MAXSIZE UNLIMITED
    NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
---AERSTBL
CREATE SMALLFILE TABLESPACE AERSTBL
    DATAFILE 'D:\ORADATA\AERS\AERSTBL.DBF' SIZE 20G AUTOEXTEND
    ON NEXT 10M MAXSIZE UNLIMITED
    NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
---AERSTMP
CREATE SMALLFILE TEMPORARY TABLESPACE AERSTMP
    TEMPFILE 'D:\ORADATA\AERS\AERSTMP.DBF' SIZE 5G AUTOEXTEND
    ON NEXT 10M MAXSIZE 10G
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ユーザ作成

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
CREATE USER    AERS
         PROFILE DEFAULT
         IDENTIFIED BY "*******"
         DEFAULT   TABLESPACE AERS
         TEMPORARY TABLESPACE AERSTMP ACCOUNT UNLOCK;
 GRANT CREATE PUBLIC SYNONYM TO AERS;
 GRANT DROP PUBLIC SYNONYM   TO AERS;
 GRANT CREATE VIEW           TO AERS;
 GRANT EXECUTE ANY PROCEDURE TO AERS;
 GRANT CONNECT               TO AERS;
 GRANT CTXAPP                TO AERS;
 GRANT RESOURCE              TO AERS;

ちなみにこのロールがどのようなシステム権限を持っているかは

select GRANTEE,PRIVILEGE from dba_sys_privs where
GRANTEE in ('CONNECT','RESOURCE','OEM_MONITOR')
order by GRANTEE,PRIVILEGE;

で見れる。

四半期データの取り込み

ORACLEにaersデータをインポートするにはSQLローダを使います。
各種のテーブルのテーブル・コントロールファイルは下記のとおりです。

2004Q1〜2005Q22005Q3〜書式
DEMOfiledemo04q1.sql
fileDEMO04Q1.ctl
filedemo05q3.sql
fileDEMO05Q3.ctl
sqlplus aers@aers @demo04q1.sql
sqlldr aers@aers @control=DEMO04Q1.ctl skip=1
DRUGfiledrug04q1.sql
fileDRUG04Q1.ctl
INDIfileINDI04Q1.ctl

各四半期、各テーブルごとに作成します。
1四半期で7テーブル作成され、1年では7(テーブル) x 4(四半期)で28テーブル。
2004Q1から2011Q4だと28(テーブル) x 8年で224個のテーブルが作られます。

集約テーブル作成

各目的テーブルごとにデータを統合します。ここでMERGE文を使う。

CREATE TABLEMERGE
DEMO
DRUG
INDI
OUTC
REAC
RPSR
THER

MedDRA-J

ぜひ用意された方がいい。便利

全体像

2013y01m24d_232052101.png

二次テーブル

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
----1. REAC_KANJI
DROP TABLE REAC_KANJI;
CREATE TABLE REAC_KANJI TABLESPACE AERSTBL PARALLEL AS
SELECT ISR,PT,QTR,PT_KANJI,PT_NAME as MEDDRA_PT
FROM REAC LEFT JOIN MEDDRA ON REAC.PT=MEDDRA.LLT_NAME;
ALTER TABLE REAC_KANJI ADD CONSTRAINT PK_REAC_KANJI PRIMARY KEY(ISR,PT) USING INDEX TABLESPACE AERSIND;
---
---
----2. INDI_KANJI
DROP TABLE INDI_KANJI;
CREATE TABLE INDI_KANJI TABLESPACE AERSTBL PARALLEL AS
SELECT ISR,DRUG_SEQ,INDI_PT,QTR,PT_KANJI,PT_NAME as MEDDRA_PT
FROM INDI LEFT JOIN MEDDRA ON INDI.INDI_PT=MEDDRA.LLT_NAME;
ALTER TABLE INDI_KANJI ADD CONSTRAINT PK_INDI_KANJI PRIMARY KEY(ISR,DRUG_SEQ,INDI_PT) USING INDEX TABLESPACE AERSIND;
---
---
----3. THER_DRUG
DROP TABLE THER_DRUG;
CREATE TABLE THER_DRUG TABLESPACE AERSTBL PARALLEL AS
SELECT THER.ISR,THER.DRUG_SEQ,START_DT,END_DT,DUR,DUR_COD,THER.QTR,DRUG.DRUGNAME
FROM THER LEFT JOIN DRUG ON THER.ISR=DRUG.ISR AND THER.DRUG_SEQ=DRUG.DRUG_SEQ;
ALTER TABLE THER_DRUG ADD CONSTRAINT UK_THER_DRUG UNIQUE(ISR,DRUG_SEQ,START_DT,END_DT,DUR,DUR_COD) USING INDEX TABLESPACE AERSIND;

PublicSynonym

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
CREATE PUBLIC SYNONYM DEMO FOR AERS.DEMO;
CREATE PUBLIC SYNONYM DRUG FOR AERS.DRUG;
CREATE PUBLIC SYNONYM INDI FOR AERS.INDI;
CREATE PUBLIC SYNONYM OUTC FOR AERS.OUTC;
CREATE PUBLIC SYNONYM REAC FOR AERS.REAC;
CREATE PUBLIC SYNONYM RPSR FOR AERS.RPSR;
CREATE PUBLIC SYNONYM THER FOR AERS.THER;
CREATE PUBLIC SYNONYM MEDDRA FOR AERS.MEDDRA;
CREATE PUBLIC SYNONYM REAC_KANJI FOR AERS.REAC_KANJI;
CREATE PUBLIC SYNONYM INDI_KANJI FOR AERS.INDI_KANJI;
CREATE PUBLIC SYNONYM THER_DRUG  FOR AERS.THER_DRUG;
CREATE PUBLIC SYNONYM NEWEST_CASE FOR AERS.NEWEST_CASE;
 
GRANT SELECT ON DEMO TO PUBLIC;
GRANT SELECT ON DRUG TO PUBLIC;
GRANT SELECT ON INDI TO PUBLIC;
GRANT SELECT ON OUTC TO PUBLIC;
GRANT SELECT ON REAC TO PUBLIC;
GRANT SELECT ON RPSR TO PUBLIC;
GRANT SELECT ON THER TO PUBLIC;
GRANT SELECT ON MEDDRA TO PUBLIC;
GRANT SELECT ON REAC_KANJI TO PUBLIC;
GRANT SELECT ON INDI_KANJI TO PUBLIC;
GRANT SELECT ON THER_DRUG  TO PUBLIC;
GRANT SELECT ON NEWEST_CASE TO PUBLIC;

確認用SQL

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
select * from all_synonyms where table_owner = 'AERS';
新規作成
CREATE VIEW DRUG_ AS SELECT DRUG.ISR,DRUG_SEQ,ROLE_COD,DRUGNAME,VAL_VBM,
ROUTE,DOSE_VBM,DECHAL,RECHAL,LOT_NUM,EXP_DT,NDA_NUM,QTR,
SELECTED_DRUG.QUERYSTR,VALIDATE_QTR
FROM DRUG LEFT JOIN SELECTED_DRUG ON DRUG.ISR=SELECTED_DRUG.ISR;
 
CREATE TABLE SELECTED_DRUG (
  ISR NUMBER(12),
  QUERYQTR VARCHAR(70)
);

トップ   編集 添付 複製 名前変更     ヘルプ   最終更新のRSS
Last-modified: 2013-06-13 (木) 01:14:08 (2865d)