SHRD0014: GLLEZL - process exiting with failure

 

SYMPTOMS

Journal Import completes with the following error:

Error

------

ORA-01653 : unable to extend table GL.GL_IMPORT_REFERENCES

ORA-01653 : unable to extend table GL.GL_JE_LINES

shrd0014: gllezl - process exiting with failure

gllcje- for the consolidation transfer program.

CAUSE

The tablespace must be increased at the database level. 

SOLUTION

To implement the solution, please execute the following steps:

The DBA needs to increase or extend the tablespace for the tables referenced in the error at the database level.

Use this specific SQL to find the tablespace name:

SQL > select OWNER, TABLE_NAME , TABLESPACE_NAME

from dba_tables

where owner = 'GL'

and table_name like '%GL_IMPORT_REFERENCES%';

There are other columns on dba_tables that the DBA may want to query to get the extents etc.

 Add a Datafile:

ALTER TABLESPACE ADD DATAFILE '' SIZE ;

If the tablespace is being used as a temporary tablespace, temporary segments may be still holding the space.

You can query this for how much free, used, total, and percentage filled space is available for each tablespace.

SQL> select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused

from (select a.tablespace_name, a.bytes_free,b.bytes_total,

ROUND((100-(100*a.bytes_free/b.bytes_total)),2)  perused from

(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,

(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b

where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;

select tablespace_name,sum(bytes)/(1024*1024) from dba_free_space

group by tablespace_name;

## ADD TABLESPACE

SQL > alter tablespace DATA add datafile '/u01/data/data13.dbf' size 10G;

## Check Temp tablespace.

SQL> select TABLESPACE_NAME, BYTES_USED/1024/1024, BYTES_FREE/1024/1024 from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                BYTES_USED/1024/1024 BYTES_FREE/1024/1024

------------------------------ -------------------- --------------------

TEMP1                                         10240                10240

TEMP2                                         10240                10240

##Resize/add temp datafile.

SQL> alter tablespace TEMP2 add tempfile '/u01/data/temp04.dbf' size 30G;

SQL> alter tablespace TEMP1 add tempfile '/u01/data/temp05.dbf' size 30G;

SQL> ALTER DATABASE TEMPFILE '/u01/data/temp02.dbf' RESIZE 30G;

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string"

Cause: Used the system rollback segment for non-system tablespace operations.

 If this is a clone database, this will happen when attempting any data modification 

outside the UNDOTBS tablespace.

Solution:

1) Switch to Automatic Undo Management

$ sqlplus / as sysdba

alter system set undo_management=auto scope=spfile;

2) Restart the database.

SQL> Shutdown immediate;

SQL> startup

ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:-

IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1)

SYMPTOMS:-

You may have noticed that IEX_DELINQUENCIES_ALL is not getting updated with closed delinquencies or new delinquencies on past due invoices when the IEX: 

Scoring Engine Harness or IEX: Delinquency Management processes run.  You have confirmed that you are not putting in a value for the Maximum Number of Records parameter on the IEX: Scoring Engine Harness, however no delinquencies are being created.

On further investigation, even though IEX: Scoring Engine Harness shows successful the following message can be seen in the log -

ERROR IN CONCUR: IEX_SCORE: storeDelBuffest: UnExpected Error ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE and cannot be instatiated

CAUSE:-

The IEX_DELINQUENCIES_ALL table has run out of sequence numbers and you can no longer create new records.

Bug 21812472

SOLUTION:-

To resolve the issue, the following SQL will recreate the sequence IEX_DEL_BUFFERS_S with NO Max Value:

set serveroutput on;

declare

BEGIN

execute immediate ('drop sequence IEX.iex_del_buffers_s');

execute immediate ('create sequence IEX.IEX_DEL_BUFFERS_S START WITH 10000 '

  || 'MINVALUE 1 INCREMENT BY 1 CACHE 100 NOORDER ');

 DBMS_OUTPUT.put_line('New Sequence for IEX_DEL_BUFFERS_S ' || sqlerrm);

EXCEPTION

  WHEN OTHERS then

  DBMS_OUTPUT.put_line('Sequence creation failed IEX_DEL_BUFFERS_S ' ||

sqlerrm);

end;

commit;

Output like below:-

New Sequence for IEX_DEL_BUFFERS_S ORA-0000: normal, successful completion

PL/SQL procedure successfully completed.

Commit complete.