前言
俗话说得好：工欲善其事，必先利其器。SQL* Plus作为Oracle数据库强大管理工具，相信各位DBA在日常工作中都会高效使用它。本文分享一下使用的技巧和心得。
SQL*Plus概述
SQL*Plus是什么
SQL*Plus 是 Oracle 数据库提供的一个交互式命令行工具，用于执行 SQL 命令和 PL/SQL 块，以及生成报表。它是 Oracle 数据库管理员和开发人员最常用的工具之一。
主要功能：
- 执行 SQL 语句和 PL/SQL 块
- 格式化查询结果
- 检查表和对象定义
- 执行数据库管理
- 生成报表
使用SQL*Plus
启动SQL*Plus命令行
SQLPlus是可执行文件通常安装在$ORACLE_HOME/bin中，通常包含在您的操作系统路径环境变量中。您可能需要将目录更改为$ORACLE_HOME/bin目录以启动SQLPlus 。
SQL*Plus命令行连接的方式
普通用户登录方式
在以下示例中在数据库开启监听的情况下，使用简单连接标识符连接到mymachine上运行的mydb数据库中的HR模式如下
sqlplus <username>/<password>@<tns_alias> sqlplus <username>/<password>@<database_ip>:<port>/<database_name> sqlplus hr/hr@//mymachine:1521/mydb sqlplus hr/hr@192.168.17.91:1521/mydb sqlplus hr/hr@MYDB sqlplus hr/hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.17.91)(PORT=1521))(CONNECT_DATA=(SID=mydb)))
SYSDBA登录方式
一般数据库管理员都是通过操作系统验证免密的方式以SYSDBA的权限登录到数据库中进行管理，比如以SYSDBA的权限登录到本机运行的mydb数据库中的SYS模式如下
sqlplus / as sysdba
如果登录报错如下：
ERROR:
ORA-01017: invalid username/password; logon denied
ORA-01017排查问题
查看用户的组
Linux操作系统dba组必须要有
id oracle uid=1001(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper)
Windows操作系统ora_dba组必须要有
C:\Users\Administrator>net user
\\WIN-4B01U3NU6BE 的用户帐户
-------------------------------------------------------------------------------
Administrator Guest
命令成功完成。
C:\Users\Administrator>net localgroup ora_dba
别名 ora_dba
注释 Oracle DBA Group
成员
-------------------------------------------------------------------------------
Administrator
NT AUTHORITY\SYSTEM
命令成功完成。
C:\Users\Administrator>
通过密码文件验证
sqlnet.ora中SQLNET.AUTHENTICATION_SERVICES参数限制，密码文件验证登录，通过设置密码文件
#linux操作系统创建密码文件，ignorecase=n大小写敏感
orapwd file=<ORACLE_HOME>/dbs/orapw<ORACLE_SID> ignorecase=n password=<password> force=y
#linux操作系统创建密码文件，ignorecase=n大小写敏感
orapwd file=<ORACLE_HOME>\database\PWD<ORACLE_SID>.ora ignorecase=n password=<password> force=y
密码文件和输入的密码是否匹配
sqlplus sys/<password> as sysdba
注意sqlnet.ora中SQLNET.AUTHENTICATION_SERVICES是针对windows操作系统验证免密登录和密码文件验证的作用
下面表格简单说明通过设置SQLNET.AUTHENTICATION_SERVICES参数分别在Linux和Windows操作系统不同的作用
| sqlnet.ora中参数 | Linux操作系统 | Windows操作系统 |
|---|---|---|
| 无参数 | 操作系统验证通过 | 密码文件验证通过，操作系统验证无法通过 |
| SQLNET.AUTHENTICATION_SERVICES = (NTS) | 操作系统验证无法通过，密码文件验证通过 | 操作系统验证通过 |
| SQLNET.AUTHENTICATION_SERVICES = (NONE) | 操作系统验证无法通过，密码文件验证通过 | 密码文件验证通过，操作系统验证无法通过 |
| SQLNET.AUTHENTICATION_SERVICES = (ALL) | 操作系统验证通过 | 错误方式报ORA-12641 |
如果要操作系统验证，一般Linux操作系统不要需要设置，Windows操作系统需要设置SQLNET.AUTHENTICATION_SERVICES = (NTS)
如果要密码文件验证，需要设置数据库参数remote_login_passwordfile=EXCLUSIVE配合，查询select * from v$pwfile_users;哪些用户具有SYSDBA权限
密码含特殊字符时登录方式
linux操作系统环境将HR用户密码设置成特殊字符Ora_!@#$%^&*
[oracle@mymachine ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 12 22:32:07 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter user hr identified by "Ora_!@#$%^&*";
User altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mymachine ~]$ sqlplus 'hr/"Ora_!@#$%^&*"'@192.168.17.91:1521/mydb
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 12 22:35:12 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
windows操作系统将HR用户密码设置成特殊字符Ora_!@#$%^&*
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on 星期二 5月 13 15:17:26 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter user hr identified by "Ora_!@#$%^&*" account unlock;
用户已更改。
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开
C:\Users\Administrator>sqlplus hr/\"Ora_!@#$%^&*\"@127.0.0.1:1521/mydb
SQL*Plus: Release 11.2.0.4.0 Production on 星期二 5月 13 15:19:41 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开
C:\Users\Administrator>sqlplus hr/"""Ora_!@#$%^&*"""@127.0.0.1:1521/mydb
SQL*Plus: Release 11.2.0.4.0 Production on 星期二 5月 13 15:20:18 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
密码含特殊字符时登录方式总结如下：
| 操作系统 | 命令 | 说明 |
|---|---|---|
| Linux操作系统 | sqlplus ‘hr/“Ora_!@#$%^&*”’@192.168.17.91:1521/mydb | 单引号将用户密码括起来，然后里面的密码用双引号括起来 |
| Windows操作系统 | sqlplus hr/""“Ora_!@#$%^&*”""@127.0.0.1:1521/mydb | 密码用3个双引号括起来，或者用一个\加上1个双引号括起来 |
SQL*Plus环境设置
SQL*Plus的使用环境是可以通过glogin.sql或login.sql脚本来设置的。
- glogin.sql文件是全局设置文件，位于$ORACLE_HOME/sqlplus/admin下
- login.sql文件是个性化设置文件，则可以位于任何位置。既可以通过SQLPATH环境变量设置或不设置
在glogin.sql中可以使用以下 SQL*Plus 预定义变量：
| 变量名 | 描述 |
|---|---|
_USER |
当前登录用户名 |
_CONNECT_IDENTIFIER |
连接标识符 |
_PRIVILEGE |
连接权限级别 |
_DATE |
当前日期和时间 |
_O_VERSION |
Oracle 版本信息 |
_O_RELEASE |
Oracle 发布号 |
_EDITOR |
默认编辑器 |
全局设置
数据库管理员一般都会设置全局glogin.sql来区分登录所在的数据库和用户名。如下操作：
[oracle@mymachine ~]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2011, Oracle and/or its affiliates.
-- All rights reserved.
--
-- NAME
-- glogin.sql
--
-- DESCRIPTION
-- SQL*Plus global login "site profile" file
--
-- Add any SQL*Plus commands here that are to be executed when a
-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
-- This script is automatically run
--
set sqlprompt "_DATE _USER'@'_CONNECT_IDENTIFIER> "
[oracle@mymachine ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 13 16:58:26 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2025-05-13 16:58:26 SYS@mydb>
个性化设置文件
将login.sql设置当前目录或者设置SQLPATH环境变量
[oracle@cms-db-test ~]$ mkdir bak
[oracle@cms-db-test ~]$ mv login.sql bak/
[oracle@cms-db-test ~]$ export SQLPATH=/home/oracle/bak
[oracle@cms-db-test ~]$ cat /home/oracle/bak/login.sql
set sqlprompt "_DATE _USER'@'_CONNECT_IDENTIFIER> "
[oracle@cms-db-test ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 14 13:25:10 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2025-05-14 13:25:10 SYS@cmstest>
变量设置
变量是在使用SQL*Plus过程中其值可以更改的关键属性。下表总结了必须了解的最常见的变量。
| 变量（全部） | 功能 | 用法 |
|---|---|---|
| ARRAY[SICE] | 确定一次性从数据库中获取的行数 | SET ARRAY 50 |
| AUTO[COMMIT] | 指定事务的提交为自动或手动 | SET AUTO ON |
| COLSEP | 指定列值之间要打印的文本 | SET COLSEP |
| COPY[COMMIT] | 设置使用COPY命令时提交的频率 | SET COPY 10000 |
| DEF[INE] [B/C/ON/ OFF] | 设置在变量置换中使用的前缀字符 | SET DEFINE ON |
| ECHO [OFF/ON] | 设置回显为ON或OFF；如果ECHO ON，则每个命令将在其输出到屏幕前被显示 | SET ECHO ON |
| EDIT[ILE] | 设置在使用默认编辑器时的默认文件名 | SET EDITFILE draft.sql |
| FEEDBACK {OFF/ON} | 指定SQL*Plus是否显示查询返回的记录数 | SET FEEDBACK OFF |
| FLUSH {OFF/ON} | 确定输出是否缓冲或清除屏幕 | SET FLUSH OFF |
| HEADING [OFF/ON] | 指定是否打印列标题 | SET HEAD OFF |
| LIN[ESIZE] [80/n] | 指定每行显示的字符数 | SET LINESIZE 40 |
| LONG [80/n] | 指定LONG、CLOB、NCLOB和XMLType值的最大长度 | SET LONG 100000 |
| NEWPAGE [AGE] {1/n/none} | 指定每个新页顶部的空行数 | SET NEWPAGE 0 |
| NUM[WIDTH] [10/n] | 指定数字的显示格式 | SET NUM |
| PAGESIZE [120/n] [24/n] | 指定每页的行数 | SET PAGESIZE 60 |
| PAUSE [OFF/ON/TEXT] | 指定打印到屏幕的输出量 | SET PAUSE ON |
| SERVEROUT[PUT] {OFF/ON} [SIZE n] | 指定是否显示PL/SQL代码的输出结果 | SET SERVEROUTPUT ON |
| SQLPROMPT {SQL>TEXT} [OFF/ON] | 指定SQL*Plus会话的命令提示符 | SET SQLPROMPT ＊salapati ＊ |
| TERM[OUT] {OFF/ON} | 指定是否显示命令文件的输出 | SET TERMOUT OFF |
| TI[ME] {OFF/ON} | 若设置ON，显示时间 | SET TIME OFF |
| TIMI[NG] {OFF/ON} | 控制SQL命令的显示时间 | SET TIMING OFF |
| VER[IFY] {OFF/ON} | 指定在变量转换后是否显示SQL文本 | SET VERIFY OFF |
以下是一个例子，它说明如何使用SERVEROUTPUT变量显示DBMS_OUTPUT程序包的输出结果。比程序包包含一个名为PUT_LINE的过程，该过程输出一行。如果设置SERVEROUTPUT ON，可以看到PUT_LINE过程显示的输出结果：
[oracle@mymachine ~]$ sqlplus hr/hr
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 19 10:34:35 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set serveroutput on
SQL> begin
dbms_output.put_line('This is the first line');
dbms_output.put_line('This is the second line');
dbms_output.put_line('This is the last line');
end;
/ 2 3 4 5 6
This is the first line
This is the second line
This is the last line
PL/SQL procedure successfully completed.
SQL>
以下是一个例子，它说明通过设置每行字符数和每页的行数、每列宽度，显示表的输出结果：
[oracle@mymachine ~]$ sqlplus hr/hr
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 19 10:27:57 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 300
SQL> set pagesize 300
SQL> col first_name for a20
SQL> col last_name for a20
SQL> col email for a10
SQL> select * from employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- -------------------- ---------- -------------------- ------------ ---------- ---------- -------------- ---------- -------------
198 Donald OConnell DOCONNEL 650.507.9833 21-JUN-07 SH_CLERK 2600 124 50
199 Douglas Grant DGRANT 650.507.9844 13-JAN-08 SH_CLERK 2600 124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST 4400 101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-04 MK_MAN 13000 100 20
202 Pat Fay PFAY 603.123.6666 17-AUG-05 MK_REP 6000 201 20
203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 6500 101 40
204 Hermann Baer HBAER 515.123.8888 07-JUN-02 PR_REP 10000 101 70
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 12008 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 8300 205 110
100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 24000 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP 17000 100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP 17000 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG 9000 102 60
104 Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000 103 60
105 David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800 103 60
106 Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG 4800 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200 103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 12008 101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000 108 100
110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800 108 100
113 Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900 108 100
114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000 100 30
115 Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100 114 30
116 Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900 114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800 114 30
118 Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600 114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500 114 30
120 Matthew Weiss MWEISS 650.123.1234 18-JUL-04 ST_MAN 8000 100 50
121 Adam Fripp AFRIPP 650.123.2234 10-APR-05 ST_MAN 8200 100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 01-MAY-03 ST_MAN 7900 100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-OCT-05 ST_MAN 6500 100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 ST_MAN 5800 100 50
125 Julia Nayer JNAYER 650.124.1214 16-JUL-05 ST_CLERK 3200 120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-SEP-06 ST_CLERK 2700 120 50
127 James Landry JLANDRY 650.124.1334 14-JAN-07 ST_CLERK 2400 120 50
128 Steven Markle SMARKLE 650.124.1434 08-MAR-08 ST_CLERK 2200 120 50
129 Laura Bissot LBISSOT 650.124.5234 20-AUG-05 ST_CLERK 3300 121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-OCT-05 ST_CLERK 2800 121 50
131 James Marlow JAMRLOW 650.124.7234 16-FEB-05 ST_CLERK 2500 121 50
132 TJ Olson TJOLSON 650.124.8234 10-APR-07 ST_CLERK 2100 121 50
133 Jason Mallin JMALLIN 650.127.1934 14-JUN-04 ST_CLERK 3300 122 50
134 Michael Rogers MROGERS 650.127.1834 26-AUG-06 ST_CLERK 2900 122 50
135 Ki Gee KGEE 650.127.1734 12-DEC-07 ST_CLERK 2400 122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 06-FEB-08 ST_CLERK 2200 122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-JUL-03 ST_CLERK 3600 123 50
138 Stephen Stiles SSTILES 650.121.2034 26-OCT-05 ST_CLERK 3200 123 50
139 John Seo JSEO 650.121.2019 12-FEB-06 ST_CLERK 2700 123 50
140 Joshua Patel JPATEL 650.121.1834 06-APR-06 ST_CLERK 2500 123 50
141 Trenna Rajs TRAJS 650.121.8009 17-OCT-03 ST_CLERK 3500 124 50
142 Curtis Davies CDAVIES 650.121.2994 29-JAN-05 ST_CLERK 3100 124 50
143 Randall Matos RMATOS 650.121.2874 15-MAR-06 ST_CLERK 2600 124 50
144 Peter Vargas PVARGAS 650.121.2004 09-JUL-06 ST_CLERK 2500 124 50
145 John Russell JRUSSEL 011.44.1344.429268 01-OCT-04 SA_MAN 14000 .4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 05-JAN-05 SA_MAN 13500 .3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-05 SA_MAN 12000 .3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT-07 SA_MAN 11000 .3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-08 SA_MAN 10500 .2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-JAN-05 SA_REP 10000 .3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-MAR-05 SA_REP 9500 .25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-AUG-05 SA_REP 9000 .25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-MAR-06 SA_REP 8000 .2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 09-DEC-06 SA_REP 7500 .2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-NOV-07 SA_REP 7000 .15 145 80
156 Janette King JKING 011.44.1345.429268 30-JAN-04 SA_REP 10000 .35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 04-MAR-04 SA_REP 9500 .35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 01-AUG-04 SA_REP 9000 .35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-MAR-05 SA_REP 8000 .3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-DEC-05 SA_REP 7500 .3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 03-NOV-06 SA_REP 7000 .25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-NOV-05 SA_REP 10500 .25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-MAR-07 SA_REP 9500 .15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-JAN-08 SA_REP 7200 .1 147 80
165 David Lee DLEE 011.44.1346.529268 23-FEB-08 SA_REP 6800 .1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-MAR-08 SA_REP 6400 .1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-APR-08 SA_REP 6200 .1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-MAR-05 SA_REP 11500 .25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-MAR-06 SA_REP 10000 .2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-JAN-06 SA_REP 9600 .2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-FEB-07 SA_REP 7400 .15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-MAR-07 SA_REP 7300 .15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-APR-08 SA_REP 6100 .1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-04 SA_REP 11000 .3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-MAR-05 SA_REP 8800 .25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-06 SA_REP 8600 .2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-APR-06 SA_REP 8400 .2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-07 SA_REP 7000 .15 149
179 Charles Johnson CJOHNSON 011.44.1644.429262 04-JAN-08 SA_REP 6200 .1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-JAN-06 SH_CLERK 3200 120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-FEB-06 SH_CLERK 3100 120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-JUN-07 SH_CLERK 2500 120 50
183 Girard Geoni GGEONI 650.507.9879 03-FEB-08 SH_CLERK 2800 120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-JAN-04 SH_CLERK 4200 121 50
185 Alexis Bull ABULL 650.509.2876 20-FEB-05 SH_CLERK 4100 121 50
186 Julia Dellinger JDELLING 650.509.3876 24-JUN-06 SH_CLERK 3400 121 50
187 Anthony Cabrio ACABRIO 650.509.4876 07-FEB-07 SH_CLERK 3000 121 50
188 Kelly Chung KCHUNG 650.505.1876 14-JUN-05 SH_CLERK 3800 122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-AUG-05 SH_CLERK 3600 122 50
190 Timothy Gates TGATES 650.505.3876 11-JUL-06 SH_CLERK 2900 122 50
191 Randall Perkins RPERKINS 650.505.4876 19-DEC-07 SH_CLERK 2500 122 50
192 Sarah Bell SBELL 650.501.1876 04-FEB-04 SH_CLERK 4000 123 50
193 Britney Everett BEVERETT 650.501.2876 03-MAR-05 SH_CLERK 3900 123 50
194 Samuel McCain SMCCAIN 650.501.3876 01-JUL-06 SH_CLERK 3200 123 50
195 Vance Jones VJONES 650.501.4876 17-MAR-07 SH_CLERK 2800 123 50
196 Alana Walsh AWALSH 650.507.9811 24-APR-06 SH_CLERK 3100 124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-MAY-06 SH_CLERK 3000 124 50
107 rows selected.
SQL>
SQL*Plus使用方式
执行数据库管理
可以通过sqlplus来执行启动，停止数据库还有日常管理工作。如下shutdown immediate停止数据库，startup启动数据库：
[oracle@mymachine ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 19 10:39:24 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size 2253944 bytes
Variable Size 486542216 bytes
Database Buffers 1224736768 bytes
Redo Buffers 6795264 bytes
Database mounted.
Database opened.
SQL>
执行SQL脚本
使用@或者start、@@执行自定义SQL脚本，或者oracle软件安装后自带的脚本。
[oracle@mymachine ~]$ echo "select name from v\$datafile;" > run.sql
[oracle@mymachine ~]$ cat run.sql
select name from v$datafile;
[oracle@mymachine ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 19 10:46:27 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @run.sql
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/mydb/system01.dbf
/u01/app/oracle/oradata/mydb/sysaux01.dbf
/u01/app/oracle/oradata/mydb/undotbs01.dbf
/u01/app/oracle/oradata/mydb/users01.dbf
/u01/app/oracle/oradata/mydb/example01.dbf
SQL> start run.sql
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/mydb/system01.dbf
/u01/app/oracle/oradata/mydb/sysaux01.dbf
/u01/app/oracle/oradata/mydb/undotbs01.dbf
/u01/app/oracle/oradata/mydb/users01.dbf
/u01/app/oracle/oradata/mydb/example01.dbf
SQL> @@run.sql
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/mydb/system01.dbf
/u01/app/oracle/oradata/mydb/sysaux01.dbf
/u01/app/oracle/oradata/mydb/undotbs01.dbf
/u01/app/oracle/oradata/mydb/users01.dbf
/u01/app/oracle/oradata/mydb/example01.dbf
SQL>
编译失效的对象
[oracle@mymachine ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 19 10:49:51 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2025-05-19 10:53:42
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2025-05-19 10:53:43
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
执行BASH命令
可以直接在SQL*Plus下执行bash命令，比如host或者!加上bash命令：
[oracle@mymachine ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 19 10:58:10 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> host cat /home/oracle/run.sql
select name from v$datafile;
SQL> ! cat /home/oracle/run.sql
select name from v$datafile;
SQL>
生成报表
使用格式化输出spool成csv格式的平面文件。
比如要spool输出数据文件的csv格式平面文件output.csv，我们可以写如下run.sql，然后用sqlplus -S静默模式登录执行方式运行run.sql得到数据。
[oracle@mymachine ~]$ cat run.sql
set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on
set headsep off
spool output.csv
select file#||','||name||','||bytes from v$datafile;
spool off
exit
[oracle@mymachine ~]$ sqlplus -S / as sysdba @run.sql
1,/u01/app/oracle/oradata/mydb/system01.dbf,828375040
2,/u01/app/oracle/oradata/mydb/sysaux01.dbf,566231040
3,/u01/app/oracle/oradata/mydb/undotbs01.dbf,524288000
4,/u01/app/oracle/oradata/mydb/users01.dbf,5242880
5,/u01/app/oracle/oradata/mydb/example01.dbf,363069440
[oracle@mymachine ~]$ cat output.csv
1,/u01/app/oracle/oradata/mydb/system01.dbf,828375040
2,/u01/app/oracle/oradata/mydb/sysaux01.dbf,566231040
3,/u01/app/oracle/oradata/mydb/undotbs01.dbf,524288000
4,/u01/app/oracle/oradata/mydb/users01.dbf,5242880
5,/u01/app/oracle/oradata/mydb/example01.dbf,363069440
[oracle@mymachine ~]$
在12.2开始以后的版本中我们可用更方便的方式设置set markup csv on来输出csv格式平面文件output.csv。
[oracle@oracle19c ~]$ cat run.sql
set heading off
set markup csv on
spool output.csv
select file#,name,bytes from v$datafile;
spool off
exit
[oracle@oracle19c ~]$ sqlplus -S / as sysdba @run.sql
1,"/u01/app/oracle/oradata/ORCL/system01.dbf",1405091840
3,"/u01/app/oracle/oradata/ORCL/sysaux01.dbf",1457520640
4,"/u01/app/oracle/oradata/ORCL/undotbs01.dbf",1258291200
7,"/u01/app/oracle/oradata/ORCL/users01.dbf",5242880
[oracle@oracle19c ~]$ cat output.csv
1,"/u01/app/oracle/oradata/ORCL/system01.dbf",1405091840
3,"/u01/app/oracle/oradata/ORCL/sysaux01.dbf",1457520640
4,"/u01/app/oracle/oradata/ORCL/undotbs01.dbf",1258291200
7,"/u01/app/oracle/oradata/ORCL/users01.dbf",5242880
[oracle@oracle19c ~]$
异机迁移数据
通常我们要在A机上创建一个和B机一样的表的时候我们一般DBLINK，通过工具迁移或者exp/imp，expdp/impdp来实现，这里我们可用通过在SQL*Plus下执行COPY功能简单灵活快速实现小表迁移。比如下面是将192.168.17.91的hr模式中employees复制到192.168.17.26本机的orcl实列中sunying模式下并改名成employees_copy：
[oracle@oracle19c ~]$ sqlplus sunying/sunying
SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 19 20:12:30 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Tue Apr 29 2025 17:14:29 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
SQL> COPY FROM hr/hr@192.168.17.91:1521/mydb TO sunying/sunying@127.0.0.1:1521/orcl CREATE employees_copy USING select * from employees;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table EMPLOYEES_COPY created.
107 rows selected from hr@192.168.17.91:1521/mydb.
107 rows inserted into EMPLOYEES_COPY.
107 rows committed into EMPLOYEES_COPY at sunying@127.0.0.1:1521/orcl.
SQL> select count(1) from employees_copy;
COUNT(1)
----------
107
SQL>
注意COPY FROM适合没有特殊字段类型的小表。
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, …)] USING query
其他特殊使用方式
sqlplus -prelim功能主要目的是在正常连接不起作用的情况下访问数据库结构（SGA），此选项在无法连接到数据库的情况下挂起非常有帮助，但是我们可能需要执行oradebug的命令。也可用通过shutdown abort来关闭数据库，注意此操作谨慎。下面是一个例子碰到最大连接数sqlplus / as sysdba无法连接后使用sqlplus -prelim选项，将有捕获SGA，oradebug hanganalyze 12或者systemstate转储之类的诊断信息，以分析解决问题。
[oracle@mymachine ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 19 20:47:14 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (40) exceeded
Enter user-name: ^C
[oracle@mymachine ~]$ sqlplus -prelim / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 19 20:47:20 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 267
Statement processed.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_34277.trc
SQL>
总结
SQL*Plus作为Oracle原生命令行工具，虽然界面简单，但功能强大，熟练掌握可以极大提高DBA的工作效率。
最后修改时间：2025-05-20 09:57:11
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
