Resizing datafiles
                                                 Last update (2010-05-11 12:52:44)
                                                                                                                   Date added (2008-02-14 17:28:57)

Summary
Finding the highwater mark of a datafile is very important because you can resize it and take back some free space at the filesystem.
SELECT /*+ RULE */ a.file_id, a.tablespace_name ,a.file_name, 
	   ROUND((a.bytes)/(1024*1024), 3) SIZE_MB, 
	   ROUND(((b.maximum+c.blocks-1)*d.db_block_size)/(1024*1024), 3) YOU_CAN_RESIZE_UP_TO_MB, 
	   ROUND((a.bytes)/(1024*1024), 3) - ROUND(((b.maximum+c.blocks-1)*d.db_block_size)/(1024*1024), 3) TAKE_BACK_MB 
FROM   dba_data_files a, (SELECT file_id,MAX(block_id) maximum FROM dba_extents  
GROUP BY file_id) b ,dba_extents c,(SELECT VALUE db_block_size FROM v$parameter 
WHERE NAME='db_block_size') d 
WHERE a.file_id  = b.file_id
AND   c.file_id  = b.file_id 
AND   c.block_id = b.maximum
AND   a.file_name LIKE '/oracle/DwhProd/oradata10%' 
ORDER BY take_back_mb DESC;

OR query with the tablespace name

SELECT /*+ RULE */ a.file_id, a.tablespace_name ,a.file_name, 
	   ROUND((a.bytes)/(1024*1024), 3) SIZE_MB, 
	   ROUND(((b.maximum+c.blocks-1)*d.db_block_size)/(1024*1024), 3) YOU_CAN_RESIZE_UP_TO_MB, 
	   ROUND((a.bytes)/(1024*1024), 3) - ROUND(((b.maximum+c.blocks-1)*d.db_block_size)/(1024*1024), 3) TAKE_BACK_MB 
FROM   dba_data_files a, (SELECT file_id,MAX(block_id) maximum FROM dba_extents  
GROUP BY file_id) b ,dba_extents c,(SELECT VALUE db_block_size FROM v$parameter 
WHERE NAME='db_block_size') d 
WHERE a.file_id  = b.file_id
AND   c.file_id  = b.file_id 
AND   c.block_id = b.maximum
AND   a.tablespace_name = 'COSTEDEVENT' 
ORDER BY take_back_mb DESC;

The Faster query of all

SELECT D.file_id, D.file_name, D.bytes/1024/1024 SIZE_MB , take_back.take_back_mb,  
D.bytes/1024/1024 - take_back.take_back_mb RESIZE_MB, 
'ALTER DATABASE DATAFILE ' || '''' || D.file_name || '''' || 
' RESIZE ' || CEIL(D.bytes/1024/1024 - take_back.take_back_mb) || 'M;' SQL
FROM dba_data_files D, (SELECT SUM (BYTES) / 1024 / 1024 take_back_mb FROM dba_free_space
 WHERE tablespace_name = 'DWADMIN'
   AND file_id = 381
   AND block_id >= NVL ((SELECT (block_id + (BYTES / :block_size))
                  FROM dba_extents
                 WHERE block_id =
                          (SELECT MAX (block_id)
                             FROM dba_extents
                            WHERE file_id = 381
                              AND tablespace_name = 'DWADMIN')
                   AND file_id = 381
                   AND tablespace_name = 'DWADMIN'),
               0
              )) take_back 
			  WHERE D.file_id =381;

Enable DBMS Output on and execute the following sql code for a specific tablespace
DECLARE
v_stmt  VARCHAR2(500);
CURSOR c1 IS
SELECT file_id FROM dba_data_files WHERE tablespace_name='COSTEDEVENT'; 
BEGIN FOR line IN c1 LOOP SELECT 'ALTER DATABASE DATAFILE ' || '''' ||
 D.file_name || '''' || ' RESIZE ' || 
 NVL(CEIL(D.bytes/1024/1024 - take_back.take_back_mb), D.bytes/1024/1024) || 'M;' SQL 
 INTO v_stmt FROM dba_data_files D, (SELECT SUM (BYTES) / 1024 / 1024 take_back_mb
  FROM dba_free_space
 WHERE tablespace_name = 'COSTEDEVENT'
   AND file_id = line.file_id
   AND block_id >=
          NVL ((SELECT (block_id + (BYTES / 32768))
                  FROM dba_extents
                 WHERE block_id =
                          (SELECT MAX (block_id)
                             FROM dba_extents
                            WHERE file_id = line.file_id
                              AND tablespace_name = 'COSTEDEVENT')
                   AND file_id = line.file_id
                   AND tablespace_name = 'COSTEDEVENT'),
               0
              )) take_back 
              WHERE D.file_id =line.file_id; DBMS_OUTPUT.PUT_LINE(v_stmt); 
			  END LOOP; 
			  END; 
			  /


Output
file_name: /oraprom1/datafiles/mpis01.dbf
SIZE_MB : 800
YOU_CAN_RESIZE_DOWN_TO_MB: 668,625
TAKE_BACK_MB:131,375

Do the resizing
ALTER DATABASE DATAFILE '/oraprom1/datafiles/mpis01.dbf' RESIZE 669MB;
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search