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;
