If it's a full=y, then the users should be created for you (they're amongst
the first instructions in the dump file). If you get import errors, though,
try it with the users created manually, and have another go (though you
really shouldn't need to).
Regards
HJR
"Tom Brown" <tom_at_eazyriders.com> wrote in message
news:v_3v8.14181$_d7.820247_at_news-binary.blueyonder.co.uk...
> 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 - 21:28:01 CDT
