Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to Defragment tablespace / datafiles 12c

MDK999Feb 2 2017 — edited Feb 7 2017

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

USERS132767.9832766.621.3532767.98437599.99585151474548089288754124047338508291**********
APP_DATA123698541815980.68553873.3233554431.976562576.62837801457001011926126923568765590462*******
APP_INDEX11206097806631.43399465.5733554431.976562566.87948251882231928418744975925282025893******
SYSAUX132767.8817413.1215354.7532767.98437553.1408303052078671736735356099686979128*****
SYSTEM160777.436069.5732767.9843750.1223875339554699642736389850576648407098
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 7 2017
Added on Feb 2 2017
19 comments
6,086 views