Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
JamesZ
Advisor
Advisor
0 Kudos
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
1 Comment