growing tablespace

Printable View

12-17-2002, 07:22 PM
a128

growing tablespace

Hi,

If I have a tablespace containing two datafiles: data1.dbf, data2.dbf, how can I ensure both files grow evenly when new tables are created and data are inserted?

If I started out with only one datafile: data1.dbf and later decided to spread out the data to newly created data2.dbf, how do I ensure that?

Thanks,
12-17-2002, 09:01 PM
grjohnson

Call me Sherlock, but why not only have one datafile first... and then another when it's required.
12-17-2002, 09:13 PM
stecal

A woman gets onto a bus with her baby.

The bus driver says, "That's the ugliest baby that I've ever seen. Ugh!"

The woman goes to the rear of the bus and sits down, fuming. She says to a man next to her, "The driver just insulted me!"

The man says, "You go right up there and tell him off. Go ahead, I'll hold your monkey for you."
12-17-2002, 10:09 PM
a128

grjohnson,

> and then another when it's required.

You basically point out my question: when is it required? If I set it to grow "unlimited", am I pretty safe?

I currently have only one file for the tablespace. However, it now grows to 6GB and I'd like to have the second datafile. Honestly, I am not sure if it's necessary because I have 0+1 RAID. I am just curious how Oracle and what settings determine which table/data goes to which datafile, if I don't explicitly speicify it?

Is it still preferred to have several datafiles for a single tablespace even if it's 0+1?

Thanks,
12-18-2002, 12:03 AM
grjohnson

If I worked with a DBA who set the datafile size to UNLIMITED AND AUTOEXTEND ON, I'd kick his arse from one side of the room to the other. That is STUPID IMO! Set AUTOEXTEND ON BUT ALSOset it to a reasonable MAXSIZE (Mine is 2GB for < 8i, 4GB >= 8i). BUT, it also depends on DB size and filesystems. When do you add another? When it hit's your critical limit... maybe 80 - 90% of capacity... whatever. This is why we monitor the DB. Also, the "A" in DBA does stand for Adminstration.
12-18-2002, 05:32 AM
julian

Re: growing tablespace

Quote:

Originally posted by a128
Hi,

If I have a tablespace containing two datafiles: data1.dbf, data2.dbf, how can I ensure both files grow evenly when new tables are created and data are inserted?

If I started out with only one datafile: data1.dbf and later decided to spread out the data to newly created data2.dbf, how do I ensure that?

Thanks,

The extents are allocated to multiple datafiles for a tablespace in a round-robin fashion. So at some point all files are almost equally large.