DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Resize Datafile

  1. #1
    Join Date
    Jun 2002
    Posts
    65
    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter

  3. #3
    Join Date
    Jun 2002
    Posts
    65
    Thanks Jeff what if i want to add 50m in datafile which was initially 100m at the time of creation...Thanks

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    It adds it to the end of the file.
    Jeff Hunter

  5. #5
    Join Date
    Jun 2002
    Posts
    65
    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

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  7. #7
    Join Date
    Jun 2002
    Posts
    65
    Yes Pando correct i want to add 50m...Thanks for reply

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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:
    Code:
    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
    Jeff Hunter

  9. #9
    Join Date
    Jun 2002
    Posts
    65
    GREAT JEFF GOOD JOB WONDERFUL....YOU ARE THE BEST

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width