Posts Tagged ‘table’

Change the initial extent of a table

October 4, 2011 3 comments

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

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, or export/drop objects/import again the user’s data.

Categories: oracle Tags: , , , , ,

Partition an existing table using DBMS_REDEFINITION

January 14, 2011 Leave a comment

Here you can find a great post about DBMS_REDEFINITION