I got following query after google search to find tablespace utilization.
select fs.tablespace_name "Tablespace", (df.totalspace - fs.freespace) "Used MB", fs.freespace "Free MB", df.totalspace "Total MB", round(100 * (fs.freespace / df.totalspace)) "Pct. 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='AUDSYS';
Tablespace used MB free MB total MB pct. Free
AUDSYS 3169 29539 32708 90
Based on the above query, i tried to resize data file as there is 29GB is free, I got error. How to resize this ?
alter database datafile '/data/PMS/audsys01.dbf' resize 10G;
Error report -
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 - "file contains used data beyond requested RESIZE value"
*Cause: Some portion of the file in the region to be trimmed is
currently in use by a database object
*Action: Drop or move segments containing extents in this region prior to
resizing the file, or choose a resize value such that only free
space is in the trimmed.