In developpement, it can happen that you may want to shrink oracle datafiles after a huge delete (ie. drop user, drop big table, ...).
Here is a simple way to shrink datafiles using RESIZE option, but it may not be the most efficient.
RESIZE datafiles to the minimal
The following script will generate a sql file to resize every datafiles to the minimal size possible with RESIZE option.
--
-- ResizeAllDatafiles.sql
-- Run this script as sysdba
--
WHENEVER OSERROR EXIT FAILURE ;
WHENEVER SQLERROR EXIT FAILURE ;
SET TERM OFF VERIFY OFF FEED OFF ECHO OFF;
SET PAGES 0 LINES 255;
COLUMN SqlCmd FORMAT A120 WORD_WRAPPED ;
COLUMN DB_BLOCK_SIZE NEW_VALUE DbBlkSize_var ;
-- Retrieves db block size into DbBlkSize_var
SELECT value DB_BLOCK_SIZE FROM V$PARAMETER WHERE UPPER(NAME) = 'DB_BLOCK_SIZE' ;
SET TERM ON ;
PROMPT -- ;
PROMPT -- DB_BLOCK_SIZE: &DbBlkSize_var. ;
PROMPT -- SQL QUERY IN PROGRESS, IT CAN TAKE A WHILE ... ;
PROMPT -- ;
SPOOL ResizeAllDatafiles.gen.sql ;
SELECT 'SET ECHO ON ;' SqlCmd FROM dual ;
SELECT 'ALTER DATABASE DATAFILE '''
|| FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1) * &&DbBlkSize_var.) /1024 /1024 ) || 'M;'
|| ' -- CurrentSize=' || CEIL( BLOCKS * &&DbBlkSize_var. /1024 /1024) || 'M'
SqlCmd
FROM DBA_DATA_FILES DBADF
LEFT JOIN (SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
ON DBADF.FILE_ID = DBAFS.FILE_ID
WHERE CEIL(BLOCKS * &&DbBlkSize_var. /1024 /1024) - CEIL((NVL(HWM,1) * &&DbBlkSize_var.) /1024 /1024 ) > 0 ;
SPOOL OFF ;
PROMPT -- ResizeAllDatafiles.gen.sql has been generated ;
PROMPT -- Run it as sysdba to resize all your datafiles ;
EXIT;
Go further with RESIZE
Actually RESIZE option can only shrink the datafile over the lastest block in use.
So it may happen that you won't be able to shrink a datafile even if less than 1% is used.
We can try to move objects at the beginning of the datafile in order to get a more efficient RESIZE.
Or even better is to move all object to a new tablespace then skrink all datafiles (move objects back if necessary)
Here is some SQL which can hel you to archieve that:
-- Get datafiles with id and percentage of used
SELECT df.file_id,
Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,80) "File Name",
Round(df.maxbytes/1024/1024,0) "Max Size (M)",
Round(df.bytes/1024/1024,0) "Actual Size (M)",
decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM DBA_DATA_FILES DF
LEFT JOIN
(SELECT file_id, sum(bytes) used_bytes
FROM dba_extents GROUP by file_id) E
ON df.file_id = e.file_id
LEFT JOIN
(SELECT Max(bytes) free_bytes, file_id
FROM dba_free_space GROUP BY file_id) f
ON df.file_id = f.file_id
ORDER BY
df.tablespace_name,
df.file_name
;
-- Get sql to move all objects stored in datafile 16 to the same tablespace (reorganized objects)
SELECT
CASE
WHEN segment_type = 'TABLE' THEN 'ALTER TABLE '|| e.owner|| '.'|| e.segment_name|| ' MOVE TABLESPACE '||e.tablespace_name||' ;'
WHEN segment_type = 'INDEX' THEN 'ALTER INDEX '|| e.owner|| '.'|| e.segment_name|| ' REBUILD TABLESPACE '||e.tablespace_name||' ;'
WHEN segment_type = 'TABLE PARTITION' THEN 'ALTER TABLE '|| e.owner|| '.'|| e.segment_name|| ' MOVE PARTITION '|| e.partition_name|| ' TABLESPACE '||e.tablespace_name||' ;'
WHEN segment_type = 'INDEX PARTITION' THEN 'ALTER INDEX '|| e.owner|| '.'|| e.segment_name|| ' REBUILD PARTITION '|| e.partition_name|| ' TABLESPACE '||e.tablespace_name||' ;'
WHEN segment_type = 'LOBSEGMENT' THEN (
SELECT 'ALTER TABLE '|| l.owner|| '.'|| l.table_name|| ' MOVE LOB('|| l.column_name|| ') STORE AS (TABLESPACE '||e.tablespace_name||') ;'
FROM dba_lobs l
WHERE l.owner = e.owner
AND l.segment_name = e.segment_name
AND ROWNUM = 1 )
WHEN segment_type = 'LOBINDEX' THEN (
SELECT 'ALTER TABLE '|| l.owner|| '.'|| l.table_name|| ' MOVE LOB('|| l.column_name|| ') STORE AS (TABLESPACE '||e.tablespace_name||') ;'
FROM dba_lobs l
WHERE l.owner = e.owner
AND l.index_name = e.segment_name
AND ROWNUM = 1 )
ELSE '-- UNKNOW CASE '|| e.owner|| '.'|| e.segment_name|| ' ('|| segment_type|| ') ;'
END "sql",
max_block_id,
DECODE(substr(segment_type,0,5),'INDEX',2,0) "sorthelper"
FROM (
SELECT owner, segment_name, partition_name, segment_type, tablespace_name, MAX(block_id) max_block_id
FROM dba_extents
WHERE file_id = 14
GROUP BY owner, segment_name, partition_name, segment_type, tablespace_name
ORDER BY MAX(block_id) DESC
) e
ORDER BY 3, 2 DESC
;
You may want to rebuild all unusable indexes after that:
set serveroutput on size unlimited
BEGIN
FOR rec IN (
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' ONLINE PARALLEL' cmd
FROM dba_indexes
WHERE status = 'UNUSABLE'
UNION ALL
SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||' ONLINE PARALLEL' cmd
FROM dba_ind_PARTITIONS
WHERE status = 'UNUSABLE'
UNION ALL
SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD SUBPARTITION '||subpartition_name||' ONLINE PARALLEL' cmd
FROM dba_ind_SUBPARTITIONS
WHERE status = 'UNUSABLE'
) LOOP
dbms_output.put_line(rec.cmd);
EXECUTE immediate rec.cmd;
END LOOP;
END;
/
More efficient way for temporary datafile
The RESIZE option can be used for temporary datafile but it may not be very efficient with this kind of file.
HTH,
Michel.
Enjoyed this article? Please like it or share it.
Please connect with one of social login below (or fill up name and email)