When a blob is being concatenated mulitple times, the disk space for the database file grows massively. The only way to get the file size back to normal is a backup and restore of the database.
The issue can be reproduced as described below:
- Create an empty database
- Create the following procedure:
SET TERM ^ ;
CREATE OR ALTER PROCEDURE P_TEST (
BLOBTEXT BLOB SUB_TYPE 0 SEGMENT SIZE 80)
DECLARE COUNTER INTEGER;
COUNTER = 0;
BLOBTEXT = '';
WHILE (COUNTER < ITERATIONS)
BLOBTEXT = BLOBTEXT || ' ' || TEST;
COUNTER = COUNTER + 1;
SET TERM ; ^
GRANT EXECUTE ON PROCEDURE P_TEST TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE P_TEST TO SYSDBA;
- run the procedure:
SELECT BLOBTEXT FROM P_TEST('This is a test sentence, which is going to be concatenated.', 4000);
After the procedure has been executed, the disk space used by the database file increases to over 1 GB. It makes no difference, if the transaction is committed or not.
When you run the procedure another time using the SQL-command EXECUTE PROCEDURE P_TEST('This is a test sentence, which is going to be concatenated.', 4000); the file size increases again.