Google+ Followers

Pages

Tuesday, August 9, 2011

Resize Datafile to Optimal Size


SQL> Define BLKSIZE=

SELECT /*+rule*/ 'ALTER DATABASE DATAFILE '''||FILE_NAME||''' RESIZE '||CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )||'M;',
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) -
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES A,
( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM
FROM DBA_EXTENTS
GROUP BY FILE_ID ) B
WHERE A.FILE_ID = B.FILE_ID(+)




OUTPUT

'ALTERDATABASEDATAFILE'''||FILE_NAME||'''RESIZE'||CEIL((NVL(HWM,1)*8192)/1024/1024)||'M;'
SMALLEST
CURRSIZE
SAVINGS
ALTER DATABASE DATAFILE 'D:\ORACLCE\APP\ADNAN\ORADATA\ORCL\SYSTEM01.DBF' RESIZE 684M;
684
690
6
ALTER DATABASE DATAFILE 'D:\ORACLCE\APP\ADNAN\ORADATA\ORCL\SYSAUX01.DBF' RESIZE 466M;
466
490
24
ALTER DATABASE DATAFILE 'D:\ORACLCE\APP\ADNAN\ORADATA\ORCL\UNDOTBS01.DBF' RESIZE 89M;
89
90
1
ALTER DATABASE DATAFILE 'D:\ORACLCE\APP\ADNAN\ORADATA\ORCL\USERS01.DBF' RESIZE 5M;
5
5
0
ALTER DATABASE DATAFILE 'D:\ORACLCE\APP\ADNAN\ORADATA\ORCL\EXAMPLE01.DBF' RESIZE 82M;
82
100
18



No comments:

Post a Comment