Scripts for resize the data files to release space in oracle

Resize query give us the output of space free at database level. How much space we released backup to Operating system from Oracle database files. It help us to shrink the size of database.

Alter command for resize the datafiles in Oracle

alter database datafile 'file_name' resize 10G;

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 500M;

Check the size of tablespace and files present in the database:

SELECT file_id,
       file_name,
       tablespace_name,
       bytes/1024/1024 AS size_mb,
       maxbytes/1024/1024 AS max_size_mb,
       autoextensible
FROM dba_data_files
ORDER BY tablespace_name;

Enable or disable Autoextend of files to max size:

-- Disable Autoextend:
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND OFF;

-- Enabled Autoextend
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND ON
    NEXT 50M MAXSIZE 2G;

Resize query with total space released format:

SELECT file_id,
       tablespace_name,
       bytes/1024/1024 AS total_mb,
       bytes - free_bytes AS used_mb
FROM (
   SELECT f.file_id, f.tablespace_name, f.bytes,
          SUM(f.bytes) OVER(PARTITION BY f.file_id) AS free_bytes
   FROM dba_free_space f
);

Resize multiple datafiles script:

BEGIN
  FOR r IN (SELECT file_name
            FROM dba_data_files
            WHERE tablespace_name='USERS')
  LOOP
    EXECUTE IMMEDIATE 'ALTER DATABASE DATAFILE '''||r.file_name||''' RESIZE 500M';
  END LOOP;
END;
/