Auto extend

[st2000]

Hi :

I have a tablespace that is made autoextend off and I want to make it ON.. Is that OK and possible on a system where the tablespace is constantly used.. What other choices do I have ??

Secondly, the application I am working on is Siebel.. And we are doing some dataloads. What I dont understand is, the datafile size is 1.3GB and the index file is already 2GB.. The indexes are for the data and how can the indexes be occupying more space than the entire data..

THirdly, I want to re-organize the indexes and I remember that it is best to drop and create indexes rather than re-organize them. What is the best method here, if I want to take a backup of indexes alone..

Thanks, ST

[marist89]

1. sure. ALTER DATABASE DATAFILE 'xyz.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 2000M;
2. Your application may have multiple indexes on one table. This is not uncommon.
3. Backup of indexes alone? I would probably export the user with rows=n and then create an indexfile. Personally, I would rather ALTER INDEX xyz REBUILD ONLINE than drop and re-create, but that's just me.

[st2000]

Thanks Jeff.. for very quick responses..

On item1 .. I have 3 datafiles for the siebel_index TS. The 1st and 2nd datafiles are full and the 3rd one is created now with a 1GB. With what you are saying I should be altering the 3rd one only. Isn't it??
Alter database datafile '/x/y/z' autoextend on next 1M maxsize 1024M ;

On item3 .. If I export the entire schema with rows=n, then I guess I can restore back the indexes, if I drop and recreate the index tablespaces right? This time I can have 1 datafile of 3GB rather than 3 datafiles of 1GB each for Siebel_index.. and the datafile can be autoextend ON, instead of present situation where 2 df's will be OFF and the 3rd one ON. Isnt it??

Thanks, ST

[rcaballe]

Hi.
I think you should consider a default datafile size for you, mine is 1G, I don't want to work with file larger than that.
What's yours? 1G, 2G, 4G? Some OS or commands have problems with large datafile (Oracle don't)
Once you have defined the size you have 2 choices:
1.-Add every df as needed with max size or with medium size but autoextend on WITH max size defined.
2.-Guess your tablespace max size for...2 years divide by the max size and create some dfs e.g.:16Gb max tbs, 2Gb max df = 8 datafiles of...256Mb with autoextend on 128Mb and max size of 2Gb
Just 2 cents.

Siebel has a lot of indexes on each table, I implemented on Locally managed tablespaces with automatic storage.