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

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !