I have 6gb file for SYSAUX table space in 18xe oracle database.
SYSAUX table space has 61% free space :
select fs.tablespace_name as Tablespace, (df.totalspace - fs.freespace) as Used_MB, fs.freespace as Free_MB, df.totalspace as Total_MB, round(100 * (fs.freespace / df.totalspace)) as Percentage_Free from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace from dba_free_space group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name and df.tablespace_name = 'SYSAUX';
So I would like to reclaim the 61% free space if possible.
Attempt 1 :
ALTER DATABASE DATAFILE '...\XE\SYSAUX01.DBF' RESIZE 5000M;
But I'm getting the error :
ORA-03297: file contains used data beyond requested RESIZE value
Attempt 2 :
ALTER TABLESPACE SYSAUX SHRINK SPACE KEEP 5000M;
ORA-12916: cannot shrink permanent or dictionary managed tablespace
Attempt 3 :
ALTER TABLESPACE sysaux RESIZE 5000M;
ORA-32773: operation not supported for smallfile tablespace SYSAUX
Does anyone know to relcaim this space please ?