一、测试环境
| 名称 | 描述 |
|---|---|
| 操作系统 | Red Hat Enterprise Linux Server release 7.9 (Maipo) |
| oracle版本 | Release 11.2.0.1.0 - Production |
| TPC-DS工具版本 | V3.2.0 |
二、安装TPC-DS
（1）TPC-DS工具下载地址
1、TPC-DS工具官方下载地址
2、百度云盘链接
链接：https://pan.baidu.com/s/1dkfoDX_D1p4dAQkWKcPoqA
提取码：ucah
（2）解压工具包，进入到DSGen-software-code-3.2.0rc1/tools目录。
（3）执行make命令。
三、生成测试数据
（1）创建数据文件目录/opt/tpcds_data。
（2）生成测试数据命令：
./dsdgen -sc 1 -dir /opt/tpcds_data -TERMINATE N
参数介绍：
General Options
===============
ABREVIATION = <s> -- build table with abreviation <s>
DIR = <s> -- generate tables in directory <s>
HELP = <n> -- display this message
PARAMS = <s> -- read parameters from file <s>
QUIET = [Y|N] -- disable all output to stdout/stderr
SCALE = <n> -- volume of data to generate in GB
TABLE = <s> -- build only table <s>
UPDATE = <n> -- generate update data set <n>
VERBOSE = [Y|N] -- enable verbose output
PARALLEL = <n> -- build data in <n> separate chunks
CHILD = <n> -- generate <n>th chunk of the parallelized data
RELEASE = [Y|N] -- display the release information
_FILTER = [Y|N] -- output data to stdout
VALIDATE = [Y|N] -- produce rows for data validation
Advanced Options
===============
DELIMITER = <s> -- use <s> as output field separator
DISTRIBUTIONS = <s> -- read distributions from file <s>
FORCE = [Y|N] -- over-write data files without prompting
SUFFIX = <s> -- use <s> as output file suffix
TERMINATE = [Y|N] -- end each record with a field delimiter
VCOUNT = <n> -- set number of validation rows to be produced
VSUFFIX = <s> -- set file suffix for data validation
RNGSEED = <n> -- set RNG seed
（3）查看/opt/tpcds_data下是否生成数据文件
-rwxrwxrwx 1 root root 1885 Apr 8 11:27 call_center.dat
-rwxrwxrwx 1 root root 1620074 Apr 8 11:27 catalog_page.dat
-rwxrwxrwx 1 root root 21234304 Apr 8 11:27 catalog_returns.dat
-rwxrwxrwx 1 root root 294468836 Apr 8 11:27 catalog_sales.dat
-rwxrwxrwx 1 root root 5452165 Apr 8 11:27 customer_address.dat
-rwxrwxrwx 1 root root 13109372 Apr 8 11:27 customer.dat
-rwxrwxrwx 1 root root 78739296 Apr 8 11:28 customer_demographics.dat
-rwxrwxrwx 1 root root 10244389 Apr 8 11:28 date_dim.dat
-rwxrwxrwx 1 root root 64 Apr 8 11:29 dbgen_version.dat
-rwxrwxrwx 1 root root 144453 Apr 8 11:28 household_demographics.dat
-rwxrwxrwx 1 root root 308 Apr 8 11:28 income_band.dat
-rwxrwxrwx 1 root root 224675139 Apr 8 11:28 inventory.dat
-rwxrwxrwx 1 root root 5033899 Apr 8 11:28 item.dat
-rwxrwxrwx 1 root root 36933 Apr 8 11:28 promotion.dat
-rwxrwxrwx 1 root root 1304 Apr 8 11:28 reason.dat
-rwxrwxrwx 1 root root 1093 Apr 8 11:28 ship_mode.dat
-rwxrwxrwx 1 root root 3143 Apr 8 11:28 store.dat
-rwxrwxrwx 1 root root 32422491 Apr 8 11:28 store_returns.dat
-rwxrwxrwx 1 root root 385565005 Apr 8 11:28 store_sales.dat
-rwxrwxrwx 1 root root 5021380 Apr 8 11:29 time_dim.dat
-rwxrwxrwx 1 root root 580 Apr 8 11:29 warehouse.dat
-rwxrwxrwx 1 root root 5716 Apr 8 11:29 web_page.dat
-rwxrwxrwx 1 root root 9734473 Apr 8 11:29 web_returns.dat
-rwxrwxrwx 1 root root 146158290 Apr 8 11:29 web_sales.dat
-rwxrwxrwx 1 root root 8741 Apr 8 15:28 web_site.dat
三、Oracle创建TPC-DS相关表
sqlplus czg/123456789 @/opt/DSGen-software-code-3.2.0rc1/tools/tpcds.sql
四、SQLLDR装载数据
由于数据表较多我们这里只演示web_site表的数据装载。
（1）编辑控制文件web_site.ctl
load data
infile '/opt/tpcds_data/web_site.dat'
INTO TABLE web_site
truncate
fields terminated by "|"
optionally enclosed by '"'
trailing nullcols
(WEB_SITE_SK,
WEB_SITE_ID,
WEB_REC_START_DATE "to_date(:WEB_REC_START_DATE,'YYYY-MM-DD')",
WEB_REC_END_DATE "to_date(:WEB_REC_START_DATE,'YYYY-MM-DD')",
WEB_NAME,
WEB_OPEN_DATE_SK,
WEB_CLOSE_DATE_SK,
WEB_CLASS,
WEB_MANAGER,
WEB_MKT_ID,
WEB_MKT_CLASS,
WEB_MKT_DESC,
WEB_MARKET_MANAGER,
WEB_COMPANY_ID,
WEB_COMPANY_NAME,
WEB_STREET_NUMBER,
WEB_STREET_NAME,
WEB_STREET_TYPE,
WEB_SUITE_NUMBER,
WEB_CITY,
WEB_COUNTY,
WEB_STATE,
WEB_ZIP,
WEB_COUNTRY,
WEB_GMT_OFFSET,
WEB_TAX_PERCENTAGE)
（2）SQLLDR数据加载
[oracle@localhost ctl]$ sqlldr userid=czg/123456789 control=/home/oracle/ctl/web_site.ctl bad=/home/oracle/bad_file/bad.txt log=/home/oracle/log_file/log.txt
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Apr 8 15:29:37 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 30
（3）查看数据是否装载成功
SQL> select count(*) from web_site;
COUNT(*)
----------
30
