Fix: ORA-01653: unable to extend table by <size> in tablespace

Scenario:

• You have Oracle Database hosted on Linux systems located apart in two datacenters with replication enabled to synchronize the data. 
• Assume you have a web based application that reads/writes data from/to the database.  Your application is coded to replicate the data across the Oracle DBs located in datacenters. 
• All read operations working fine but whenever you attempt to write data you are encountering below error message:
  

  Error updating WebSite: Replication Exception: ORA-01653: unable to extend table PROD.DEBUGLOG by 5394 in tablespace MYAPP ORA-06512: at "PROD.REPLIB", line 30 ORA-28579: network error during callback from external procedure agent ORA-06512: at "PROD.REPL", line 208 ORA-06512: at line 1

Troubleshooting:

• From the error message, it’s obvious that its unable to extend the PROD.DEBUGLOG table in MYAPP tablespace in your Oracle DB
• From general administrator perspective, you verify that your Linux system hosting the Oracle DB has enough space to accommodate data files growth
  • you can use below command:
    

    [root@myhost ~]# df -k -h

    Filesystem Size Used Avail Use% Mounted on

    /dev/cciss/c9d9p2 63G 59G 672M 99% /

    tmpfs 3.9G 0 3.9G 0% /dev/shm

    [root@myhost~]#

  • in case if your disk is full, then run below command to see which are the top disk using files that you can free up to help your other services on the server
    

    [root@myhost ~]# find / -type f -ls | sort -k 7 -r -n | head -5
    4026531862    0 -r——–   1 root     root     9663676416 Jul 11 11:02 /proc/kcore
    5701641 2099204 -rw-r–r–   1 oracle   dba      2147483647 Jul 10 15:48 /tmp/launch.log
    13206074 2059992 -rw-r–r–   1 oracle   dba      2107367424 Nov 12  2010 /opt/oracle/full-mydc01-2012_11_13.dmp
    8585217 2050008 -rw-r—–   1 oracle   dba      2097156096 Jul 10 10:46 /opt/db1/oradata/myhost/testapp03.dbf
    13991965 2050008 -rw-r—–   1 oracle   dba      2097156096 Nov 15  2009 /opt/db1/oradata/myhost/empty/testapp02myhost.dbf
    [root@myhost ~]#
• Considering the “Replication Exception”, from network administration perspective you verify that you are able to
  • ping the Oracle servers each other
  • verify there isn’t any much latency in reaching servers from each other
  • verify that Oracle DB port 1521 is accessible across servers
• If above all turned out to be okay, then you’ll have issue at your DB level and you’ll look from a DBA perspective of it
  • Isolate whether it’s related to replication or NOT, by creating new table in the same tablespace. If you are having issue with changes to DB even locally then it’s not related to your replication and/or network
  • Get the overall current tablespace file size using below query in Oracle:
    

    select tablespace_name,sum(bytes)/1024/1024/1024 "Max Size in GB" from dba_data_files where tablespace_name='<ts_name>’ group by tablespace_name

  • Check if AutoExtension is enabled for the tablespace in error by using below query:
    

    select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name=’<ts_name>’

  • Check how much free space available within that tablespace to accommodate the new changes by using below query:
    

    select tablespace_name,sum(bytes)/1024/1024/1024 "Free Size in GB" from DBA_FREE_SPACE where tablespace_name='<ts_name>’ group by tablespace_name

  • select TABLESPACE_NAME, sum(BYTES) Total_free_space, max(BYTES) largest_free_extent from dba_free_space group by TABLESPACE_NAME

     

  • If the Free percentage is very low or your tablespace is fully used then it mostly indicates an issue with your low space on tablespace
  • Identify the tablespace file by running below query and usually the top file would be the active file.                                         

  select * from dba_data_files where tablespace_name=’<ts_name>’
•  

FIX:

• Increase the tablespace area either by
  • Set AUTOEXTEND ON for the tablespace in the trouble/error
  • Resize/Extend your datafile size for the tablespace
• A quick fix is enabling AUTOEXTEND as shown below:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND ON

Other resources:

Increasing tablespace size to resolve the "ORA-01653 unable to extend table/tablespace" error

Tablespace – free space.sql