Oracle命令行管理工具SQL * Plus技巧知多少？

Oracle命令行管理工具SQL * Plus技巧知多少？
香生享IT
香生享IT
拥有十几年数据库领域的开发运维经验的IT人
​
目录
收起
SQL * Plus概述
SQL * Plus是什么？
谁在使用SQL * Plus？
SQL * Plus可以用来什么？
使用SQL * Plus
基本操作
环境配置
格式化结果
sqlplus命令行
案例分享
生成通用的平面文件
总结
摘要
SQL* Plus作为Oracle数据库简洁强大的管理工具，估计很多人不陌生。开发者、应用维护人员及数据库管理员在工作中通常会使用它来简单而高效的实现数据库操作和管理。
SQL* Plus是什么？Oracle数据库相关的哪些人会用到SQL* Plus，用它可以来做什么？怎么来使用SQL* Plus？基于多年相关工作经验，本文从零开始一步步来讲述，文章最后分享SQL * Plus导出通用平面文件的案例。

SQL * Plus概述
SQL * Plus是什么？
SQL * Plus是一个交互式操控和管理Oracle数据库命令行工具，它有自己的环境和命令。 Oracle数据库服务器(Server)软件、客户端(Client)软件及Instant Client中都包含SQL * Plus工具，不需要额外进行安装。

谁在使用SQL * Plus？
作为Oracle自带的高效管理工具，数据库操作人员可以使用它很方便完成日常工作。在大多数使用Oracle数据库的IT部门中，有如下3类人会用到SQL * Plus工具：

数据库相关的开发人员
数据库应用维护人员
数据库管理员
SQL * Plus可以用来什么？
SQL * Plus提供对数据库的访问，它允许你输入并执行SQL，PL/SQL， SQL* Plus和操作系统命令，可以用来做如下工作：

对查询结果进行格式化、计算、存储和打印： 可以通过查询结果进行定制化的输出，还可以生成交互式的HTM报告。
开发和运行SQL语句或者批处理脚本：可以执行DML、DDL、DCL等各种SQL操作实现对Oracle中数据库的增删改查等操作。
执行数据库管理：DBA可以通过它来实现日常管理操作，启停数据库，查看系统视图进行日常巡检，通过性能视图或SQL脚本进行性能调优，还可以进行数据库诊断等操作。
使用SQL * Plus
前面通过了解SQL * Plus是什么？谁来用？可以用来解决日常工作中很多问题，接下来重点来了解下怎么来使用 SQL * Plus。 SQL * Plus本身提供非常丰富强大的功能，好多DBA可能用了多年才涉及到10%左右的功能。 “苦海无边，学海无涯”，不妨了解下去可能会有小惊喜哦，一个小小的参数或者命令可能大幅提高工作效率。

基本操作
先来看SQL * Plus怎么来调用，连接数据库并进行简单的操作。可以通过which sqlplus命令来查看命令所在的路径。

启动sqlplus命令登录连接数据库，分享几个常用的连接语句
–查看版本
$ sqlplus -V

SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

–tns_alias为tnsnames.ora中定义的别名。
–通过用户、密码、数据库地址、端口、数据库名称或服务名连接到数据库。
–Oracle监听端口默认为1521为可选，其中<database_ip>可以是scan域名或者VIP
$ sqlplus system/oracle@<tns_alias>
$ sqlplus /@<database_ip>:/<database_name>

–通过本地数据库系统以dba角色直接连接到数据库实例
$ export ORACLE_SID=<instance_name>
$ sqlplus / as sysdba

–sqlplus交互连接数据库
$ sqlplus
SQL> conn system/oracle
SQL> connect @<database_ip>/
Enter password: --此处手动输入密码
通过sqlplus交互界面查看环境变量
–show all可以查看所有的变量及其对应的值
SQL> show all;
appinfo is OFF and set to “SQL*Plus”
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator “.” (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colinvisible OFF
…

show user;
USER is “SYS”

–也可以查看具体的某个变量，可以通过set进行修改。以下将colsep列分隔符从空格设置为逗号

show colsep;
colsep " "
set colsep “,”
show colsep;
colsep “,”
SQL操作
SQL> select * from dual;

D

X

–设置会话参数，获取当前时间的
SQL> alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
SQL> select sysdate from dual;

SYSDATE

2022-10-19 11:39:47

–开发人员使用绑定变量,以包含id和cdate的test表为例
SQL> variable id number;
SQL> var dt varchar2(8);
SQL> select * from test where id= :id
and to_char(cdate, ‘yyyy-mm-dd’) = :dt;

–DBA操作 刷新共享池、关闭实例
SQL> alter system flush shared_pool;
SQL> shutdown immediate;
执行和编辑sql脚本。 指定要运行的脚本和参数的名称。可以从本地文件系统或web服务器调用脚本。 SQL * Plus将参数传递给脚本，就像使用SQL * Plus START命令执行文件一样。如果没有指定文件后缀(文件扩展名)，则使用SET suffix命令定义的后缀。默认后缀名为.sql。
SQL> @<your_script>
SQL> start <script.sql>

SQL> edit <script.sql>
执行操作系统命令。可以使用!进入操作系统界面，再通过exit可以回到SQL * Plus用户界面。
–sqlplus中调用os命令
SQL> !date
Wed Oct 19 14:28:29 CST 2022

–切换到os界面
SQL> !
$ date
Wed Oct 19 14:28:33 CST 2022

–返回到sqlplus界面
$ exit
exit

SQL> !date
Wed Oct 19 14:28:39 CST 2022
获取帮助，可以通过help加上所要了解的命令可以查看该命令的语法知识。

退出sqlplus，使用exit或者quit都可以退出
SQL> quit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
环境配置
SQL * Plus有自己的环境变量和参数，登录前的操作系统环境变量可能会影响它的使用。举个简单例子，开发人员抱怨说同一sql用图形化工具查出来的是中文数据，为什么用SQL * Plus命令行查出来的是乱码？由于字符集的差异和环境变量的设置会导致中文以其他不兼容的编码来显示。

这里主要列出常见的一些环境变量，如果想了解更多的环境配置可参考 Oracle 19c的Configuring SQL*Plus。

ORACLE_HOME, PATH ,LD_LIBRARY_PATH: 这三个分别用来指示Oracle软件的主目录、执行程序和库路径，它会告诉你sqlplus命令及依赖的库文件来自哪里？如果在多个数据库版本环境中，比如你的环境中安装了Oracle 11.2和12.2，那这两个变量的设置可能会决定你调用的是哪个sqlplus。PATH中包含的是一连串的路径清单，会从前往后遍历可执行程序。
ORACLE_SID：指定数据库实例名称。在数据库服务器上使用，直接通过实例名连接到数据库中。
NLS_LANG: 全球化行为的环境变量，一般包含地区和字符集。比如american_america.utf8。登录UTF8字符集编码的数据库以中文显示或输入可以按如下设置
export NLS_LANG=“SIMPLIFIED CHINESE_CHINA.AL32UTF8”
TNS_ADMIN: 指定tnsnames.ora的位置路局。如果不指定，默认使用$ORACLE_HOME/network/admin。
SQLPATH: 指定SQL脚本存放的路径，其值通常是是一个用冒号分隔的目录列表。在交换界执行脚本时不需要指定绝对路径。在Windows中，SQLPATH是在安装期间在注册表项中定义的。
除了环境变量配置，SQL * Plus本身提供可定义配置的概要文件。大多数情况下DBA在安装完数据库后，通常会对glogin.sql或者login.sql进行配置，以方便登录后的操作或显示。

全局配置文件(glogin.sql)： 可以包含SQL* Plus脚本中可以包含的任何内容，例如系统变量设置或DBA想要实现的其他全局设置。通过SQLPLUS或CONNECT命令成功连接Oracle数据库后会起效果。
用户配置文件(login.sql)： 可以包含SQL* Plus脚本中可以包含的任何内容，但设置只适用于用户的会话。
如下例子，通过配置glogin.sql文件设置sqlplus登录后提示符，以及默认编辑器为vi。

–不设置变量的效果
$ sqlplus / as sysdba

SQL> show linesize;
linesize 80

$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql
–用户@连接符，编辑器设置为vi
set sqlprompt "_user’@'_connect_identifier> "
define _editor=vi
–将每个报表行上显示的字符数设置为999
SET LINESIZE 999

–做了如上设置可以看到如下的效果
$ sqlplus / as sysdba

SYS@orcl1> show linesize;
linesize 999
格式化结果
许多开发人员或初学者用sqlplus命令行时，会感觉特别不习惯。相对于DB2，MySQL命令行所带的工具，SQL * Plus中显示的默认环境设置可能不能满足需求，这时需要使用者单独去做配置。类似于中文乱码一样，对于每行显示的字符数默认就是80个。比如，当你查询多列数据时，显示出来的宽度是基于列定义的长度来，长度大于80时单行的数据显示出来会跨行。如果你学会了怎么来定义列或者数据格式时，你会发现它的效率相当得高。而且相对于图形化工具，SQL * Plus执行SQL的响应速度肯定要快。

由于格式化参数比较多，这里就列出日常工作常见的参数设置供参考：

–控制START命令是否列出命令文件中的每一命令.为ON时,列出命令;为OFF时,制止列清单.
set echo off

–显示由查询返回的记录数.ON和OFF置显示为开或关
set feedback off

–控制SQL*PLUS用值替换前、后是否列出命令的文本
set verify off

–控制由文件执行命令所产生的输出的显示
set term[out] off

–控制报表中列标题的打印
set heading off

–控制当前日期的显示
set timing off

–定SQL*PLUS在每一显示行的末端是否允许带空格. ON时将每行尾部的空格去了
set trims on

–设置每一页的头和顶部标题之间要打印的空行数.如果为0, 在页之间送一换号符,并在许多终端上清屏.
set newpage none

–为显示和拷贝LONG类型值的最大宽度的设置,最大为2G
set long 999999

set NUM[WIDTH] {10(默认值)|n} 对显示数值设置缺省宽度.

set line 300 pagesize 999
set linesize 6000 pages 0
sqlplus命令行
日常运维中，DBA或者应用维护人员会考虑使用sqlplus运行自动执行任务，或者将SQL放在脚本中从而实现数据批量操作。推荐在调用sqlplus时用如下两个参数：

登录选项 -L[OGON] 指定如果第一次连接不成功时，不提示重新提示输入用户名或密码，从而避免交互错误提示，可设置在脚本中运行。
静默选项 -S[ILENT] 关闭所有SQL * Plus信息和提示消息，包括命令提示符、命令的反馈信息和启动SQL * Plus时通常显示的提示信息。
下面可以看个简单例子，Linux命令行下执行数据库查询。注意 ：特殊符号$需要转义。

sqlplus -S -L / as sysdba <<EOF
set linesize 100 pagesize 999
col banner for a99
select banner from v$version;
select sysdate from dual;
exit;
EOF

–还可以将结果输出到具体文件
sqlplus -S -L / as sysdba > test.res 2>test.err <<EOF
set heading off;
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;
exit;
EOF

–后台执行sql，可在shell脚本中定义多个
echo “select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;” > t1.sql
echo “select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;” > t2.sql
sqlplus -S -L / as sysdba < t1.sql > t1.res 2>t1.err &
sqlplus -S -L / as sysdba < t2.sql > t2.res 2>t2.err &
sqlplus -S -L /@:/<service_name> < t2.sql > t2.res 2>t2.err &
案例分享
生成通用的平面文件
通过上面的介绍，相信对SQL * Plus工具也有一定的了解。下面通过一个案例来阐述sqlplus工具在导数场景的使用。

实际需求是这样子的，需要将Oracle数据库的某张表某些内容导入到其他数据库平台（MySQL、DB2等数仓中）。通过sqlplus命令实现按需导出固定的平面文件格式，比如txt、csv、del。下面为具体SQL，文件中列分隔符为ASCII码chr(29)，若有其他要求可自行定义。

–通过0x1d作文分隔符chr(29)，
–将<table_name>中的col1-col4行内容导入到<table_name.del>文件中
set echo off
set feedback off
set newpage none
set verify off
set pagesize 0
set term off
set trims on
set linesize 6000
set heading off
set timing off
set numwidth 30
spool <table_name.del>
select col1||chr(29)||
col2||chr(29)||
to_char(col3,‘yyyy-mm-dd hh24:mi:ss’)||chr(29)||
col4
from <table_name>
where …
order by …
实际情况下可基于具体的需求而定。比如csv格式，设置colsep为`“,”``，可以参考如下：

SQL> set colsep ‘“,”’
SQL> set heading off;
SQL> select ‘a’,‘b’,‘c’ from dual;

a",“b”,"c
总结
本文通过简单介绍了SQL * Plus相关知识，分享了用sqlplus工具来连接和管理数据库的基本操作和使用经验。希望通过本文的分享给你带来SQL * Plus不一样的体验。