Printable View
Hi Friends,
Can Someone tell me what will be the implications of Resizing the datafiles if i decrease the existing size of the datafile ? I repeat I want to DECREASE the file size.
Have told by some one Oracle does not recommand to decrease the datafile size. Can someone show me link or docs where written like this ?
Thanks in advance.
UpeshP
You cannot decrease the size of an existing datafile..only option u have is to re-create a new datafile with the new size...
For doc...check oracle documentation and look for managing datafiles in the Oracle Administration guide...
regards
anandkl
Your database crashed with the following errors after you issued the command:
> ALTER DATABASE DATAFILE RESIZE...
Datafile is ONLINE, and in a tablespace that is being used. In this
case the rollback segment tablespace.
ORA-00603: "ORACLE server session terminated by fatal error"
Cause: An ORACLE server session is in an unrecoverable state.
Action: Login to ORACLE again so a new server session will be
created
ORA-00376: "file %s cannot be read at this time"
Cause: attempting to read from a file that is not readable.
Most likely the file is offline.
Action: Check the state of the file. Bring it online
ORA-01110: datafile:
Cause: This message reports the filename involved with other messages.
Action: See the associated messages for a description of the problem.
Problem Explanation:
====================
When using command ALTER DATABASE DATAFILE RESIZE..., your instance should
have the database mounted, open, or closed, and the files involved should
NOT be in use.
With rollback segments being used in this ONLINE datafile, attempts to resize
it could cause a crash and even corruption.
This is not true.Quote:
Originally posted by anandkl
You cannot decrease the size of an existing datafile..only option u have is to re-create a new datafile with the new size...
You can decrease the size of a data file as long as you don't try to shrink it past the point where it contains data.
Let me elaborate on this. as marist89 has said you can decrease the datafile size. Also I do not recall whether oracle 'Recommends' not to reduce datafile size. You can shrink datafile till the point data has spread in the file.