Home > oracle > Change the initial extent of a table

Change the initial extent of a table

I have an oracle database user that I have imported from production environment to a developing environment.

The overall size of the imported user is about 6GB, and I asked the developers to flush unneeded data. Now I have about 250MB of data, but when I want to resize the tablespace i run into the HWM problem.

When I check the storage details of the table, I see that the initial extent of the tables is high enough to get the initial data import. But as the data have been deleted, I want to reduce this initial extent of the tables.

As my segment size is 256KB, I check for the tables with an initial extent bigger than 256K

SQL > select TABLE_NAME, INITIAL_EXTENT, NEXT_EXTENT from USER_TABLES
where initial_extent > 262144;

Now, if I try to change the initial extent of a table, I get this error:

SQL> alter table DMSD_CDAD1 storage (initial 256K);
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed

To solve it, I just moved the table in the same tablespace and this way I can modify the storage clause:

SQL> alter table DMSD_CDAD1 <strong>move</strong> storage (initial 256K);

Now rebuild all the indexes, changing also the initial extent size:

SQL> select 'alter index ' || index_name ||  ' rebuild storage (initial 256K);' from user_indexes;

And now I have a lot of gaps of free space. To remove this gaps, you can use the remap tablespace process (as described in https://myotragusbalearicus.wordpress.com/2011/06/22/oracle-9i-reclaim-lob-free-space-tablespace-map/), or export/drop objects/import again the user’s data.

Advertisements
Categories: oracle Tags: , , , , ,
  1. POLTRE
    June 6, 2014 at 12:07

    I have a segment called PIFR which has 212 extents. Is there any form to shrink this extents and convert it to 1 extent?

    I know that a solution to prevent that segment grows up is to resize the next extend, but i would like to know if is there any form to shrink extents?

    Could someone tell me how to do it?

    Thanks

    Like

    • June 28, 2014 at 17:03

      You can try to CTAS (create table PIFR_new as select * from PIFR) with an initial extent big enough to allocate all your extents (using the storage clause). Then just rename the table PIFR_new to PIFR (and dont forget to create all the related constraints and indexes).

      Like

  2. Nikhil
    June 21, 2012 at 17:44

    Very helpful, thanks for posting it.

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: