# 示例：存储设计

# 表空间设计
CREATE TABLESPACE users DATAFILE ‘/oracle/app/oracle/oradata/ORCL/users01.dbf’ SIZE 100M AUTOEXTEND ON;
CREATE TABLESPACE indexes DATAFILE ‘/oracle/app/oracle/oradata/ORCL/indexes01.dbf’ SIZE 100M AUTOEXTEND ON;
CREATE TABLESPACE lob_data DATAFILE ‘/oracle/app/oracle/oradata/ORCL/lob01.dbf’ SIZE 100M AUTOEXTEND ON;

# 表存储设置
CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE,
phone VARCHAR2(20),
address VARCHAR2(255),
created_date DATE DEFAULT SYSDATE
) TABLESPACE users;

# 索引存储设置
CREATE INDEX customers_email_idx ON customers(email) TABLESPACE indexes;

# LOB存储设置
CREATE TABLE products (
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
description CLOB,
price NUMBER(10,2) NOT NULL,
stock_quantity NUMBER NOT NULL,
created_date DATE DEFAULT SYSDATE
) TABLESPACE users
LOB (description) STORE AS SECUREFILE (TABLESPACE lob_data);

# 示例：索引设计

# 主键索引
CREATE UNIQUE INDEX customers_pk ON customers(customer_id) TABLESPACE indexes;

# 外键索引
CREATE INDEX orders_customer_idx ON orders(customer_id) TABLESPACE indexes;
CREATE INDEX order_items_order_idx ON order_items(order_id) TABLESPACE indexes;
CREATE INDEX order_items_product_idx ON order_items(product_id) TABLESPACE indexes;

# 复合索引
CREATE INDEX orders_customer_date_idx ON orders(customer_id, order_date) TABLESPACE indexes;

# 位图索引（适合低 cardinality列）
CREATE BITMAP INDEX orders_status_idx ON orders(status) TABLESPACE indexes;

# 示例：分区设计

# 范围分区
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER NOT NULL,
order_date DATE DEFAULT SYSDATE,
total_amount NUMBER(10,2) NOT NULL,
status VARCHAR2(20) DEFAULT ‘PENDING’
) PARTITION BY RANGE (order_date) (
PARTITION p2023q1 VALUES LESS THAN (TO_DATE(‘2023-04-01’, ‘YYYY-MM-DD’)),
PARTITION p2023q2 VALUES LESS THAN (TO_DATE(‘2023-07-01’, ‘YYYY-MM-DD’)),
PARTITION p2023q3 VALUES LESS THAN (TO_DATE(‘2023-10-01’, ‘YYYY-MM-DD’)),
PARTITION p2023q4 VALUES LESS THAN (TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’))
);

# 列表分区
CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
country VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE
) PARTITION BY LIST (country) (
PARTITION p_usa VALUES (‘USA’),
PARTITION p_canada VALUES (‘Canada’),
PARTITION p_europe VALUES (‘UK’, ‘Germany’, ‘France’),
PARTITION p_asia VALUES (‘China’, ‘Japan’, ‘India’)
);

# 哈希分区
CREATE TABLE order_items (
order_item_id NUMBER PRIMARY KEY,
order_id NUMBER NOT NULL,
product_id NUMBER NOT NULL,
quantity NUMBER NOT NULL,
unit_price NUMBER(10,2) NOT NULL
) PARTITION BY HASH (order_id) PARTITIONS 4;

# 示例：参数配置

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

# 进程参数
ALTER SYSTEM SET processes = 300 SCOPE=SPFILE;
ALTER SYSTEM SET sessions = 335 SCOPE=SPFILE;

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

# 日志参数
ALTER SYSTEM SET log_buffer = ’16M’ SCOPE=SPFILE;
ALTER SYSTEM SET log_checkpoint_interval = 10000 SCOPE=SPFILE;

# 示例：创建数据库

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

# 示例：创建表空间和用户

# 创建表空间
CREATE TABLESPACE users DATAFILE ‘/oracle/app/oracle/oradata/ORCL/users01.dbf’ SIZE 100M AUTOEXTEND ON;
CREATE TABLESPACE indexes DATAFILE ‘/oracle/app/oracle/oradata/ORCL/indexes01.dbf’ SIZE 100M AUTOEXTEND ON;

# 创建用户
CREATE USER app_user IDENTIFIED BY Password123 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE SYNONYM TO app_user;
GRANT UNLIMITED TABLESPACE TO app_user;

# 示例：创建表和索引

# 连接到用户
CONNECT app_user/Password123;

# 创建客户表
CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE,
phone VARCHAR2(20),
address VARCHAR2(255),
created_date DATE DEFAULT SYSDATE
);

# 创建产品表
CREATE TABLE products (
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
description VARCHAR2(500),
price NUMBER(10,2) NOT NULL,
stock_quantity NUMBER NOT NULL,
created_date DATE DEFAULT SYSDATE
);

# 创建订单表
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER NOT NULL,
order_date DATE DEFAULT SYSDATE,
total_amount NUMBER(10,2) NOT NULL,
status VARCHAR2(20) DEFAULT ‘PENDING’,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

# 创建订单详情表
CREATE TABLE order_items (
order_item_id NUMBER PRIMARY KEY,
order_id NUMBER NOT NULL,
product_id NUMBER NOT NULL,
quantity NUMBER NOT NULL,
unit_price NUMBER(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

# 创建索引
CREATE INDEX customers_email_idx ON customers(email);
CREATE INDEX orders_customer_idx ON orders(customer_id);
CREATE INDEX order_items_order_idx ON order_items(order_id);
CREATE INDEX order_items_product_idx ON order_items(product_id);

# 示例：加载初始数据

# 插入客户数据
INSERT INTO customers (customer_id, first_name, last_name, email, phone, address) VALUES
(1, ‘John’, ‘Doe’, ‘john.doe@fgedu.net.cn’, ‘555-1234’, ‘123 Main St’),
(2, ‘Jane’, ‘Smith’, ‘jane.smith@fgedu.net.cn’, ‘555-5678’, ‘456 Oak Ave’),
(3, ‘Bob’, ‘Johnson’, ‘bob.johnson@fgedu.net.cn’, ‘555-9012’, ‘789 Pine Rd’);

# 插入产品数据
INSERT INTO products (product_id, product_name, description, price, stock_quantity) VALUES
(1, ‘Laptop’, ‘High performance laptop’, 999.99, 50),
(2, ‘Smartphone’, ‘Latest smartphone’, 699.99, 100),
(3, ‘Tablet’, ‘Portable tablet’, 399.99, 75);

# 插入订单数据
INSERT INTO orders (order_id, customer_id, order_date, total_amount, status) VALUES
(1, 1, SYSDATE, 999.99, ‘COMPLETED’),
(2, 2, SYSDATE, 1399.98, ‘PENDING’),
(3, 3, SYSDATE, 399.99, ‘PROCESSING’);

# 插入订单详情数据
INSERT INTO order_items (order_item_id, order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 1, 999.99),
(2, 2, 2, 2, 699.99),
(3, 3, 3, 1, 399.99);

# 提交事务
COMMIT;

# 示例：测试数据库功能

# 查询客户数据
SELECT * FROM customers;

# 查询产品数据
SELECT * FROM products;

# 查询订单数据
SELECT * FROM orders;

# 查询订单详情数据
SELECT * FROM order_items;

# 测试连接查询
SELECT c.first_name, c.last_name, o.order_id, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

# 测试聚合查询
SELECT status, COUNT(*) AS order_count, SUM(total_amount) AS total_revenue
FROM orders
GROUP BY status;