Re: Relocate table to a different tablespace in SAP

The current tool for doing that in SAP is brspace.

brspace uses dbms_redefinition under the covers.

(Which is unfortunately broken in 9.2.0.5 <http://9.2.0.5>)

You can use a manual process to move the table with materialized views (with prebuilt tables).

It involves a couple of minimal downtimes for SAP ( a few seconds ). That is just to ensure that no transaction is trying to use the table during the rename.

SAP will be none the wiser.

Jared

On 11/4/05, Roger Xu <roger_xu_at_dp7uptx.com> wrote:
>
> Hi,
> Below is what I see when I used sapdba to move a table.
> CREATE TABLE "SAPR3"."CKMI1#" ...TABLESPACE "PSAPBTA3D"... AS SELECT *
> FROM "SAPR3"."CKMI1";
> DROP TABLE "SAPR3"."CKMI1" if successfully created;
> RENAME "CKMI1#" TO "CKMI1";
> Question#1: What is "if successfully created" in the second sql
> statement?
> Question#2: Is this same as "alter table move tablespace ..."?
> Thanks,
> Roger
>
> This e-mail is intended solely for the person or entity to which it is
> addressed and may contain confidential and/or privileged information. Any
> review, dissemination, copying, printing or other use of this e-mail by
> persons or entities other than the addressee is prohibited. If you have
> received this e-mail in error, please contact the sender immediately and
> delete the material.
> ____________________________________________________________________
> This email has been scanned for all viruses by the MessageLabs Email
> Security System. Any questions please call 972-721-8257 or email your
> request to tech_support_at_dp7uptx.com.
>

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l