Re: Resize datafile

Thanks for the feedback. If my current plan does not work i'll try it - What i'm doing as we speak is creating a new instance with much cleaner datafiles which do not auto extend etc. I'm going to try and import the full export of the other instance into this one to see if that works. I was wondering of as i did as FULL=Y as system to take the exp will this recreate the users when i do the imp or will i have to do that manually?

thanks

Tom
"Howard Rogers" <Howard.Rogers_at_oracle.com> wrote in message news:DN3v8.25$tV6.225_at_news.oracle.com...
> Sounds about the right procedure to me.
>
> Unfortunately, it might not work. If the user that you drop happens to
"own"
> a billion extents in the middle of the datafile, then a coalesce will turn
> those billion empty and free extents into a single large extent... but
that
> does nothing to help you resize the file downwards, since that operation
> will throw a wobbly the first time it bumps into data on the way down...
> and, of course, in this scenario, it's going to bump into someone else's
> data.
>
> So the user that you drop needs to be the one that owns the last extents
in
> the tablespace, and that can be tricky to work out (but dba_extents and
> dba_segments can help you work it out).
>
> Having said all of that, you're running on 8.1.5 (bad move, by the way:
you
> should upgrade to 8.1.7 pronto). So you have the magic of the 'move'
> command at your disposal. Hence, create a new tablespace somewhere with a
> fair bit of free space, and then just sit there issuing 'alter table X
move
> tablespace newone' to relocate the tables. When enough tables have been
> removed (and again, they really need to be the ones at the end of the data
> file), resize the data file downwards, and then add in your new datafiles
to
> the original tablespace. Now sit there issuing 'move' commands to move the
> tables from tablespace 'newone' back into tablespace X. Much quicker, no
> nasty drops of users (which can have nasty side effects on others, if that
> user happens to own some procedures, triggers and so on). And no reliance
on
> export and import. The only drawback is that the moves invalidate the
> indexes on the tables which are moved each time, so at the very end of the
> process, you need to rebuild them all.... on big tables, that can be a big
> cost (but you'd be doing the same with the export/import method anyway, so
> it's not like its an *additional* cost, or a cost you wouldn't have to
bear
> anyway).
>
> Personally, I never recommend the use of autoextend. It's sloppy practice
> and has the potential (as you've discovered) to get you into precisely
this
> sort of hole.
>
> Regards
> HJR
>
>
> "Tom Brown" <tom_at_eazyriders.com> wrote in message
> news:Ti3v8.13894$_d7.814862_at_news-binary.blueyonder.co.uk...
> > Hi,
> >
> > I have a major problem. One of my datafiles has become over 4 gigs in
size
> > and this is causing major problems. We are using Solaris 7 and 8.1.5 -
> Could
> > i drop a user that has used lots of space, after an export of this user,
> > coalesce the tablespace and resize the datafile down, make this datafile
> non
> > autoextend and add another datafile or two, recreate the user and then
> > import it?
> >
> > I'm desparate now so any help would be appreciated.
> >
> > Tom
> >
> >
>
>
Received on Tue Apr 16 2002 - 20:13:01 CDT