位置：文档库 > 数据库 > Oracle数据库管理常用SQL

Oracle数据库管理常用SQL

飞鸟与鱼上传于 2020-01-10 11:10

《Oracle数据库管理常用SQL》

Oracle数据库作为企业级关系型数据库的代表，其强大的功能与稳定性使其成为金融、电信、政府等行业的首选。数据库管理员（DBA）在日常工作中，需通过SQL语句实现数据查询、表空间管理、用户权限控制、性能优化等核心任务。本文将系统梳理Oracle数据库管理中的常用SQL，涵盖基础操作、高级管理、性能调优及安全控制四大领域，为DBA提供实战指南。

一、基础数据操作SQL

1. 数据查询与过滤

SELECT语句是DBA最常用的操作之一，其核心语法为：

SELECT column1, column2, ... 
FROM table_name 
WHERE condition 
ORDER BY column_name [ASC|DESC];

示例：查询员工表中工资高于5000的部门经理

SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE job_id = 'IT_PROG' AND salary > 5000
ORDER BY salary DESC;

2. 数据插入与更新

INSERT语句用于向表中添加数据，支持单行与多行插入：

-- 单行插入
INSERT INTO departments (department_id, department_name, location_id)
VALUES (280, 'Business Intelligence', 1700);

-- 多行插入（Oracle 12c+）
INSERT ALL
  INTO orders (order_id, customer_id) VALUES (1001, 101)
  INTO orders (order_id, customer_id) VALUES (1002, 102)
SELECT * FROM dual;

UPDATE语句修改现有数据，需谨慎使用WHERE子句避免全表更新：

UPDATE products
SET price = price * 1.1, update_date = SYSDATE
WHERE category_id = 10;

3. 数据删除

DELETE语句用于删除数据，TRUNCATE则快速清空表（不可回滚）：

-- 条件删除
DELETE FROM log_entries
WHERE create_date

二、数据库对象管理SQL

1. 表空间管理

表空间是Oracle的逻辑存储单元，常用操作包括创建、调整大小及查询状态：

-- 创建表空间
CREATE TABLESPACE data_ts
DATAFILE '/u01/oradata/data01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 2G
EXTENT MANAGEMENT LOCAL;

-- 调整数据文件大小
ALTER DATABASE DATAFILE '/u01/oradata/data01.dbf' RESIZE 1G;

-- 查询表空间使用情况
SELECT tablespace_name, 
       ROUND(SUM(bytes)/1024/1024,2) "Total(MB)",
       ROUND(SUM(bytes)/1024/1024 - SUM(NVL(free_bytes,0))/1024/1024,2) "Used(MB)"
FROM dba_data_files
GROUP BY tablespace_name;

2. 用户与权限管理

用户管理涉及创建、修改及权限分配，需遵循最小权限原则：

-- 创建用户并指定默认表空间
CREATE USER app_user IDENTIFIED BY "SecurePass123"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

-- 授予系统权限
GRANT CREATE SESSION, CREATE TABLE TO app_user;

-- 授予对象权限（精细控制）
GRANT SELECT, INSERT ON hr.employees TO app_user;

-- 创建角色简化权限管理
CREATE ROLE analyst_role;
GRANT SELECT ON sales.orders TO analyst_role;
GRANT analyst_role TO app_user;

3. 索引与约束管理

索引优化查询性能，约束保证数据完整性：

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

-- 创建唯一约束
ALTER TABLE customers ADD CONSTRAINT uk_cust_email UNIQUE (email);

-- 禁用/启用约束
ALTER TABLE orders DISABLE CONSTRAINT ord_cust_fk;
ALTER TABLE orders ENABLE CONSTRAINT ord_cust_fk;

三、性能监控与优化SQL

1. 执行计划分析

通过EXPLAIN PLAN查看SQL执行路径，识别性能瓶颈：

EXPLAIN PLAN FOR
SELECT e.last_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. 统计信息收集

优化器依赖统计信息生成执行计划，需定期更新：

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

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

3. 性能监控视图

Oracle提供丰富动态性能视图（V$开头）用于监控：

-- 查询高负载SQL
SELECT sql_id, executions, elapsed_time/1000000 "Elapsed(s)"
FROM v$sqlarea
WHERE executions > 10
ORDER BY elapsed_time DESC;

-- 监控会话等待事件
SELECT event, count(*) 
FROM v$session_wait
WHERE wait_class != 'Idle'
GROUP BY event;

四、备份与恢复SQL

1. 导出导入数据（Data Pump）

Data Pump（expdp/impdp）替代传统exp/imp，支持并行操作：

-- 导出整个用户
expdp system/password DIRECTORY=dpump_dir DUMPFILE=hr_full.dmp SCHEMAS=hr

-- 导入特定表
impdp system/password DIRECTORY=dpump_dir DUMPFILE=hr_full.dmp TABLES=employees,departments

2. RMAN备份脚本

RMAN（Recovery Manager）是Oracle推荐备份工具：

-- 全库备份
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

-- 增量备份（Level 0为完全备份）
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

-- 恢复控制文件
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

五、高级管理技巧

1. 分区表管理

分区表提升大表查询性能与管理效率：

-- 创建范围分区表
CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
  PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY'))
);

-- 交换分区（快速数据加载）
ALTER TABLE sales EXCHANGE PARTITION sales_q1 WITH TABLE sales_q1_temp;

2. 物化视图管理

物化视图存储查询结果，减少重复计算：

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_dept_salary
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS SELECT d.department_name, AVG(e.salary) avg_sal
   FROM departments d JOIN employees e ON d.department_id = e.department_id
   GROUP BY d.department_name;

-- 手动刷新
EXEC DBMS_MVIEW.REFRESH('MV_DEPT_SALARY', 'C');

3. 闪回技术

闪回功能快速恢复误操作数据：

-- 闪回查询（查看历史数据）
SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2023-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE employee_id = 101;

-- 闪回表（恢复误删数据）
ALTER TABLE employees ENABLE ROW MOVEMENT;
FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('2023-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

六、安全审计SQL

1. 审计配置

Oracle提供细粒度审计功能，记录敏感操作：

-- 启用标准审计
AUDIT CREATE SESSION BY ACCESS;

-- 启用细粒度审计（FGA）
BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema => 'HR',
    object_name => 'EMPLOYEES',
    policy_name => 'SALARY_AUDIT',
    audit_condition => 'salary > 20000',
    audit_column => 'SALARY',
    enable => TRUE
  );
END;

-- 查询审计记录
SELECT * FROM dba_audit_trail WHERE obj_name = 'EMPLOYEES';

2. 数据加密

透明数据加密（TDE）保护敏感数据：

-- 创建钱包并配置加密
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "WalletPass123";

-- 创建加密表空间
CREATE TABLESPACE encrypted_ts
DATAFILE '/u01/oradata/enc01.dbf' SIZE 100M
ENCRYPTION USING 'AES128';

3. 数据库链接管理

数据库链接实现跨库数据访问，需严格控制权限：

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

-- 通过链接查询数据
SELECT * FROM employees@remote_db WHERE department_id = 10;

七、自动化管理脚本示例

1. 表空间监控脚本

-- 监控表空间使用率超过90%的表空间
SELECT a.tablespace_name,
       ROUND(a.bytes_used/1024/1024,2) "Used(MB)",
       ROUND(a.bytes_free/1024/1024,2) "Free(MB)",
       ROUND(a.bytes_used/(a.bytes_used+a.bytes_free)*100,2) "Usage(%)"
FROM (
  SELECT d.tablespace_name,
         SUM(d.bytes) bytes_used,
         SUM(f.bytes) bytes_free
  FROM dba_data_files d, dba_free_space f
  WHERE d.tablespace_name = f.tablespace_name
  GROUP BY d.tablespace_name
) a
WHERE a.bytes_used/(a.bytes_used+a.bytes_free) > 0.9
ORDER BY 4 DESC;

2. 无效对象检查脚本

-- 检查无效的存储过程、视图等对象
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status != 'VALID'
AND owner NOT IN ('SYS','SYSTEM')
ORDER BY owner, object_type;

3. 会话阻塞分析脚本

-- 识别阻塞会话
SELECT blocking.session_id blocking_sid,
       blocking.serial# blocking_serial,
       blocking.username blocking_user,
       blocking.machine blocking_machine,
       blocked.session_id blocked_sid,
       blocked.serial# blocked_serial,
       blocked.username blocked_user,
       blocked.machine blocked_machine,
       blocked.sql_id blocked_sql
FROM v$session blocking, v$session blocked
WHERE blocking.sid = blocked.blocking_session
AND blocking.serial# != blocked.serial#;

关键词

Oracle数据库、SQL管理、表空间管理、用户权限、索引优化、执行计划、RMAN备份、Data Pump、分区表、物化视图、闪回技术、审计配置、数据加密、数据库链接、自动化脚本

简介

本文系统梳理Oracle数据库管理中的常用SQL，涵盖基础数据操作、数据库对象管理、性能监控与优化、备份与恢复、高级管理技巧及安全审计六大领域，提供从表空间创建到性能调优、从用户权限管理到数据加密的完整解决方案，并附有实战脚本示例，适合DBA及开发人员提升Oracle管理能力。

立即下载

数据库相关