Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Resize datafile

Re: Resize datafile

From: Howard Rogers <Howard.Rogers_at_oracle.com>
Date: Wed, 17 Apr 2002 11:00:55 +1000
Message-ID: <DN3v8.25$tV6.225@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:00:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US