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