SAP Tablespace Conventions (WAS: Convert to LMTs)

Jared,
SAP does use default tablespace names as you have stated, but will allow you to move them to alternate locations and recommends it under certain conditions. We separated 10 or 12 of the tables that we thought would grow the fastest into dedicated tablespaces when we originally installed R/3 40B several years ago and have added to that number over time. SAPDBA has options to perform a move between tablespaces via EXP/IMP or CTAS.

As far as the impact on upgrades goes, we are migrating to SAP Enterprise 4.7 and I have found minimal impact from the nondefault layout. Only 1 or 2 tables reverted to their default tablespaces and no errors for tables that were elsewhere.

(Nitty-gritty details if you want to read on)

Now creating new tablespaces via SAPDBA (with the PSAP prefix) is recommended as it modifies the SAP internal data dictionary to include them. Also, the default tablespace can be changed by adding userdefined  data classes associated with the new tablespaces, then assigning them to the table/s in question via SE11 and the Repair/ Correction&Transport mechanism. We created our own data classes but never assigned them, leaving the original data classes in place. Figured it would be less of a problem than keeping track of 100 or so Repair/Transports. The discrepancy is only evident in the SE14 transaction under Storage parameters between the actual values in the database (DBS) and expected/calculated values (CMP).

Hope this isn't more than you wanted or needed ;)

Mike Hand
Polaroid Corp.

-----Original Message-----
Sent: Tuesday, January 06, 2004 9:04 PM
To: Multiple recipients of list ORACLE-L

SAP expects tables to be in certain tablespaces: PSAPBTABD for instance.

Same with indexes: PSAPBTABI

The SAPDBA interactive tool is being phased out in favor of the command line tool brconnect. I don't know if brconnect allows some flexibility in tablespace names, but I doubt it. Just haven't got around to that particular topic yet.

There are methods for doing fairly fast migrations should you need it, such as changing platforms. I would have preferred to do an exp/imp or something like it when we moved to new servers, but in the amount of time I had to work with, I could not come up with what I thought an acceptable method on windows.

On *nix there would have been more leeway. There isn't any native named pipe functionality that is exposed to the shell on Win32. On top of that, Oracle compiles the utilities such as imp/exp with libs that prevent you from using stdin/stdout in the manner you would on unix.

I found out too late about netcat:
(http://dbasupport.com/oracle/ora9i/resolutions.shtml)

So, it was the mig utility for us.

Jared

On Tue, 2004-01-06 at 12:54, DENNIS WILLIAMS wrote:
> 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: 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).

--
This transmission is intended only for use by the addressee(s) named herein and may contain information that is proprietary, confidential and/or legally privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hand, Michael T
  INET: HANDM_at_polaroid.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).