Oracle 常用开发命令

数据库连接与管理

连接到数据库

# 使用SQL*Plus连接本地数据库
sqlplus / as sysdba

# 使用SQL*Plus连接远程数据库
sqlplus username/password@hostname:port/service_name

# 使用SQL*Plus连接TNS服务
sqlplus username/password@tns_alias

# 使用SQLcl连接数据库
./sql username/password@hostname:port/service_name

会话管理

-- 查看当前会话信息
SELECT SYS_CONTEXT('USERENV', 'SESSIONID') AS session_id,
       SYS_CONTEXT('USERENV', 'SID') AS sid,
       SYS_CONTEXT('USERENV', 'SERIAL#') AS serial,
       SYS_CONTEXT('USERENV', 'USERNAME') AS username
FROM dual;

-- 查看当前会话参数
SHOW PARAMETER;

-- 设置会话参数
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

-- 终止会话
ALTER SYSTEM KILL SESSION '<sid>,<serial#>';

表与索引管理

表操作

-- 创建表
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    hire_date DATE DEFAULT SYSDATE,
    department_id NUMBER
);

-- 修改表
ALTER TABLE employees ADD (salary NUMBER(8,2));
ALTER TABLE employees MODIFY (email VARCHAR2(150));
ALTER TABLE employees DROP COLUMN email;

-- 重命名表
ALTER TABLE employees RENAME TO staff;

-- 删除表
DROP TABLE staff CASCADE CONSTRAINTS;

-- 截断表
TRUNCATE TABLE employees;

-- 查看表结构
DESCRIBE employees;

-- 查看表定义
SELECT dbms_metadata.get_ddl('TABLE', 'EMPLOYEES') FROM dual;

索引操作

-- 创建索引
CREATE INDEX emp_last_name_idx ON employees(last_name);

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

-- 创建复合索引
CREATE INDEX emp_dept_salary_idx ON employees(department_id, salary);

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

-- 重建索引
ALTER INDEX emp_last_name_idx REBUILD;

-- 重组索引
ALTER INDEX emp_last_name_idx COALESCE;

-- 删除索引
DROP INDEX emp_last_name_idx;

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

数据操作

DML操作

-- 插入数据
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'John', 'Doe');

-- 批量插入
INSERT ALL
  INTO employees (employee_id, first_name, last_name) VALUES (2, 'Jane', 'Smith')
  INTO employees (employee_id, first_name, last_name) VALUES (3, 'Mike', 'Johnson')
SELECT * FROM dual;

-- 更新数据
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

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

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 创建保存点
SAVEPOINT before_update;
ROLLBACK TO before_update;

查询操作

-- 基本查询
SELECT first_name, last_name, salary FROM employees;

-- 条件查询
SELECT * FROM employees WHERE department_id = 10 AND salary > 5000;

-- 排序查询
SELECT * FROM employees ORDER BY salary DESC, last_name ASC;

-- 分组查询
SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- 连接查询
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 子查询
SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- 分页查询
SELECT * FROM (
    SELECT rownum AS rn, e.*
    FROM (SELECT * FROM employees ORDER BY salary DESC) e
) WHERE rn BETWEEN 11 AND 20;

-- 分析函数
SELECT first_name, last_name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

PL/SQL开发

存储过程

-- 创建存储过程
CREATE OR REPLACE PROCEDURE get_employee_details (
    p_employee_id IN NUMBER,
    p_first_name OUT VARCHAR2,
    p_last_name OUT VARCHAR2,
    p_salary OUT NUMBER
) AS
BEGIN
    SELECT first_name, last_name, salary
    INTO p_first_name, p_last_name, p_salary
    FROM employees
    WHERE employee_id = p_employee_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_first_name := NULL;
        p_last_name := NULL;
        p_salary := NULL;
END;
/

-- 执行存储过程
DECLARE
    v_first_name VARCHAR2(50);
    v_last_name VARCHAR2(50);
    v_salary NUMBER;
BEGIN
    get_employee_details(100, v_first_name, v_last_name, v_salary);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
END;
/

函数

-- 创建函数
CREATE OR REPLACE FUNCTION calculate_bonus (
    p_salary IN NUMBER,
    p_percentage IN NUMBER DEFAULT 10
) RETURN NUMBER AS
BEGIN
    RETURN p_salary * p_percentage / 100;
END;
/

-- 调用函数
SELECT first_name, last_name, salary, calculate_bonus(salary, 15) AS bonus
FROM employees WHERE department_id = 10;

触发器

-- 创建触发器
CREATE OR REPLACE TRIGGER emp_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
    v_action VARCHAR2(10);
BEGIN
    IF INSERTING THEN
        v_action := 'INSERT';
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
    ELSIF DELETING THEN
        v_action := 'DELETE';
    END IF;
    
    INSERT INTO emp_audit (
        audit_id,
        table_name,
        action,
        employee_id,
        audit_date
    ) VALUES (
        emp_audit_seq.NEXTVAL,
        'EMPLOYEES',
        v_action,
        NVL(:NEW.employee_id, :OLD.employee_id),
        SYSDATE
    );
END;
/

性能调优

SQL性能分析

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 查看实际执行计划
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- 启用SQL监控
ALTER SESSION SET statistics_level = all;

-- 查看SQL监控报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '<sql_id>') FROM dual;

-- 查看Top SQL
SELECT * FROM (
    SELECT sql_id, elapsed_time/1000000 as elapsed_seconds, cpu_time/1000000 as cpu_seconds,
           buffer_gets, disk_reads, executions, sql_text
    FROM v$sql
    ORDER BY elapsed_time DESC
) WHERE ROWNUM <= 10;

统计信息管理

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => true, estimate_percent => 100);

-- 收集模式统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR', cascade => true);

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

-- 锁定统计信息
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- 解锁统计信息
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- 查看统计信息
SELECT table_name, num_rows, last_analyzed
FROM user_tables
WHERE table_name = 'EMPLOYEES';

数据库对象管理

视图管理

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

-- 创建物化视图
CREATE MATERIALIZED VIEW emp_salary_mv
REFRESH COMPLETE ON DEMAND
AS SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;

-- 刷新物化视图
EXEC DBMS_MVIEW.REFRESH('EMP_SALARY_MV');

-- 删除视图
DROP VIEW emp_dept_view;

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

序列与同义词

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

-- 使用序列
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'New', 'Employee');

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

-- 删除序列
DROP SEQUENCE emp_seq;

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

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

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

安全管理

用户与权限

-- 创建用户
CREATE USER dev_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

-- 授予权限
GRANT CONNECT, RESOURCE TO dev_user;
GRANT CREATE VIEW, CREATE PROCEDURE TO dev_user;
GRANT SELECT ON hr.employees TO dev_user;

-- 回收权限
REVOKE CREATE VIEW FROM dev_user;

-- 创建角色
CREATE ROLE developer_role;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO developer_role;
GRANT developer_role TO dev_user;

-- 修改用户密码
ALTER USER dev_user IDENTIFIED BY new_password;

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

-- 删除用户
DROP USER dev_user CASCADE;

监控与诊断

系统监控

-- 查看数据库实例状态
SELECT instance_name, status, database_status FROM v$instance;

-- 查看数据库状态
SELECT name, open_mode, log_mode FROM v$database;

-- 查看会话信息
SELECT sid, serial#, username, status, machine, program
FROM v$session
WHERE status = 'ACTIVE';

-- 查看等待事件
SELECT event, COUNT(*) AS wait_count
FROM v$session_wait
GROUP BY event
ORDER BY wait_count DESC;

-- 查看表空间使用情况
SELECT tablespace_name, 
       ROUND(bytes/1024/1024, 2) AS total_mb,
       ROUND((bytes - free_bytes)/1024/1024, 2) AS used_mb,
       ROUND(free_bytes/1024/1024, 2) AS free_mb,
       ROUND((bytes - free_bytes)/bytes * 100, 2) AS used_pct
FROM (
    SELECT tablespace_name, 
           SUM(bytes) AS bytes,
           SUM(CASE WHEN status = 'FREE' THEN bytes ELSE 0 END) AS free_bytes
    FROM dba_data_files
    LEFT JOIN dba_free_space USING (tablespace_name)
    GROUP BY tablespace_name
);

日志查看

-- 查看告警日志位置
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

-- 查看最近的告警日志条目
SELECT message_text
FROM v$diag_alert_ext
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

-- 查看监听器日志
lsnrctl status
lsnrctl services

-- 查看SQL跟踪文件
ALTER SESSION SET sql_trace = true;
-- 执行SQL语句
ALTER SESSION SET sql_trace = false;
-- 查看跟踪文件位置
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

版本差异

Oracle 11g

• 支持基本的SQL和PL/SQL功能
• 提供DBMS_XPLAN查看执行计划
• 支持自动SQL优化器

Oracle 12c

• 引入PL/SQL内联声明
• 支持FETCH FIRST n ROWS ONLY语法
• 引入IDENTITY列
• 支持会话级PL/SQL优化

Oracle 19c

• 增强了SQL优化功能
• 改进了PL/SQL性能
• 支持自动索引创建
• 增强了诊断功能

Oracle 21c

• 支持SQL宏
• 增强了JSON支持
• 引入区块链表
• 支持更高级的PL/SQL功能

常见问题

Q: 如何查看Oracle版本？

A: 使用 SELECT * FROM v$version; 或 SELECT banner FROM v$version;

Q: 如何导出/导入数据？

A: 使用 expdp/impdp 数据泵工具，或传统的 exp/imp 工具。

Q: 如何查看表的约束？

A: 使用 SELECT * FROM user_constraints WHERE table_name = 'TABLE_NAME';

Q: 如何查看存储过程的错误？

A: 使用 SHOW ERRORS PROCEDURE procedure_name; 或查询 USER_ERRORS 视图。

Q: 如何生成DDL语句？

A: 使用 DBMS_METADATA.GET_DDL 函数，如 SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES') FROM dual;

Q: 如何查看当前用户的权限？

A: 使用 SELECT * FROM session_privs; 查看系统权限，SELECT * FROM session_roles; 查看角色。

Q: 如何清理过期的统计信息？

A: 使用 DBMS_STATS.PURGE_STATS 函数，如 EXEC DBMS_STATS.PURGE_STATS(SYSDATE - 30);

Q: 如何查看表的索引？

A: 使用 SELECT * FROM user_indexes WHERE table_name = 'TABLE_NAME';

Q: 如何查看锁信息？

A: 使用 SELECT * FROM v$lock; 和 SELECT * FROM v$locked_object;

Q: 如何优化慢查询？

A: 分析执行计划，创建合适的索引，优化SQL语句，更新统计信息，考虑使用提示。