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

On Fri, 15 Apr 2011 09:48:45 -0700, Dean wrote:

And how would you do it with a closed instance?



--
http://mgogala.byethost5.com

On 15-04-2011 18:48, Dean wrote:
http://download.oracle.com/docs/cd/B...3.htm#i1106377

--
Luuk

On Apr 15, 9:48*am, Dean <dh6... (AT) gmail (DOT) com> wrote:
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/...nusedSpace.php

You should of course have a test database where you can try things.

jg
--
@home.com is bogus.
"I'm not that crazy." http://www.signonsandiego.com/news/2...ney-talks-u-t/

On Apr 15, 12:48*pm, Dean <dh6... (AT) gmail (DOT) com> wrote:

Dean:

# Hi, I am a newbie administrator.

Hey Dean I am an old grumpy DBA.

Sure you can "probably" safely grow and resize datafiles while the
database is up especially with more recent versions of Oracle.


# If not, what would be the proper procedures to take before executing
the alter sqls?

Do you have backups in place? Do you have a tried and proven recovery
strategy?

While you are learning and trying things first point is to do your
learning and experimenting on a test system.

Mladen Gogala wrote,on my timestamp of 16/04/2011 3:24 AM:
LOL! Indeed...

On Apr 15, 7:04*pm, Noons <wizofo... (AT) yahoo (DOT) com.au> wrote:
Concatenate unformatted Oracle blocks to the end of the file? Do a
tail youremptyattheendfile.dbf|od -c to see what's there...

10017720 \0 : 001 : 242 \0 \0 001 \0 002 t \0 \0 \0 \0
\0
10017740 \0 001 004 002 323 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10017760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10037720 \0 : 001 : 242 \0 \0 001 \0 002 u \0 \0 \0 \0
\0
10037740 \0 001 004 002 322 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10037760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10057720 \0 : 001 : 242 \0 \0 001 \0 002 v \0 \0 \0 \0
\0
10057740 \0 001 004 002 321 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10057760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10077720 \0 : 001 : 242 \0 \0 001 \0 002 w \0 \0 \0 \0
\0
10077740 \0 001 004 002 320 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10077760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10117720 \0 : 001 : 242 \0 \0 001 \0 002 x \0 \0 \0 \0
\0
10117740 \0 001 004 002 337 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10117760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10137720 \0 : 001 : 242 \0 \0 001 \0 002 y \0 \0 \0 \0
\0
10137740 \0 001 004 002 336 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10137760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10157720 \0 : 001 : 242 \0 \0 001 \0 002 z \0 \0 \0 \0
\0
10157740 \0 001 004 002 335 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10157760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10177720 \0 : 001 : 242 \0 \0 001 \0 002 { \0 \0 \0 \0
\0
10177740 \0 001 004 002 334 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10177760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10217720 \0 : 001 : 242 \0 \0 001 \0 002 | \0 \0 \0 \0
\0
10217740 \0 001 004 002 333 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10217760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10237720 \0 : 001 : 242 \0 \0 001 \0 002 } \0 \0 \0 \0
\0
10237740 \0 001 004 002 332 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10237760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10257720 \0 : 001 : 242 \0 \0 001 \0 002 ~ \0 \0 \0 \0
\0
10257740 \0 001 004 002 331 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10257760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10277720 \0 : 001 : 242 \0 \0 001 \0 002 177 \0 \0 \0 \0
\0
10277740 \0 001 004 002 330 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10277760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10317720 \0 : 001 \0 242 \0 \0 001 \0 002 200 \0 \0 \0 \0
\0
10317740 \0 001 005 002 & \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
10317760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
\0
*
10337720 \0 \0 001
10337723

(Note to newbies: Don't concatenate unformatted Oracle blocks to the
end of a data file. It's just interesting to see things with od.)

jg
--
@home.com is bogus.
http://www.basementcoders.com/transc...ranscript.html

On Apr 15, 11:33*am, joel garry <joel-ga... (AT) home (DOT) com> wrote:
Thanks Joel. Reclaiming Unused Space is very useful link.
Dean

On Apr 15, 4:30*pm, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:
Thanks John for the advices.
I had tried to resize datafile on a test instance before I posted the
question.
It seemed to work fine to me because there was no error.
But as a newbie, I was just too nervous to conclude it works
Because I didn't know how to verify it thoroughly and have the
confidence to say it works.
Dean