# 示例：创建数据仓库数据库

# 使用DBCA创建数据库
$ dbca -silent -createDatabase \
-templateName Data_Warehouse.dbc \
-gdbname dwdb \
-sid dwdb \
-responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword Password123 \
-systemPassword Password123 \
-createAsContainerDatabase false \
-databaseType DATA_WAREHOUSE \
-automaticMemoryManagement true \
-totalMemory 8192 \
-storageType FS \
-datafileDestination /oracle/app/oracle/oradata \
-redoLogFileSize 100 \
-emConfiguration NONE \
-ignorePreReqs

# 示例：创建表空间

# 创建维度表空间
CREATE TABLESPACE dim_tbs DATAFILE ‘/oracle/app/oracle/oradata/DWDB/dim01.dbf’ SIZE 500M AUTOEXTEND ON;

# 创建事实表空间
CREATE TABLESPACE fact_tbs DATAFILE ‘/oracle/app/oracle/oradata/DWDB/fact01.dbf’ SIZE 2048M AUTOEXTEND ON;

# 创建索引表空间
CREATE TABLESPACE idx_tbs DATAFILE ‘/oracle/app/oracle/oradata/DWDB/idx01.dbf’ SIZE 500M AUTOEXTEND ON;

# 示例：创建维度表

# 时间维度表
CREATE TABLE dim_time (
time_id NUMBER PRIMARY KEY,
date_value DATE NOT NULL,
day_of_week NUMBER,
day_name VARCHAR2(20),
week_number NUMBER,
month_number NUMBER,
month_name VARCHAR2(20),
quarter NUMBER,
year NUMBER
) TABLESPACE dim_tbs;

# 产品维度表
CREATE TABLE dim_product (
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
product_category VARCHAR2(50),
product_brand VARCHAR2(50),
product_price NUMBER(10,2)
) TABLESPACE dim_tbs;

# 客户维度表
CREATE TABLE dim_customer (
customer_id NUMBER PRIMARY KEY,
customer_name VARCHAR2(100) NOT NULL,
customer_city VARCHAR2(50),
customer_region VARCHAR2(50),
customer_country VARCHAR2(50)
) TABLESPACE dim_tbs;

# 销售维度表
CREATE TABLE dim_fgfgfgsales_channel (
channel_id NUMBER PRIMARY KEY,
channel_name VARCHAR2(50) NOT NULL,
channel_type VARCHAR2(50)
) TABLESPACE dim_tbs;

# 示例：创建事实表

# 销售事实表
CREATE TABLE fact_fgfgfgsales (
fgfgfgsales_id NUMBER PRIMARY KEY,
time_id NUMBER NOT NULL,
product_id NUMBER NOT NULL,
customer_id NUMBER NOT NULL,
channel_id NUMBER NOT NULL,
fgfgfgsales_quantity NUMBER NOT NULL,
fgfgfgsales_amount NUMBER(10,2) NOT NULL,
FOREIGN KEY (time_id) REFERENCES dim_time(time_id),
FOREIGN KEY (product_id) REFERENCES dim_product(product_id),
FOREIGN KEY (customer_id) REFERENCES dim_customer(customer_id),
FOREIGN KEY (channel_id) REFERENCES dim_fgfgfgsales_channel(channel_id)
) TABLESPACE fact_tbs
PARTITION BY RANGE (time_id) (
PARTITION p2023 VALUES LESS THAN (20240000),
PARTITION p2024 VALUES LESS THAN (20250000),
PARTITION p2025 VALUES LESS THAN (20260000)
);

# 示例：创建索引

# 维度表索引
CREATE INDEX dim_time_date_idx ON dim_time(date_value) TABLESPACE idx_tbs;
CREATE INDEX dim_product_category_idx ON dim_product(product_category) TABLESPACE idx_tbs;
CREATE INDEX dim_customer_region_idx ON dim_customer(customer_region) TABLESPACE idx_tbs;

# 事实表索引
CREATE INDEX fact_fgfgfgsales_time_idx ON fact_fgfgfgsales(time_id) TABLESPACE idx_tbs;
CREATE INDEX fact_fgfgfgsales_product_idx ON fact_fgfgfgsales(product_id) TABLESPACE idx_tbs;
CREATE INDEX fact_fgfgfgsales_customer_idx ON fact_fgfgfgsales(customer_id) TABLESPACE idx_tbs;
CREATE INDEX fact_fgfgfgsales_channel_idx ON fact_fgfgfgsales(channel_id) TABLESPACE idx_tbs;

# 复合索引
CREATE INDEX fact_fgfgfgsales_time_product_idx ON fact_fgfgfgsales(time_id, product_id) TABLESPACE idx_tbs;

# 示例：配置数据库参数

# 内存参数
ALTER SYSTEM SET sga_target = ‘4G’ SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = ‘2G’ SCOPE=SPFILE;

# 并行参数
ALTER SYSTEM SET parallel_max_servers = 16 SCOPE=SPFILE;
ALTER SYSTEM SET parallel_degree_policy = ‘AUTO’ SCOPE=SPFILE;

# 优化器参数
ALTER SYSTEM SET optimizer_mode = ‘ALL_ROWS’ SCOPE=SPFILE;
ALTER SYSTEM SET optimizer_use_invisible_indexes = TRUE SCOPE=SPFILE;

# 存储参数
ALTER SYSTEM SET db_files = 200 SCOPE=SPFILE;
ALTER SYSTEM SET undo_tablespace = ‘UNDOTBS1’ SCOPE=SPFILE;

# 示例：使用ODI创建ETL流程

# 1. 配置ODI
– 安装ODI
– 配置主存储库
– 配置工作存储库
– 配置数据源

# 2. 创建模型
– 创建源模型：连接源系统
– 创建目标模型：连接数据仓库
– 导入元数据：导入表结构

# 3. 创建映射
– 创建映射：从源到目标的映射
– 配置转换：配置数据转换规则
– 配置过滤器：配置数据过滤器
– 配置聚合：配置数据聚合

# 4. 创建包
– 创建包：组织ETL流程
– 添加步骤：添加ETL步骤
– 配置依赖：配置步骤依赖关系
– 配置错误处理：配置错误处理

# 5. 执行ETL
– 执行包：执行ETL包
– 监控执行：监控执行状态
– 查看日志：查看执行日志
– 处理错误：处理执行错误

# 示例：使用SQL*Loader加载数据

# 1. 创建控制文件
$ cat > dim_time.ctl << EOF LOAD DATA INFILE 'dim_time.csv' INTO TABLE dim_time FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( time_id, date_value DATE 'YYYY-MM-DD' , day_of_week, day_name, week_number, month_number, month_name, quarter, year ) EOF # 2. 准备数据文件 $ cat> dim_time.csv << EOF 20230101,2023-01-01,1,Monday,1,1,January,1,2023 20230102,2023-01-02,2,Tuesday,1,1,January,1,2023 20230103,2023-01-03,3,Wednesday,1,1,January,1,2023 EOF # 3. 执行SQL*Loader $ sqlldr userid=dw_user/Password123 control=dim_time.ctl log=dim_time.log # 示例：使用External Tables加载数据 # 1. 创建外部表 CREATE TABLE dim_time_ext ( time_id NUMBER, date_value DATE, day_of_week NUMBER, day_name VARCHAR2(20), week_number NUMBER, month_number NUMBER, month_name VARCHAR2(20), quarter NUMBER, year NUMBER ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL (time_id, date_value DATE 'YYYY-MM-DD' , day_of_week, day_name, week_number, month_number, month_name, quarter, year) ) LOCATION ('dim_time.csv') ); # 2. 加载数据 INSERT INTO dim_time SELECT * FROM dim_time_ext; # 示例：使用Data Pump导入数据 # 1. 导出源数据 $ expdp source_user/Password123@source_db TABLES=source_fgfgfgsales DIRECTORY=dump_dir DUMPFILE=fgfgfgsales.dmp LOGFILE=exp_fgfgfgsales.log # 2. 导入到数据仓库 $ impdp dw_user/Password123@dwdb TABLES=source_fgfgfgsales DIRECTORY=dump_dir DUMPFILE=fgfgfgsales.dmp LOGFILE=imp_fgfgfgsales.log REMAP_TABLE=source_fgfgfgsales:fact_fgfgfgsales