Search This Blog

Monday, November 26, 2007

Data file fragmentation and Data file resize

There is no fragmentation at datafile level but sometime datafile is created in big size and actually data in datafile is very less then we can resize datafile to reduce database size.
We can reduce datafile size upto HWM (High Water Mark)

SQL> select df.file_name,round(df.bytes/1024/1024) TotalSize,fs.FreeSize
from dba_data_files df,
(select file_id,round(sum(bytes/1024/1024)) FreeSize
from dba_free_space
group by file_id
) fs
where df.file_id=fs.file_id(+)
order by 2,3 desc

FILE_NAME TOTALSIZE FREESIZE
-------------------------------------------------- ---------- ----------
D:\ORACLE\PRODUCT\10.1.0\ORADATA\USERS01.DBF 46 1
D:\BIG01.DBF 100 100
D:\ORACLE\PRODUCT\10.1.0\ORADATA\UNDOTBS01.DBF 465 436
D:\ORACLE\PRODUCT\10.1.0\ORADATA\SYSTEM01.DBF 620 63
D:\ORACLE\PRODUCT\10.1.0\ORADATA\SYSAUX01.DBF 700 32


Note: Size in MB (Mega Bytes)

Two datafiles which we can reduce 1.big01.dbf 2.undotbs01.dbf

SQL> alter database
datafile ‘d:\big01.dbf’ resize 50m;

Database altered.

SQL> alter database
datafile 'd:\oracle\product\10.1.0\oradata\undotbs01.dbf' resize 100m;
alter database
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

http://dbataj.blogspot.com/2007/01/ora-03297.html

Note: ORA-03297 error happens because we are trying to reduce datafile below HWM and it is not possible. Go through above link and check from where you can reduce datafile size.

No comments: