BDLS IN LESS THAN 2 HOURS – Part 4

Excellent explanation and solution for this always time consuming refresh activity 🙂

Thanks for such good job and efforts taken.

Can you please summarise your steps with corresponding SQL commands too?

It wil be easy for us who are not Oracle experts.

I have did for same may be you can update and correct for others, also if you can add step or provide query for tables u found > 10000 rows as that part is not much clear for me

1-----------------------------------------
Put database in NOARCHIVELOG mode

alter database no archivelog;

2------------------------------------------
Create PSAPBDLS tablespace of 5 GB

CREATE TABLESPACE PSAPBDLS
  DATAFILE 'tbs_perm_01.dbf'
    SIZE 5000M
  ONLINE;

3-----------------------------------------------------------------------------------------------------------
Enable parallelism for all tables invloved in BDLS

ALTER TABLE COEP PARALLEL;

4-----------------------------------------------------------------------------------------------------------
Enable parallellism for all indexes of the tables invloved in BDLS
ALTER INDEX "COEP~0" PARALLEL;

5-------------------------------------------------------------------------------------------------------------
Create bitmap indexes with PARALLEL NOLOGGING COMPUTE STATITICS for tables with total number of rows > 10000
 
CREATE BITMAP INDEX COEP_BDLS_IDX_12345 ON COEP(MANDT, LOGSYSO) NOLOGGING PARALLEL TABLESAPCE PSAPBDLS COMPUTE STATISTICS;

6-------------------------------------------------------------------------------------------------------------
Calculate rows to be updated by BDLS

7------------------------------------------------------------------------------------------------------------
Build CTAS command lines with decode funtions

CREATE TABLE EDIDC_BDLS_BKUP

PARALLEL COMPRESS FOR OLTP NOLOGGING

STORAGE ( INITIAL 3253M NEXT 50M MINEXTENTS 1 MAXEXTENTS UNLIMITED)

TABLESPACE PSAPNXP

AS

(

SELECT

MANDT, DOCNUM, DOCREL, STATUS, DOCTYP, DIRECT, RCVPOR, RCVPRT,

   CASE LOGSYS

     WHEN 'S1P100' THEN 'S1Q100'

     WHEN 'S1P500' THEN 'S1Q500'

     WHEN 'BWP200' THEN 'BWQ200'

     WHEN 'PIP300' THEN 'PIQ300'

     ELSE  LOGSYS

   END LOGSYS,

RCVSAD, RCVSMN, RCVSNA, RCVSCA, RCVSDF, RCVSLF, RCVLAD, STD,

STDVRS, STDMES, MESCOD, MESFCT, OUTMOD, TEST, SNDPOR, SNDPRT,

decode(MANDT||SNDPRN,'500S1P500','S1Q500',SNDPRN) SNDPRN,

SNDSAD, SNDSMN, SNDSNA, SNDSCA, SNDSDF, SNDSLF, SNDLAD, REFINT,

REFGRP, REFMES, ARCKEY, CREDAT, CRETIM, MESTYP, IDOCTP, CIMTYP,

RCVPFC, SNDPFC, SERIAL, EXPRSS, UPDDAT, UPDTIM, MAXSEGNUM

From EDIDC

);

8----------------------------------------------------------------------------
Perform coversion of tables where rows to update > 1000 using CTAS

9-------------------------------------------------------------------------------
Save original index definitions

set timi on

set echo off

set head off

set long 5000

set pagesize 0

set linesize 150

column DDL format A150

select DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','EDIDC~0') DDL from dual;

10--------------------------------------------------------------------------------
Drop original indexes
Drop index COEP~0;

11-------------------------------------------------------------------------------
Rename original table to old

rename EDIDC to EDIDC_ORIG;

12-------------------------------------------------------------
Rename newly created tables to original
rename EDIDC_BDLS_BKUP to EDIDC;

13-----------------------------------------------------------------------------
Recreate indexes
Create as per step 9 and NOLOGGING PARALLEL COMPUTE STATISTICS
check for the NULL constratints

ALTER TABLE "EDIDC" MODIFY RCVPRN NOT NULL;
ALTER TABLE "EDIDC" MODIFY SNDPRN NOT NULL;

14------------------------------------------------------------------------------
Verify and Drop old table
Drop table EDIDC_ORIG;

15------------------------------------------------------------------------------------------
Run BDLS in SAP using report RBDLS<CLNT> in parallel (A*....Z*, etc) using Background sessions.

16------------------------------------------------------------------------------
      Clean up
 
17----------------------------------------------------------------------------------
Drop bitmap indexes
drop index COEP_BDLS_IDX_12345;

18----------------------------------------------------------------------------------
Revert parallelism to original state for all tables and indexes

ALTER INDEX "EDIDC~0" NOPARALLEL;

19----------------------------------------------------------------------------------
Drop function created, if any

20----------------------------------------------------
Drop extra tables created, if any

21----------------------------------------------------
Run update statistics
brconnect -u / -c -f stats -t system_stats

brconnect -u / -c -f stats -t oradict_stats

brconnect -u / -c -f stats -t all -p 15 -f nocheck -f collect

22----------------------------------------------------
Put database back on ARCHIVELOG mode

alert database archivelog;

Regards,
Nitin Salunkhe