RE: Convert to Locally-Managed Tablespaces

The release specific bits are stored in release specific tablespaces, which are migrated to new tablespaces during the upgrade. You can reclaim the space afterward.

For example, upgrading 4.0B to 4.6C will cause a migration of data from PSAPES40BD to PSAPES46CD, among others. Newer releases based on WAS 6.20 and higher use PSAP<SCHEMA><REL> instead (e.g. PSAPDEV620 or PSAPPRD620 for a DEV or PRD db running 6.20). Upgrades are done the same way, e.g. PSAPDEV620 -> PSAPDEV630 and you can free up PSAPDEV620's segments afterward.

Rich

--
Rich Holland        (913) 645-1950        SAP Technical Consultant
print unpack("u","92G5S\=\"!A;F]T:&5R(\'!E<FP\@:&%C:V5R\"\@\`\`");



> -----Original Message-----

> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On 

> Behalf Of DENNIS WILLIAMS

> Sent: Tuesday, January 06, 2004 3:54 PM

> To: Multiple recipients of list ORACLE-L

> Subject: RE: Convert to Locally-Managed Tablespaces

> 

> 

> Jared - I don't know SAP, but other canned applications have 

> a "wonderful"

> process they put you through every few years called an 

> upgrade. Often you

> end up creating another database and moving the data. This is a great

> opportunity for the DBA to fix some issues like this. 

> However, since SAP is

> used by some REALLY large organizations with really, really 

> large amounts of

> data, perhaps SAP uses a different practice. I would like to 

> know if that is

> the case.

> 

> Dennis Williams

> DBA

> Lifetouch, Inc.

> dwilliams_at_lifetouch.com 

> 

> -----Original Message-----

> Sent: Monday, January 05, 2004 8:09 PM

> To: Multiple recipients of list ORACLE-L

> 

> 

> Tanel,

> 

> That's a good idea.  I briefly considered this, but

> didn't really dig into it. 

> 

> The systems I need to do this on is our SAP systems, and

> downtime is a precious commodity, especially for production.

> 

> I just may try this on our test system.  The problem with

> SAP of course, and many other ERP's is that there are 22k+

> tables, which could consume a bit of time.

> 

> The amount of fragmented space that would be recovered is

> probably not worth the trouble of this procedure, depending

> on how much time it takes.

> 

> I see that you too need to keep the original tablespace names,

> is this SAP per chance?

> 

> If you have already performed a test of this, what kind of

> times are you seeing, along with relevant platform information,

> and the number of tables/indexes?

> 

> Jared

> 

> 

> On Mon, 2004-01-05 at 16:49, Tanel Poder wrote:

> > 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: Tanel Poder

> >   INET: tanel.poder.003_at_mail.ee

> > 

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

> > 

> 

> 

> -- 

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> -- 

> Author: Jared Still

>   INET: jkstill_at_cybcon.com

> 

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

> -- 

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> -- 

> Author: DENNIS WILLIAMS

>   INET: DWILLIAMS_at_LIFETOUCH.COM

> 

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

> 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rich Holland
  INET: holland_at_guidancetech.com

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