André Spadini | MultiCloud | DBA

André Spadini | MultiCloud | DBA

Oracle: Shrink em Datafiles

Oracle: Shrink em Datafiles

Subscribe to my newsletter and never miss my upcoming articles

Segue abaixo comando para realizar shrink em datafiles do Oracle. Não se preocupe, pois o Oracle não corrompe o datafiles, mesmo se o tamanho informado for menor do que o utilizado.

SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)* 8192)/1024/1024 ) || 'M;' SHRINK_DATAFILES 
  FROM DBA_DATA_FILES DBADF,
      (SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS 
 WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS* 8192/1024/1024)- CEIL((NVL(HWM,1)* 8192)/1024/1024 ) > 0 ;

ALTER DATABASE DATAFILE '+DATA/expdb_gru17g/datafile/system.261.1017320455' RESIZE 15584M;
ALTER DATABASE DATAFILE '+DATA/expdb_gru17g/datafile/tools.281.1048952019' RESIZE 6804M;
ALTER DATABASE DATAFILE '+DATA/expdb_gru17g/datafile/sysaux.262.1017320455' RESIZE 448M;
ALTER DATABASE DATAFILE '+DATA/expdb_gru17g/datafile/users.266.1017320801' RESIZE 24M;
ALTER DATABASE DATAFILE '+DATA/expdb_gru17g/datafile/undotbs1.263.1017320455' RESIZE 360M;
ALTER DATABASE DATAFILE '+DATA/expdb_gru17g/datafile/sgtlogdt.280.1022610921' RESIZE 2M;
ALTER DATABASE DATAFILE '+DATA/expdb_gru17g/datafile/nfepackdt.267.1019522627' RESIZE 1M;
ALTER DATABASE DATAFILE '+DATA/expdb_gru17g/datafile/nfepackdt.276.1019522645' RESIZE 1M;
ALTER DATABASE DATAFILE '+DATA/expdb_gru17g/datafile/nfepackdt.277.1019522685' RESIZE 1M;

Abs!!!

 
Share this