Sample Header Ad - 728x90

How to reclaim unused space in SYSAUX tablespace in Oracle?

1 vote
1 answer
4251 views
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'; returns enter image description here 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; Error : ORA-12916: cannot shrink permanent or dictionary managed tablespace Attempt 3 : =========== ALTER TABLESPACE sysaux RESIZE 5000M; Error ORA-32773: operation not supported for smallfile tablespace SYSAUX Does anyone know to relcaim this space please ? Thanks.
Asked by user32026 (141 rep)
Sep 6, 2021, 05:16 PM
Last activity: Jul 29, 2025, 09:04 PM