This procedure can quickly help to reclaim space above the HWM in a datafile
CREATE OR REPLACE PROCEDURE reclaim_datafile_space (fileid NUMBER)
IS
taille NUMBER;
block_size INTEGER;
BEGIN
SELECT VALUE
INTO block_size
FROM V$PARAMETER
WHERE NAME = 'db_block_size';
SELECT CEIL ( (highblock * block_size + block_size) / 1024)
INTO taille
FROM ( SELECT file_id, MAX (block_id + blocks) highblock
FROM dba_extents
WHERE file_id = fileid
GROUP BY file_id);
EXECUTE IMMEDIATE
'alter database datafile ' || fileid || ' resize ' || taille || 'K';
END;
/
It is based on Tanel Poder script trim_database
No comments:
Post a Comment