Re: decreasing initial extent

From: Peter van Rijn <p.vanrijnREMOVE_at_THISzhew.nl>
Date: Tue, 14 Jan 2003 13:53:08 +0100
Message-ID: <v28247a4eclb76@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

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> schreef in bericht news:3e23fca1$0$241$ed9e5944_at_reading.news.pipex.net...
> "Peter Laursen" <ptl_at_edbgruppen.dk> wrote in message
> news:3e23fa9e$0$264$edfadb0f_at_dread15.news.tele.dk...
> >
> > "Peter van Rijn" <p.vanrijnREMOVE_at_THISzhew.nl> wrote in message
> > news:v27u50s55or2f8_at_corp.supernews.com...
> > > alter table <table_name>
> > > move
> > > storage(initial <desired size>;
> > >
> > > regards,
> > > Peter

>

> This will work if the table is empty, however it looks to me (from the
> number that is given for initial extent) that you have issued exp with
> COMPRESS=Y which will try to give an initial extent into which the entire
> table fits. You don't want this especially if you have tabls with several
gb
> of data!.

>
>

> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer

>


> ******************************************

>
>

Received on Tue Jan 14 2003 - 06:53:08 CST