Looking for a good way to convert from dictionnary manged TBS to Locally managed ones

Hello,

We are in the process of putting all our segments in locally managed tablespaces (Uniform Sizes) to get rid of tablespace fragmentation and get better performance. We are on oracle 8.1.7.4, standard edition. Databases are in archivelog mode, these are small databases, in the order of 15-20 Gb each.

We do not want to use the DBMS_SPACE_ADMIN package to do so because the allocation type is going to be USER and we want the UNIFORM type.

I plan to first categorize all my tables and index accross a specific set of extents sizes (Eg: 64K, 128K, 512K, 2M, 10M, if anyone has a suggestion about what size they use, you're welcome) and then have some sort of program that will generate a matrix of "what goes where" (And eventually ALTER TABLE MOVE and ALTER INDEX rebuild commands to put stuff in the right UNIFORM LMT tablespace) We will probably have to mess around with various other objects (LOBS, Intermedia Indexes, Rollback TS) but this can be done manually.

Is there a way on the STANDARD edition of the database to do this while the database is actively in use ? (I could do that on low activity period tough)
? I'm planning to proceed in small steps, not doing the whole database in
one shot.

Am I right that in the STANDARD edition, if I move a table or rebuild an index, it will be unavailable while the operation is in progress ? What happens then ? Transactions are held as if the underlying tables were locked
? Apart for sluggish performance, will the users transactions fail miserably
?

Has anyone in this newgroup done something similar ?

Any help or pointers would be appreciated.

And YES, I have RTFM and researched this stuff before, I just want to know if if is easily feasible, or if I risk destroying/corrupting something in the process. I will take a cold backup before I begin, but should I take cold backups from time to time (After each major steps) or hot backups would suffice ?

Thanks for your time. And sorry for my bad english. Received on Thu Mar 18 2004 - 12:38:57 CST