Resize Datafile

[Fiza15]

Hi All
say for instance i have datafile 100M and i want resize it to 50M...how oracle decides that if we want to add 50M in 100M or subtract 50M from 100M??? Thanks in advance for your time

[marist89]

Oracle will resize from the end of the file. If you have a 100M file and want to shrink it to 50M, Oracle will take the last 50M. However, if you have any segments in that last 50M, oracle will not let you resize it.

[Fiza15]

Thanks Jeff what if i want to add 50m in datafile which was initially 100m at the time of creation...Thanks

[marist89]

It adds it to the end of the file.

[Fiza15]

Thanks again Jeff i think i havent stated my question properly...lets say i have datafile 100m and its reached to HWM and i want to resize same datafile 50m...will it give me error mssge that i cant resize because there are DB Blocks having data....or my command will make my datafile to 150...hope not confusing you...Thanks again

[pando]

Originally posted by Fiza15
Thanks Jeff what if i want to add 50m in datafile which was initially 100m at the time of creation...Thanks

your question is confusing, you said you want to ADD 50MB not to reduce

[Fiza15]

Yes Pando correct i want to add 50m...Thanks for reply

[marist89]

Originally posted by Fiza15
will it give me error mssge that i cant resize because there are DB Blocks having data

Yes, you're very confused. If you are getting this error message, you are trying to shrink your data file.

For example:

SQL> select file_name, bytes/1024/1024 mb
  2  from dba_data_files
  3  where tablespace_name = 'USERS';

FILE_NAME                                                      MB
------------------------------------------------------ ----------
E:\ORADATA\NT817\USERS01.DBF                                    4

SQL> alter database datafile 'E:\ORADATA\NT817\USERS01.DBF' resize 1M;
alter database datafile 'E:\ORADATA\NT817\USERS01.DBF' resize 1M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> alter database datafile 'E:\ORADATA\NT817\USERS01.DBF' resize 5M;

Database altered.

SQL> select file_name, bytes/1024/1024 mb
  2  from dba_data_files
  3  where tablespace_name = 'USERS';

FILE_NAME                                                      MB
------------------------------------------------------ ----------
E:\ORADATA\NT817\USERS01.DBF                                    5

[Fiza15]

GREAT JEFF GOOD JOB WONDERFUL....YOU ARE THE BEST