I am using Oracle 12.2.0.1. My tablespaces appear to be too much fragmented and I would need to reclaim the free space at data file level. How can I do it without using enterprise Manager (since I dont have it) or major manual steps?
I used following query -
SELECT
ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",
RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph"
FROM
(SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name
AND fr.tablespace_name = ts.tablespace_name
ORDER BY "% Free" desc
/
and from results I can see that my APP_DATA has 76% free space. I would like to reclaim 181 GB physical disk space. How can I do it? Thanks
USERS | 1 | 32767.98 | 32766.62 | 1.35 | 32767.984375 | 99.99585151474548089288754124047338508291 | ********** |
APP_DATA | 1 | 2369854 | 1815980.68 | 553873.32 | 33554431.9765625 | 76.62837801457001011926126923568765590462 | ******* |
APP_INDEX | 1 | 1206097 | 806631.43 | 399465.57 | 33554431.9765625 | 66.87948251882231928418744975925282025893 | ****** |
SYSAUX | 1 | 32767.88 | 17413.12 | 15354.75 | 32767.984375 | 53.1408303052078671736735356099686979128 | ***** |
SYSTEM | 1 | 6077 | 7.43 | 6069.57 | 32767.984375 | 0.1223875339554699642736389850576648407098 | |