Fooling Oracle re: extent allocation

[Jared]

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

[Svend Jensen]

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

[Jared]

Svend Jensen <Sv************@it.dkwrote in message news:<3f***********************@dread16.news.tele. dk>...

Jared wrote:
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,

Thanks, Svend, I will test that today.