Wednesday, May 11, 2016

ORA-1654: unable to extend index SYS.WRH$_BG_EVENT_SUMMARY_PK by 1024 in tablespace SYSTEM

ORA-1654: unable to extend index SYS.WRH$_BG_EVENT_SUMMARY_PK by 1024 in tablespace SYSTEM

While doing Datapump Import operation on RHEL 5.4 server everything went good but at the end of the job import operation experienced resumable wait with an error mentioned below.

ERROR:
ORA-01654: unable to extend index SYS.I_HH_OBJ#_COL# by 128 in tablespace SYSTEM
ORA-39171: Job is experiencing a resumable wait.

Solution :
Check the size and maxsize of the data files in the SYSTEM tablespace

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 – Production

SQL> select file_name, bytes, autoextensible, maxbytes from dba_data_files where tablespace_name='SYSTEM';

SQL> select sum(bytes)/1024/1024 MB from dba_free_space  where TABLESPACE_NAME='SYSTEM';

The SYSTEM tablespace has no space to allocate any more in it then we increased the size of the SYSTEM’s data file then our problem got solved.

SQL> alter database datafile '<Path to datafile>' resize <larger size> ;

or

SQL> alter tablespace system add datafile '<path of datafile>' size 2g;

No comments:

Post a Comment