storage parameters in create tabelspace

Dear all,

An index tablespace, holding several bitmap indexes, has three datafiles. One datafile was extended to be very big (12G) by a mistake which uses this tablespace as a query user's temporary tablespace. These indexes are of only 500M bytes as a whole, while the tablespace occupies 14G bytes (12G+2G+2G). I decided to drop the tablespace, and re-create all indexes in it. I will make these datafiles 512M initially and extendable to 1024M for each of them, the SQL is as:

CREATE TABLESPACE "TS_IX_ORDER" NOLOGGING DATAFILE
'D:\TWDM\DATFILE\TSORDERIDX1.ora' SIZE 512M REUSE AUTOEXTEND ON NEXT
512K MAXSIZE 1024M,
'D:\TWDM\DATFILE\TSORDERIDX2.ora' SIZE 512M REUSE AUTOEXTEND ON NEXT
512K MAXSIZE 1024M,
'D:\TWDM\DATFILE\TSORDERIDX3.ora' SIZE 512M REUSE AUTOEXTEND ON NEXT
512K MAXSIZE 1024M
DEFAULT STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 1 MAXEXTENTS 20480 PCTINCREASE 0 ) I am not sure about the relationship between AUTOEXTEND clause and the DEFAULT STORAGE clause, the former means this datafile can extend in 512K (NEXT) steps, up to 1024M as total datafile size, while the latter means any object without its own STORAGE clause will allocate its extents in 512K (NEXT) steps, up to 20480 (MAXEXTENS) as total tablespace size. As I observed, any objects in the tablespace will span three datafiles evenly. Can anyone tell me how you make decisions about these parameters? Thanks in advance.

Dino Received on Sun Aug 26 2001 - 02:32:03 CDT