Space - Database Reorganization

HOW TO EXECUTE REORG ZOPIT EXERCISE 1. Use DB02 – Detail Analysis to check each partition size for ZOPIT 

DB02 – Database Performance : Tables and Indexes  Detail Analysis - Memory Management : Tables and Indexes  Enter Tablespace = PSAPZOPIT – (F6)  Tables and Indexes : Analysis

Owner Object SAPECP ZOPIT0180 SAPECP ZOPIT0061

Type Tablespace TABLE PART PSAPZOPIT TABLE PART PSAPZOPIT

KBytes 14,501,568 610,304

Blocks Extents MaxExtents Next (K) 1,812,696 3,299 10 76,288 874 10

2. Use DB02 OR BRTOOLS – Check the current free space available in PSAPZOPIT 

DB02 – Database Performance : Tables and Indexes  Freespace statistics

Tablespace Maximum/kb SYSTEM PSAPUNDO PSAPTEMP PSAPECP PSAPZOPIT

Freespace Fragments Maximum/kb Total/kb

Max next Critical objects extent/kb now after reorg a. autoext

185216 195008 3 1024 0 0 0 2592640 39464896 1387 8192 0 0 0 0 0 not calc. not calc. not calc. not calc. not calc. 873408 31988480 1127 65536 0 0 0 182912 15996544 7729 65536 0 0 0

 Freespace statistics – History Of Tablespaces Scale: Day Tablespace PSAPZOPIT

Size (Kbyte) Free(Kbyte) Used (Kbyte) %-Used Tables/Indices Extents Total Chg/day Total Total Chg/day Total Chg Total Chg/day Total Chg/day 880,235,888

0 15,996,544 864,239,344 3,652,862

98

0 1,205

5

532,257

833

3. Ensure the free space in PSAPZOPIT is bigger than the ZOPIT partition you want to reorg. 4. Use sqlplus to execute the reorg script for each station/partition. The reorg scripts are in /home/ecpadm/reorg. Example :% pg z0061.sql set timing on; spool z0061; lock table zopit partition (zopit0061) in exclusive mode; create table z0061 tablespace psapzopit parallel nologging as select * from zopit partition (zopit0061); alter table zopit exchange partition zopit0061 with table z0061 without validation;

commit; alter index "ZOPIT~0" rebuild partition zopit0061 parallel nologging online; alter index "ZOPIT~1" rebuild partition zopit0061 parallel nologging online; alter index "ZOPIT~2" rebuild partition zopit0061 parallel nologging online; alter index "ZOPIT~3" rebuild partition zopit0061 parallel nologging online; alter index "ZOPIT~4" rebuild partition zopit0061 parallel nologging online; alter index "ZOPIT~5" rebuild partition zopit0061 parallel nologging online; alter index "ZOPIT~7" rebuild partition zopit0061 parallel nologging online; alter index "ZOPIT~8" rebuild partition zopit0061 parallel nologging online; exit;

Login : ecpadm %cd /home/ecpadm/reorg %sqlplus sapecp/<password>@ECP @z<SSSS>.sql The reorg execution log will be created as /home/ecpadm/reorg/z<SSSS>.lst. 5. Check if the reorg station is ok by comparing the record counts as per following example :%sqlplus sapecp/<password>@ECP sql> select count(*) from zopit partition(zopit<SSSS>); AND sql> select count(*) from z<SSSS>; OR %sqlplus sapecp/<password>@ECP @c.sql <SSSS> 6. If the count above are the same, then drop table z<SSSS> as follows :%sqlplus sapecp/<password>@ECP sql> drop table z<SSSS>; 7. Repeat step 2 to step 6.