sqlplus系统变量

男娘i 2022-10-02 10:50 212阅读 0赞

今天看老盖的书《循序渐进Oracle》，书中有提到看DBA\_USERS视图的定义，语句如下：

SELECT TEXT FROM DBA\_VIEWS WHERE VIEW\_NAME='DBA\_USERS';

查询结果却差强人意：

TEXT  
\--------------------------------------------------------------------------------  
select u.name, u.user\#, u.password,  
m.status,  
decode(u.astatus, 4,

一个view的定义语句，被阉割了，不成样子。很久不碰Oracle，总不免有些生疏。我先修改了列的显示格式：

COL TEXT FORMAT A500;

当然，这是无效的，只是在我的屏幕上，多出了很多“---------”。后来百度，终于明白，字段内容长度，是由long这个sqlplus的环境变量来控制的，索性将这个变量设置的巨大：

set long 2000;

Technorati 标签: [sqlplus][], [sqlprompt][], [lang][], [pagesize][]

终于有了自己想要的结果：

SQL> /

TEXT  
\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
select u.name, u.user\#, u.password,  
m.status,  
decode(u.astatus, 4, u.ltime,  
5, u.ltime,  
6, u.ltime,  
8, u.ltime,  
9, u.ltime,  
10, u.ltime, to\_date(NULL)),  
decode(u.astatus,  
1, u.exptime,  
2, u.exptime,  
5, u.exptime,  
6, u.exptime,  
9, u.exptime,  
10, u.exptime,  
decode(u.ptime, '', to\_date(NULL),  
decode(pr.limit\#, 2147483647, to\_date(NULL),  
decode(pr.limit\#, 0,  
decode(dp.limit\#, 2147483647, to\_date(NULL), u.ptime +  
dp.limit\#/86400),  
u.ptime + pr.limit\#/86400)))),  
dts.name, tts.name, u.ctime, p.name,  
nvl(cgm.consumer\_group, 'DEFAULT\_CONSUMER\_GROUP'),  
u.ext\_username  
from sys.user$ u left outer join sys.resource\_group\_mapping$ cgm  
on (cgm.attribute = 'ORACLE\_USER' and cgm.status = 'ACTIVE' and  
cgm.value = u.name),  
sys.ts$ dts, sys.ts$ tts, sys.profname$ p,  
sys.user\_astatus\_map m, sys.profile$ pr, sys.profile$ dp  
where u.datats\# = dts.ts\#  
and u.resource$ = p.profile\#  
and u.tempts\# = tts.ts\#  
and u.astatus = m.status\#  
and u.type\# = 1  
and u.resource$ = pr.profile\#  
and dp.profile\# = 0  
and dp.type\#=1  
and dp.resource\#=1  
and pr.type\# = 1  
and pr.resource\# = 1

这才窥得DBA\_USERS的真面目。

以此为契机，讲讲sqlplus的变量应用：

一、保存当前的系统变量设置：

SQL>RESTORE SET <FILENAME.SQL>

这将会生成一个sqlplus命令文件，可以指定全路径名，也可以只写文件名，拓展名可写可不写，默认人sql，默认的拓展名可以通过命令SET SUFFIX <TXX>来指定

保存好后，当然可以通过执行这个sql脚本文件，将自己的偏好设置导入进来

SQL>@<FILENAME.SQL>

同样，后缀名是可选的

二、查看当前的系统变量设置

查看当前所有的系统变量，可以用SHOW ALL命令，如果只是想看某个系统变量，可以在show命令后面单独列出，比如

SQL>show pagesize  
pagesize 20

三、设置系统变量

用SET命令，可以修改当前系统变量，在退出sqlplus后，变量设置失效，比如

SQL>SET LINESIZE 120

四、重要的系统变量介绍

ARRAYSIZE： sqlplus一次性从数据库中读取的行数

FLUSH:OFF允许操作系统缓存查询结果，在脚本执行完后，再将结果输出到屏幕上，ON则禁止操作系统缓存结果。在很多时候，数据实时回显到屏幕上，是很耗费时间和资源的

LINESIZE:每行可以显示的字符数，不应过长，否则会造成额外的内存占用和内存复制，也不应过短，否则会造成字段值被不必要的截短

PAGESIZE:每页显示的数据行数，稍长，可以避免频繁的打印字段头部，设置为0，可以不显示字段头部

SERVEROUTPUT:ON允许存储过程中调用DBMS\_OUTPUT.PUT\_LINE()函数在屏幕输出信息，OFF则禁止DBMS\_OUTPUT包在屏幕上输出信息

SQLPROMPT:sqlplus的提示符，本人的设置如下

col u\_time new\_value utime  
select to\_char(sysdate,'hh24:mm:ss') u\_time from dual

SET SQLPROMPT [">">">">\_USER'@'\_CONNECT\_IDENTIFIER \_DATE &utime>][USER_CONNECT_IDENTIFIER _DATE _utime]

效果为

[![p\_w\_picpath][p_w_picpath]][p_w_picpath_p_w_picpath]

可以根据自己的喜好，自由进行设置

TERMOUT:OFF则不在屏幕上显示结果，经常与spool合用。ON正常显示结果

LANG:决定字段显示的长度

TIMING:ON显示个命令执行时间，供参考，OFF则不显示

ECHO：ON则在执行sql脚本是，及执行start命令或者@命令是，实时在屏幕上回显执行信息，OFF则不显示

FEEDBACK：ON，显示查询到的数据行数，OFF则不显示

五、自动化设置

如果希望在每次打开sqlplus的时候，自动应用自己的偏好设置，那么可以在

$ORACLE\_HOME/sqlplus/admin/glogin.sql

中添加相应的set语句，在每次sqlplus启动的时候，会自动应用这个sql脚本文件，免去不少麻烦

六、结语

好的sqlplus系统变量定制，会给dba的工作带来很多益处。比如sqlprompt，如果定制了[\_USER'@'\_CONNECT\_IDENTIFIER][USER_CONNECT_IDENTIFIER _DATE _utime],在一个服务器上配置了多个数据库，那么就很容易区分不同的实例和数据库，不至于对错误的数据库进行了不恰当的操作。如果在sqlprompt中定制了时间，在误操作后，翻看屏幕上的命令记录，可以快速方便的确认失误发生的时间，为数据恢复提供可靠的时间信息。

总之，适合的，才是最重要的，磨刀不误砍柴工，何不用自己的闲暇时间，为自己打造一个舒适的sqlplus工作间呢？

心动不如行动，更多好处，我们一起共享。

转载于:https://blog.51cto.com/mckingstar/641850

[sqlplus]: http://technorati.com/tags/sqlplus
[sqlprompt]: http://technorati.com/tags/sqlprompt
[lang]: http://technorati.com/tags/lang
[pagesize]: http://technorati.com/tags/pagesize
[USER_CONNECT_IDENTIFIER _DATE _utime]: mailto:_USER'%20rel=
[p_w_picpath]: https://s1.51cto.com/attachment/201108/17/3193806_1313598379Ok4c.png
[p_w_picpath_p_w_picpath]: https://s1.51cto.com/attachment/201108/17/3193806_1313598378Jiqq.png