Re: Convert to Locally-Managed Tablespaces

Tanel

I am curious - why are you forced to keep segments in specifically named tablespaces.

I did come across one system that would
actually reject (application) upgrades if the database didn't have exactly the right two tablespaces (E_DATA and E_INDX or
some such) but I can't think of any other reason.

A couple of add-on points for Paula:

There is a drawback with using parallel moves - at parallel 16, you end up with 16 extents with an average of 50% space wastage - This may not be significant, especially on a large table with a reasonable number of extents that will be subject to lots of subsequent inserts - but it could make a big difference in some cases. (Side effects include changes in execution path)

Regardless of whether you use export or move, you may also have to consider a few special cases of tables where the typical row starts small, and grows over the course of time. You do get systems where a graph of row-lengths shows things like:

    80%        140 bytes
    10%        100 bytes
    10%         60 bytes

If this is the case, then moving the table can result in a problems with lots of wasted space (if you set pctfree for the new rows that need to grow from 60 bytes to 140 bytes) or lots of chained rows (if you set pctfree to suit the 80% of the data that is never going to grow again).

I think the ideal is to MINIMIZE records_per_block on such tables, then set pctfree to zero, then MOVE them, then set pctfree back to what it was. But if you do have any such tables, you might want to experiment.

One last thought - does the entire operation have to be done in one shot anyway ? You could create a few spare empty tablespaces, and move a couple of tables and indexes at a time if you wanted to avoid high visibility periods of non-availability, and didn't want to come in at the week-end.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, January 06, 2004 12:49 AM

> Hi!
>
> > This is what I will need to use on our systems, as there are about 400
gig
> > of data and indexes. 200 gig of data is too large to export/import, at
> least
> > it is for this project. So dbms_space_admin it will be.
>
> I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week,
> here's what I'll do (there is practically no free space for temporary
> usage):
>
> 1) Export index definitions (normal export with rows=n)
> 2) Drop all indexes
> 3) use alter table move with parallel 16 and nologging to move all tables
to
> old index tablespaces (the indexes consumed more space than tables)
> 4) drop and recreate data tablespaces
> 5) use alter table move again to move tables back (the segments have to
> reside in original tablespaces, otherwise I could have skipped this step)
> 6) drop and recreate index tablespaces
> 7) get index definitions out of exportfile and modify them to add parallel
&
> nologging (with big sort area size)
> 8) rebuild indexes
> 9) do a full backup
>
> It might help to recreate index tablespaces even before step 3, to speed
up
> parallel table moving a bit..
>
> Maybe you want to test this Jared, this approach is much faster than
> export/import, because everything can be done with direct path operations
> and nologging (import doesn't have direct path facility, so regular array
> inserts are used, which always require logging as well).
> Also, your tables/datablocks will be optimized after moving them (which is
> not the case with dbms_space_admin) and you don't have to have any space
for
> reorg in case your cleared index tablespace can temporarily accommodate
your
> data.
>
> > IIRC one of the drawbacks of using dbms_space_admin to convert is
> > that you won't be converting to nice uniform extent sizes for existing
> data.
>
> Yes, and if your tablespace is fragmented, the fragmentation will remain
> there, despite your conversions (of course, smaller extents might be able
to
> use some of this fragmented space later on).
>
> Tanel.
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).