Re: Can datafile be resized while instance is open?
Date: Fri, 15 Apr 2011 11:33:07 -0700 (PDT)
Message-ID: <7b7dd8d3-05ca-42d0-92d7-5e832e96c303_at_j9g2000prj.googlegroups.com>
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. See http://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-attorney-talks-u-t/Received on Fri Apr 15 2011 - 13:33:07 CDT