Changing objects storage settings on a Locally Managed Tablespace. [message #60784] |
Thu, 04 March 2004 23:32 |
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 |
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
|
|
|
|