ORA-01653: unable to extend table JSH.JA_LOG by 1024 in tablespace USERS

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

에러 : ORA-1653: unable to extend table JSH.JA_LOG by 1024 in tablespace USERS

alert log에 아래 메세지 발생

1 2	Wed Jan  2 10:30:41 2019 ORA-1653: unable to extend table JSH.JA_LOG by 1024 in tablespace USERS

해결 방법 : tablespace에 datafile 을 추가해주거나 datafile resize 해준다.

사용중인 tablespace 확인

SQL>
set pagesize 1000
set linesize 132 trims on
col "TS NAME" format a30
col "USED(%)" format 999.00
col "TOTAL SIZE(MB)" format 999,999,999.99
col "USED SIZE(MB)" format 999,999,999.99
col "FREE SIZE(MB)" format 999,999,999.99
col tablespace_name format a14
set trimspool on
 
select  
       a.tablespace_name "TS NAME", 
       a.bytes "TOTAL SIZE(MB)",
       (a.bytes-b.bytes) "USED SIZE(MB)",
       b.bytes "FREE SIZE(MB)",  
       (a.bytes-b.bytes)/(a.bytes)*100 "USED(%)"
from ( select sum(bytes)/1024/1024 bytes, tablespace_name
       from dba_data_files
       group by tablespace_name ) a,
     ( select nvl(sum(bytes)/1024/1024,0) bytes, nvl(max(bytes)/1024/1024,0) max_free, tablespace_name
       from dba_free_space
       group by tablespace_name )  b
where a.tablespace_name = b.tablespace_name(+)
order by a.tablespace_name
/
 
TS NAME             TOTAL SIZE(MB)  USED SIZE(MB) FREE SIZE(MB)    USED(%)
------------------ --------------- ------------ --------------- --------- 
MOVE_IMSI             1,024.00          1.00      1,023.00          .10
JSHTS                 1,024.00        321.06        702.94        31.35
SYSAUX                  699.00        677.63         21.38        96.94
SYSTEM                  699.00        276.50        422.50        39.56
UNDOTBS1                199.00        110.44         88.56        55.50
UNDOTBS2                699.00         91.25        607.75        13.05
USERS                   699.00        696.56          2.44        99.98
 
7 rows selected.

USERS tablespace의 사용량(USED)이 99%임을 확인 가능함

USERS tablespace에 datafile 추가

SQL> alter tablespace users add datafile '/app/oracle/oradata/users02.dbf' size 1000m;
 
Tablespace altered.

사용중인 tablespace 재확인

SQL>
set pagesize 1000
set linesize 132 trims on
col "TS NAME" format a30
col "USED(%)" format 999.00
col "TOTAL SIZE(MB)" format 999,999,999.99
col "USED SIZE(MB)" format 999,999,999.99
col "FREE SIZE(MB)" format 999,999,999.99
col tablespace_name format a14
set trimspool on
 
select  
       a.tablespace_name "TS NAME", 
       a.bytes "TOTAL SIZE(MB)",
       (a.bytes-b.bytes) "USED SIZE(MB)",
       b.bytes "FREE SIZE(MB)",  
       (a.bytes-b.bytes)/(a.bytes)*100 "USED(%)"
from ( select sum(bytes)/1024/1024 bytes, tablespace_name
       from dba_data_files
       group by tablespace_name ) a,
     ( select nvl(sum(bytes)/1024/1024,0) bytes, nvl(max(bytes)/1024/1024,0) max_free, tablespace_name
       from dba_free_space
       group by tablespace_name )  b
where a.tablespace_name = b.tablespace_name(+)
order by a.tablespace_name
/
 
TS NAME             TOTAL SIZE(MB)  USED SIZE(MB) FREE SIZE(MB)    USED(%)
------------------ --------------- ------------ --------------- --------- 
MOVE_IMSI             1,024.00          1.00      1,023.00          .10
JSHTS                 1,024.00        321.06        702.94        31.35
SYSAUX                  699.00        677.63         21.38        96.94
SYSTEM                  699.00        276.50        422.50        39.56
UNDOTBS1                199.00        110.44         88.56        55.50
UNDOTBS2                699.00         91.25        607.75        13.05
USERS                  1699.00        696.56       1002.44        30.11
 
7 rows selected.

USERS tablespace 의 사용량(USED)이 줄어든 것을 확인가능함

*참고

사용중인 datafile resize 방법

SQL> alter database datafile '/app/oracle/oradata/users01.dbf' resize 1699m;
 
Tablespace altered.

원인 : tablespace 공간부족

참조 :