TS from Locally managed to Dictionary

Hello DBAs :

Has anyone tried to alter a tablespace from a locally managed to dictionary managed... I have created the TS 's locally with uniform extents (Since I can't have variation in extent sizes).. Since I want only first extent to be 8k and the rest (next to unlimited) as 100K or so...
Reason : The application is Siebel and there are lots of objects that do not need 100k and lots of space is wasted. About 3500 objects (Data and Indexes)...

Thanks ST2000

Yes, I have done it on 8.1.7.2. Use the dbms_space_admin package.

Thanks a lot Jeff :

Looked through docs in technet and metalink and I can do the same.. One Qn I have is, once my locally managed TS becomes Dictionary managed, I can manipulate the extents.. But all the docs seem to talk or show about the NEXT parameter in the TS as manipulated.. and my goal is purely to see if I can manipulate the INITIAL extent.. If I cannot do that I don't want to do this exercise.. As it is there are 3200 objects (1400 tables and 1800 indexes) and most are empty.. and I can see much space wasted.

How risky is this, or what is a safer or alternative way.. Should I atleast take an export of the entire schema.. or simply not worry about it when i execute the package..

Thank, ST2000

You don't HAVE to change back to DMT to solve this problem.

you could :

1. create 2 or 3 tablespaces with a different uniform extent size and house each object into the appropriately sized tablespace.

2.Create a LMT with Automatic Extent Allocation instead of Uniform Extent Size. Oracle will deal with assigning appropriately sized extents for these objects and will allocate larger extents to the objects as they grow.

The sizes it allocates are initialy 64k, until the object reaches 1mb in size, the extent will then change to 1Mb, when the object reaches 64mb then extent = 8mb, until it reaches 1Gb then extent = 64Mb.

A lot of people don't actually use the Automatic Extent Allocation because I guess it has something to do with space being wasted.

Can anyone explain any disadvantages to using Automatic Extent Allocation ?

Thanks Suresh.. But I cannot do an local with autoallocate of 8k in initial extent size.. Autoallocate starts at 64K.. You are right about space.. My db is fully furnished and I want to do this now since I realised lots of space is wasted as 85% of the tables have 0 rows... That is the reason, why I want to convert. Would any Sr. DBA's explain the process please..

Thanks, ST2000

Hello :

I converted the datafile/TS using space admin package from Locally managed to Dictionary managed and so was able to alter the extent size (Initial 16K).. What I still do not understand is, the dba_tablespaces view shows my initial extent as 16k, as done, but any other view say dba_segments and result of analyze show the initial extent as the old size only which was while created... With this scenario, my db size has not changed at all though I have changed my initial extent sizes and 85% of tables in schema has 0 rows..

Can anyone explain? Is it because extent allocation has taken place my db size does not change? If so, how can I decrase the size.. Is my choice, only to do an export of the user, drop the user and recreate or will truncate alone might solve this.. Infact to support the drop user and recreate, I just created a new table and it is perfect (initial 16k) as desired..


Thanks ST2000