[20250320]DBMS_OUTPUT.PUT_LINE输出限制.txt

[20250320]DBMS_OUTPUT.PUT_LINE输出限制.txt --//去年遇到的问题，一直没时间看。生产系统遇到的问题。 1.环境: SYS@127.0.0.1:9105/xtdb/xtdb1> @ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.14.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.问题提出: SYS@127.0.0.1:9105/xtdb/xtdb1> @ ashtop sql_id 1=1  &day     Total                                                                         Distinct Distinct    Distinct   Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1 --------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------     56967      .7   61% |               2024-12-25 08:45:13 2024-12-26 08:45:08          2    48165       48165      2014      .0    2% | 6wcywxctbbvs8 2024-12-25 08:50:53 2024-12-26 08:41:17        283     2014         283       697      .0    1% | 0za9fv0j1vgkk 2024-12-25 08:45:25 2024-12-26 08:43:02        682      696         684       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~       613      .0    1% | 41a4d5z9cswza 2024-12-25 08:51:02 2024-12-26 08:41:19        280      603         280       584      .0    1% | 0315jsvndf9s1 2024-12-25 08:45:47 2024-12-26 03:06:18        582      584         584       526      .0    1% | d7sa2ga44303r 2024-12-25 08:51:13 2024-12-26 08:41:24        278      526         278       521      .0    1% | fnxvn2huxfy5y 2024-12-25 08:51:11 2024-12-26 08:41:23        276      521         276 ... SYS@127.0.0.1:9105/xtdb/xtdb1> @ sql_id 0za9fv0j1vgkk -- SQL_ID = 0za9fv0j1vgkk come from shared pool DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 11 --//直接使用我写的sql_id获取sql语句报错.执行查看这条语句来自MMON_SLAVE模块，超级长,而且似乎没有换行. --//仔细检查才发现是DBMS_OUTPUT.PUT_LINE输出限制导致的问题。 3.简单验证： $ uniq -c a.txt       1 select /*+     327 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000       1 00000000000000000000000000000000000       1 */ sysdate from dual; $ ls -l a.txt -rw-r--r--. 1 oracle oinstall 32769 2025-03-20 16:44:31 a.txt --//如果不包括最后分号以及回车，大小32767. SCOTT@book01p> @ a.txt SYSDATE ------------------- 2025-03-20 16:45:54 SCOTT@book01p> @ hashz HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- ---------- ------------------- -----------  456068362 buzxt98dky38a            0      68874  1b2f0d0a  2025-03-20 16:45:53    16777216 SCOTT@book01p> @ sql_id buzxt98dky38a -- SQL_ID = buzxt98dky38a come from shared pool DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 11 --//报错。如果删除a.txt 1个字符，执行后使用写的sql_id.sql脚本查询就不会报错。 $ wc  a.txt   330   334 32768 a.txt --//执行就不会报错,测试略。 --//另外一个简单验证。 SCOTT@book01p> variable v clob; SCOTT@book01p> exec :v := lpad('x',32767,'x')||'y'; PL/SQL procedure successfully completed. SCOTT@book01p> exec DBMS_OUTPUT.PUT_LINE(:v); BEGIN DBMS_OUTPUT.PUT_LINE(:v); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1 --//实际上DBMS_OUTPUT.PUT_LINE的参数是VARCHAR2，不是clob类型。 SCOTT@book01p> @ desc_proc sys DBMS_OUTPUT PUT_LINE SCOTT@book01p>  @pr ============================== OWNER                         : SYS PACKAGE_NAME                  : DBMS_OUTPUT OBJECT_NAME                   : PUT_LINE SEQUENCE                      : 1 ARGUMENT_NAME                 : A DATA_TYPE                     : VARCHAR2 IN_OUT                        : IN DEFAULTED                     : N PL/SQL procedure successfully completed. --//一般sql语句很少超过32K，放弃修改。