Skip to Content

Here is a test case shows how to use brspace to convert a partitioned table to a non-partitioned table.  The basic idea is to use -d (first) option to change table definition during reorg.

Brspace help link:

https://help.sap.com/saphelp_nw73/helpdata/en/46/ade1f9dac33b2fe10000000a1553f7/content.htm

By using -d(first) option brspace will resume and give our opportunity to change ANY ddl we want, here we only care partition.

1. Preparation:

1.1 create a partitioned table partab:

create table sapsr3.partab (c1 int)
partition by range (c1)
(partition par1 values less than (10),
partition par2 values less than (20)
);

1.2 insert some rows:

SQL> insert into sapsr3.partab values (1);
SQL> insert into sapsr3.partab values (11);
SQL> insert into sapsr3.partab values (2);
SQL> insert into sapsr3.partab values (12);

2 covert to non-partitioned table:

2.1 call brspace:

brspace -p initZ07.sap -s 20 -l E -f tbreorg -a reorg -t “partab”

————————————–brspace screen——————————————————–
Options for reorganization of tables: SAPSR3.PARTAB (degree 1)

1 * Reorganization action (action) ………… [reorg]
2 – Reorganization mode (mode) ……………. [online]
3 – Create DDL statements (ddl) …………… [first] !!!!! change to first option
4 ~ New destination tablespace (newts) …….. []
5 ~ Separate index tablespace (indts) ……… []
6 – Parallel threads (parallel) …………… [1]
7 ~ Table/index parallel degree (degree) …… []
8 ~ Category of initial extent size (initial) . []
9 ~ Sort by fields of index (sortind) ……… []
10 # Index for IOT conversion (iotind) ……… [FIRST]
11 – Compression action (compress) …………. [none]
12 # LOB compression degree (lobcompr) ……… [medium]
13 # Index compression method (indcompr) ……. [ora_proc]
————————————brspace screen————————————————————-


2.2 then continue, you will see:

————————————–brspace screen——————————————————–

BR0280I BRSPACE time stamp: 2017-04-12 10:52:05
BR1115I Number of tables DDL for which statements were successfully created: 1

BR0280I BRSPACE time stamp: 2017-04-12 10:52:05
BR1148I You can check/change the DDL statements in file /oracle/Z07/sapreorg/sevownms/ddl.sql now

BR0280I BRSPACE time stamp: 2017-04-12 10:52:05
BR0256I Enter ‘c[ont]’ to continue, ‘s[top]’ to cancel BRSPACE:

————————————–brspace screen——————————————————–

2.3 edit ddl.sql

Before continue, we need to edit /oracle/Z07/sapreorg/sevownms/ddl.sql by removing partition
After editing /oracle/Z07/sapreorg/sevownms/ddl.sql, continue brspace will guide us to finish.

3. Verify:

3.1 check definition

SQL> select dbms_metadata.get_ddl(‘TABLE’, ‘PARTAB’, ‘SAPSR3’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,’PARTAB’,’SAPSR3′)
——————————————————————————–

CREATE TABLE “SAPSR3″.”PARTAB”
( “C1” NUMBER(*,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DE
FAULT)
TABLESPACE “PSAPSR3USR”

3.2 check table rows

SQL> select * from “SAPSR3″.”PARTAB”;

C1
———-
1
2
11
12

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. James Zhang Post author

    update /oracle/Z07/sapreorg/sevownms/ddl.sql content:

    cat /oracle/Z07/sapreorg/sevownms/ddl.sql
    /* #TABL SAPSR3.PARTAB */

    CREATE TABLE “SAPSR3″.”PARTAB#$”
    ( “C1” NUMBER(*,0)
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    STORAGE(
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE “PSAPSR3USR”
    PARTITION BY RANGE (“C1”)
    (PARTITION “PAR1” VALUES LESS THAN (10)
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS LOGGING
    STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE “PSAPSR3USR” ,
    PARTITION “PAR2” VALUES LESS THAN (20)
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS LOGGING
    STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE “PSAPSR3USR” ) ;

    /* #STMT */

    /* #INDX */

    /* #GRNT */

    /* #CONS */

    /* #RCON */

    /* #TRIG */

    /* #COMM */

    /* #MLOG */

    (0) 

Leave a Reply