Re: Changing to LMT tablespaces

From: Brian Wisniewski <brian_wisniewski_at_yahoo.com>
Date: Wed, 25 Oct 2000 05:01:21 -0700 (PDT)
Message-Id: <10660.120157@fatcity.com>

Diana, there is a supplied package to do this but I heard in the base 8.1.6 release there were bugs in it which are fixed in 8.1.7. The bug was if the database was migrated from 7.3.4 but ...

I've used it in a test database just to see what it does but you don't gain all of the benefits of a locally-managed tablespace using this package. See below.

The following note expains the packages and how to use it. Note 103020.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=103020.1

dbms_space_admin.tablespace_migrate_to_local() This takes three arguments:

   o Tablespace name - The Tablespace to be Migrated. ( Mandatory ) 
   o Unit Size       - The Unit size required for space management.
   o Rfno            - The relative file number.

Even though it is locally-managed the tablespace is not subject to uniform or system extent allocation, it is still based upon the values one would create the table as (initial, next). See note 120061.1 for more info.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=120061.1

If you are looking for uniform extent allocation then you will need to do the process you described below and yes you will need to move each partition.

• Brian
  • Diana Duncan <Diana_at_fileFRENZY.com> wrote:
    > All,
    >
    > I'm planning on turning my dictionary managed tablespaces into
    > locally
    > managed tablespaces. Am I correct in assuming that I'll have to
    > perform the
    > following steps:
    >
    > 1) Create new tablespaces
    > 2) Move tables into the new tablespaces
    > a) Since most of my tables are partitioned, I'll actually have to
    > move each partition, right?
    > 3) Rebuild indices into the new tablespaces
    > 4) Drop the old tablespaces
    >
    > Or is there an easier way?
    >
    > Thanks,
    >
    > Diana Duncan
    > Database Architect
    > filefrenzy
    > diana_at_filefrenzy.com
    > 919.760.1151 <-- New number
    > --
    > Please see the official ORACLE-L FAQ: http://www.orafaq.com
    > --
    > Author: Diana Duncan
    > INET: Diana_at_fileFRENZY.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    > San Diego, California -- Public Internet access / Mailing
    > Lists
    > --------------------------------------------------------------------
    > To REMOVE yourself from this mailing list, send an E-Mail message
    > to: ListGuru_at_fatcity.com (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).
    

    ---

Do You Yahoo!?
Yahoo! Messenger - Talk while you surf! It's FREE. Received on Wed Oct 25 2000 - 07:01:21 CDT