Kettle构建ETL项目方案过程完全指导

0. Oracle基础环境准备

创建表空间(参考)

# EDW_ETL_DAT 。创建时指定temporary表示创建临时表空间
DROP [temporary] TABLESPACE EDW_ETL_DAT INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE EDW_ETL_DAT DATAFILE 
  '+DG_DATA' SIZE 10G AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

# EDW_ETL_IDX
DROP TABLESPACE EDW_ETL_IDX INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE EDW_ETL_IDX DATAFILE 
  '+DG_DATA' SIZE 10G AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

拓展表空间

#查看数据文件
select * from dba_data_files;
#拓展表空间
alter tablespace EDW_ETL_DAT add datafile size 30G；
alter tablespace EDW_ETL_IDX add datafile size 30G；

创建用户并进行授权(参考)

#EDWADM
DROP USER EDBADM CASCADE;

CREATE USER EDBADM
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE EDW_COM_DAT  #设置默认表空间
  TEMPORARY TABLESPACE TEMP       #设置默认表空间
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 3 Roles for EDBADM 
  GRANT CONNECT TO EDBADM;
  GRANT RESOURCE TO EDBADM;
  GRANT SELECT_CATALOG_ROLE TO EDBADM;
  ALTER USER EDBADM DEFAULT ROLE ALL;
  -- 13 System Privileges for EDBADM 
  GRANT ADVISOR TO EDBADM;
  GRANT ALTER ANY SEQUENCE TO EDBADM;
  GRANT ALTER ANY TABLE TO EDBADM;
  GRANT ALTER SESSION TO EDBADM;
  GRANT CREATE DATABASE LINK TO EDBADM;
  GRANT CREATE PROCEDURE TO EDBADM;
  GRANT CREATE SEQUENCE TO EDBADM;
  GRANT CREATE SYNONYM TO EDBADM;
  GRANT CREATE TABLE TO EDBADM;
  GRANT CREATE VIEW TO EDBADM;
  GRANT EXECUTE ANY PROCEDURE TO EDBADM;
  GRANT SELECT ANY DICTIONARY TO EDBADM;
  GRANT UNLIMITED TABLESPACE TO EDBADM;
  -- 2 Tablespace Quotas for EDBADM 
  # 授予用户使用表空间的权限
  ALTER USER EDBADM QUOTA UNLIMITED ON EDW_DWT_GLS_DAT;
  ALTER USER EDBADM QUOTA UNLIMITED ON EDW_DWT_GLS_IDX;
  -- 15 Object Privileges for EDBADM 
  GRANT INSERT, SELECT, UPDATE ON EDWTEAM.DWR_LOT_DEFECT_HIS_M_GREA TO EDBADM;
  GRANT EXECUTE, READ, WRITE ON DIRECTORY EDWDUMP TO EDBADM;
  GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON