We have to check the below details before reorganize the table
TABLE DETAILS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (‘WF_ITEM_ACTIVITY_STATUSES_H’);
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES
———- ————————- ———— ————— ———–
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H TABLE APPS_TS_TX_DATA 14.9451904
Elapsed: 00:00:00.31
CHECKING INVALID OBJECT
SQL> select OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS=’INVALID‘ AND OWNER=’APPLSYS‘ AND OBJECT_NAME=‘WF_ITEM_ACTIVITY_STATUSES_H’;
no rows selected
Elapsed: 00:00:00.02
TABLE ROW COUNT:
select count(*) from APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H;
COUNT(*)
———-
3803468
Elapsed: 00:17:27.44
INDEX DETAIL:
SELECT INDEX_NAME,TABLE_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME IN (‘WF_ITEM_ACTIVITY_STATUSES_H’);
INDEX_NAME TABLE_NAME STATUS
—————————— —————————— ——–
WF_ITEM_ACTIVITY_STATUSES_H_N1 WF_ITEM_ACTIVITY_STATUSES_H VALID
WF_ITEM_ACTIVITY_STATUSES_H_N2 WF_ITEM_ACTIVITY_STATUSES_H VALID
WF_ITEM_ACTIVITY_STATUSES_H_N3 WF_ITEM_ACTIVITY_STATUSES_H VALID
Elapsed: 00:00:00.00
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (‘WF_ITEM_ACTIVITY_STATUSES_H_N1′,’WF_ITEM_ACTIVITY_STATUSES_H_N2′,’WF_ITEM_ACTIVITY_STATUSES_H_N3’);
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES
——– —————————— ——————– ————————– ———-
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H_N1 INDEX APPS_TS_TX_IDX 15.3917236
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H_N2 INDEX APPS_TS_TX_IDX .000976563
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H_N3 INDEX APPS_TS_TX_IDX .000610352
Elapsed: 00:00:00.39
MOVING TABLE
02:29:24 SQL > alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H move;
Elapsed: 00:01:38.17
Rebuild the associated indexes:
02:31:03 SQL> ALTER INDEX APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H_N1 REBUILD ONLINE;
Index altered.
Elapsed: 00:00:23.40
02:31:53 SQL> ALTER INDEX APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H_N2 REBUILD ONLINE;
Index altered.
Elapsed: 00:00:04.78
02:32:17 SQL> ALTER INDEX APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H_N3 REBUILD ONLINE;
Index altered.
Elapsed: 00:00:05.08
Collecting the Statistics:
02:32:28 SQL> BEGIN
02:32:56 2 DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => ‘APPLSYS’,
tabname => ‘WF_ITEM_ACTIVITY_STATUSES_H’,
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
METHOD_OPT => ‘FOR ALL INDEXED COLUMNS SIZE SKEWONLY’,
DEGREE => CEIL(DBMS_STATS.AUTO_DEGREE/2),
GRANULARITY => ‘ALL’,
NO_INVALIDATE => FALSE,
CASCADE => TRUE);
END;
/02:32:57 10 02:32:57 11 02:32:57 12
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.05
AFTER REORGANIZED TABLES:
TABLE SIZE
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024 as MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (‘WF_ITEM_ACTIVITY_STATUSES_H’);
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB
———- —————————— —————— —————————— ———-
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H TABLE APPS_TS_TX_DATA 260.875
INDEX SIZE
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (‘WF_ITEM_ACTIVITY_STATUSES_H_N1′,’WF_ITEM_ACTIVITY_STATUSES_H_N2′,’WF_ITEM_ACTIVITY_STATUSES_H_N3’);
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB
———- ——————————————– ——————– ————————- —————————
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H_N1 INDEX APPS_TS_TX_IDX 130.25
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H_N2 INDEX APPS_TS_TX_IDX .25
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H_N3 INDEX APPS_TS_TX_IDX .25
ROW COUNT OUTPUT TIMING
02:36:08 SQL> select count(*) from APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H;
COUNT(*)
————-
3803468
Elapsed: 00:00:00.20
INVALID OBJECTS DETAILS NOW
INDEX_NAME TABLE_NAME STATUS
—————————— ————————- ———–
WF_ITEM_ACTIVITY_STATUSES_H_N1 WF_ITEM_ACTIVITY_STATUSES_H VALID
WF_ITEM_ACTIVITY_STATUSES_H_N2 WF_ITEM_ACTIVITY_STATUSES_H VALID
WF_ITEM_ACTIVITY_STATUSES_H_N3 WF_ITEM_ACTIVITY_STATUSES_H VALID
