You didn't say what version of Oracle you are using. But since you are
going to rebuild all the indexes and create a new tablespace anyway, you
might take a look at locally managed tablespaces (with autoallocate or fixed
as appropriate) if you are using 8i.
"Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message
news:iv7hot09mo9lggee7kons9p7nb97oam88c_at_4ax.com...
> 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 - 09:55:04 CDT
