ORACLE 19C安装测试数据库

安装测试数据库

1. 目标及下载

1. 数据库里面只安装HR用户, 19C也提供安装脚本；
2. 19C不在提供安装脚本，需要从GITHUB上下载；
   下载地址：https://github.com/oracle/db-sample-schemas/releases/tag/v19c
   直接下载：https://codeload.github.com/oracle/db-sample-schemas/zip/refs/tags/v19c
3. 下载后的文件：db-sample-schemas-19c.zip

1.1 安装包内容

解压后查看readme.md有内容介绍和安装步骤：

The schemas are:

1. HR: Human Resources – 人力资源
2. OE: Order Entry – 订单录入
3. PM: Product Media – 产品物资
4. IX: Information Exchange – 信息交换
5. SH: Sales History – 销售历史
6. BI: Business Intelligence – 商务智能

2. 安装

2.1 查看数据库情况

-- 1. 查看都有哪些表空间
select tablespace_name from dba_tablespaces;

-- 2. 查看 user 表空间中有无表存在，如果有表，则最好创建一个新的表空间
select table_name from dba_tables a where a.tablespace_name = 'USERS';

-- 3. 查看表空间, 确定数据库文件dbf存放的路径
select a.tablespace_name, a.file_name, a.bytes/1024/1024 size_m from dba_data_files a  order by a.tablespace_name;

-- 4. 创建使用的表空间: DB_SAMPLE
create tablespace DB_SAMPLE datafile '/data1/opt/oracle/app/oracle/oradata/ORCL/datafile/db_sample.dbf'
size 32M AUTOEXTEND on NEXT 32M MAXSIZE 1024M;

2.2 创建和unlock用户

-- 1. 安装时需要如下用户，否则则会出错：
specify password for SYSTEM as parameter 1:
specify password for SYS as parameter 2:

specify password for HR as parameter 3:
specify password for OE as parameter 4:
specify password for PM as parameter 5:
specify password for IX as parameter 6:
specify password for SH as parameter 7:
specify password for BI as parameter 8:


-- 根据查看的信息，可以把测试表都存放在表空间 DB_SAMPLE 中
-- 2. 创建用户, 密码都是: admin123, sys and system password is oracle
create user HR identified by hrpw default tablespace DB_SAMPLE temporary tablespace temp; 
create user OE identified by oepw default tablespace DB_SAMPLE temporary tablespace temp; 
create user PM identified by pmpw default tablespace DB_SAMPLE temporary tablespace temp; 
create user IX identified by ixpw default tablespace DB_SAMPLE temporary tablespace temp; 
create user SH identified by shpw default tablespace DB_SAMPLE temporary tablespace temp; 
create user BI identified by bipw default tablespace DB_SAMPLE temporary tablespace temp; 

-- 3. 解锁system用户
alter user  system account unlock;

-- 4. 查看已创建的用户和状态
col username format A32;
col account_status format A32;

select username, account_status from dba_users 
where username in ('SYSTEM', 'SYS', 'HR', 'OE', 'PM', 'IX', 'SH', 'BI')
;

2.3 设置环境

# 1. 进入到解压后的目录中 db-sample-schema
cd /home/oracle/apex_soft/db-sample-schemas-19c

# 2. 设置环境, 命令行下执行
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat 

# 3. 设置oralce环境, 因为脚本用到了sqlldr
source /usr/local/bin/oraenv

2.4 执行安装

# 进入到解压后的目录中 db-sample-schema
cd /home/oracle/apex_soft/db-sample-schemas-19c

# 登录oracle，执行命令， 
# 注意：
# 1) oracle oracle hrpw oepw pmpw ixpw shpw bipw 分别是用户 system,sys,hr,oe,pm,ixpw,sh,bi的密码
# 2) DB_SAMPLE -- 表空间
# 3) temp       -- 临时表空间
# 4) 不用创建log目录，会自动创建
sqlplus /as sysdba
@mksample oracle oracle hrpw oepw pmpw ixpw shpw bipw DB_SAMPLE temp /home/oracle/log/ localhost:1521/orcl

# 安装后查看安装日志

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UtYH1AHH-1646901304176)(.\img\ORACLE 19C测试数据库\image-20220309173707650.png)]

3. 测试

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-THXuLIaD-1646901304179)(img/ORACLE 19C测试数据库/image-20220310092532996.png)]

4. 表结构

-- 查询表、属主和注释
select a.owner, a.table_name, b.comments 
  from dba_tables a, ALL_TAB_COMMENTS b 
 where a.table_name = b.table_name
   and a.owner in ('HR', 'OE', 'PM', 'IX', 'SH', 'BI')
order by 1, 2;

4.1 HR

序号	OWNER	TABLE_NAME	COMMENTs
1	HR	COUNTRIES	country table. Contains 25 rows. References with locations table.
2	HR	COUNTRIES	country dimension table (snowflake)
3	HR	DEPARTMENTS	Departments table that shows details of departments where employees work. Contains 27 rows; references with locations, employees, and job_history tables.
4	HR	EMPLOYEES	employees table. Contains 107 rows. References with departments, jobs, job_history tables. Contains a self reference.
5	HR	JOBS	jobs table with job titles and salary ranges. Contains 19 rows. References with employees and job_history table.
6	HR	JOB_HISTORY	Table that stores job history of the employees. If an employee changes departments within the job or changes jobs within the department, new rows get inserted into this table with old job information of the employee. Contains a complex primary key: employee_id+start_date. Contains 25 rows. References with jobs, employees, and departments tables.
7	HR	LOCATIONS	Locations table that contains specific address of a specific office, warehouse, and/or production site of a company. Does not store addresses / locations of customers. Contains 23 rows; references with the departments and countries tables.

4.2 OE

序号	OWNER	TABLE_NAME	COMMENTs
1	OE	CUSTOMERS	dimension table
2	OE	CUSTOMERS	Contains customers data either entered by an employee or by the customer him/herself over the Web.
3	OE	INVENTORIES	Tracks availability of products by product_it and warehouse_id.
4	OE	ORDERS	Contains orders entered by a salesperson as well as over the Web.
5	OE	ORDER_ITEMS	Example of many-to-many resolution.
6	OE	PRODUCT_DESCRIPTIONS	Non-industry-specific design, allows selection of NLS-setting-specific data derived at runtime, for example using the products view.
7	OE	PRODUCT_INFORMATION	Non-industry-specific data in various categories.
8	OE	PROMOTIONS
9	OE	PROMOTIONS	dimension table without a PK-FK relationship with the facts table, to show outer join functionality
10	OE	WAREHOUSES	Warehouse data unspecific to any industry.

4.3 PM

序号	OWNER	TABLE_NAME	COMMENTs
1	PM	PRINT_MEDIA

4.4 IX

序号	OWNER	TABLE_NAME	COMMENTs
1	IX	AQ$_ORDERS_QUEUETABLE_G
2	IX	AQ$_ORDERS_QUEUETABLE_H
3	IX	AQ$_ORDERS_QUEUETABLE_I
4	IX	AQ$_ORDERS_QUEUETABLE_L
5	IX	AQ$_ORDERS_QUEUETABLE_S
6	IX	AQ$_ORDERS_QUEUETABLE_T
7	IX	AQ$_STREAMS_QUEUE_TABLE_C
8	IX	AQ$_STREAMS_QUEUE_TABLE_G
9	IX	AQ$_STREAMS_QUEUE_TABLE_H
10	IX	AQ$_STREAMS_QUEUE_TABLE_I
11	IX	AQ$_STREAMS_QUEUE_TABLE_L
12	IX	AQ$_STREAMS_QUEUE_TABLE_S
13	IX	AQ$_STREAMS_QUEUE_TABLE_T
14	IX	ORDERS_QUEUETABLE
15	IX	STREAMS_QUEUE_TABLE

4.5 SH

序号	OWNER	TABLE_NAME	COMMENTs
1	SH	CHANNELS	small dimension table
2	SH	COSTS
3	SH	COUNTRIES	country dimension table (snowflake)
4	SH	COUNTRIES	country table. Contains 25 rows. References with locations table.
5	SH	CUSTOMERS	dimension table
6	SH	CUSTOMERS	Contains customers data either entered by an employee or by the customer him/herself over the Web.
7	SH	DR$SU{P}_{T}EX{T}_{I}DX$I
8	SH	DR$SU{P}_{T}EX{T}_{I}DX$K
9	SH	DR$SU{P}_{T}EX{T}_{I}DX$N
10	SH	DR$SU{P}_{T}EX{T}_{I}DX$U
11	SH	PRODUCTS	dimension table
12	SH	PRODUCTS	This view joins product_information and product_descriptions, using NLS settings to pick the appropriate language-specific product description.
13	SH	PROMOTIONS	dimension table without a PK-FK relationship with the facts table, to show outer join functionality
14	SH	PROMOTIONS
15	SH	SALES	facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys
16	SH	SALES_TRANSACTIONS_EXT
17	SH	SUPPLEMENTARY_DEMOGRAPHICS
18	SH	TIMES	Time dimension table to support multiple hierarchies and materialized views

4.6 BI

BI用户主要是新增和使用数据;