Ever wanted to shrink a datafile?

Are you like me and you might get a little bit too aggressive on space creation – ask for 4TB not 1…

Anyway, reality might mean that you need to scale things back, so here is some handy commands to do that:

see the size of the data files:

SELECT name, bytes/1024/1024 AS size_mb

FROM v$datafile;

Shrink datafile –

ALTER DATABASE DATAFILE '/u02/app/oracle/oradata/datastore/CRDTAI.dbf' RESIZE 1024G;

ALTER DATABASE DATAFILE '/u02/app/oracle/oradata/datastore/CRPDTAT.dbf' RESIZE 800G;

It’s that simple (well it was for me).