DBMS_WM.MergeWorkspace fails with ORA-06502: PL/SQL: numeric or value error: character string buffer too small (Doc ID 2687520.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

On : 12.2.0.1 version, Core Workspace Server

When attempting to run DBMS_WM.MergeWorkspace
the following error occurs.

ERROR
-----------------------
ORA-06502: PL/SQL: numeerinen virhe tai arvovirhe : liian pieni merkkijonopuskuri
ORA-06512: "INFRA.WORKSPACE_API", line 56
ORA-06512: "WMSYS.LT", line 8402
ORA-06512: "WMSYS.LT", line 9264
ORA-06512: "WMSYS.LT", line 581
ORA-06512: "WMSYS.LT", line 9037
ORA-06512: "WMSYS.LT", line 8888
ORA-06512: "WMSYS.LT", line 8390
ORA-06512: "INFRA.WORKSPACE_API", line 47

Here is an simple testcase:

conn scott/tiger

drop table foo;

create table foo(i int constraint foo_pk primary key, jj varchar2(20));

EXEC DBMS_WM.EnableVersioning('SCOTT.FOO');

--truncate table foo;

declare
m_ws number(10);
begin
m_ws:=1;
while (m_ws < 100000) loop
 m_ws := m_ws+1;
 DBMS_WM.CreateWorkspace('bar'||m_ws, 'baz'||m_ws);
 insert into foo (i,jj) values(m_ws,'aa');
 commit;
 DBMS_WM.MergeWorkspace('bar'||m_ws);
end loop;
end;
/

select max(version), max(parent_version) from all_version_hview;

select * from all_version_hview order by version desc;

--EXEC DBMS_WM.DisableVersioning('SCOTT.FOO');
 
The loop runs several hours but will eventually fail and after that all DBMS_WM.MergeWorkspace commands have the same problem.

SQL> select max(version), max(parent_version) from all_version_hview;

MAX(VERSION) MAX(PARENT_VERSION)
------------ -------------------
      100001               99998

SQL> select max(i) from foo;


    MAX(I)
----------
     33335

SQL> show user
USER is "SCOTT"
SQL> exec DBMS_WM.CreateWorkspace('fail_merge', 'fail_merge');

PL/SQL procedure successfully completed.

SQL> insert into foo (i,jj) values(-2,'aa');

1 row created.

SQL> commit;

Commit complete.

SQL> exec DBMS_WM.MergeWorkspace('fail_merge');
BEGIN DBMS_WM.MergeWorkspace('fail_merge'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "WMSYS.LT", line 8402
ORA-06512: at "WMSYS.LT", line 8900
ORA-06512: at "WMSYS.LT", line 9264
ORA-06512: at "WMSYS.LT", line 581
ORA-06512: at "WMSYS.LT", line 9037
ORA-06512: at "WMSYS.LT", line 8895
ORA-06512: at "WMSYS.LT", line 8886
ORA-06512: at "WMSYS.LT", line 8390
ORA-06512: at line 1

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.