Re: Creating Tablespace with multiple Datafiles.

Hi Jigar,

The other posters have pretty well covered it and in a more polite way than it could have been !!

A few little things to add:

Assuming this is a tablespace for RBS, Oracle does allow the 1st extent to be sized differently from all others (which have to be the same) as the 1st extent is somewhat special as it's the only one that can't be deallocated. However, having said all that, you would have to give me a pretty good reason why it should be sized differently, a reason that I've yet to hear. INITIAL and NEXT really should be the same.

Minextents of 1 is plain wrong as RBS must have at least 2 extents. However, if it only has two extents, then it's quite likely it will need to grow as the odds that one teeny weeny transaction is still hanging around in half of itself is reasonable high (and you only have the other half to fill up before it's decision time again). You now have 3 extents. Who knows, same thing is likely to happen again as 1/3 is still a hefty chunk of the RBS and it grows to 4 extents. It might be 4 or 5 or 6 or whatever until the RBS stabilises in size (the extent size, volume of changes, length of transactions, etc all contribute to this). Therefore a number greater than 2 is generally recommended. I know Howard Rogers likes 6, I like 10, Oracle Education like 20, my gran likes 69, but 1 it most definitely is not.

There's been a fair bit of debate in the past whether or not you should use an OPTIMAL size for RBS. I won't go there. But if you want to go down the optimal path, then it makes sense to size your RBS so that they are of *optimal* size to begin with. Therefore if we agree that 1 is not a good default minextents figure and you decide to go for 6 extents of 8M, then this figure (calculator is out, ah ha, 48M) should be your optimal/starting figure. Subsequent tuning of your RBS will tell you how appropriate this is. Having a RBS dynamically growing to it's optimal value or having an RBS that never reaches it's optimal value are both questionable scenarios.

You haven't mentioned your DB version but Locally Managed Tablespaces should be the go. Get your DBA to check them out, they could make his life a little easier.

I bet you're glad you asked the question :)

Cheers

Richard

"Jigar Parsana" <jigarparsana_at_yahoo.com> wrote in message news:a079a49c.0212090813.4e845e2d_at_posting.google.com...
> Hi ,
>
> I am trying to create a tablespace with multiple data files.here
> is the statement i am using
>
> CREATE TABLESPACE RBSP
> DATAFILE 'D:\oracle\oradata\epidb\rbsp01.dbf' SIZE 500M,
> DATAFILE 'D:\oracle\oradata\epidb\rbsp02.dbf' SIZE 100M
> AUTOEXTEND ON NEXT 5120K MAXSIZE 500M
> MINIMUM EXTENT 512K
> DEFAULT STORAGE ( INITIAL 8M NEXT 1M MINEXTENTS 1 MAXEXTENTS 200
> OPTIMAL 15M);
>
> this statement is actully a part of the database creation script that
> was prepared by our dba. apparantly everytime i try to execute this
> script the above statement gives an error saying "invalid file name"
> for the second datafile. I dont know what is going wrong here....any
> help will be highly appreciated.
>
> thanx in advance,
>
> jigar
Received on Tue Dec 10 2002 - 06:37:24 CST