Oracle中SQL*plus常用命令

目录

1 SQL*plus工具

1.1 oracle自带的工具

1.2 cmd命令

1.3 修改sys密码

2 SET 命令概述

2.1 使用 SET 命令设置运行环境

2.1.1 PAGESIZE 变量

2.1.2 NEWPAGE 变量

2.1.3  LINESIZE 变量

2.1.4 PAUSE 变量

2.1.5 NUMFORMAT 变量

3 SQL*plus的常用命令

3.1 HELP 命令

3.2 DESCRIBE 命令

3.3 SPOOL 命令

3.4 DEFINE 命令

3.5 SHOW 命令

3.6 EDIT 命令

3.7 SAVE 命令

3.8 GET 命令

3.9 START 和@命令

4 格式化查询结果

4.1 COLUMN 命令

4.1.1 FORMAT 选项

4.1.2 HEADING 选项

4.1.3 NULL 选项

4.1.4 ON|OFF 选项

4.1.5 WRAPPED/WORD_WRAPPED 选项

4.2 TTITLE 和 BTITLE 命令

1 SQL*plus工具
在 Oracle 11g 数据库系统中，用户对数据库的操作主要是通过 SQL*Plus 来完成的。 SQL*Plus 作为 Oracle 的客户端工具，既可以建立位于数据库服务器上的数据连接，也可以建立位于网络中的数据连接。下面将介绍如何启动 SQL*Plus 和如何使用 SQL*Plus 连接到数据库。

1.1 oracle自带的工具
选择“开始” /“所有程序” /Oracle-OraDb11g_home1/“应用程序开发” /SQL*Plus ，打开如图 所示的 SQL*Plus 启动界面

在命令提示符的位置输入登录用户（如 SYSTEM 或 SYS 等系统管理账户）和口令（口令是在安装或创建数据库时指定的）， 若输入的用户名和口令正确， 则 SQL*Plus 将连接到数据库， 如图所示。需要特殊身份的as sysdba或者sysoper，否则就是普通的用户，就什么也不用添加

1.2 cmd命令
还可以通过在“运行”中输入 cmd 命令来启动命令行窗口，然后在该窗口输入 SQL*Plus命令来连接数据库。使用 SQL*Plus 命令连接数据库实例的语法格式如下：

SQLPLUS username[/password][@connect_identifier] [AS SYSOPER|SYSDBA]
username：表示登录用户名。
password：表示登录口令。
@connect_identifier：表示连接的全局数据库名，若连接本机上的默认数据库，则可以省略。
在输入 Oracle 数据库命令时，其关键字不区分大小写（比如，输入 sqlplus 或 SQLPLUS 都可以），但参数区分大小写。

1.3 修改sys密码
oracle初始密码有人说是没有密码或者密码是 任何字母+as+sysdba，可以通过在dos窗口里面使用命令sqlplus /nolog登录，然后用conn /as sysdba尝试下，反正结果都不顺利，那就用如下命令
orapwd file=D:\SoftWare\SQL\Oracle\product\11.1.0\db_1\database\PWDorcl.ora password=你设定的新密码;

可能报错:

OPW-00005: 存在同名文件 - 请删除或重命名。只需要把已经存在的PWDorcl.ora删除掉，或者用新名字PWDorcl123.ora
OPW-00001: 无法打开口令文件。win环境需要用管理员启动dos窗口执行上述命令就可以成功修改sys密码
或者用命令修改：

alter user sys identified by 新密码;
 在使用sqlplus命令用sys用户登录oracle时，需要 添加sysdba，比如：sys as sysdba ，然后在输入上面重新修改的密码就可以了，但是登录system就不用添加了，这是sys和system用户不一样导致

sys和system用户的区别：

system：用户具有DBA权限，但是没有SYSDBA权限，用户只能用normal身份登陆。system是数据库内置的一个普通管理员，手工创建的任何用户在被授予dba角色后都跟这个用户差不多。

sys：用户具有“SYSDBA”或者“SYSOPER”权限，登陆也只能用这两个身份，不能用normal。SYS用户具有DBA权限，并具有SYS模式。只能通过SYSDBA登录数据库，是Oracle数据库中权限最高的帐号

2 SET 命令概述
在 Oracle 11g 数据库中，用户可以使用 SET 命令来设置 SQL*Plus 的运行环境， SET 命令的语法格式为：

SET system_variable value
system_variable：变量名。
value：变量值。
SET 命令的常用变量名、可选值及其说明下图所示。
变 量 名    变量可选值    说 明
ARRAY[SIZE]    20（默认值） |n    设置查询的行数，是 SQL*Plus 一次从数据库获取的行数，有
效值为 1 至 5000。大的值可提高查询和子查询的有效性,可获取
许多行，但也需要更多的内存。当超过 1000 时，其效果不大
AUTO[COMMIT]    OFF（默认值） |ON|
IMMEDIATE    控制 Oracle 对数据库的修改的提交。设置为 ON 时，在 Oracle
执行每个 SQL 命令或 PL/SQL 块后对数据库自动提交修改；设
置为 OFF 时，则制止自动提交，需要手工地提交修改（即需要
执行 COMMIT 命令）； IMMEDIATE 功能同 ON
BLO[CKTERMINATOR]    .（默认值） |C    设置非字母数字字符，用于结束 PL/SQL 块。要执行块时，必
须发出 RUN 命令或/命令
CMDS[EP]    ;|C|OFF（默认值） |ON    设置非字母数字字符， 用于分隔在一行中输入的多个 SQL*Plus
命令， ON 或 OFF 控制在一行中是否能输入多个命令。 ON 时
将自动地将命令分隔符设为分号（;），其中 C 表示所置字符
ECHO    OFF|ON    控制 START 命令是否列出命令文件中的每一命令，若设置为
ON 时，则列出命令；若为 OFF，则制止列清单
FLU[SH]    OFF|ON（默认值）    控制输出送至用户的显示设备。设置为 OFF 时，运行操作系统
做缓冲区输出；设置为 ON 时，不允许缓冲。仅当非交互方式
运行命令文件时使用 OFF，这样可减少程序 I/O 总数，从而改
进性能
HEA[DING]    OFF|ON（默认值）    控制报表中列标题的打印。设置为 ON 时，在报表中打印列标
题；为 OFF 时禁止打印列标题
LIN[ESIZE]    80（默认值） |n    设置 SQL*Plus 在一行中显示的最多字符总数，它还控制在
TTITLE 和 BTITLE 中对准中心的文本和右对齐文本。可定义
LINESIZE 为 1 至最大值，其最大值依赖于操作系统
NEWP[AGE]    1（默认值） |n    设置一页中空行的数量
NULL    text    设置表示空值（NULL）的文本，如果 NULL 没有文本，则显
示空格（默认时）。使用 COLUMN 命令中的 NULL 子句可控
制 NULL 变量对该列的设置
NUMF[ORMAT]    格式    设置显示数值的默认格式，该格式是数值格式
PAGES[IZE]    14（默认值） |n    设置从顶部标题至页结束之间的行数
PAU[SE]    OFF（默认值） |ON|TEXT    设置 SQL*Plus 输出结果是否滚动显示。当设置为 ON 时，表示
输出结果的每一页都暂停，用户按 Enter 键后继续显示。在设置
PAUSE 的值为 ON 之后，若再设置字符串时——设置 TEXT 的
值，则每次暂停都将显示该字符串
RECSEP    WR[APPED] （默认值）|EA
[CH]|OFF    指定显示或打印记录分行符的条件。一个记录分行符是由
RECSEPCHAR 指定的字符组成的单行，空格为 RECSEPCHAR
的默认字符
SERVEROUT[PUT]    OFF|ON [SIZE n]    控制在 SQL*Plus 中的存储过程是否显示输出。若设置为 OFF，
则禁止；若设置为 ON，则显示输出。 SIZE 设置缓冲输出的字节
数，缺省值为 2000， n 不能小于 2000 或大于 100 万
SHOW[MODE]    OFF（默认值） |ON    控制 SQL*Plus 在执行 SET 命令时是否列出其新老值 old 或 new
的设置。其中， ON 值表示列出新老值
SPA[CE]    1（默认值） |n    设置输出列之间空格的数目，其最大值为 10
SQLCO[NTINUE]    >;（默认值） |文本    在一附加行上继续某个 SQL*Plus 命令时， SQL*Plus 以该设置的
字符序列进行提示
SQLN[UMBER]    OFF|ON（默认值）    为 SQL 命令和 PL/SQL 块的第二行和后继行设置提示。若为 ON，
则提示行号；若为 OFF，则提示设置为 SQLPROMPT 的值
TI[ME]    OFF（默认值） |ON    控制当前日期的显示。若为 ON，则在每条命令提示前显示当前
时间；若为 OFF，则禁止时间的显示
TIMI[NG]    OFF（默认值） |ON    控制时间统计的显示。若为 ON，则显示每一个运行的 SQL 命令
或 PL/SQL 块的时间统计；若为 OFF，则禁止每一个命令的时间
统计
UND[ERLINE]    （默认值） - |C|OFF|ON（默
认值）    设置用在 SQL*Plus 报表中下划线列标题的字符。 ON 或 OFF 将
下划线置成开或关状态
VER[IFY]    OFF|ON（默认值）    控制 SQL*Plus 用值替换前、后是否列出命令的文本。若为 ON，
则显示文本；若为 OFF，则禁止列清单
WRA[P]    OFF|ON（默认值）    控制 SQL*Plus 是否截断数据项的显示。若为 OFF，则截断数据
项；若为 ON，则允许数据项缠绕到下一行
用户可以设置在 SQL*Plus 命令提示符“SQL>”前面显示当前的系统时间。但需要注意的是：通过 SET 命令设置的环境变量是临时的，不是永久的。当用户退出 SQL*Plus 环境后，用户设置的环境参数会全部丢失。
使用“set time on”命令设置在 SQL*Plus 命令提示符“SQL>”前面显示当前的系统时间，具体代码如下。

SQL> set time on
10:27:06 SQL>
2.1 使用 SET 命令设置运行环境
在对 SET 命令的功能及其若干常用变量选项了解之后，以下经常用到的几个变量选项及其实例应用进行详细讲解。

2.1.1 PAGESIZE 变量
该变量用来设置从顶部标题至页结束之间的行数，其语法格式如下：

SET PAGESIZE value
value 变量的默认值为 14，根据实际情况的需要，用户可以修改 value 的值，该值是一个正整数。当 SQL*Plus 返回查询结果时，它首先会显示用户所选择数据的列标题，然后在相应列标题下显示数据行，上下两个列标题所在行之间的空间就是 SQL*Plus 的一页。一页中所显示的数据行的数量就是PAGESIZE 变量的值。若要查看当前 SQL*Plus 环境中的一页有多少行，可以使用 show pagesize 命令。

SQL> show pagesize
pagesize 14
不要把当前窗口区域内能够显示的行数看做 SQL*Plus 环境中一页的行数，一页的行数由PAGESIZE 变量值来决定。如果默认的 14 行不符合实际情况的需要，可以修改 PAGESIZE 变量的值。
使用 set pagesize 命令修改一页的行数为 18，然后再使用新的 pagesize 值显示数据行，具体代码如下。

SQL> set pagesize 18
SQL> select user_id,username,account_status from dba_users;
2.1.2 NEWPAGE 变量
该变量用来设置一页中空行的数量，其语法格式如下：

SET NEWPAGE value
value 的默认值为 1，根据实际情况的需要，用户可以修改 value 的值，该值是一个正整数

2.1.3  LINESIZE 变量
该变量用来设置在 SQL*Plus 环境中一行所显示的最多字符总数，其语法格式如下：

SET LINESIZE value
value 的默认值为 80，根据实际情况的需要，用户可以修改 value 的值，该值是一个正整数。如果数据行的宽度大于 LINESIZE 变量的值，当在 SQL*Plus 环境中按照 LINESIZE 指定的数量输出字符时，数据就会发生折行显示的情况。如果适当调整 LINESIZE 的值，使其值等于或稍大于数据行的宽度，则输出的数据就不会折行。所以在实际操作 Oracle 数据库的过程中，要根据具体情况来适当调整 LINESIZE 的值。
 

2.1.4 PAUSE 变量
该变量用来设置 SQL*Plus 输出结果是否滚动显示，其语法格式如下：

SET PAUSE value
value 变量值有以下 3 种情况：

OFF：这是默认值，返回结果一次性输出完毕，中间的每一页不会暂停。
ON：表示输出结果的每一页都暂停，用户按 Enter 键后继续显示。
TEXT：在设置 PAUSE 的值为 ON 之后，若再设置 TEXT 的值，则每次暂停都将显示该字符串。当 PAUSE 的值为 OFF 时，设置TEXT 值没有任何意义。
当在 SQL*Plus 环境中显示多行数据，并且一页无法容纳下这么多数据行时，如果 PAUSE 变量值为 OFF，则 SQL*Plus 窗口输出的数据行会快速滚动，非常不利于用户查看。这就需要数据行在滚动时最好能够按页暂停，以便于用户逐页地查看输出结果。当把 PAUSE 变量的值设置为 ON 时，就可以实现控制 SQL*Plus 在显示完一页后暂停滚动，直到按 Enter 键后才继续显示下一页。另外， 在设置 PAUSE
变量值为 ON 之后，还可以通过 PAUSE 变量设置暂停后显示的字符串，以便于提示用户操作。

SQL> set pause on
SQL> set pause '按<enter>键继续'
SQL> select user_id,username,account_status from dba_users;
2.1.5 NUMFORMAT 变量
该变量用来设置显示数值的默认格式，该格式是数值格式，其语法格式如下：

SET NUMFORMAT format
format 为数值的掩码，数值的常用掩码及其说明如表

掩 码    说 明    举 例
9    查询结果中数字替换格式中的掩码（不会占用空格）    999
0    格式中的掩码屏蔽掉查询结果中的数字（但是会占用空格位）    999.00
$    在查询结果中的数字前添加美元前缀    $999
S    为数字显示符号类型，通常用于显示查询结果中的正负数字    S999
,    在字符“,”位置上放置逗号    999,99
3 SQL*plus的常用命令
3.1 HELP 命令
SQL*Plus 工具提供了许多操作 Oracle 数据库的命令，并且每个命令都有很多选项，把所有命令的选项都记住，这对于用户来说非常困难。为了解决这个难题， SQL*Plus 提供了 HELP 命令来帮助用户查询指定命令的选项。 HELP 可以向用户提供被查询命令的标题、功能描述、缩写形式和参数选项（包括必选参数和可选参数）等信息。 HELP 命令的语法形式如下：

HELP|? [topic]
“?”表示一个命令的部分字符，这样就可以通过提供命令的部分字符以模糊查询的方式来查询命令格式；
topic 参数表示将要查询的命令的完整名称。若省略“?”和“topic”参数，直接执行 HELP命令，则会输出 HELP 命令本身的语法格式及其功能描述信息。
使用 help index 命令来查看 SQL*Plus 命令清单，具体代码如下
 

SQL> help index

3.2 DESCRIBE 命令
在 SQL*Plus 的众多命令中， DESCRIBE 命令可能是被使用得最频繁的一个，它用来查询指定数据对象的组成结构。比如，通过 DESCRIBE 命令查询表和视图的结构，查询结果就可以列出其各个列的名称、是否为空及类型等属性。 DESCRIBE 命令的语法形式如下：

desc[ribe] object_name;
describe 可以缩写为 desc， object_name 表示将要查询的对象名称。
DESCRIBE 命令不仅可以查询表、视图的结构，而且还可以查询过程、函数和程序包等PL/SQL 对象的规范

3.3 SPOOL 命令
SPOOL 命令可以把查询结果输出到指定文件中，这样可以保存查询结果并方便打印。

SPOOL 命令的语法格式如下：

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
参数 file_name 用于指定脱机文件的名称，默认的文件扩展名为 LST。在该参数后面可以跟一个关键字，该关键字有以下几种情况：

CRE[ATE]：表示创建一个新的脱机文件，这也是 SPOOL 命令的默认状态。
REP[LACE]：表示替代已经存在的脱机文件。
APP[END]：表示把脱机内容附加到一个已经存在的脱机文件中。
OFF | OUT：表示关闭 SPOOL 输出

注意：只有使用 spool off 或 spool out 命令关闭输出，才会在输出文件中看到输出的内容，否则输出文件中无内容或无更新内容。

3.4 DEFINE 命令
该命令用来定义一个用户变量并且可以分配给它一个 CHAR 值，其语法格式如下：

DEF[INE] [variable] | [variable = text]
variable：表示定义的变量名。
 text：变量的 CHAR 值
例子如下： 

SQL> def a =qwe;
SQL> def a;
DEFINE a                = "qwe" (CHAR)
3.5 SHOW 命令
该命令用来显示 SQL*Plus 系统变量的值或 SQL*Plus 环境变量的值，其语法格式如下：

SHO[W] option
option 表示要显示的系统选项，常用的选项有 ALL、 PARAMETERS [parameter_name]、 SGA、SPOOL、 USER 等

3.6 EDIT 命令
SQL 语句或 PL/SQL 块在执行完毕之后，可以被存储在一个被称为 SQL 缓冲区的内存区域中，用户可以从 SQL 缓冲区中重新调用、编辑或运行那些最近输入的 SQL 语句。若要编辑 SQL 缓冲区中的最近一条 SQL 语句或 PL/SQL 块，即可以在 SQL*Plus 环境中直接编辑，也可以使用 EDIT 命令实现在记事本中编辑。 EDIT 命令用来编辑 SQL 缓冲区或指定磁盘文件中的 SQL 语句或 PL/SQL 块，其语法
格式如下：

ED[IT] [file_name[.ext]]
参数 file_name 表示要编辑的磁盘文件名。若在 SQL*Plus 中只输入“EDIT”命令，而不指定 file_name参数的值，则表示编辑 SQL 缓冲区中的最近一条 SQL 语句或 PL/SQL 块。
执行 EDIT 命令后， SQL*Plus 工具将打开一个包含有 SQL 语句或 PL/SQL 块的记事本，用户就可以在记事本环境下编辑 SQL 语句或 PL/SQL 块。
注意：如果要执行 SQL 缓冲区中最近一条 SQL 语句，可以使用运行命令“ /”来执行，这样可以简化操作

3.7 SAVE 命令
该命令实现将 SQL缓冲区中的最近一条 SQL语句或 PL/SQL块保存到一个文件中，其语法格式如下：

SAVE file_name
参数 file_name 表示要保存的文件名，如果不为保存的文件指定路径，则该文件会保存在 Oracle系统安装的主目录中（但不建议这样做）。如果不为保存的文件指定扩展名，则默认扩展名为“.sql”，即保存的文件为一个 SQL 脚本文件。

如果要清空 SQL 缓冲区中的内容，可以使用 clear buffer 命令

3.8 GET 命令
该命令实现把一个 SQL 脚本文件的内容放进 SQL 缓冲区，其语法格式如下：

GET [FILE] file_name[.ext] [LIST | NOLIST]
file_name：要检索的文件名，如果省略了文件的扩展名，则默认文件的扩展名为“.sql”。
LIST：指定文件的内容加载到缓冲区时显示文件的内容。
NOLIST：指定文件的内容加载到缓冲区时不显示文件的内容。
执行 GET 命令时，如果 file_name 参数不包括被检索文件的路径，则 SQL*Plus 工具会在 Oracle系统安装的主目录下检索指定文件。在 SQL*Plus 找到指定文件后， 会把文件中的内容加载到 SQL*Plus缓冲区，并显示该文件的内容。

3.9 START 和@命令
这两个命令都可以用来执行一个 SQL 脚本文件，它们的语法格式如下：

STA[RT] {url|file_name[.ext]} [arg ...]
@ {url|file_name[.ext]} [arg ...]
url：表示要执行的 SQL 脚本文件的路径。
file_name：表示包含 SQL 脚本的文件名。
arg：其他参数。
4 格式化查询结果
4.1 COLUMN 命令
该命令可以实现格式化查询结果、设置列宽度、重新设置列标题等功能。其语法格式如下：

COL[UMN] [column_name | alias | option]
column_name：用于指定要设置的列的名称。
alias：用于指定列的别名，通过它可以把英文列标题设置为汉字。
option：用于指定某个列的显示格式， option 选项的值及其说明如下表所示。
option 选项的值    说 明
CLEAR    清除指定列所设置的显示属性，从而回复列使用默认的显示属性
FORMAT    格式化指定的列
HEADING    定义列标题
JUSTIFY    调整列标题的对齐方式。默认情况下：数值类型的列为右对齐，其他类型的列为左对齐
NULL    指定一个字符串，如果列的值为 null，则由该字符串代替
PRINT/NOPRINT    显示列标题或隐藏列标题，默认为 PRINT
ON|OFF    控制定义的显示属性的状态， OFF 表示定义的所有显示属性都不起作用，默认为 ON
WRAPPED    当字符串的长度超过显示宽度时，将字符串的超出部分折叠到下一行显示
WORD_WRAPPED    表示从一个完整的字符处折叠
TRUNCATED    表示截断字符串尾部
如果在关键字column 后面未指定任何参数， 则 column 命令将显示 SQL*Plus 环境中所有列的当前定义属性；如果在 column 后面指定某个列名，则显示指定列的当前定义属性。
常用 option 选项值进行举例说明。

4.1.1 FORMAT 选项
该选项用于格式化指定的列，需要在 FORMAT 关键字的后面跟一个掩码格式。

SQL> col sal format $999,999.00
SQL> select empno,ename,sal from scott.emp;
4.1.2 HEADING 选项
该选项用于定义列标题，比如，许多数据表或视图的列名都为英文形式，可以使用此选项将英文形式的列标题显示为中文形式。

SQL> col empno heading 雇员编号
SQL> col ename heading 雇员姓名
SQL> col sal heading 雇员工资
SQL> select empno,ename,sal from scott.emp;
4.1.3 NULL 选项
在该选项的后面指定一个字符串，如果列的值为 null，则用该字符串代替空值

SQL> col comm null '空值'
SQL> select empno,ename,comm from scott.emp where comm is null;
4.1.4 ON|OFF 选项
该选项用于控制定义的显示属性的状态， OFF 表示定义的所有显示属性都不起作用，默认为 ON。

4.1.5 WRAPPED/WORD_WRAPPED 选项
这两个选项都用于实现折行的功能， WRAPPED 选项按照指定长度折行， WORD_WRAPPED 选项按照完整字符串折行。

4.2 TTITLE 和 BTITLE 命令
在 SQL*Plus 环境中，执行 SQL 语句后的显示结果在默认情况下包括列标题、页分割线、查询结果和行数合计等内容，用这些默认的输出信息打印报表，如果能为整个输出结果设置报表头（即头标题）、为每页都设置页标题和页码、为整个输出结果设置报表尾（如打印时间或打印人员），那么使用这样的输出结果打印报表一定非常美观。为了实现这些功能， SQL*Plus 工具提供了TTITLE 和 BTITLE 命令，这两个命令分别用来设置打印时每页的顶部和底部标题。其中， TTITLE 命令的语法格式如下：

TTI[TLE] [printspec [text|variable] ...] | [OFF|ON]
printspec：用来作为头标题的修饰性选项， printspec 选项的值及其说明如下所示
text：用于设置输出结果的头标题（即报表头文字）。
variable：用于在头标题中输出相应的变量值。
OFF：表示禁止打印头标题。
ON：表示允许打印头标题。
printspec 选项的值及其说明

printspec 选项的值    说 明
COL    指定在当前行的第几列打印头部标题
SKIP    跳到从下一行开始的第几行，默认为 1
LEFT    在当前行中左对齐打印数据
CENTER    在当前行中间打印数据
RIGHT    在当前行中右对齐打印数据
BOLD    以黑体打印数据
注意：BTITLE 的语法格式与 TTITLE 的语法格式相同。 如果在 TTITLE 或 BTITLE 命令后没有任何参数，则显示当前的 TTITLE 或 BTITLE 的定义。
本文于转载
原文链接：https://blog.csdn.net/u012060033/article/details/90575037