Re: decreasing initial extent

"Peter van Rijn" <p.vanrijnREMOVE_at_THISzhew.nl> wrote in message news:v28247a4eclb76_at_corp.supernews.com...
> Niall, this will also work if the table is not empty:
>
> SQL> create table a(a1 number)
> storage(initial 1m);
>
> Table created.
>
> SQL>begin
> 2 for i in 1..1000 loop
> 3 insert into a values(i);
> 4 end loop;
> 5 end;
> /
>
> PL/SQL procedure successfully completed.
>
> SQL> select table_name, initial_extent from user_tables where
> table_name='A';
>
> TABLE_NAME INITIAL_EXTENT
> ------------------------------ --------------
> A 1048576
>
>
> SQL> alter table a move
> storage(initial 500k);
>
> Table altered.
>
> SQL>
> select table_name, initial_extent from user_tables where table_name='A';
>
> TABLE_NAME INITIAL_EXTENT
> ------------------------------ --------------
> A 516096
>
>
> regards,
> Peter
>

What I was on about was the compress parameter of the export command.

COMPRESS
Default: y

Specifies how Export and Import manage the initial extent for table data.

The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon Import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.

So if you have 2gb of data in the table compress should specify that import gets a create table with an initial extent of 2gb. Received on Tue Jan 14 2003 - 08:01:03 CST