FW: 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

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