Re: Creating Tablespace with multiple Datafiles.

You say a DBA created this script?

Crikey. It's one of the worst pieces of handiwork I've seen in a while.

"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

The datafile clause takes a comma-separated list of datafiles. There is only one datafile clause.

Therefore it should read

DATAFILE 'X', 'Y' ...and not DATAFILE 'X', DATAFILE 'Y'

> AUTOEXTEND ON
Uh huh. Bad for performance, but good for the lazy DBA. Given what follows, why am I not surprised??

>NEXT 5120K MAXSIZE 500M
> MINIMUM EXTENT 512K
> DEFAULT STORAGE ( INITIAL 8M NEXT 1M MINEXTENTS 1 MAXEXTENTS 200
> OPTIMAL 15M);
>

Your DBA has clearly gone stark staring bonkers, or s/he knows nothing to begin with. Tablespaces should always contain similarly-sized extents. Not only that, but Oracle documentation is chock full of statements to the effect that rollback segments *specifically* ought to use identically-sized extents.

This bastardization of a default storage clause will mean your rollback segments have an initial extent of 8M and then obtain next extents of 1M. That's odd-sized extents, and is utterly incompetent.

If you're going to have 8M extents, then the NEXT clause should say 8M too. And if that's the case, there's little point in specifying a 512K minimum extent... that ought to be 8M as well.

Given that this is a rollback segment (at least, that's what I'm assuming the name 'RBSP' means) tablespace, then it is utterly daft to specify a minextents of 1, since rollback segments HAVE to have at least 2 extents. Oracle will ignore the cock-up and create the rollback segments anyway, but it's poor to set completely the wrong setting in stone like this.

Optimal is not usually a good idea for performance reasons, but since your DBA clearly doesn't have a clue what s/he is doing, I suppose there's no harm in completely buggering the thing up by setting it.

Regards
HJR
> 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 - 02:22:34 CST