# 1. 设置环境变量
export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# 2. 创建初始化参数文件
$ORACLE_HOME/dbs/orainitORCL.ora

# 3. 创建密码文件
orapwd FILE=$ORACLE_HOME/dbs/orapwORCL password=SysPassword123 entries=30

# 4. 创建目录
mkdir -p /u01/app/oracle/oradata/ORCL
mkdir -p /u01/app/oracle/fast_recovery_area/ORCL

# 5. 使用 DBCA 图形化创建（推荐）
dbca

-- 启动数据库（3 种模式）
STARTUP NOMOUNT;     -- 仅启动实例，读取参数文件
STARTUP MOUNT;       -- 加载数据库，打开控制文件
STARTUP OPEN;        -- 打开数据库（默认）
STARTUP FORCE;       -- 强制重启

-- 关闭数据库
SHUTDOWN IMMEDIATE;  -- 立即关闭，回滚未提交事务（推荐）
SHUTDOWN TRANSACTIONAL; -- 等待事务完成
SHUTDOWN NORMAL;     -- 等待所有用户断开（不推荐）
SHUTDOWN ABORT;      -- 强制终止（紧急情况，恢复需更长）

-- 只读打开（DataGuard 场景）
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

-- 查看数据库基本信息
SELECT 
    name,
    database_role,
    open_mode,
    log_mode,
    flashback_on,
    guard_status
FROM v$database;

-- 查看实例信息
SELECT 
    instance_name,
    host_name,
    version,
    status,
    startup_time,
    database_status
FROM v$instance;

-- 查看连接会话
SELECT 
    sid,
    serial#,
    username,
    status,
    program,
    logon_time
FROM v$session
WHERE username IS NOT NULL;

┌─────────────────────────────────────────────────────────────────┐
│                     Oracle 表空间架构                              │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  永久表空间 (Permanent)                                          │
│  ├── SYSTEM      - 数据字典（不能脱机）                           │
│  ├── SYSAUX      - 辅助系统表空间                               │
│  ├── USERS       - 用户数据                                     │
│  └── 自定义表空间  - 业务数据                                     │
│                                                                 │
│  临时表空间 (Temporary)                                          │
│  └── TEMP        - 排序、Hash join、临时结果集                    │
│                                                                 │
│  UNDO 表空间                                                     │
│  └── UNDOTBS1    - 回滚段，事务一致性                            │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

-- 基础表空间
CREATE TABLESPACE users_ts
DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
NOLOGGING;  -- 不记录重做日志（快速加载）

-- 大文件表空间（适合海量数据）
CREATE BIGFILE TABLESPACE bigdata_ts
DATAFILE '/u01/app/oracle/oradata/ORCL/bigdata01.dbf'
SIZE 100G
AUTOEXTEND ON NEXT 10G MAXSIZE 32T;

-- 加密表空间
CREATE TABLESPACE secure_ts
DATAFILE '/u01/app/oracle/oradata/ORCL/secure01.dbf'
SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT);

-- 临时表空间
CREATE TEMPORARY TABLESPACE temp_ts
TEMPFILE '/u01/app/oracle/oradata/ORCL/temp01.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

-- UNDO 表空间
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs201.dbf'
SIZE 1G
AUTOEXTEND ON;

-- 修改表空间大小
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' RESIZE 200M;

-- 添加数据文件
ALTER TABLESPACE users_ts ADD DATAFILE 
'/u01/app/oracle/oradata/ORCL/users02.dbf' SIZE 100M;

-- 收缩数据文件（释放空间）
ALTER TABLESPACE users_ts SHRINK SPACE KEEP 100M;

-- 脱机/联机表空间
ALTER TABLESPACE users_ts OFFLINE NORMAL;
ALTER TABLESPACE users_ts ONLINE;

-- 重命名数据文件
ALTER TABLESPACE users_ts RENAME DATAFILE 
'/u01/app/oracle/oradata/ORCL/users01.dbf' TO 
'/u01/app/oracle/oradata/ORCL/users_new01.dbf';

-- 删除表空间
DROP TABLESPACE users_ts INCLUDING CONTENTS AND DATAFILES;

-- 查看表空间使用情况
SELECT 
    tablespace_name,
    ROUND(tablespace_size/1024/1024/1024, 2) AS size_gb,
    ROUND(tablespace_used_space/1024/1024/1024, 2) AS used_gb,
    ROUND(tablespace_used_percent, 2) AS usage_pct
FROM dba_tablespace_usage_metrics
ORDER BY usage_pct DESC;

-- 详细使用情况（包含空闲空间）
SELECT 
    a.tablespace_name,
    a.total_size_gb,
    a.total_size_gb - b.free_size_gb AS used_size_gb,
    b.free_size_gb,
    ROUND((1 - b.free_size_gb/a.total_size_gb) * 100, 2) AS usage_pct
FROM (
    SELECT tablespace_name, SUM(bytes)/1024/1024/1024 AS total_size_gb
    FROM dba_data_files
    GROUP BY tablespace_name
) a,
(
    SELECT tablespace_name, SUM(bytes)/1024/1024/1024 AS free_size_gb
    FROM dba_free_space
    GROUP BY tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name;

-- 基础建表
CREATE TABLE employees (
    employee_id    NUMBER(10) PRIMARY KEY,
    first_name    VARCHAR2(50) NOT NULL,
    last_name     VARCHAR2(50) NOT NULL,
    email         VARCHAR2(100) UNIQUE,
    phone_number  VARCHAR2(20),
    hire_date     DATE DEFAULT SYSDATE,
    job_id        VARCHAR2(20),
    salary        NUMBER(10,2) CHECK (salary > 0),
    commission_pct NUMBER(5,2),
    manager_id    NUMBER(10),
    department_id NUMBER(10),
    created_at    TIMESTAMP DEFAULT SYSTIMESTAMP,
    updated_at    TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 使用子查询建表
CREATE TABLE emp_high_salary AS
SELECT * FROM employees WHERE salary > 10000;

-- 使用 CTAS 创建表
CREATE TABLE dept_salary_summary AS
SELECT 
    department_id,
    COUNT(*) AS emp_count,
    SUM(salary) AS total_salary,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- 创建全局临时表
CREATE GLOBAL TEMPORARY TABLE temp_results (
    session_id VARCHAR2(50),
    result_data CLOB
) ON COMMIT DELETE ROWS;  -- 事务结束删除
-- 或 ON COMMIT PRESERVE ROWS;  -- 会话结束删除

-- 添加列
ALTER TABLE employees ADD (
    bonus NUMBER(10,2),
    profile_pic BLOB
);

-- 修改列
ALTER TABLE employees MODIFY (
    email VARCHAR2(200) NOT NULL,  -- 扩大并设为非空
    salary NUMBER(12,2)             -- 扩大精度
);

-- 重命名列
ALTER TABLE employees RENAME COLUMN email TO email_address;

-- 删除列（Oracle 10g+）
ALTER TABLE employees DROP COLUMN profile_pic;

-- 设置不可用列（标记删除，节省空间）
ALTER TABLE employees SET UNUSED COLUMN bonus;
ALTER TABLE employees DROP UNUSED COLUMNS;

-- 添加主键
ALTER TABLE employees ADD CONSTRAINT pk_employee PRIMARY KEY (employee_id);

-- 重命名表
RENAME employees TO staff_members;

-- 压缩表（节约存储空间）
CREATE TABLE compressed_emp (
    employee_id NUMBER(10),
    name VARCHAR2(100)
) COMPRESS FOR OLTP;

-- 压缩现有表
ALTER TABLE employees COMPRESS FOR OLTP;

-- 启用行移动（闪回表、分区操作必需）
ALTER TABLE employees ENABLE ROW MOVEMENT;

-- 只读表
ALTER TABLE employees READ ONLY;
ALTER TABLE employees READ WRITE;

-- 表注释
COMMENT ON TABLE employees IS '员工信息表';
COMMENT ON COLUMN employees.email IS '员工邮箱地址';

-- 创建表时定义约束
CREATE TABLE orders (
    order_id     NUMBER(10) PRIMARY KEY,
    customer_id  NUMBER(10) NOT NULL,
    order_date   DATE NOT NULL,
    total_amount NUMBER(12,2) CHECK (total_amount >= 0),
    status       VARCHAR2(20) CHECK (status IN ('PENDING', 'SHIPPED', 'DELIVERED')),
    
    -- 外键约束
    CONSTRAINT fk_orders_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    
    -- 唯一约束
    CONSTRAINT uk_orders_order_no 
        UNIQUE (customer_id, order_date, order_seq)
);

-- 独立创建约束
ALTER TABLE employees ADD CONSTRAINT 
    uk_employees_email UNIQUE (email);

ALTER TABLE employees ADD CONSTRAINT 
    fk_employees_dept 
    FOREIGN KEY (department_id) 
    REFERENCES departments(department_id);

-- 检查约束
ALTER TABLE employees ADD CONSTRAINT 
    chk_employees_salary 
    CHECK (salary > 0 AND salary < 1000000);

ALTER TABLE employees ADD CONSTRAINT 
    chk_employees_date 
    CHECK (hire_date >= TO_DATE('1990-01-01', 'YYYY-MM-DD'));

-- 禁用/启用约束
ALTER TABLE employees DISABLE CONSTRAINT uk_employees_email;
ALTER TABLE employees ENABLE CONSTRAINT uk_employees_email;

-- 级联禁用（外键）
ALTER TABLE employees DISABLE CONSTRAINT fk_employees_dept CASCADE;

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT uk_employees_email;

-- 重命名约束
ALTER TABLE employees RENAME CONSTRAINT old_name TO new_name;

-- 查询约束
SELECT constraint_name, constraint_type, status, validated
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

-- 查询约束列
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';

┌─────────────────────────────────────────────────────────────────┐
│                      Oracle 分区表类型                            │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  范围分区 (RANGE)                                                │
│  ─────────────────                                              │
│  CREATE TABLE sales (                                          │
│      sale_date DATE                                            │
│  ) PARTITION BY RANGE (sale_date) (                           │
│      PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),   │
│      PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),   │
│      PARTITION p_future VALUES LESS THAN (MAXVALUE)            │
│  );                                                            │
│                                                                 │
│  列表分区 (LIST)                                                │
│  ─────────────────                                              │
│  CREATE TABLE customers (                                      │
│      region VARCHAR2(20)                                       │
│  ) PARTITION BY LIST (region) (                                │
│      PARTITION p_east VALUES ('北京', '上海'),                │
│      PARTITION p_west VALUES ('西安', '成都'),                │
│      PARTITION p_other VALUES (DEFAULT)                        │
│  );                                                            │
│                                                                 │
│  哈希分区 (HASH)                                                │
│  ─────────────────                                              │
│  CREATE TABLE orders (                                        │
│      order_id NUMBER                                           │
│  ) PARTITION BY HASH (order_id) PARTITIONS 16;               │
│                                                                 │
│  组合分区                                                       │
│  ─────────                                                     │
│  RANGE-LIST: 范围 + 列表组合                                    │
│  RANGE-HASH: 范围 + 哈希组合                                     │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

-- 范围分区（按日期）
CREATE TABLE sales (
    sale_id      NUMBER(10),
    sale_date    DATE NOT NULL,
    customer_id  NUMBER(10),
    amount       NUMBER(12,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023_q1 VALUES LESS THAN (DATE '2023-04-01'),
    PARTITION p_2023_q2 VALUES LESS THAN (DATE '2023-07-01'),
    PARTITION p_2023_q3 VALUES LESS THAN (DATE '2023-10-01'),
    PARTITION p_2023_q4 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p_2024_q1 VALUES LESS THAN (DATE '2024-04-01'),
    PARTITION p_2024_q2 VALUES LESS THAN (DATE '2024-07-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- 列表分区（按地区）
CREATE TABLE customers (
    customer_id   NUMBER(10),
    customer_name VARCHAR2(100),
    region        VARCHAR2(20) NOT NULL,
    city          VARCHAR2(50)
)
PARTITION BY LIST (region) (
    PARTITION p_beijing VALUES ('北京'),
    PARTITION p_shanghai VALUES ('上海'),
    PARTITION p_guangzhou VALUES ('广州', '深圳'),
    PARTITION p_other VALUES (DEFAULT)
);

-- 哈希分区（均匀分布）
CREATE TABLE transactions (
    transaction_id NUMBER(20),
    account_id     NUMBER(10),
    amount         NUMBER(12,2),
    trans_date     DATE
)
PARTITION BY HASH (account_id) (
    PARTITION p_01,
    PARTITION p_02,
    PARTITION p_03,
    PARTITION p_04
);

-- 组合分区：范围 + 列表
CREATE TABLE sales_by_region (
    sale_id     NUMBER(10),
    sale_date   DATE,
    region      VARCHAR2(20),
    city        VARCHAR2(50),
    amount      NUMBER(12,2)
)
PARTITION BY RANGE (sale_date) 
SUBPARTITION BY LIST (region) (
    PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01') (
        SUBPARTITION p_2024_east VALUES ('北京', '上海'),
        SUBPARTITION p_2024_west VALUES ('西安', '成都'),
        SUBPARTITION p_2024_other VALUES (DEFAULT)
    )
);

-- 添加分区
ALTER TABLE sales ADD PARTITION p_2024_q3 
VALUES LESS THAN (DATE '2024-10-01');

-- 拆分分区
ALTER TABLE sales SPLIT PARTITION p_future 
AT (DATE '2024-07-01') 
INTO (PARTITION p_2024_q2, PARTITION p_future);

-- 合并分区
ALTER TABLE sales MERGE PARTITIONS p_2024_q1, p_2024_q2 
INTO PARTITION p_2024_h1;

-- 删除分区
ALTER TABLE sales DROP PARTITION p_2023_q1;

-- 截断分区
ALTER TABLE sales TRUNCATE PARTITION p_2023_q1;

-- 重命名分区
ALTER TABLE sales RENAME PARTITION p_2024_q1 TO p_2024_1q;

-- 查询分区信息
SELECT partition_name, high_value, num_rows
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_position;

-- 查询子分区
SELECT partition_name, subpartition_name, high_value
FROM user_tab_subpartitions
WHERE table_name = 'SALES_BY_REGION';

-- B-Tree 索引
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- 复合索引（列顺序很重要）
CREATE INDEX idx_emp_dept_job ON employees(department_id, job_id);

-- Bitmap 索引（低基数列）
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
CREATE BITMAP INDEX idx_emp_status ON employees(status);

-- 函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));
CREATE INDEX idx_emp_sal_annual ON employees(salary * 12);

-- 位图连接索引
CREATE BITMAP INDEX idx_sales_prod 
ON sales(product_name) FROM sales, products 
WHERE sales.product_id = products.product_id;

-- 本地索引（分区表）
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

-- 全局索引
CREATE INDEX idx_orders_date ON orders(order_date) GLOBAL;

-- 重建索引
ALTER INDEX idx_emp_dept REBUILD;
ALTER INDEX idx_emp_dept REBUILD ONLINE;  -- 在线重建，不阻塞 DML

-- 合并索引（释放空白空间）
ALTER INDEX idx_emp_dept COALESCE;

-- 监控索引使用
ALTER INDEX idx_emp_dept MONITORING USAGE;

-- 查看使用情况
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_DEPT';

-- 删除不可用索引
DROP INDEX idx_emp_dept;

-- 查看索引信息
SELECT index_name, index_type, uniqueness, status, num_rows
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES';

-- 创建序列
CREATE SEQUENCE seq_employee_id
START WITH 1000
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999999
NOCACHE          -- 每次生成 20 个预分配（NOCACHE 更安全）
CYCLE            -- 达到最大值后循环
ORDER;           -- 保证顺序

-- 使用序列
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (seq_employee_id.NEXTVAL, 'John', 'Doe');

-- 获取当前值
SELECT seq_employee_id.CURRVAL FROM dual;

-- 修改序列
ALTER SEQUENCE seq_employee_id
INCREMENT BY 10
MAXVALUE 99999999999;

-- 删除序列
DROP SEQUENCE seq_employee_id;

-- 创建私有同义词
CREATE SYNONYM emp FOR hr.employees;
SELECT * FROM emp;

-- 创建公共同义词（所有用户可用）
CREATE PUBLIC SYNONYM departments FOR hr.departments;

-- 删除同义词
DROP SYNONYM emp;
DROP PUBLIC SYNONYM departments;

✅ 掌握数据库启动/关闭的多种模式
✅ 表空间类型：永久、临时、UNDO
✅ 分区表：RANGE、LIST、HASH、组合分区
✅ 约束类型：主键、唯一、外键、检查约束
✅ 索引类型：B-Tree、Bitmap、函数索引
✅ 序列和同义词的创建与使用