Is it good practise?

[sujosh]

I am doing an initial load on a db which has several tables with 2 million records.

1. Is it good practise to have to automatically extend datafile when full?

2. To increment the datafile by 1000kb?

3. The maximum size to be unlimited or have a value?

Thanks

 

[Bastien]

1. yes

2. depends on the size of the rows (if you have a small row size, then 1Mg extents may be enough, but if you have a larger rows size or high transaction counts(lots of new data) then 1Mg may not be enough

3. Auto-extend unlimited...remeber to keep an eye on the growth and not exceed OS file limits...

Bastien

Cat, the other other white meat

 

[sujosh]

Thanks Bastien! I would like to know a little bit more about question 2.

I have one table with 110 columns and another with 320 columns. This is a conversion project and I inherited this table struc. Unfortunately, I do not have control to normalize it.

What kind of extents should I have for these kind of tables. The table with 110 columns has more than 2 million records and the one with 320 cols has more than 1 million records.

Thanks

 

[SantaMufasa]

Sujosh,

Actually, with current Oracle technology, you do not even worry about extent sizes. You should be using "Locally Managed Tablespaces" (LMT). Locally managed tablespaces automatically allocate extents of appropriate size. LMTs are also MUCH more efficient with vastly improved performance. Here is code to create a Locally Managed Tablespace (with autoextending datafile):

CREATE TABLESPACE <ts_name> DATAFILE '<filename>' SIZE <n M>
AUTOEXTEND ON NEXT <n M> MAXSIZE <n M>
EXTEND MANAGEMENT LOCAL AUTOALLOCATE;

If/when you add datafiles to this tablespace, they automatically are subject to the same extent allocation as the original data file.

If you wish to relocate your existing segments (i.e, tables and indexes) in a LMT, you can do so quickly and easily. Just follow the "MOVE" coding in the last post in the 16Jun2004 thread, "Moving Data" (thread186-862230).

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:11 (19Jul04) UTC (aka "GMT" and "Zulu"), 14:11 (19Jul04) Mountain Time)

 

[SantaMufasa]

Sujosh,

Please forgive my chubbly little fingers that mistakenly typed "...extend management...", which should have read "...extent management...".

Sorry,

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:15 (19Jul04) UTC (aka "GMT" and "Zulu"), 14:15 (19Jul04) Mountain Time)

 

[sujosh]

Mufasa,

<<Actually, with current Oracle technology, you do not even worry about extent sizes.>>


Can I do this on 9i? Please verify. Also when I use enterprise manager to change the maximum size in storage tab it automatically reverts back to 32767 MB. Why does that happen?


<<If you wish to relocate your existing segments (i.e, tables and indexes) in a LMT>>
Could you please tell me what is the reason to do this?

Thanks much

 

[SantaMufasa]

Sujosh,

<<Can I do this on 9i? Please verify.>>

Locally Managed Tablespaces have been available in all versions since Oracle 8i.

<<...when I use enterprise manager to change the maximum size in storage tab it automatically reverts back to 32767 MB. Why does that happen?>>

32GB is a VERY LARGE maximum for a tablespace datafile. 32GB is probably the nominal maximum for Enterprise Manager. Some operating systems have trouble if the datafile exceeds 2GB. I highly recommend your using MAXSIZE 2000M per file. There is absolutely no penalty in have multiple 2GB-maximum datafiles for a tablespace.

<<Could you please tell me what is the reason to do this?>>

Given: You have a tablespace that is not LMT.
Given: Once you create a tablespace, you cannot change it from non-LMT to LMT.
Given: You want your segments (tables and indexes) to reside in a LMT.

Resolution: You must create a new LMT tablespace, move all the objects out of the non-LMT tablespace into the LMT tablespace; then you can drop the empty non-LMT tablespace.

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:52 (19Jul04) UTC (aka "GMT" and "Zulu"), 15:52 (19Jul04) Mountain Time)

 

[sujosh]

Thanks you very much Mufasa! You have been very helpful