Oracle数据库基础知识总结

一、Oracle数据库概述

Oracle数据库是由Oracle公司开发的关系型数据库管理系统(RDBMS)，是目前最流行的企业级数据库之一。主要特点包括：

1. 支持大规模数据处理
2. 高可用性架构
3. 强大的安全特性
4. 完善的备份恢复机制
5. 跨平台支持

二、Oracle体系结构

1. 存储结构

• 物理存储结构：

  • 数据文件(.dbf)
  • 控制文件(.ctl)
  • 重做日志文件(.log)
  • 参数文件(pfile/spfile)
  • 归档日志文件

• 逻辑存储结构：

  • 表空间(Tablespace)
  • 段(Segment)
  • 区(Extent)
  • 数据块(Block)

2. 内存结构

• 系统全局区(SGA)：

  • 共享池(Shared Pool)
  • 数据库缓冲区(Database Buffer Cache)
  • 重做日志缓冲区(Redo Log Buffer)
  • 大池(Large Pool)
  • Java池(Java Pool)
  • 流池(Streams Pool)

• 程序全局区(PGA)：

  • 排序区
  • 会话信息
  • 游标状态

3. 进程结构

• 用户进程
• 服务器进程
• 后台进程：
  • DBWn(数据库写进程)
  • LGWR(日志写进程)
  • CKPT(检查点进程)
  • SMON(系统监控进程)
  • PMON(进程监控进程)
  • ARCn(归档进程)

三、SQL基础

1. DDL(数据定义语言)

-- 创建表
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50) NOT NULL,
    hire_date DATE,
    salary NUMBER(10,2),
    dept_id NUMBER
);

-- 修改表
ALTER TABLE employees ADD (email VARCHAR2(100));

-- 删除表
DROP TABLE employees;

-- 创建索引
CREATE INDEX idx_emp_name ON employees(emp_name);

2. DML(数据操作语言)

-- 插入数据
INSERT INTO employees VALUES (1, '张三', TO_DATE('2020-01-15', 'YYYY-MM-DD'), 8000, 10);

-- 更新数据
UPDATE employees SET salary = 8500 WHERE emp_id = 1;

-- 删除数据
DELETE FROM employees WHERE emp_id = 1;

-- 查询数据
SELECT emp_id, emp_name, salary FROM employees WHERE dept_id = 10 ORDER BY salary DESC;

3. DCL(数据控制语言)

-- 授予权限
GRANT SELECT, INSERT ON employees TO user1;

-- 撤销权限
REVOKE INSERT ON employees FROM user1;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

四、PL/SQL编程

1. PL/SQL块结构

DECLARE
    -- 声明部分
    v_emp_name VARCHAR2(50);
    v_salary NUMBER;
BEGIN
    -- 执行部分
    SELECT emp_name, salary INTO v_emp_name, v_salary
    FROM employees WHERE emp_id = 100;

    DBMS_OUTPUT.PUT_LINE('员工姓名: ' || v_emp_name || ', 薪资: ' || v_salary);
EXCEPTION
    -- 异常处理部分
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('未找到员工记录');
END;

2. 存储过程

CREATE OR REPLACE PROCEDURE update_salary(
    p_emp_id IN NUMBER,
    p_increase IN NUMBER
) AS
BEGIN
    UPDATE employees 
    SET salary = salary + p_increase
    WHERE emp_id = p_emp_id;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('薪资更新成功');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END update_salary;

3. 函数

CREATE OR REPLACE FUNCTION get_avg_salary(
    p_dept_id IN NUMBER
) RETURN NUMBER AS
    v_avg_salary NUMBER;
BEGIN
    SELECT AVG(salary) INTO v_avg_salary
    FROM employees
    WHERE dept_id = p_dept_id;

    RETURN v_avg_salary;
END get_avg_salary;

4. 触发器

CREATE OR REPLACE TRIGGER trg_emp_audit
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO emp_audit VALUES ('INSERT', :NEW.emp_id, SYSDATE, USER);
    ELSIF UPDATING THEN
        INSERT INTO emp_audit VALUES ('UPDATE', :OLD.emp_id, SYSDATE, USER);
    ELSIF DELETING THEN
        INSERT INTO emp_audit VALUES ('DELETE', :OLD.emp_id, SYSDATE, USER);
    END IF;
END;

五、数据库管理

1. 用户和权限管理

-- 创建用户
CREATE USER testuser IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;

-- 授予角色
GRANT CONNECT, RESOURCE TO testuser;

-- 修改用户
ALTER USER testuser QUOTA 200M ON users;

-- 锁定/解锁用户
ALTER USER testuser ACCOUNT LOCK;
ALTER USER testuser ACCOUNT UNLOCK;

2. 表空间管理

-- 创建表空间
CREATE TABLESPACE app_data
DATAFILE '/u01/oracle/data/app01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 2G;

-- 扩展表空间
ALTER TABLESPACE app_data
ADD DATAFILE '/u01/oracle/data/app02.dbf' SIZE 300M;

-- 修改表空间状态
ALTER TABLESPACE app_data READ ONLY;
ALTER TABLESPACE app_data READ WRITE;

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

3. 备份与恢复

-- RMAN备份示例
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

-- 表空间热备份
ALTER TABLESPACE users BEGIN BACKUP;
-- 使用操作系统命令复制数据文件
ALTER TABLESPACE users END BACKUP;

-- 数据泵导出(expdp)
expdp system/password DIRECTORY=dpump_dir DUMPFILE=expfull.dmp FULL=Y

-- 数据泵导入(impdp)
impdp system/password DIRECTORY=dpump_dir DUMPFILE=expfull.dmp FULL=Y

六、性能优化

1. SQL优化

• 使用EXPLAIN PLAN分析执行计划
• 创建适当的索引
• 避免全表扫描
• 使用绑定变量
• 优化连接操作

2. 索引策略

• 为频繁查询的列创建索引
• 考虑复合索引的顺序
• 避免在索引列上使用函数
• 定期重建碎片化的索引

3. 统计信息收集

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME');

-- 收集索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA', 'INDEX_NAME');

-- 收集模式统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA');

七、高级特性

1. 分区表

-- 范围分区
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
) PARTITION BY RANGE (sale_date) (
    PARTITION p2020 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),
    PARTITION p2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- 列表分区
CREATE TABLE customers (
    cust_id NUMBER,
    cust_name VARCHAR2(50),
    region VARCHAR2(20)
) PARTITION BY LIST (region) (
    PARTITION p_east VALUES ('EAST'),
    PARTITION p_west VALUES ('WEST'),
    PARTITION p_other VALUES (DEFAULT)
);

2. 物化视图

CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT product_id, SUM(quantity), SUM(amount)
FROM sales
GROUP BY product_id;

3. 数据库链接

-- 创建数据库链接
CREATE DATABASE LINK remote_db
CONNECT TO remote_user IDENTIFIED BY password
USING 'remote_tns';

-- 使用数据库链接
SELECT * FROM employees@remote_db;

八、常用数据字典视图

九、常用工具

1. SQL*Plus: Oracle命令行工具
2. SQL Developer: Oracle图形化开发工具
3. Enterprise Manager: Web管理控制台
4. RMAN: 恢复管理器
5. Data Pump: 数据导入导出工具
6. OEM: Oracle企业管理器

十、最佳实践

1. 定期备份数据库
2. 监控数据库性能
3. 实施适当的权限控制
4. 定期维护统计信息
5. 合理设计表结构和索引
6. 使用绑定变量防止SQL注入
7. 实施归档日志模式以提高可恢复性
8. 定期检查数据库健康状况

以上是Oracle数据库的基础知识总结，涵盖了从体系结构到日常管理的各个方面。实际应用中需要根据具体场景和需求进行深入学习和实践。