DB: ORA-01688: unable to extend table

Recently, I encountered following error in Oracle Database 12c log files.

Error:
ORA-02002: error while writing to audit trail
ORA-55917: Table flush I/O failed for log ID: 1 bucket ID: 0
ORA-01688: unable to extend table AUDSYS.CLI_SWP$8ab1e924$1$1 partition HIGH_PART by 1024 in tablespace SYSAUX partition by in tablespace

Then I logged into Database 12c as SYSDBA and ran following queries to verify tablespace quota.

Query 1:
========
SELECT df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
       df.totalspace "Total MB",
        round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"
FROM   (SELECT tablespace_name,round(sum(bytes) / 1048576) TotalSpace FROM dba_data_files GROUP BY tablespace_name) df,
       (SELECT round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name FROM dba_segments GROUP BY tablespace_name) tu
WHERE  df.tablespace_name = tu.tablespace_name;

Tablespace                      Used MB    Free MB   Total MB  Pct. Free
------------------------------ ----------     ---------- ---------- ----------
SYSAUX                               5075         45       5120          1
USERS                               21082       3677      24759         15
SYSTEM                                728         12        740          2

Query 2:
========
SQL> SELECT * FROM dba_tablespace_usage_metrics;

TABLESPACE_NAME    USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX                             649784          655360   99.1491699
SYSTEM                              93312          655360   14.2382813
USERS                             2699032        14028248   19.2399792

Just to rewind, a tablespace is where the physical Oracle meets the logical Oracle and a tablespace is a bit like a file system for your database. It is a logical entity (this means the tablespace only exists within the Oracle database itself, not outside of it) that provides storage space so that users can create things like tables and indexes. And one datafile can only be assigned to one tablespace.

And I fixed this issue by adding data file to SYSAUX tablespace using below command.

> ALTER TABLESPACE SYSAUX ADD DATAFILE '/xyz/oradata/data01/sysaux01.dbf' SIZE 200M AUTOEXTEND ON NEXT 20M MAXSIZE 5120M;

Note that I used 200M to indicate that I wanted to create the tablespace 200 Megabytes in size and the tablespace will auto extend in increments of 20 Megabytes until it reaches a maximum size of 5120 Megabytes. It's recommended to use auto extend on all tablespaces for Production databases.

Note, if you want to resize data file, use alter database command.

> ALTER DATABASE DATAFILE '/xyz/oradata/data01/sysaux01.dbf' resize 500M