Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We ensure that unused space in the datafiles are reclaimed by the ASM disk group with the resize process. This ensures that unused areas are reused.
You can find the script below for the resize process:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | select * from (select file_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest, ceil( blocks*&&blksize/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings, 'alter database datafile ''' || file_name || ''' resize ' || ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'M;' sql from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extentsgroup by file_id ) b where a.file_id = b.file_id(+) ) where savings > 0 order by savings desc; |
You need to set the && blksize variable in the query according to the db_block_size parameter of your database.
As a result of the query, the savings column shows the amount of space to be earned. The sql column also specifies the query required for the resize operation.
