Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Convert to Locally-Managed Tablespaces

RE: Convert to Locally-Managed Tablespaces

From: <Stephen.Lee_at_DTAG.Com>
Date: Tue, 06 Jan 2004 07:09:37 -0800
Message-ID: <>

An additional consideration: If you convert the tablespaces in place with dbms_space_admin, check the default storage for initial and next extent. When you do the conversion with dbms_space_admin, a space header is created and the extent allocation for the space header will be whatever the default extent allocation is.

You might also have to change how you monitor the free space in your locally managed tablespaces. For example, we used to monitor for the ability to add additional extents for whatever the largest next extent size for all the tables in the tablespace. Monitoring would e-mail for inability to accommodate two additional extents and send out a page for inability to accommodate one additional extent. We had to change this some with local management using uniform extents. With uniform extents, it is likely that you will be using smaller extents. So if you don't trust the users enough to allow auto-extend, then your space monitoring must be enhanced to take into consideration smaller uniform extents.

The dbms_space_admin conversion takes only a few seconds even on 50 Gig tablespaces. And if you don't like what you see, you can convert the tablespace back to dictionary managed; then back to local; then back to dictionary .... Oh this is fun!

Please see the official ORACLE-L FAQ:

Author: <Stephen.Lee_at_DTAG.Com
  INET: Stephen.Lee_at_DTAG.Com

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 06 2004 - 09:09:37 CST

Original text of this message