[20260531]sqlplus set emb on使用问题.txt
--//偶尔协助别人解决数据库问题,在服务端以sys用户登录后最大的问题,许多情况下环境都没有设置好,更加无法理解是没有安装
--//rlwrap,无法使用上下键.每次都会花一些时间设置简单的sqlplus使用环境,也就是在这类设置中,set emb on|off的奇怪现象.
--//通过例子演示展示该问题.
1.环境:
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
--//官方的解析如下:
SET EMB [EDDED] Is the command, which may be abbreviated SET EMB. ON Turns the embedded report feature on. Executing a
SELECT statement will not force a page break, nor will it reset the page number. OFF Turns the embedded report feature
off. Executing a SELECT statement will force a page break, and the page number will be reset to 1.
--//翻译如下:
SET EMB [ EDDED ] 是一条命令，可简写为SET EMB。ON：启用嵌入式报告功能。执行选择语句时不会强制换页，也不会重置页码；OFF：
禁用嵌入式报告功能。执行选择语句时会强制换页，并将页码重置为1。
--//官方文档非常清晰的说明emb on,SELECT语句不会出现分页情况.我个人以前喜欢设置on,缺省是off.这样分页出现字段标题.
2.简单测试说明问题:
SCOTT@book01p> show emb
embedded ON
SCOTT@book01p> set pagesize 10
SCOTT@book01p> select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
--//有14条记录,而pagesize=10,实际上显示并没有出现分页情况.以后无论执行多次都不会出现分页情况.
SCOTT@book01p> set emb off pagesize 10
SCOTT@book01p> select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
--//出现分页并且页面显示字段标题.
--//问题是当设置emb on时,应该回到前面的显示,可实际的情况如下:
SCOTT@book01p> set emb on
SCOTT@book01p> select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
--//输出还是与emb off一样的情况.必须经过一次设置pagesize很大的值,才能纠正该问题.
--//实际上这个问题我花了一点时间才明白问题在那里，可以逐步增加pagesize 大小就明白问题在那里。
--//可以简单理解即使设置emb on，但是处在分页状态。
SCOTT@book01p> set pagesize 9999
SCOTT@book01p> select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
SCOTT@book01p> set pagesize 10
SCOTT@book01p> select * from emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
3.设置emb on trimspool on时会出现一个奇怪的bug,但是仅仅sql语句的第一次执行显示字段标题仅仅80列.
--//注：直接使用-R 3测试. 这样可以绕过glogin.sql以及环境变量ORACLE_PATH,SQL_PATH的设置以及其中脚本的执行,保证测试的一致
--//性,不受其他干扰.
--//我仅仅21c下测试遇到。11g没有该问题。
$ echo -e 'set linesize 200 emb on trimspool on\nselect systimestamp,localtimestamp,sysdate from dual ;' | sqlplus -s -l -R 3 system/bookbook@192.168.56.101:1521/book
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
SYSTIMESTAMP LOCA
--------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------
2026-05-31 16:05:25.655059 +08:00 2026-05-31 16:05:25.655061 2026-05-31 16:05:25
--//标题仅仅显示到LOCA,正好80列.
$ echo -e 'set linesize 200 emb on trimspool off\nselect systimestamp,localtimestamp,sysdate from dual ;' | sqlplus -s -l -R 3 system/bookbook@192.168.56.101:1521/book
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
SYSTIMESTAMP LOCALTIMESTAMP SYSDATE
--------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------
2026-05-31 16:06:17.557893 +08:00 2026-05-31 16:06:17.557895 2026-05-31 16:06:17
--//其他组合不会出现该问题.
$ echo -e 'set linesize 200 emb on trimspool on\nselect systimestamp,localtimestamp,sysdate from dual ;\nselect systimestamp,localtimestamp,sysdate from dual ;' | sqlplus -s -l -R 3 system/bookbook@192.168.56.101:1521/book
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
SP2-0738: Restricted command "@@ (START)" not available
SYSTIMESTAMP LOCA
--------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------
2026-05-31 16:07:07.799621 +08:00 2026-05-31 16:07:07.799623 2026-05-31 16:07:07
SYSTIMESTAMP LOCALTIMESTAMP SYSDATE
--------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------
2026-05-31 16:07:07.800371 +08:00 2026-05-31 16:07:07.80
--//第2次执行问题消失.
4.小结:
--//某种情况应该感谢对方没有设置环境,才让我发现这些问题,不知道是否算bug.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3172802/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
