Oracle SQL*Plus 使用指南

连接数据库

基本连接

-- 连接到本地数据库，使用操作系统认证
sqlplus / as sysdba

-- 连接到本地数据库，使用用户名密码
sqlplus scott/tiger

-- 连接到远程数据库
sqlplus scott/tiger@localhost:1521/orcl

-- 连接到 PDB
sqlplus sys/password@localhost:1521/pdb1 as sysdba

连接选项

• / as sysdba：使用操作系统认证，以 SYSDBA 身份连接
• username/password：使用密码认证
• @tnsname：使用 TNS 名称连接
• @host:port/service：使用 Easy Connect 连接字符串
• as sysdba：以 SYSDBA 身份连接
• as sysoper：以 SYSOPER 身份连接

连接故障排除

• TNS-12154：无法解析连接标识符

  • 检查 TNS 名称配置
  • 检查网络连接
  • 检查监听器状态

• ORA-01017：无效的用户名/密码

  • 检查用户名和密码
  • 确保密码大小写正确
  • 检查用户状态

• ORA-12541：无监听器

  • 启动监听器：lsnrctl start
  • 检查监听器状态：lsnrctl status

基本命令

SQL 语句执行

-- 执行 SELECT 语句
SELECT * FROM employees WHERE department_id = 50;

-- 执行 INSERT 语句
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id)
VALUES (207, 'John', 'Doe', 'JDOE', SYSDATE, 'IT_PROG', 60);

-- 执行 UPDATE 语句
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 207;

-- 执行 DELETE 语句
DELETE FROM employees WHERE employee_id = 207;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

PL/SQL 块执行

-- 执行 PL/SQL 块
DECLARE
  v_employee_id employees.employee_id%TYPE := 206;
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id;
  DBMS_OUTPUT.PUT_LINE('Employee salary: ' || v_salary);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/

SQL*Plus 命令

-- 显示表结构
DESCRIBE employees;

-- 列出当前用户的表
SELECT table_name FROM user_tables;

-- 显示用户角色
SELECT * FROM session_roles;

-- 显示环境变量
SHOW ALL;

-- 设置行宽
SET LINESIZE 120;

-- 设置页大小
SET PAGESIZE 50;

-- 保存环境设置
STORE SET mysettings.sql CREATE;

-- 加载环境设置
@mysettings.sql;

-- 编辑文件
EDIT myscript.sql;

-- 执行脚本
@myscript.sql;

-- 执行操作系统命令
host dir;

-- 退出 SQL*Plus
EXIT;

格式化输出

基本格式化

-- 设置列宽
COLUMN employee_id FORMAT 99999;
COLUMN last_name FORMAT A20;
COLUMN salary FORMAT $999,999.99;

-- 设置列标题
COLUMN last_name HEADING 'Last Name';
COLUMN salary HEADING 'Annual Salary';

-- 设置数字格式
COLUMN hire_date FORMAT DD-MON-YYYY;

-- 清除列格式
CLEAR COLUMNS;

-- 设置页标题
TTITLE 'Employee Report';

-- 设置页脚
BTITLE 'Page: ' FORMAT 999;

-- 清除标题
CLEAR TITLE;

高级格式化

-- 设置空值显示
SET NULL 'N/A';

-- 设置行分隔符
SET RECSEP WRAPPED;

-- 设置长行处理
SET WRAP ON;

-- 设置验证变量替换
SET VERIFY ON;

-- 设置时间显示
SET TIMING ON;

-- 设置自动提交
SET AUTOCOMMIT ON;

-- 设置数组提取大小
SET ARRAYSIZE 15;

-- 设置分页
SET PAGESIZE 24;

脚本管理

创建和执行脚本

-- 创建脚本文件
EDIT create_users.sql

-- 脚本内容
CREATE USER test_user IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO test_user;
ALTER USER test_user QUOTA 100M ON users;

-- 执行脚本
@create_users.sql;

-- 执行带参数的脚本
@run_report.sql department_id=50;

脚本参数

-- 在脚本中使用替代变量
ACCEPT p_dept_id PROMPT 'Enter department ID: ';
SELECT * FROM employees WHERE department_id = &p_dept_id;

-- 在脚本中定义绑定变量
VAR v_dept_id NUMBER;
EXEC :v_dept_id := 50;
SELECT * FROM employees WHERE department_id = :v_dept_id;

-- 使用 && 定义持久变量
SELECT * FROM employees WHERE department_id = &&dept_id;
-- 后续可以直接使用 &dept_id
SELECT * FROM departments WHERE department_id = &dept_id;

脚本调试

-- 启用调试信息
SET ECHO ON;
SET VERIFY ON;
SET FEEDBACK ON;

-- 执行脚本
@debug_script.sql;

-- 禁用调试信息
SET ECHO OFF;
SET VERIFY OFF;
SET FEEDBACK OFF;

数据库管理

实例管理

-- 启动实例
STARTUP;

-- 启动到挂载状态
STARTUP MOUNT;

-- 启动到只读状态
STARTUP READ ONLY;

-- 关闭实例
SHUTDOWN;

-- 立即关闭
SHUTDOWN IMMEDIATE;

-- 事务关闭
SHUTDOWN TRANSACTIONAL;

-- 中止关闭
SHUTDOWN ABORT;

表空间管理

-- 创建表空间
CREATE TABLESPACE users DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

-- 扩展表空间
ALTER TABLESPACE users ADD DATAFILE '/u01/app/oracle/oradata/orcl/users02.dbf' SIZE 100M;

-- 重命名表空间
ALTER TABLESPACE users RENAME TO user_data;

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

用户管理

-- 创建用户
CREATE USER test_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

-- 修改用户
ALTER USER test_user IDENTIFIED BY new_password;
ALTER USER test_user QUOTA 50M ON users;

-- 锁定用户
ALTER USER test_user ACCOUNT LOCK;

-- 解锁用户
ALTER USER test_user ACCOUNT UNLOCK;

-- 删除用户
DROP USER test_user CASCADE;

权限管理

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

-- 授予对象权限
GRANT SELECT, INSERT, UPDATE ON employees TO test_user;

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

-- 回收权限
REVOKE CREATE TABLE FROM test_user;

-- 查看用户权限
SELECT * FROM dba_sys_privs WHERE grantee = 'TEST_USER';
SELECT * FROM dba_tab_privs WHERE grantee = 'TEST_USER';
SELECT * FROM dba_role_privs WHERE grantee = 'TEST_USER';

数据导入导出

导出数据

-- 使用 SPOOL 导出查询结果
SPOOL employees.csv;
SET COLSEP ',';
SET HEAD OFF;
SET PAGESIZE 0;
SET FEEDBACK OFF;
SELECT * FROM employees;
SPOOL OFF;

-- 导出表结构
SPOOL table_structure.sql;
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM DUAL;
SPOOL OFF;

导入数据

-- 使用 SQL*Loader 控制文件
-- 创建控制文件 load_employees.ctl
LOAD DATA
INFILE 'employees.csv'
APPEND INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(employee_id, first_name, last_name, email, hire_date "TO_DATE(:hire_date, 'DD-MON-YYYY')", job_id, department_id)

-- 执行 SQL*Loader
sqlldr scott/tiger control=load_employees.ctl

-- 直接插入数据
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id)
VALUES (&emp_id, '&first_name', '&last_name', '&email', TO_DATE('&hire_date', 'DD-MON-YYYY'), '&job_id', &dept_id);

性能监控

会话监控

-- 查看当前会话
SELECT SID, SERIAL#, STATUS, USERNAME, PROGRAM FROM v$session WHERE USERNAME IS NOT NULL;

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

-- 查看会话等待
SELECT sid, event, wait_class, seconds_in_wait FROM v$session_wait WHERE state = 'WAITING';

SQL 性能

-- 查看执行计划
EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 50;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 查看 SQL 统计信息
SET AUTOTRACE ON;
SELECT * FROM employees WHERE department_id = 50;
SET AUTOTRACE OFF;

-- 跟踪 SQL 执行
ALTER SESSION SET SQL_TRACE = TRUE;
-- 执行 SQL
SELECT * FROM employees WHERE department_id = 50;
ALTER SESSION SET SQL_TRACE = FALSE;
-- 查看跟踪文件

系统监控

-- 查看实例状态
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM v$instance;

-- 查看表空间使用
SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics;

-- 查看数据文件
SELECT name, status, bytes/1024/1024 MB FROM v$datafile;

-- 查看 redo 日志
SELECT group#, status, bytes/1024/1024 MB FROM v$log;

-- 查看归档日志
SELECT sequence#, name, status FROM v$archived_log WHERE completion_time > SYSDATE - 1;

高级功能

报告生成

-- 创建 HTML 报告
SET MARKUP HTML ON SPOOL ON HEAD '<title>Employee Report</title>' ENTMAP ON PREFORMAT OFF;
SPOOL employees.html;
SELECT * FROM employees WHERE department_id = 50;
SPOOL OFF;

-- 创建 CSV 报告
SET COLSEP ',';
SET HEAD OFF;
SET PAGESIZE 0;
SET FEEDBACK OFF;
SPOOL employees.csv;
SELECT * FROM employees;
SPOOL OFF;

自动化脚本

-- 循环执行
BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO test_table (id, value) VALUES (i, 'Test ' || i);
  END LOOP;
  COMMIT;
END;
/

-- 错误处理
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE test_table (id NUMBER, value VARCHAR2(50))';
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- 动态 SQL
DECLARE
  v_table_name VARCHAR2(30) := 'employees';
  v_sql VARCHAR2(200);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM ' || v_table_name;
  EXECUTE IMMEDIATE v_sql INTO v_count;
  DBMS_OUTPUT.PUT_LINE('Total rows: ' || v_count);
END;
/

配置文件

-- SQL*Plus 配置文件
-- 创建 glogin.sql
SET ECHO OFF;
SET FEEDBACK 6;
SET HEADING ON;
SET PAGESIZE 24;
SET LINESIZE 100;
SET TRIMOUT ON;
SET TRIMSPOOL ON;
SET TIMING ON;
SET AUTOTRACE OFF;
SET VERIFY OFF;
SET COLSEP ' ';

-- 每次启动 SQL*Plus 时自动执行
-- 位置：$ORACLE_HOME/sqlplus/admin/glogin.sql

常见问题（FAQ）

Q1: 如何在 SQL*Plus 中执行操作系统命令？

A1: 使用 host 命令执行操作系统命令：

host dir;
host ls -la;
host mkdir backup;

Q2: 如何保存 SQL*Plus 的输出到文件？

A2: 使用 SPOOL 命令：

SPOOL output.txt;
SELECT * FROM employees;
SPOOL OFF;

Q3: 如何在 SQL*Plus 中编辑命令？

A3: 使用 EDIT 命令：

EDIT;
-- 编辑当前缓冲区中的 SQL 语句

EDIT filename.sql;
-- 编辑指定文件

Q4: 如何在 SQL*Plus 中设置变量？

A4: 使用 DEFINE 或 VAR 命令：

-- 使用替代变量
DEFINE dept_id = 50;
SELECT * FROM employees WHERE department_id = &dept_id;

-- 使用绑定变量
VAR v_dept_id NUMBER;
EXEC :v_dept_id := 50;
SELECT * FROM employees WHERE department_id = :v_dept_id;

Q5: 如何查看 SQL*Plus 的执行历史？

A5: 使用向上箭头键浏览历史命令，或使用 HISTORY 命令（需要 Oracle 11g+）：

-- 查看历史命令
HISTORY;

-- 执行历史命令
HISTORY 5;

Q6: 如何在 SQL*Plus 中启用自动提交？

A6: 使用 SET AUTOCOMMIT 命令：

-- 启用自动提交
SET AUTOCOMMIT ON;

-- 设置自动提交间隔
SET AUTOCOMMIT 5;
-- 每 5 个语句自动提交

-- 禁用自动提交
SET AUTOCOMMIT OFF;

Q7: 如何在 SQL*Plus 中格式化日期输出？

A7: 使用 ALTER SESSION 设置 NLS 参数：

-- 设置日期格式
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

-- 设置数字格式
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

Q8: 如何连接到 Oracle 云数据库？

A8: 使用 Easy Connect 字符串：

-- 连接到 Oracle 云数据库
sqlplus admin/password@adb.example.com:1521/ORCL19

-- 或使用 TNS 名称
-- 在 tnsnames.ora 中配置

最佳实践

1. 脚本管理

• 使用标准命名：为脚本文件使用有意义的名称
• 添加注释：在脚本中添加详细注释
• 错误处理：在脚本中添加异常处理
• 版本控制：将脚本纳入版本控制系统
• 测试脚本：在生产环境前测试脚本

2. 性能优化

• 设置适当的数组大小：SET ARRAYSIZE 100
• 禁用不必要的输出：SET FEEDBACK OFF
• 使用绑定变量：减少硬解析
• 批量执行：减少网络往返

3. 安全性

• 避免硬编码密码：使用外部认证或钱包
• 限制权限：遵循最小权限原则
• 加密连接：使用 SSL/TLS
• 审计操作：记录敏感操作

4. 自动化

• 创建配置文件：使用 glogin.sql 设置环境
• 使用变量：减少重复代码
• 模块化：将复杂任务分解为小脚本
• 调度执行：使用 cron 或调度器执行脚本

5. 故障排除

• 启用跟踪：ALTER SESSION SET SQL_TRACE = TRUE
• 查看 alert 日志：host tail -f $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/alert_orcl.log
• 检查监听日志：host tail -f $ORACLE_BASE/diag/tnslsnr/orcl/listener/trace/listener.log