需求描述为什么会有这个需求 因为一些SQL输出很多例如数千行但我们只需要他的执行计划或SQL Monitor报告。太多的输出不仅干扰了我们所需的信息而且也拉长了elapsed time。基本概念方法其实就是SQL Plus的SET TERMOUT OFF选项。其说明参见这里Controls the display of output generated by commands in a script that is executed with , or START. OFF suppresses the display so that you can spool output to a file without displaying the output on screen. ON displays the output on screen. TERMOUT OFF does not affect output from commands you enter interactively or redirect to SQL*Plus from the operating system.这里的核心意思是说TERMOUT控制SQL的输出是否输出到屏幕ON是输出OFF是不输出TERMOUT默认是ON对于交互式输出不管用只适用于非交互式的通过 , 或 START调用的脚本。SQLsettermout SP2-0265: termout must besetONorOFFSQLshowtermout termoutON极简示例有两个脚本main.sql调用test1.sql。内容如下其中--是注释。$ cat main.sql-- SET TERMOUT OFFtest1exit$ cat test1.sqlselectsysdatefromdual;由于TERMOUT默认是打开因此可以看到输出$ sqlplus-S/ as sysdba main SYSDATE ---------25-JUN-26把main.sql中的注释去掉再次执行这回没有输出了$ sqlplus-S/ as sysdba main $如果SET TERMOUT写在被调用的脚本test1.sql效果也是一样的。但放在调用脚本控制更方便。实用场景示例常用的场景包括只需要执行计划不需要结果集只需要实时SQL Monitor 报告不需要结果集我们来演示下场景1。两个脚本main.sql调用test1.sql$ cat main.sqlSETTERMOUTOFFtest1.sqlSETTERMOUTONSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);exit$ cat test1.sqlselect*fromemployees;执行结果只有执行计划没有结果集$ sqlplus-S hr/******orclpdb1mainPLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID7jk33n4f4mpy9,child number0-------------------------------------select*fromhr.employeesPlanhashvalue:1445457117-------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------|0|SELECTSTATEMENT||||3(100)|||1|TABLEACCESSFULL|EMPLOYEES|106|7314|3(0)|00:00:01|PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------------------------------13rowsselected.实用场景示例进阶需求和上一个场景的唯一不同是我们需要把输出写到文件中但不包含结果集。例如实时SQL Monitor的html输出。还是两个脚本main.sql和test1.sql。test1.sql不变。来看下main.sql的三种写法。第一种写法$ cat main.sqlSPOOL test1.outSETTERMOUTOFFtest1.sqlSETTERMOUTONSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);SPOOLOFFEXIT执行如下$ sqlplus-Shr/********orclpdb1 main PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 7jk33n4f4mpy9, child number0-------------------------------------select* from hr.employees Planhashvalue:1445457117-------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------|0|SELECT STATEMENT||||3(100)|||1|TABLE ACCESS FULL|EMPLOYEES|106|7314|3(0)|00:00:01|PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------13rows selected. $ls-ltest1.out -rw-r--r--.1oracle oinstall65471Jun2509:53 test2.out $headtest1.out EMPLOYEE_ID FIRST_NAME LAST_NAME ----------- -------------------- ------------------------- EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY ------------------------- -------------------- --------- ---------- ---------- COMMISSION_PCT MANAGER_ID DEPARTMENT_ID -------------- ---------- -------------100Steven King SKING515.123.456717-JUN-03 AD_PRES2400090第二种写法$ cat main.sqlSETTERMOUTOFFtest1.sqlSETTERMOUTONSPOOL test1.outSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);SPOOLOFFEXIT执行如下$ sqlplus-Shr/********orclpdb1 main PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 7jk33n4f4mpy9, child number0-------------------------------------select* from hr.employees Planhashvalue:1445457117-------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------|0|SELECT STATEMENT||||3(100)|||1|TABLE ACCESS FULL|EMPLOYEES|106|7314|3(0)|00:00:01|PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------13rows selected. $ls-ltest1.out -rw-r--r--.1oracle oinstall1399Jun2509:56 test2.out $headtest2.out PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 7jk33n4f4mpy9, child number0-------------------------------------select* from hr.employees Planhashvalue:1445457117-------------------------------------------------------------------------------第三种写法$ cat main.sqlSETTERMOUTOFFtest1.sqlSPOOL test1.outSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);SPOOLOFFEXIT执行如下$ sqlplus-Shr/********orclpdb1 main $ls-ltest1.out -rw-r--r--.1oracle oinstall1399Jun2510:00 test1.out $headtest1.out PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 7jk33n4f4mpy9, child number0-------------------------------------select* from hr.employees Planhashvalue:1445457117-------------------------------------------------------------------------------哪一种写法是正确的其实二和三都正确但我常用三。以上示例揭示了TERMOUT OFF的一个坑即如果写在SPOOL后面尽管不会输出到屏幕但会将输出定向到spool文件。
Oracle SQL Plus 如何不显示结果集 （TERMOUT OFF）
需求描述为什么会有这个需求 因为一些SQL输出很多例如数千行但我们只需要他的执行计划或SQL Monitor报告。太多的输出不仅干扰了我们所需的信息而且也拉长了elapsed time。基本概念方法其实就是SQL Plus的SET TERMOUT OFF选项。其说明参见这里Controls the display of output generated by commands in a script that is executed with , or START. OFF suppresses the display so that you can spool output to a file without displaying the output on screen. ON displays the output on screen. TERMOUT OFF does not affect output from commands you enter interactively or redirect to SQL*Plus from the operating system.这里的核心意思是说TERMOUT控制SQL的输出是否输出到屏幕ON是输出OFF是不输出TERMOUT默认是ON对于交互式输出不管用只适用于非交互式的通过 , 或 START调用的脚本。SQLsettermout SP2-0265: termout must besetONorOFFSQLshowtermout termoutON极简示例有两个脚本main.sql调用test1.sql。内容如下其中--是注释。$ cat main.sql-- SET TERMOUT OFFtest1exit$ cat test1.sqlselectsysdatefromdual;由于TERMOUT默认是打开因此可以看到输出$ sqlplus-S/ as sysdba main SYSDATE ---------25-JUN-26把main.sql中的注释去掉再次执行这回没有输出了$ sqlplus-S/ as sysdba main $如果SET TERMOUT写在被调用的脚本test1.sql效果也是一样的。但放在调用脚本控制更方便。实用场景示例常用的场景包括只需要执行计划不需要结果集只需要实时SQL Monitor 报告不需要结果集我们来演示下场景1。两个脚本main.sql调用test1.sql$ cat main.sqlSETTERMOUTOFFtest1.sqlSETTERMOUTONSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);exit$ cat test1.sqlselect*fromemployees;执行结果只有执行计划没有结果集$ sqlplus-S hr/******orclpdb1mainPLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID7jk33n4f4mpy9,child number0-------------------------------------select*fromhr.employeesPlanhashvalue:1445457117-------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------|0|SELECTSTATEMENT||||3(100)|||1|TABLEACCESSFULL|EMPLOYEES|106|7314|3(0)|00:00:01|PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------------------------------13rowsselected.实用场景示例进阶需求和上一个场景的唯一不同是我们需要把输出写到文件中但不包含结果集。例如实时SQL Monitor的html输出。还是两个脚本main.sql和test1.sql。test1.sql不变。来看下main.sql的三种写法。第一种写法$ cat main.sqlSPOOL test1.outSETTERMOUTOFFtest1.sqlSETTERMOUTONSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);SPOOLOFFEXIT执行如下$ sqlplus-Shr/********orclpdb1 main PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 7jk33n4f4mpy9, child number0-------------------------------------select* from hr.employees Planhashvalue:1445457117-------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------|0|SELECT STATEMENT||||3(100)|||1|TABLE ACCESS FULL|EMPLOYEES|106|7314|3(0)|00:00:01|PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------13rows selected. $ls-ltest1.out -rw-r--r--.1oracle oinstall65471Jun2509:53 test2.out $headtest1.out EMPLOYEE_ID FIRST_NAME LAST_NAME ----------- -------------------- ------------------------- EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY ------------------------- -------------------- --------- ---------- ---------- COMMISSION_PCT MANAGER_ID DEPARTMENT_ID -------------- ---------- -------------100Steven King SKING515.123.456717-JUN-03 AD_PRES2400090第二种写法$ cat main.sqlSETTERMOUTOFFtest1.sqlSETTERMOUTONSPOOL test1.outSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);SPOOLOFFEXIT执行如下$ sqlplus-Shr/********orclpdb1 main PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 7jk33n4f4mpy9, child number0-------------------------------------select* from hr.employees Planhashvalue:1445457117-------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------|0|SELECT STATEMENT||||3(100)|||1|TABLE ACCESS FULL|EMPLOYEES|106|7314|3(0)|00:00:01|PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------13rows selected. $ls-ltest1.out -rw-r--r--.1oracle oinstall1399Jun2509:56 test2.out $headtest2.out PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 7jk33n4f4mpy9, child number0-------------------------------------select* from hr.employees Planhashvalue:1445457117-------------------------------------------------------------------------------第三种写法$ cat main.sqlSETTERMOUTOFFtest1.sqlSPOOL test1.outSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);SPOOLOFFEXIT执行如下$ sqlplus-Shr/********orclpdb1 main $ls-ltest1.out -rw-r--r--.1oracle oinstall1399Jun2510:00 test1.out $headtest1.out PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 7jk33n4f4mpy9, child number0-------------------------------------select* from hr.employees Planhashvalue:1445457117-------------------------------------------------------------------------------哪一种写法是正确的其实二和三都正确但我常用三。以上示例揭示了TERMOUT OFF的一个坑即如果写在SPOOL后面尽管不会输出到屏幕但会将输出定向到spool文件。
