【Oracle入门到删库跑路-07】核心技能：数据库对象管理

表空间和数据文件管理

表空间是Oracle数据库中最重要的逻辑存储结构，它将数据库的物理存储和逻辑存储分离。

表空间基础概念

表空间是数据库对象（如表、索引等）的逻辑容器，每个表空间由一个或多个数据文件组成。

创建表空间

-- 创建永久表空间
CREATE TABLESPACE users_data
  DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE 500M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

-- 创建临时表空间
CREATE TEMPORARY TABLESPACE temp_ts
  TEMPFILE '/u01/app/oracle/oradata/ORCL/temp01.dbf' SIZE 50M
  AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

-- 创建撤销表空间
CREATE UNDO TABLESPACE undo_ts
  DATAFILE '/u01/app/oracle/oradata/ORCL/undo01.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

管理表空间

-- 查看表空间信息
SELECT tablespace_name, status, contents FROM dba_tablespaces;

-- 查看表空间使用情况
SELECT tablespace_name,
       ROUND(bytes/1024/1024, 2) AS size_mb,
       ROUND(maxbytes/1024/1024, 2) AS max_size_mb,
       ROUND((bytes/maxbytes)*100, 2) AS used_percent
FROM dba_data_files;

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

-- 添加数据文件到表空间
ALTER TABLESPACE users_data
  ADD DATAFILE '/u01/app/oracle/oradata/ORCL/users02.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

-- 设置默认表空间
ALTER DATABASE DEFAULT TABLESPACE users_data;

-- 设置临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts;

删除表空间

-- 删除空表空间
DROP TABLESPACE example_ts;

-- 删除表空间及其内容
DROP TABLESPACE example_ts INCLUDING CONTENTS;

-- 删除表空间、内容和数据文件
DROP TABLESPACE example_ts INCLUDING CONTENTS AND DATAFILES;

3.2 索引类型和优化

索引是提高数据库查询性能的重要工具，它通过创建指向表中数据的指针来加速数据检索。

索引类型

B树索引（默认索引类型）

-- 创建B树索引
CREATE INDEX idx_employee_last_name ON employees(last_name);

-- 创建复合索引
CREATE INDEX idx_employee_name ON employees(last_name, first_name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_employee_email ON employees(email);

位图索引

-- 创建位图索引（适用于低基数列）
CREATE BITMAP INDEX idx_employee_gender ON employees(gender);

-- 创建复合位图索引
CREATE BITMAP INDEX idx_employee_dept_gender ON employees(department_id, gender);

函数索引

-- 创建函数索引
CREATE INDEX idx_employee_upper_name ON employees(UPPER(last_name));

-- 创建表达式索引
CREATE INDEX idx_employee_full_name ON employees(first_name || ' ' || last_name);

分区索引

-- 创建本地分区索引
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

-- 创建全局分区索引
CREATE INDEX idx_sales_amount ON sales(amount) GLOBAL PARTITION BY RANGE (amount)
(PARTITION p1 VALUES LESS THAN (1000),
 PARTITION p2 VALUES LESS THAN (5000),
 PARTITION p3 VALUES LESS THAN (MAXVALUE));

索引管理

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

-- 查看索引列信息
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

-- 重建索引
ALTER INDEX idx_employee_last_name REBUILD;

-- 收缩索引
ALTER INDEX idx_employee_last_name SHRINK SPACE;

-- 删除索引
DROP INDEX idx_employee_last_name;

索引优化策略

-- 查看未使用的索引
SELECT index_name, table_name
FROM dba_indexes
WHERE owner = 'HR'
AND index_name NOT IN (
  SELECT object_name
  FROM v$sql_plan
  WHERE object_type = 'INDEX'
  AND object_owner = 'HR'
);

-- 监控索引使用情况
ALTER INDEX idx_employee_last_name MONITORING USAGE;

-- 停止监控
ALTER INDEX idx_employee_last_name NOMONITORING USAGE;

-- 查看索引监控结果
SELECT index_name, table_name, monitoring, used
FROM v$object_usage
WHERE index_name = 'IDX_EMPLOYEE_LAST_NAME';

3.3 视图和物化视图

视图是基于一个或多个表的虚拟表，物化视图是存储查询结果的物理表。

视图

-- 创建简单视图
CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, email, salary, department_id
FROM employees
WHERE department_id IS NOT NULL;

-- 创建复杂视图
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, e.salary,
       d.department_name, d.location_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 创建带检查选项的视图
CREATE VIEW high_salary_emp AS
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE salary > 15000
WITH CHECK OPTION;

-- 查询视图
SELECT * FROM emp_dept_view WHERE department_name = 'IT';

-- 更新视图（必须满足可更新条件）
UPDATE emp_view SET salary = 16000 WHERE employee_id = 101;

-- 删除视图
DROP VIEW emp_view;

物化视图

-- 创建快速刷新物化视图
CREATE MATERIALIZED VIEW mv_emp_summary
REFRESH FAST ON DEMAND
AS
SELECT department_id, COUNT(*) emp_count, AVG(salary) avg_salary
FROM employees
GROUP BY department_id;

-- 创建完全刷新物化视图
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT product_id, SUM(quantity) total_quantity, SUM(amount) total_amount
FROM sales
GROUP BY product_id;

-- 创建定时刷新物化视图
CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1
AS
SELECT sale_date, COUNT(*) transaction_count, SUM(amount) daily_total
FROM sales
WHERE sale_date = TRUNC(SYSDATE)
GROUP BY sale_date;

-- 手动刷新物化视图
EXEC DBMS_MVIEW.REFRESH('mv_emp_summary', 'F');  -- 快速刷新
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', 'C');  -- 完全刷新

-- 查看物化视图信息
SELECT mview_name, refresh_mode, build_mode, fast_refreshable
FROM user_mviews;

-- 删除物化视图
DROP MATERIALIZED VIEW mv_emp_summary;

3.4 序列、同义词和约束

序列

-- 创建序列
CREATE SEQUENCE emp_seq
  START WITH 200
  INCREMENT BY 1
  MAXVALUE 999999
  NOCACHE
  NOCYCLE;

-- 使用序列
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe', 'JDOE@EXAMPLE.COM', SYSDATE, 'IT_PROG', 7000);

-- 查看序列当前值
SELECT emp_seq.CURRVAL FROM dual;

-- 修改序列
ALTER SEQUENCE emp_seq INCREMENT BY 2;

-- 删除序列
DROP SEQUENCE emp_seq;

同义词

-- 创建私有同义词
CREATE SYNONYM emp FOR employees;

-- 创建公共同义词
CREATE PUBLIC SYNONYM dept FOR departments;

-- 使用同义词
SELECT * FROM emp WHERE department_id = 60;

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

约束管理

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- 禁用约束
ALTER TABLE employees DISABLE CONSTRAINT chk_salary;

-- 启用约束
ALTER TABLE employees ENABLE CONSTRAINT chk_salary;

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

-- 查看约束信息
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

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

3.5 分区表

分区是将大表分割成更小、更易管理的部分的技术。

分区类型

范围分区

-- 创建范围分区表
CREATE TABLE sales (
  sale_id NUMBER,
  product_id NUMBER,
  customer_id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
  PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
  PARTITION p_2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
  PARTITION p_2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

列表分区

-- 创建列表分区表
CREATE TABLE customers (
  customer_id NUMBER,
  customer_name VARCHAR2(50),
  region VARCHAR2(20)
)
PARTITION BY LIST (region) (
  PARTITION p_north VALUES ('North'),
  PARTITION p_south VALUES ('South'),
  PARTITION p_east VALUES ('East'),
  PARTITION p_west VALUES ('West')
);

哈希分区

-- 创建哈希分区表
CREATE TABLE orders (
  order_id NUMBER,
  customer_id NUMBER,
  order_date DATE,
  amount NUMBER
)
PARTITION BY HASH (customer_id)
PARTITIONS 4;

复合分区

-- 创建范围-哈希复合分区表
CREATE TABLE transactions (
  trans_id NUMBER,
  account_id NUMBER,
  trans_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (trans_date)
SUBPARTITION BY HASH (account_id) SUBPARTITIONS 4 (
  PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
  PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
  PARTITION p_2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
  PARTITION p_2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

分区管理

-- 查看分区信息
SELECT table_name, partition_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'SALES';

-- 添加分区
ALTER TABLE sales ADD PARTITION p_2024_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'));

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

-- 合并分区
ALTER TABLE sales MERGE PARTITIONS p_2023_q2, p_2023_q3 INTO PARTITION p_2023_h1;

-- 拆分分区
ALTER TABLE sales SPLIT PARTITION p_2023_h1 AT (TO_DATE('2023-05-01', 'YYYY-MM-DD'))
INTO (PARTITION p_2023_q2, PARTITION p_2023_q3);

3.6 本章小结

本章详细介绍了Oracle数据库中各种重要对象的管理，包括表空间、索引、视图、物化视图、序列、同义词、约束和分区表。掌握这些对象的管理技能对于数据库管理员和开发人员都是非常重要的。

练习题

1. 创建一个包含多个数据文件的表空间，并设置自动扩展
2. 为employees表的不同列创建不同类型的索引，并比较查询性能
3. 创建一个复杂的视图，包含多个表的连接，并测试其可更新性
4. 创建一个物化视图，定期刷新销售数据汇总
5. 创建一个序列，并在插入数据时使用该序列生成主键值
6. 创建一个范围分区表，用于存储年度销售数据
7. 管理现有表的约束，包括添加、禁用、启用和删除约束