Re: LOCALLY MANAGED TABLESPACE

Seema,

Our production Student Information database (8.1.7 under Win2k) has 40,000 tables and 60,000 indexes. It's a third party app designed for dBaseIV - go ahead and laugh, we do all the time (when we're not crying). Anyhow, we have to regularly clone out the data to a couple of other databases, both on HPUX. The different OS means we have to use export/import, not restore from hot backup or transportable tablespaces, to move the data.

Both of the recipient databases (with dictionary-managed tablespaces) started out needing about 2 hours to drop all the tables and indexes
(tables change, so we can't truncate) and about 6 hours to import the full
dataset. After several "refreshes" the time requirement grew to almost 30 hours for each DB. I think the data dictionary tables that record info. about tables, indexes, and extents (someone else on this list could probably name the very ones) got totally mucked up (a techical term) after so many massive drops and creates.

After I recreated the recipient tablespaces as locally-managed, drop and import times returned to 2 and 6 hours, respectively, and have remained there through numerous subsequent "refreshes". Needless to say, we are *very* happy with LMTs.

BTW, our Student Info. system is clunky (we're going to redesign it into a couple hundred partitioned tables with 40,000 views and 120,000 Instead-Of Triggers, but that's another story) but several thousand teachers and administrators basically like the way it manages our 80,000 students. How's that for a client base?

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715
JApplewhite_at_austin.isd.tenet.edu

                                                                                            
                    "Seema Singh"                                                           
                    <oracledbam_at_ho       To:     Multiple recipients of list ORACLE-L       
                    tmail.com>            <ORACLE-L_at_fatcity.com>                            
                    Sent by:             cc:                                                
                    root_at_fatcity.c       Subject:     LOCALLY MANAGED TABLESPACE            
                    om                                                                      
                                                                                            
                                                                                            
                    10/24/2002                                                              
                    01:49 PM                                                                
                    Please respond                                                          
                    to ORACLE-L                                                             
                                                                                            
                                                                                            

Hi
I am thinking to change our few dictinary manages tablespace to locally managed tablespace.Can any one experienced any issues with locally managed tablespace?
Do any one experience what gain after changing to locally managed tablespace?

Thx
-Seema

--
Author: Seema Singh
  INET: oracledbam_at_hotmail.com




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: JApplewhite_at_austin.isd.tenet.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).