SQLPlus Script – Alter Database Resize Datafile

von Tobias Redmann

Auf Test- und Entwicklungsumgebungen habe ich häufig mit Platzproblemen zu kämpfen. Oft hilft ein RESIZE der DATAFILES – sofern möglich – um schnell und unklompiziert Speicherplatz freizugeben. Im produktiven Umfeld empfehle ich jedoch den Tablespace zu reoganisieren um ungenutzen Speicherplatz freizugeben – gerade nach umfangreichen Datenmigrationen.

Das folgende SQLPlus-Script generiert DDL-Statements um ungenutzen Speicher vor der HWM – HighWaterMark – wieder freizugeben, demzufolge ein RESIZE DATAFILE.

Wie schon erwähnt werden diese DDL-Statements inklusive der potenziellen Platzeinsparung generiert, jedoch nicht ausgeführt. Sucht euch das gewünschte DATAFILE aus und führt dieses als SYS aus.

SET LINESIZE 400
SET PAGESIZE 5000

with
tab_max_block_id_from_file 
AS (
  select 
    file_id
   ,max(block_id) max_block_id        
  from dba_extents        
  group by file_id
),
tab_datafile_extend_info
AS (
  select
   ddf.tablespace_name  tablespace_name
  ,ddf.file_name        datafile_name
  ,ddf.bytes/1024/1024  datafile_size_MByte
  ,ddf.bytes
  ,de.blocks            size_extend_in_db_blocks
  ,ddf.maxbytes
  ,ddf.user_bytes
  ,ddf.file_id
  ,de.block_id
  ,((((de.blocks + de.block_id)) * dt.block_size)  + (1024 *1024)) hwm_byte
  from 
    dba_data_files ddf
  , dba_extents de
  , dba_tablespaces dt
  where 1=1
  and ddf.file_id = de.file_id
  AND dt.tablespace_name = ddf.tablespace_name
  AND ddf.maxbytes &gt; ((((de.blocks + de.block_id)) * dt.block_size) + (1024 *1024)) -- Datafile darf nicht voll sein
  AND ROUND((ddf.bytes)/1024/1024) > ROUND(((((de.blocks + de.block_id)) * dt.block_size) + (1024 *1024))/1024/1024)
)
Select
  'ALTER DATABASE DATAFILE'||' '''||tdei.datafile_name||''''||' RESIZE '||to_char(ROUND(hwm_byte/1024/1024)) ||' '||'M'||';'
  || '   -- *** saved in Datafile: ' || to_char(tdei.datafile_size_MByte - (hwm_byte/1024/1024),'FM9999999') || ' MByte'
  || ' *** saved in Tablespace ' || tablespace_name || ': ' || to_char(SUM(tdei.datafile_size_MByte - (hwm_byte/1024/1024)) OVER (PARTITION BY tablespace_name),'FM9999999') || ' MByte'
  || ' *** saved in Database: ' || to_char(SUM(tdei.datafile_size_MByte - (hwm_byte/1024/1024)) OVER (),'FM9999999') || ' MByte'
FROM
  tab_datafile_extend_info tdei
, tab_max_block_id_from_file tmbiff
WHERE 1=1
AND tdei.file_id = tmbiff.file_id
AND tdei.block_id = tmbiff.max_block_id
AND tdei.datafile_size_MByte - (hwm_byte/1024/1024) >= 1
order by
  tdei.tablespace_name
, tdei.datafile_name;

SET LINESIZE 400

SET PAGESIZE 5000

with

tab_max_block_id_from_file

AS (

select

file_id

,max(block_id) max_block_id

from dba_extents

group by file_id

tab_datafile_extend_info

AS (

select

ddf.tablespace_name tablespace_name

,ddf.file_name datafile_name

,ddf.bytes/1024/1024 datafile_size_MByte

,ddf.bytes

,de.blocks size_extend_in_db_blocks

,ddf.maxbytes

,ddf.user_bytes

,ddf.file_id

,de.block_id

,((((de.blocks + de.block_id)) * dt.block_size) + (1024 *1024)) hwm_byte

from

dba_data_files ddf

, dba_extents de

, dba_tablespaces dt

where 1=1

and ddf.file_id = de.file_id

AND dt.tablespace_name = ddf.tablespace_name

AND ddf.maxbytes > ((((de.blocks + de.block_id)) * dt.block_size) + (1024 *1024)) -- Datafile darf nicht voll sein

AND ROUND((ddf.bytes)/1024/1024) > ROUND(((((de.blocks + de.block_id)) * dt.block_size) + (1024 *1024))/1024/1024)

)

Select

'ALTER DATABASE DATAFILE'||' '''||tdei.datafile_name||''''||' RESIZE '||to_char(ROUND(hwm_byte/1024/1024)) ||' '||'M'||';'

|| ' -- *** saved in Datafile: ' || to_char(tdei.datafile_size_MByte - (hwm_byte/1024/1024),'FM9999999') || ' MByte'

|| ' *** saved in Tablespace ' || tablespace_name || ': ' || to_char(SUM(tdei.datafile_size_MByte - (hwm_byte/1024/1024)) OVER (PARTITION BY tablespace_name),'FM9999999') || ' MByte'

|| ' *** saved in Database: ' || to_char(SUM(tdei.datafile_size_MByte - (hwm_byte/1024/1024)) OVER (),'FM9999999') || ' MByte'

FROM

tab_datafile_extend_info tdei

, tab_max_block_id_from_file tmbiff

WHERE 1=1

AND tdei.file_id = tmbiff.file_id

AND tdei.block_id = tmbiff.max_block_id

AND tdei.datafile_size_MByte - (hwm_byte/1024/1024) >= 1

order by

tdei.tablespace_name

, tdei.datafile_name;

SCHREIBEN SIE EINEN KOMMENTAR

Sie sind hier: