Home » RDBMS Server » Server Administration » Changing objects storage settings on a Locally Managed Tablespace.
Changing objects storage settings on a Locally Managed Tablespace. [message #60784] Thu, 04 March 2004 23:32 Go to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi,

I'm using Oracle 9i R2.

All my tablespaces are locally managed tablespaces with uniform size 100M (it was like this when I came here..)!!! Of course it's far too huge! And I have some tables with just a couple of rows (some of them are still empty) but they are allocating by default 100 MB place!!!

As we can't alter the storage (extent) setting of a tablespace that is locally managed tablespace uniform size, how could I change the storage settings of a segment on a locally managed tablespace?

I tried the MOVE command with:

ALTER TABLE table_name MOVE
TABLESPACE
USERS
STORAGE
(
initial 1M
next 1M
);

But the storage settings remain the same: still have NEXT 100M!! :(

AND


ALTER
TABLE table_name
STORAGE
(
initial 1M
next 1M
);

But I receive an ORRA error message: ORA-02203: INITIAL storage options not allowed.

AND

ALTER TABLE table_name
STORAGE
(
next 100M
);

But I receive an ORRA error message: ORA-25150: ALTERING of extent parameters not permitted.

Thank you very much for your help!

Regards,

Patrick Tahiri.
Re: Changing objects storage settings on a Locally Managed Tablespace. [message #60790 is a reply to message #60784] Fri, 05 March 2004 09:55 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Patrick,
I know you will be disappointed, but all the 3 cases above show expected behaviour.

For eg)
-- consider a locally managed tablespace with uniform extent sizes of 1M

SQL> select * from dba_tablespaces where tablespace_name='LOCAL_UNIFORM';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- --- ----------
ALLOCATIO PLU SEGMEN
--------- --- ------
LOCAL_UNIFORM                        8192        [b]1048576[/b]     1048576           1
 2147483645            0    [b]1048576[/b] ONLINE    PERMANENT LOGGING   NO  LOCAL
UNIFORM   NO  MANUAL

-- as you see, the minimum extent length is also set at 1M. You cant alter it for locally managed tablespace

SQL> alter tablespace LOCAL_UNIFORM minimum extent 64k;
alter tablespace LOCAL_UNIFORM minimum extent 64k
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy

--Lets create a table with 512K initial and next

SQL> create table t(x int) storage(initial 512k next 512k) tablespace local_uniform;

Table created.

SQL> analyze table t compute statistics;

Table analyzed.

-- but its allocated 1M which is the uniform extent size

SQL> select EXTENT_ID,BYTES from user_extents where segment_name='T';

 EXTENT_ID      BYTES
---------- ----------
         0    1048576

SQL> select table_name,initial_extent,next_extent,blocks from user_tables where table_name='T';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT     BLOCKS
------------------------------ -------------- ----------- ----------
T                                      524288     1048576          0

-- although the initial_extent shows as 512K

-- lets try altering the storage parameters

SQL> alter table t move tablespace local_uniform storage(initial 64k next 64k);

Table altered.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select table_name,initial_extent,next_extent,blocks from user_tables where table_name='T';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT     BLOCKS
------------------------------ -------------- ----------- ----------
T                                       65536     1048576          0

SQL> select EXTENT_ID,BYTES from user_extents where segment_name='T';

 EXTENT_ID      BYTES
---------- ----------
         0    1048576

-- no effect

Using [url=http://www.orafaq.com/forum/t/21684/0/]Deallocate space[/url] , you can decrease the initial_extent and release space, but not below the uniform extent size of the locally managed tablespace. 

For such small objects, you will need to create another locally managed tablespace with the appropriate uniform extent size( say 1m ) ,so as not to waste space and then move those small tables.

Hope this helps
Thiru

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Re: Changing objects storage settings on a Locally Managed Tablespace. [message #60803 is a reply to message #60790] Sun, 07 March 2004 21:47 Go to previous message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Thank you for your confirmation and tips.

Regards,

Patrick Tahiri.
Previous Topic: connection to support server
Next Topic: distributed_lock_timeout
Goto Forum:
  


Current Time: Tue Jan 07 23:05:13 CST 2025