Re: Can datafile be resized while instance is open?
From: Dean <dh6864_at_gmail.com>
Date: Mon, 25 Apr 2011 14:39:44 -0700 (PDT)
Message-ID: <e90defc6-be9c-4a9b-a3fc-e09ace58c4ed_at_d19g2000prh.googlegroups.com>
On Apr 15, 11:33 am, joel garry <joel-ga..._at_home.com> wrote:
> On Apr 15, 9:48 am, Dean <dh6..._at_gmail.com> wrote:
>
> > Hi, I am a newbie administrator. Is it safe to alter datafile size
> > while database is open?
> > I need to free up disk space of our database. I found scripts online
> > to calculate high water mark of extents and resize the datafile
> > accordingly.
> > What I am not sure if I could execute sqls like "alter database
> > datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DB05\TL30.dbf' resize
> > 108m;" while database is online and open?
> > If not, what would be the proper procedures to take before executing
> > the alter sqls?
>
> > Thanks,
> > Dean
>
> Decades ago, it was a dangerous operation, but now it is solid as a
> rock. Oracle will give you an error if it finds data above where you
> are trying to resize it to.
>
> Be wary of scripts from online, they may be out of date or wrong,
> examine them carefully and understand what they do. There are
> multiple high water marks these days.
>
> dbconsole has a tablespace map option, it lets you look at the
> tablespace with colored segments, and you can find out the segment
> name by hovering the mouse over it. Compare this to what the scripts
> tell you. You have to look carefully to see where each datafile ends
> in this display. Seehttp://www.oracle-base.com/articles/misc/ReclaimingUnusedSpace.php
>
> You should of course have a test database where you can try things.
>
> jg
> --
> _at_home.com is bogus.
> "I'm not that crazy."http://www.signonsandiego.com/news/2011/apr/15/q-infamous-foreclosure...
Date: Mon, 25 Apr 2011 14:39:44 -0700 (PDT)
Message-ID: <e90defc6-be9c-4a9b-a3fc-e09ace58c4ed_at_d19g2000prh.googlegroups.com>
On Apr 15, 11:33 am, joel garry <joel-ga..._at_home.com> wrote:
> On Apr 15, 9:48 am, Dean <dh6..._at_gmail.com> wrote:
>
> > Hi, I am a newbie administrator. Is it safe to alter datafile size
> > while database is open?
> > I need to free up disk space of our database. I found scripts online
> > to calculate high water mark of extents and resize the datafile
> > accordingly.
> > What I am not sure if I could execute sqls like "alter database
> > datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DB05\TL30.dbf' resize
> > 108m;" while database is online and open?
> > If not, what would be the proper procedures to take before executing
> > the alter sqls?
>
> > Thanks,
> > Dean
>
> Decades ago, it was a dangerous operation, but now it is solid as a
> rock. Oracle will give you an error if it finds data above where you
> are trying to resize it to.
>
> Be wary of scripts from online, they may be out of date or wrong,
> examine them carefully and understand what they do. There are
> multiple high water marks these days.
>
> dbconsole has a tablespace map option, it lets you look at the
> tablespace with colored segments, and you can find out the segment
> name by hovering the mouse over it. Compare this to what the scripts
> tell you. You have to look carefully to see where each datafile ends
> in this display. Seehttp://www.oracle-base.com/articles/misc/ReclaimingUnusedSpace.php
>
> You should of course have a test database where you can try things.
>
> jg
> --
> _at_home.com is bogus.
> "I'm not that crazy."http://www.signonsandiego.com/news/2011/apr/15/q-infamous-foreclosure...
Thanks Joel. Reclaiming Unused Space is very useful link. Dean Received on Mon Apr 25 2011 - 16:39:44 CDT