[20260523]ORA-01801 date format is too long for internal buffer.txt
[20260523]ORA-01801 date format is too long for internal buffer.txt
--//测试使用dbms_profiler遇到的问题，做1个记录。
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.
2.测试：
--//测试完成后查询plsql_profiler_units 报错。
SCOTT@book01p> select * from plsql_profiler_units ;
ERROR:
ORA-01801: date format is too long for internal buffer
--//明显日期字段的显示存在问题。
SCOTT@book01p> @ desc plsql_profiler_units
Name Null? Type
-------------- -------- -------------
1 RUNID NOT NULL NUMBER
2 UNIT_NUMBER NOT NULL NUMBER
3 UNIT_TYPE VARCHAR2(128)
4 UNIT_OWNER VARCHAR2(128)
5 UNIT_NAME VARCHAR2(128)
6 UNIT_TIMESTAMP DATE
7 TOTAL_TIME NOT NULL NUMBER
8 SPARE1 NUMBER
9 SPARE2 NUMBER
SCOTT@book01p> @ col_list plsql_profiler_units UNIT_TIMESTAMP n
SELECT
runid
,unit_number
,unit_type
,unit_owner
,unit_name
,total_time
,spare1
,spare2
FROM plsql_profiler_units
--//参数3=n表示生成脚本的显示字段没有UNIT_TIMESTAMP字段。
SCOTT@book01p> /
RUNID UNIT_NUMBER UNIT_TYPE UNIT_OWNER UNIT_NAME TOTAL_TIME SPARE1 SPARE2
----- ----------- --------------- ----------- -------------- ---------- ------ ------
1 1 PROCEDURE SCOTT INSERT_EXAMPLE 0
2 1 ANONYMOUS BLOCK <anonymous> <anonymous> 0
2 2 ANONYMOUS BLOCK <anonymous> <anonymous> 0
2 3 ANONYMOUS BLOCK <anonymous> <anonymous> 0
2 4 PROCEDURE SCOTT INSERT_EXAMPLE 0
2 5 ANONYMOUS BLOCK <anonymous> <anonymous> 0
2 6 ANONYMOUS BLOCK <anonymous> <anonymous> 0
3 1 PROCEDURE SCOTT INSERT_EXAMPLE 0
8 rows selected.
SELECT
runid
,unit_number
,unit_type
,unit_owner
,unit_name
,total_time
,to_char(UNIT_TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')
,spare1
,spare2
FROM plsql_profiler_units
SCOTT@book01p> /
RUNID UNIT_NUMBER UNIT_TYPE UNIT_OWNER UNIT_NAME TOTAL_TIME TO_CHAR_UNIT_TIMEST SPARE1 SPARE2
----- ----------- --------------- ----------- -------------- ---------- ------------------- ------ ------
1 1 PROCEDURE SCOTT INSERT_EXAMPLE 0 2026-05-22 16:04:33
2 1 ANONYMOUS BLOCK <anonymous> <anonymous> 0 0000-00-00 00:00:00
2 2 ANONYMOUS BLOCK <anonymous> <anonymous> 0 0000-00-00 00:00:00
2 3 ANONYMOUS BLOCK <anonymous> <anonymous> 0 0000-00-00 00:00:00
2 4 PROCEDURE SCOTT INSERT_EXAMPLE 0 2026-05-22 16:09:18
2 5 ANONYMOUS BLOCK <anonymous> <anonymous> 0 0000-00-00 00:00:00
2 6 ANONYMOUS BLOCK <anonymous> <anonymous> 0 0000-00-00 00:00:00
3 1 PROCEDURE SCOTT INSERT_EXAMPLE 0 2026-05-22 16:28:59
8 rows selected.
--//显示的是0000-00-00 00:00:00，
--//注如果使用toad执行select * from plsql_profiler_units ;,正常执行,查询UNIT_TIMESTAMP字段显示的是0001-01-01.
SCOTT@book01p> select dump(UNIT_TIMESTAMP,16) c30 from plsql_profiler_units where UNIT_TIMESTAMP<=trunc(sysdate) and rownum=1;
C30
------------------------------
Typ=12 Len=7: 0,0,0,0,0,0,0
--//不知道为什么使用dbms_profiler包，插入这样的日期数据。
--//可以参考一些链接https://www.anbob.com/archives/8511.html，视乎一些序可以实现这样的功能，sqlplus不经过处理不行。
3.正好有测试环境,使用10046跟踪看看:
--//首先重新安装dbms_profile需要的表.
SCOTT@book01p> @?/rdbms/admin/proftab.sql
Table dropped.
Table dropped.
Table dropped.
Sequence dropped.
Table created.
Comment created.
Table created.
Comment created.
Table created.
Comment created.
Sequence created.
--//SCOTT@book01p> purge tablespace users ;
--//Tablespace purged.
--//create global temporary table gtt ( col1 number, col2 varchar2(10) ) on commit preserve rows;
--//修改测试过程insert_example程序,循环10次,另外修改dbms_output.put_line输出显示秒数.
--//注解dbms_profiler相关行,在外部执行才能再现前面遇到的情况.
create or replace procedure insert_example is
nr_to_loop number:=10;
nr number:=0;
type col_type is table of gtt%ROWTYPE;
col col_type:=col_type();
begin
--dbms_profiler.start_profiler('profiler run '||to_char(sysdate,'yyyymmddhh24miss'));
dbms_output.put_line('Start: '||to_char(sysdate,'hh24:mi:ss'));
for nr in 1..nr_to_loop loop
/* gtt */
insert into gtt (col1, col2) values ( nr, 'AABBCCDDEE' );
/* collection */
col.extend;
col(nr).col1 := nr;
col(nr).col2 := 'AABBCCDDEE';
end loop;
commit;
dbms_output.put_line('Stop: '||to_char(sysdate,'hh24:mi:ss'));
--dbms_profiler.stop_profiler;
end;
/
$ cat z1.txt
set serveroutput on;
@ 10046on 12
exec dbms_profiler.start_profiler('profiler run '||to_char(sysdate,'yyyymmddhh24miss'));
exec insert_example
exec dbms_profiler.stop_profiler;
@ 10046off
SCOTT@book01p> @ z1.txt
Session altered.
PL/SQL procedure successfully completed.
Start: 10:40:05
Stop: 10:40:05
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.
--//查看跟踪文件,显示如下:
=====================
PARSING IN CURSOR #139725550738144 len=239 dep=1 uid=109 oct=47 lid=109 tim=2090375495 hv=1593796405 ad='64be6910' sqlid='10v0pwpggyttp'
declare pragma autonomous_transaction; begin insert into plsql_profiler_units (runid, unit_number, unit_type, unit_owner, unit_name, unit_timestamp) values (:1, :2, :3, :4, :5, :6); commit; end;
END OF STMT
PARSE #139725550738144:c=530,e=530,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=2090375494
BINDS #139725550738144:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=144 off=0
kxsbbbfp=7f1463d256c0 bln=22 avl=02 flg=05
value=1
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f1463d256d8 bln=22 avl=02 flg=01
value=1
Bind#2
oacdty=01 mxl=32(15) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=01 csi=852 siz=0 off=48
kxsbbbfp=7f1463d256f0 bln=32 avl=15 flg=01
value="ANONYMOUS BLOCK"
Bind#3
oacdty=01 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=01 csi=852 siz=0 off=80
kxsbbbfp=7f1463d25710 bln=32 avl=11 flg=01
value="<anonymous>"
Bind#4
oacdty=01 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=01 csi=852 siz=0 off=112
kxsbbbfp=7f1463d25730 bln=32 avl=11 flg=01
value="<anonymous>"
Bind#5
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7f14639cfd28 bln=07 avl=07 flg=09
value="0/0/-10100 -1:-1:-1"
=====================
--//注意看实际上日期类型绑定变量值是"0/0/-10100 -1:-1:-1".
--//注意跟踪文件记录的日期格式是 月/日/年 时:分:秒.
SCOTT@book01p> create table tt (id number,cr date);
Table created.
SCOTT@book01p> variable v_cr varchar2(32);
--//sqlplus下不支持日期类似。
SCOTT@book01p> exec :v_cr := '-10100/0/0 -1:-1:-1'
PL/SQL procedure successfully completed.
SCOTT@book01p> insert into tt values (1,:v_cr);
insert into tt values (1,:v_cr)
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
--//toad下也做了尝试，无法插入这样的值，放弃探究。
--//测试使用dbms_profiler遇到的问题，做1个记录。
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.
2.测试：
--//测试完成后查询plsql_profiler_units 报错。
SCOTT@book01p> select * from plsql_profiler_units ;
ERROR:
ORA-01801: date format is too long for internal buffer
--//明显日期字段的显示存在问题。
SCOTT@book01p> @ desc plsql_profiler_units
Name Null? Type
-------------- -------- -------------
1 RUNID NOT NULL NUMBER
2 UNIT_NUMBER NOT NULL NUMBER
3 UNIT_TYPE VARCHAR2(128)
4 UNIT_OWNER VARCHAR2(128)
5 UNIT_NAME VARCHAR2(128)
6 UNIT_TIMESTAMP DATE
7 TOTAL_TIME NOT NULL NUMBER
8 SPARE1 NUMBER
9 SPARE2 NUMBER
SCOTT@book01p> @ col_list plsql_profiler_units UNIT_TIMESTAMP n
SELECT
runid
,unit_number
,unit_type
,unit_owner
,unit_name
,total_time
,spare1
,spare2
FROM plsql_profiler_units
--//参数3=n表示生成脚本的显示字段没有UNIT_TIMESTAMP字段。
SCOTT@book01p> /
RUNID UNIT_NUMBER UNIT_TYPE UNIT_OWNER UNIT_NAME TOTAL_TIME SPARE1 SPARE2
----- ----------- --------------- ----------- -------------- ---------- ------ ------
1 1 PROCEDURE SCOTT INSERT_EXAMPLE 0
2 1 ANONYMOUS BLOCK <anonymous> <anonymous> 0
2 2 ANONYMOUS BLOCK <anonymous> <anonymous> 0
2 3 ANONYMOUS BLOCK <anonymous> <anonymous> 0
2 4 PROCEDURE SCOTT INSERT_EXAMPLE 0
2 5 ANONYMOUS BLOCK <anonymous> <anonymous> 0
2 6 ANONYMOUS BLOCK <anonymous> <anonymous> 0
3 1 PROCEDURE SCOTT INSERT_EXAMPLE 0
8 rows selected.
SELECT
runid
,unit_number
,unit_type
,unit_owner
,unit_name
,total_time
,to_char(UNIT_TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')
,spare1
,spare2
FROM plsql_profiler_units
SCOTT@book01p> /
RUNID UNIT_NUMBER UNIT_TYPE UNIT_OWNER UNIT_NAME TOTAL_TIME TO_CHAR_UNIT_TIMEST SPARE1 SPARE2
----- ----------- --------------- ----------- -------------- ---------- ------------------- ------ ------
1 1 PROCEDURE SCOTT INSERT_EXAMPLE 0 2026-05-22 16:04:33
2 1 ANONYMOUS BLOCK <anonymous> <anonymous> 0 0000-00-00 00:00:00
2 2 ANONYMOUS BLOCK <anonymous> <anonymous> 0 0000-00-00 00:00:00
2 3 ANONYMOUS BLOCK <anonymous> <anonymous> 0 0000-00-00 00:00:00
2 4 PROCEDURE SCOTT INSERT_EXAMPLE 0 2026-05-22 16:09:18
2 5 ANONYMOUS BLOCK <anonymous> <anonymous> 0 0000-00-00 00:00:00
2 6 ANONYMOUS BLOCK <anonymous> <anonymous> 0 0000-00-00 00:00:00
3 1 PROCEDURE SCOTT INSERT_EXAMPLE 0 2026-05-22 16:28:59
8 rows selected.
--//显示的是0000-00-00 00:00:00，
--//注如果使用toad执行select * from plsql_profiler_units ;,正常执行,查询UNIT_TIMESTAMP字段显示的是0001-01-01.
SCOTT@book01p> select dump(UNIT_TIMESTAMP,16) c30 from plsql_profiler_units where UNIT_TIMESTAMP<=trunc(sysdate) and rownum=1;
C30
------------------------------
Typ=12 Len=7: 0,0,0,0,0,0,0
--//不知道为什么使用dbms_profiler包，插入这样的日期数据。
--//可以参考一些链接https://www.anbob.com/archives/8511.html，视乎一些序可以实现这样的功能，sqlplus不经过处理不行。
3.正好有测试环境,使用10046跟踪看看:
--//首先重新安装dbms_profile需要的表.
SCOTT@book01p> @?/rdbms/admin/proftab.sql
Table dropped.
Table dropped.
Table dropped.
Sequence dropped.
Table created.
Comment created.
Table created.
Comment created.
Table created.
Comment created.
Sequence created.
--//SCOTT@book01p> purge tablespace users ;
--//Tablespace purged.
--//create global temporary table gtt ( col1 number, col2 varchar2(10) ) on commit preserve rows;
--//修改测试过程insert_example程序,循环10次,另外修改dbms_output.put_line输出显示秒数.
--//注解dbms_profiler相关行,在外部执行才能再现前面遇到的情况.
create or replace procedure insert_example is
nr_to_loop number:=10;
nr number:=0;
type col_type is table of gtt%ROWTYPE;
col col_type:=col_type();
begin
--dbms_profiler.start_profiler('profiler run '||to_char(sysdate,'yyyymmddhh24miss'));
dbms_output.put_line('Start: '||to_char(sysdate,'hh24:mi:ss'));
for nr in 1..nr_to_loop loop
/* gtt */
insert into gtt (col1, col2) values ( nr, 'AABBCCDDEE' );
/* collection */
col.extend;
col(nr).col1 := nr;
col(nr).col2 := 'AABBCCDDEE';
end loop;
commit;
dbms_output.put_line('Stop: '||to_char(sysdate,'hh24:mi:ss'));
--dbms_profiler.stop_profiler;
end;
/
$ cat z1.txt
set serveroutput on;
@ 10046on 12
exec dbms_profiler.start_profiler('profiler run '||to_char(sysdate,'yyyymmddhh24miss'));
exec insert_example
exec dbms_profiler.stop_profiler;
@ 10046off
SCOTT@book01p> @ z1.txt
Session altered.
PL/SQL procedure successfully completed.
Start: 10:40:05
Stop: 10:40:05
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.
--//查看跟踪文件,显示如下:
=====================
PARSING IN CURSOR #139725550738144 len=239 dep=1 uid=109 oct=47 lid=109 tim=2090375495 hv=1593796405 ad='64be6910' sqlid='10v0pwpggyttp'
declare pragma autonomous_transaction; begin insert into plsql_profiler_units (runid, unit_number, unit_type, unit_owner, unit_name, unit_timestamp) values (:1, :2, :3, :4, :5, :6); commit; end;
END OF STMT
PARSE #139725550738144:c=530,e=530,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=2090375494
BINDS #139725550738144:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=144 off=0
kxsbbbfp=7f1463d256c0 bln=22 avl=02 flg=05
value=1
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f1463d256d8 bln=22 avl=02 flg=01
value=1
Bind#2
oacdty=01 mxl=32(15) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=01 csi=852 siz=0 off=48
kxsbbbfp=7f1463d256f0 bln=32 avl=15 flg=01
value="ANONYMOUS BLOCK"
Bind#3
oacdty=01 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=01 csi=852 siz=0 off=80
kxsbbbfp=7f1463d25710 bln=32 avl=11 flg=01
value="<anonymous>"
Bind#4
oacdty=01 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=01 csi=852 siz=0 off=112
kxsbbbfp=7f1463d25730 bln=32 avl=11 flg=01
value="<anonymous>"
Bind#5
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7f14639cfd28 bln=07 avl=07 flg=09
value="0/0/-10100 -1:-1:-1"
=====================
--//注意看实际上日期类型绑定变量值是"0/0/-10100 -1:-1:-1".
--//注意跟踪文件记录的日期格式是 月/日/年 时:分:秒.
SCOTT@book01p> create table tt (id number,cr date);
Table created.
SCOTT@book01p> variable v_cr varchar2(32);
--//sqlplus下不支持日期类似。
SCOTT@book01p> exec :v_cr := '-10100/0/0 -1:-1:-1'
PL/SQL procedure successfully completed.
SCOTT@book01p> insert into tt values (1,:v_cr);
insert into tt values (1,:v_cr)
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
--//toad下也做了尝试，无法插入这样的值，放弃探究。
浙公网安备 33010602011771号