Large client deletion case - Note 857973

[Former Member]

This document explains a sample large client deletion case. I tried to delete a huge productive client with size some hundred gigabytes. I needed to follow those instructions expressed in Note 857973 - Deleting clients efficiently using Oracle.

Login with sapsr3 in order to create _TMP tables under sapsr3's schema.

sqlplus sapsr3/*******

1)We've chosen tables larger than 15GB and had created _TMP tables, to which we transferred data of clients rather than the deleted one (500 in this case).

CREATE TABLE DFKKLOCKS_TMP NOLOGGING PARALLEL 2 AS SELECT /*+ INDEX(DFKKLOCKS "DFKKLOCKS~0") */ * FROM DFKKLOCKS WHERE CLIENT < '500' OR  CLIENT  > '500';

CREATE TABLE JEST_TMP NOLOGGING PARALLEL 2 AS SELECT /*+ INDEX(JEST "JEST~0") */ * FROM JEST WHERE MANDT < '500' OR MANDT  > '500';

CREATE TABLE EABLG_TMP NOLOGGING PARALLEL 2 AS SELECT /*+ INDEX(EABLG "EABLG~0") */ * FROM EABLG WHERE MANDT < '500' OR MANDT  > '500';

CREATE TABLE SWW_WI2OBJ_TMP NOLOGGING PARALLEL 2 AS SELECT /*+ INDEX(SWW_WI2OBJ "SWW_WI2OBJ~0") */ * FROM SWW_WI2OBJ WHERE CLIENT < '500' OR CLIENT  > '500';

CREATE TABLE DFKKKOBW_TMP NOLOGGING PARALLEL 2 AS SELECT /*+ INDEX(DFKKKOBW "DFKKKOBW~0") */ * FROM DFKKKOBW WHERE MANDT < '500' OR MANDT  > '500';

2)Then truncate those tables:

truncate table  DFKKLOCKS ;

truncate table JEST;

truncate table EABLG;

truncate table SWW_WI2OBJ;

truncate table DFKKKOBW;

3)After truncating it we reload saved data in TMP tables. Thus we've eliminated productive client data with truncate.

INSERT INTO DFKKLOCKS SELECT * FROM DFKKLOCKS_TMP;

INSERT INTO JEST SELECT * FROM JEST_TMP;

INSERT INTO EABLG SELECT * FROM EABLG_TMP;

INSERT INTO SWW_WI2OBJ SELECT * FROM SWW_WI2OBJ_TMP;

INSERT INTO DFKKKOBW SELECT * FROM DFKKKOBW_TMP;