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:
Post a Comment