Re: Fooling Oracle re: extent allocation

Jared wrote:
> Normally when one is creating a table in a striped tablespace one can,
> with a little trouble, force extent allocation in a round-robin
> fashion. I never do things the easy way :-); what I want to do (in an
> 8.1.7.4.1 db) is create a striped tablespace and do an 'alter table
> move' and get somewhat balanced extent allocation over four datafiles;
> more important, get balanced allocation going forward.
>
> I am trying to do this with some (hash-)partitioned tables and some
> non-partitioned.
>
> I have yet to devise a reasonable way to do this. I've come up with
> some schemes - e.g., setting the striped tablespace just slightly
> larger than the table, moving it, then setting datafiles to autoextend
> - but they always have the flaw of getting round-robin allocation
> afterwards. Unfortunately this is a production, third-party
> application so a programmatic change for inserts is out of the
> question.
>
> If anyone has done this, or can point me in a useful direction for
> further reading or research, I would be grateful.
>
> TIA -
>
> Best regards,
> jh

Hi jh

You are working hard on a problem that does not exist! Oracle will automatic stripe the extents for you. If you create a tablespace locally managed uniform size and the tablespace is extended with - say - tree more datafiles, sized like the first datafile.
Then your objects in that tablespace will have extents that spawn all datafiles if they have more extents than you have datafiles. First extent will go on file 'a', next extent of the same object will go on datafile 'b' and so forth. It might not be quite what you wanted, but it is close.
This behavior is seen on windos versions of 8.1.7 - and i think linux too, but don't nail me on that.

You can test that pretty easy, create small tablespace as described, and a table (with small extents) from select * from all_objects. Insert as select from.... a couple of times to get more extents. Then check the dba_extents for your object, look for file_id and relative_fno.

Regards

/Svend Jensen Received on Mon Jul 21 2003 - 21:04:58 CEST