--Ensure to take backup of table HISTORYMESSAGE in Prod and
ST6. In ST6 we used below steps to take export:
create or replace directory ST6_Nimbus as
'/pac/prod_backup/WF_MILESTONE'
grant read,write on directory ST6_NIMBUS to nimbus;
nohup expdp nimbus/bbnms0l4s2021 DIRECTORY = ST6_Nimbus
DUMPFILE = WF_MILESTONE_20221025_%U.dmp filesize=10G TABLES=HISTORYMESSAGE
PARALLEL=8 LOGFILE =WF_MILESTONE_20221025.log COMPRESSION=ALL &
-- Ensure to copy the table script using toad. In ST6 it was
copied and pasted at end of this doc.
-- Ensure to take Snapshot before starting activity
-- Dependent object count and status
--check grant ans referential integrity
-- Tablespace free and available space
set timing on;
select
tbs.tablespace_name,
tot.bytes/1024/1024/1024 total,
tot.bytes/1024/1024/1024-sum(nvl(fre.bytes,0))/1024/1024/1024
used,
sum(nvl(fre.bytes,0))/1024/1024/1024 free,
(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct
from
dba_free_space fre,
(select
tablespace_name, sum(bytes) bytes
from
dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where
tot.tablespace_name =
tbs.tablespace_name
and
fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
union
select
tsh.tablespace_name,
dtf.bytes/1024/1024/1024 total,
sum(nvl(tsh.bytes_used,0))/1024/1024/1024 used,
sum(nvl(tsh.bytes_free,0))/1024/1024/1024 free,
(1-sum(nvl(tsh.bytes_free,0))/dtf.bytes)*100 pct
from
v$temp_space_header tsh,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) dtf
where
dtf.tablespace_name =
tsh.tablespace_name(+)
group by tsh.tablespace_name, dtf.bytes/1024/1024/1024,
dtf.bytes
order by 1;
|
TABLESPACE_NAME TOTAL USED FREE PCT ------------------------------
---------- ---------- ---------- ---------- GGS_DATA 30 .000976563
29.9990234 .003255208 JBBOS_ARCHIVE 255 104.233032
150.766968 40.8756989 JBBOS_DAT 4770 3678.96765
1091.03235 77.1272044 JBBOS_DAT_1 752.5 166.051270
586.448730 22.0666139 JBBOS_IDX 1233.75 685.821899
547.928101 55.5884012 SYSAUX 30
20.1910400 9.80895996 67.3034668 SYSTEM 30
1.02807617 28.9719238 3.42692057 TEMP 120
119.992188 .0078125 99.9934896 TOOLS 30
.000976563 29.9990234 .003255208 UNDOTBS1 60 26.2839966
33.7160034 43.8066610 UNDOTBS2 60 52.8189087
7.18109131 88.0315145 UNDOTBS3 60 59.7031860
.296813965 99.5053101 UNDOTBS4 90 88.0750732
1.92492676 97.8611925 UNDOTBS5 60 59.5672607
.432739258 99.2787679 UNDOTBS6 60 39.5926514 20.4073486
65.9877523 USERS 30
.000976563 29.9990234 .003255208 VENUS_DAT 90 71.7305908
18.2694092 79.7006565 17 rows selected. Elapsed: 00:00:21.98 |
--Count of indexes,triggers,synonyms and constraints
set timing on;
select index_name from user_indexes where table_name='HISTORYMESSAGE';
select trigger_name from user_triggers where table_name='HISTORYMESSAGE';
select constraint_name from user_constraints where
table_name='HISTORYMESSAGE';
select synonym_name from user_synonyms where table_name='HISTORYMESSAGE';
|
INDEX_NAME
-------------------------------------------------------------------------------- HISTORYMESSAGE_PK
I_HSTRMSG_SONUM
I_HSTRMSG_TASKID
I_HSTRMSG_SOERRID
I_HSTRMSG_SOVID
I_HSTRMSG_BANNUMBER
I_HSTRMSG_PRODUCTTYPE
7 rows selected. Elapsed: 00:00:01.26 no rows selected. Elapsed: 00:00:02.32 CONSTRAINT_NAME
-------------------------------------------------------------------------------- SYS_C00286531
1 row selected. Elapsed: 00:00:04.42 no rows selected. Elapsed: 00:00:01.50 |
--Count of invalid objects in schema:
SELECT OBJECT_NAME, OBJECT_TYPE,status
FROM USER_OBJECTS where status <>'VALID';
|
OBJECT_NAME
-------------------------------------------------------------------------------- OBJECT_TYPE STATUS -----------------------
------- IPTVBATCHMIGRATION
PACKAGE BODY INVALID
MULTITHREAD_MIGRATION_UTIL
PACKAGE BODY INVALID
ORDERANALYSIS
PACKAGE BODY INVALID
PRIORITYORDER
PACKAGE BODY INVALID
SUBMIGRATION
PACKAGE BODY INVALID
SUPPORT_UTIL
PACKAGE BODY INVALID
UV10BATCHMIGRATION
OBJECT_NAME
-------------------------------------------------------------------------------- OBJECT_TYPE STATUS -----------------------
------- PACKAGE BODY INVALID
CHECK_YESTERDAY_REC
PROCEDURE INVALID
CLOB_TO_BLOB
PROCEDURE INVALID
DM_MIGRATE_SUBTYPE
PROCEDURE INVALID
GRANITE_REPORT_TEST
PROCEDURE INVALID
MIGRATION_NPATTA
PROCEDURE INVALID
MIGRATION_SUBTYPE
PROCEDURE INVALID OBJECT_NAME
-------------------------------------------------------------------------------- OBJECT_TYPE STATUS -----------------------
-------
MIGRATION_WIRECENTER
PROCEDURE INVALID
OUTPUT_BKDN_REPORT_DATA
PROCEDURE INVALID
OUTPUT_BREAK_REPORT_DATA
PROCEDURE INVALID
OUTPUT_EXP_REPORT_DATA
PROCEDURE INVALID
OUTPUT_SPLIT_REPORT_DATA
PROCEDURE INVALID
TEST_GRANITE
PROCEDURE INVALID
OBJECT_NAME
-------------------------------------------------------------------------------- OBJECT_TYPE STATUS -----------------------
------- TEST_PROC
PROCEDURE INVALID
BATCHORDER
PACKAGE BODY INVALID
DBCLEANUP
PACKAGE BODY INVALID
DB_UTIL
PACKAGE BODY INVALID
HIBERNATE_MIGRATION
PACKAGE BODY INVALID
DUE_ORDER_SUMMARY
VIEW INVALID
FAILED_ORDER_SUMMARY
OBJECT_NAME
-------------------------------------------------------------------------------- OBJECT_TYPE STATUS -----------------------
------- VIEW INVALID
NONFAILED_ORDER_SUMMARY
VIEW INVALID
ORDER_DUE_BY_TYPE
VIEW INVALID
ORDER_STATUS_BY_TYPE
VIEW INVALID
WFMBISMIGRATION
PROCEDURE INVALID
WORKFLOWMIGRATION
PROCEDURE INVALID
31 rows selected. Elapsed: 00:00:01.05 |
--Index Status before reorg
set timing on;
SELECT OBJECT_NAME, OBJECT_TYPE,status
FROM USER_OBJECTS
where object_name in (select index_name from user_indexes where table_name='HISTORYMESSAGE') and status<>'VALID';
no rows
selected.
--Find ASM diskgroup space:
The Following query can be used to find out the total/free
space in ASM Diskgroups: SQL:
set timing on;
ASM>select name,total_mb/1024 TOTAL_in_GB,free_mb/1024
FREE_in_GB,state from v$asm_diskgroup;
ASMCMD tool can also be used to find the free space.
--To check Fragmanted space:
select table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),0)
"TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),0)
"ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),0)
"FRAGMENTED_SPACE" from
user_tables where
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 100 order by 7 desc;
select
table_name,round(((blocks*8)/1024/1024),2) "size
(gb)" ,
round(((num_rows*avg_row_len/1024))/1024/1024,2) "actual_data (gb)",
round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2) "wasted_space
(gb)",
round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) "reclaimable space %",
partitioned
from
user_tables
where table_name='HISTORYMESSAGE' and
(round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;
COLUMN OBJECT_NAME FORMAT A40
COLUMN OBJECT_NAME FORMAT A40
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='NIMBUS' and object_name like '%HISTORYMESSAGE%';
--Gather table stats:
exec
DBMS_STATS.GATHER_TABLE_STATS('NIMBUS', 'HISTORYMESSAGE', degree=>6, method_opt=> 'for all
columns size 1', granularity => 'AUTO',
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE=>TRUE);
PL/SQL procedure successfully
completed.
Elapsed:
00:14:18.73
--Check the Table size and Index size
--Table and Index size output before REORG :-
select segment_name,TABLESPACE_NAME ,segment_type,
bytes/1024/1024/1024 size_gb from user_segments where segment_name = 'HISTORYMESSAGE'
or
segment_name in
(select index_name from user_indexes where table_name='HISTORYMESSAGE');
|
HISTORYMESSAGE |
JBBOS_DAT |
TABLE |
226.7764 |
|
I_HSTRMSG_PRODUCTTYPE |
JBBOS_DAT |
INDEX |
17.90918 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
1.866638 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
2.965454 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
1.671753 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
2.402893 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
2.152893 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
1.903381 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
1.652893 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
2.340393 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
1.879578 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
2.090393 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
1.652466 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
1.528015 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
2.715515 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
2.248657 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
2.778198 |
|
I_HSTRMSG_BANNUMBER |
JBBOS_IDX |
INDEX
PARTITION |
1.828979 |
|
I_HSTRMSG_BANNUMBER |
JBBOS_IDX |
INDEX
PARTITION |
1.823547 |
|
I_HSTRMSG_BANNUMBER |
JBBOS_IDX |
INDEX
PARTITION |
1.867004 |
|
I_HSTRMSG_BANNUMBER |
JBBOS_IDX |
INDEX
PARTITION |
3.632019 |
|
I_HSTRMSG_BANNUMBER |
JBBOS_IDX |
INDEX
PARTITION |
2.139038 |
|
I_HSTRMSG_BANNUMBER |
JBBOS_IDX |
INDEX
PARTITION |
1.833801 |
|
I_HSTRMSG_BANNUMBER |
JBBOS_IDX |
INDEX
PARTITION |
5.700745 |
|
I_HSTRMSG_BANNUMBER |
JBBOS_IDX |
INDEX
PARTITION |
5.493835 |
|
I_HSTRMSG_SOVID |
JBBOS_IDX |
INDEX
PARTITION |
0.948853 |
|
I_HSTRMSG_SOVID |
JBBOS_IDX |
INDEX
PARTITION |
1.212463 |
|
I_HSTRMSG_SOVID |
JBBOS_IDX |
INDEX
PARTITION |
4.927307 |
|
I_HSTRMSG_SOVID |
JBBOS_IDX |
INDEX
PARTITION |
0.915039 |
|
I_HSTRMSG_SOVID |
JBBOS_IDX |
INDEX
PARTITION |
3.450012 |
|
I_HSTRMSG_SOVID |
JBBOS_IDX |
INDEX
PARTITION |
3.112549 |
|
I_HSTRMSG_SOVID |
JBBOS_IDX |
INDEX
PARTITION |
2.032104 |
|
I_HSTRMSG_SOVID |
JBBOS_IDX |
INDEX
PARTITION |
2.060242 |
|
I_HSTRMSG_SOERRID |
JBBOS_IDX |
INDEX
PARTITION |
6.10E-05 |
|
I_HSTRMSG_SOERRID |
JBBOS_IDX |
INDEX
PARTITION |
6.10E-05 |
|
I_HSTRMSG_SOERRID |
JBBOS_IDX |
INDEX
PARTITION |
6.10E-05 |
|
I_HSTRMSG_SOERRID |
JBBOS_IDX |
INDEX
PARTITION |
6.10E-05 |
|
I_HSTRMSG_SOERRID |
JBBOS_IDX |
INDEX
PARTITION |
6.10E-05 |
|
I_HSTRMSG_SOERRID |
JBBOS_IDX |
INDEX
PARTITION |
6.10E-05 |
|
I_HSTRMSG_SOERRID |
JBBOS_IDX |
INDEX
PARTITION |
6.10E-05 |
|
I_HSTRMSG_SOERRID |
JBBOS_IDX |
INDEX
PARTITION |
6.10E-05 |
|
I_HSTRMSG_TASKID |
JBBOS_IDX |
INDEX
PARTITION |
6.870972 |
|
I_HSTRMSG_TASKID |
JBBOS_IDX |
INDEX
PARTITION |
6.882751 |
|
I_HSTRMSG_TASKID |
JBBOS_IDX |
INDEX
PARTITION |
6.918457 |
|
I_HSTRMSG_TASKID |
JBBOS_IDX |
INDEX
PARTITION |
6.917358 |
|
I_HSTRMSG_TASKID |
JBBOS_IDX |
INDEX
PARTITION |
6.920959 |
|
I_HSTRMSG_TASKID |
JBBOS_IDX |
INDEX
PARTITION |
6.902527 |
|
I_HSTRMSG_TASKID |
JBBOS_IDX |
INDEX
PARTITION |
6.917542 |
|
I_HSTRMSG_TASKID |
JBBOS_IDX |
INDEX
PARTITION |
6.936523 |
|
I_HSTRMSG_SONUM |
JBBOS_IDX |
INDEX
PARTITION |
2.272644 |
|
I_HSTRMSG_SONUM |
JBBOS_IDX |
INDEX
PARTITION |
2.211853 |
|
I_HSTRMSG_SONUM |
JBBOS_IDX |
INDEX
PARTITION |
3.20636 |
|
I_HSTRMSG_SONUM |
JBBOS_IDX |
INDEX
PARTITION |
4.943176 |
|
I_HSTRMSG_SONUM |
JBBOS_IDX |
INDEX
PARTITION |
2.225281 |
|
I_HSTRMSG_SONUM |
JBBOS_IDX |
INDEX
PARTITION |
3.766724 |
|
I_HSTRMSG_SONUM |
JBBOS_IDX |
INDEX
PARTITION |
6.312683 |
|
I_HSTRMSG_SONUM |
JBBOS_IDX |
INDEX
PARTITION |
2.09314 |
|
HISTORYMESSAGE_PK |
JBBOS_IDX |
INDEX
PARTITION |
1.90271 |
--Last analyzed
SELECT LAST_ANALYZED FROM user_TABLES WHERE TABLE_NAME IN 'HISTORYMESSAGE';
--High water mark(HWM) check query :
select a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
hgwtr
from user_tables
a,user_segments b where a.table_name=b.segment_name
and a.blocks <>
(b.blocks-a.empty_blocks-1)
and a.table_name like 'HISTORYMESSAGE'
order by 1,2;
2nd Method: Redifinition:
grant execute on
DBMS_REDEFINITION to Nimbus;
grant redefine any table to
nimbus;
grant CREATE ANY TABLE to
NIMBUS;
grant ALTER ANY TABLE to
NIMBUS;
grant DROP ANY TABLE to
NIMBUS;
grant LOCK ANY TABLE to
NIMBUS;
grant SELECT ANY TABLE to
NIMBUS;
Grant CREATE ANY TRIGGER to
NIMBUS;
Grant CREATE ANY INDEX to
NIMBUS;
Grant CREATE VIEW to NIMBUS;
Grant CREATE MATERIALIZED VIEW
to NIMBUS;
Grant execute any procedure to
nimbus;
Grant select any dictionary to
nimbus;
--Verify that the table is a candidate for online
redefinition. In this case you specify that the redefinition is to be done
using primary keys or pseudo-primary keys.
--priviledged user is needed
set timing on;
1.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('NIMBUS','HISTORYMESSAGE',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/
--If table is candidate then it will return PL/SQL procedure
successfully completed otherwise it raises an error.
2,
set timing on;
alter session force parallel dml parallel 16;
alter session force parallel query parallel 16;
|
Session altered. Elapsed: 00:00:01.52 Session altered. Elapsed: 00:00:00.40 |
3.
set timing on;
CREATE TABLE HISTORYMESSAGE_REORG AS SELECT * FROM HISTORYMESSAGE
WHERE 1=2 ;
|
Table created. Elapsed: 00:00:00.52 |
--Not Null constraint should not be created and dropped if
created.
select
constraint_name from user_constraints where table_name ='HISTORYMESSAGE_REORG';
alter table historymessage_reorg drop constraint sys_C00294849;
SELECT COUNT(*) FROM HISTORYMESSAGE_REORG ;
COUNT(*)
----------
0
--START REDEFINITION
set timing on;
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('NIMBUS','HISTORYMESSAGE',
'HISTORYMESSAGE_REORG');
PL/SQL procedure successfully
completed.
Elapsed:
00:12:00.70
--COPY DEPENDENT OBJECTS
The following automatically copy dependent objects like
mview, primary key, view, sequence, and triggers.
IGNORE_ERROR is set to TRUE to avoid primary key violation
with the COPY_TABLE_DEPENDENTS command.
set timing on;
DECLARE
N PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('NIMBUS','HISTORYMESSAGE', 'HISTORYMESSAGE_REORG',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, N);
END;
/
|
PL/SQL procedure
successfully completed. Elapsed: 04:50:19.35 |
--CHECK FOR ERRORS
set timing on;
COL OBJECT_NAME FOR A25
SET LIN200 PAGES 200
COL DDL_TEXT FOR A60
SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM
DBA_REDEFINITION_ERRORS;
|
No error |
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
--VALIDATE BOTH TABLES
The following validates the row count on both tables and
syncing with the interim table:
set timing on;
SELECT COUNT(*) FROM NIMBUS.HISTORYMESSAGE_REORG ;
set timing on;
SELECT COUNT(*) FROM NIMBUS.HISTORYMESSAGE ;
|
COUNT(*) ---------- 715618108 1 row selected. Elapsed: 00:01:03.19 COUNT(*) ---------- 715621658 1 row selected. Elapsed: 00:02:27.26 |
set timing on;
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('NIMBUS','HISTORYMESSAGE','HISTORYMESSAGE_REORG');
PL/SQL procedure successfully
completed.
Elapsed: 00:02:27.91
--FINISH THE REDEFINITION
The following
finishes the redefinition:
set timing on;
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('NIMBUS','HISTORYMESSAGE','HISTORYMESSAGE_REORG');
PL/SQL procedure successfully
completed.
Elapsed: 00:02:42.11
SQL>
COLUMN OBJECT_NAME FORMAT A40
COLUMN OBJECT_NAME FORMAT A40
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='NIMBUS' and object_name like '%HISTORYMESSAGE%';
--High water mark(HWM) check query post upgrade:
select a.table_name,b.blocks alcblks,a.blocks
usdblks,(b.blocks-a.empty_blocks-1) hgwtr
from user_tables
a,user_segments b where a.table_name=b.segment_name
and a.blocks <>
(b.blocks-a.empty_blocks-1)
and a.table_name like 'HISTORYMESSAGE'
order by 1,2;
--CHECK ERROR AND RECOMPILE THE SCHEMA
If any invalid objects are there recompile them. Other wise
ignore.
--EXEC UTL_RECOMP.RECOMP_SERIAL('NIMBUS') ;
Gather table stats:
exec
DBMS_STATS.GATHER_TABLE_STATS('NIMBUS', 'HISTORYMESSAGE', degree=>6, method_opt=> 'for all
columns size 1', granularity => 'AUTO',
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE=>TRUE);
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS
WHERE OWNER='NIMBUS' and object_name='HISTORYMESSAGE';
--To check Fragmanted space:
select
table_name,round(((blocks*8)/1024/1024),2) "size
(gb)" ,
round(((num_rows*avg_row_len/1024))/1024/1024,2) "actual_data (gb)",
round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2) "wasted_space
(gb)",
round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) "reclaimable space %",
partitioned
from
user_tables
where table_name='HISTORYMESSAGE' and
(round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;
--Table and Index size output after REORG :-
select segment_name,TABLESPACE_NAME ,segment_type,
bytes/1024/1024/1024 size_gb from user_segments where segment_name = 'HISTORYMESSAGE'
or
segment_name in
(select index_name from user_indexes where table_name='HISTORYMESSAGE');
|
HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0693359375 I_HSTRMSG_BANNUMBER JBBOS_IDX INDEX PARTITION 1.1875 I_HSTRMSG_BANNUMBER JBBOS_IDX INDEX PARTITION 1.1875 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 I_HSTRMSG_SONUM JBBOS_IDX INDEX
PARTITION 4.0673828125 I_HSTRMSG_SOERRID JBBOS_IDX INDEX
PARTITION 6.103515625E-5 I_HSTRMSG_SOERRID JBBOS_IDX INDEX
PARTITION 6.103515625E-5 I_HSTRMSG_TASKID JBBOS_IDX INDEX PARTITION 5 I_HSTRMSG_SOVID JBBOS_IDX INDEX PARTITION 1.625 I_HSTRMSG_SOVID JBBOS_IDX INDEX PARTITION 0.5390625 I_HSTRMSG_SOVID JBBOS_IDX INDEX PARTITION 3.125 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 I_HSTRMSG_SOERRID JBBOS_IDX INDEX
PARTITION 6.103515625E-5 I_HSTRMSG_TASKID JBBOS_IDX INDEX PARTITION 4.970703125 I_HSTRMSG_BANNUMBER JBBOS_IDX INDEX PARTITION 5.0625 I_HSTRMSG_BANNUMBER JBBOS_IDX INDEX PARTITION 1.25 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 I_HSTRMSG_SONUM JBBOS_IDX INDEX
PARTITION 1.33984375 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.11328125 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 I_HSTRMSG_TASKID JBBOS_IDX INDEX PARTITION 4.982421875 I_HSTRMSG_SONUM JBBOS_IDX INDEX
PARTITION 1.25 I_HSTRMSG_SOVID JBBOS_IDX INDEX PARTITION 0.5625 I_HSTRMSG_BANNUMBER JBBOS_IDX INDEX PARTITION 2.6875 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 I_HSTRMSG_SONUM JBBOS_IDX INDEX
PARTITION 1.25 HISTORYMESSAGE JBBOS_DAT TABLE 184.997314453125 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 I_HSTRMSG_TASKID JBBOS_IDX INDEX PARTITION 5 I_HSTRMSG_SONUM JBBOS_IDX INDEX
PARTITION 2.375 I_HSTRMSG_SONUM JBBOS_IDX INDEX
PARTITION 5.314453125 I_HSTRMSG_SONUM JBBOS_IDX INDEX
PARTITION 1.3125 I_HSTRMSG_BANNUMBER JBBOS_IDX INDEX PARTITION 1.375 I_HSTRMSG_SOVID JBBOS_IDX INDEX PARTITION 2.1875 I_HSTRMSG_TASKID JBBOS_IDX INDEX PARTITION 4.9853515625 I_HSTRMSG_TASKID JBBOS_IDX INDEX PARTITION 4.9375 I_HSTRMSG_TASKID JBBOS_IDX INDEX PARTITION 5.0029296875 I_HSTRMSG_TASKID JBBOS_IDX INDEX PARTITION 4.982421875 I_HSTRMSG_SOVID JBBOS_IDX INDEX PARTITION 0.84375 I_HSTRMSG_SOERRID JBBOS_IDX INDEX
PARTITION 6.103515625E-5 I_HSTRMSG_SOERRID JBBOS_IDX INDEX
PARTITION 6.103515625E-5 I_HSTRMSG_SOERRID JBBOS_IDX INDEX
PARTITION 6.103515625E-5 I_HSTRMSG_BANNUMBER JBBOS_IDX INDEX PARTITION 4.7685546875 I_HSTRMSG_SOERRID JBBOS_IDX INDEX
PARTITION 6.103515625E-5 I_HSTRMSG_SONUM JBBOS_IDX INDEX
PARTITION 2.875 I_HSTRMSG_SOVID JBBOS_IDX INDEX PARTITION 1.75 I_HSTRMSG_SOVID JBBOS_IDX INDEX PARTITION 4.4375 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 I_HSTRMSG_BANNUMBER JBBOS_IDX INDEX PARTITION 1.1875 HISTORYMESSAGE_PK JBBOS_IDX INDEX
PARTITION 1.0625 I_HSTRMSG_PRODUCTTYPE JBBOS_DAT INDEX 13.5869140625 I_HSTRMSG_SOERRID JBBOS_IDX INDEX
PARTITION 6.103515625E-5 |
--Count of indexes,triggers,synonyms and constraints
select index_name from user_indexes where table_name='HISTORYMESSAGE';
select trigger_name from user_triggers where table_name='HISTORYMESSAGE';
select constraint_name from user_constraints where
table_name='HISTORYMESSAGE';
select synonym_name from user_synonyms where table_name='HISTORYMESSAGE';
INDEX_NAME
--------------------------------------------------------------------------------
I_HSTRMSG_PRODUCTTYPE
I_HSTRMSG_BANNUMBER
I_HSTRMSG_SOVID
I_HSTRMSG_SOERRID
I_HSTRMSG_TASKID
I_HSTRMSG_SONUM
HISTORYMESSAGE_PK
7 rows selected.
no rows selected.
CONSTRAINT_NAME
--------------------------------------------------------------------------------
SYS_C00286531
1 row selected.
no rows selected.
SELECT OBJECT_NAME, OBJECT_TYPE,status
FROM USER_OBJECTS where status <>'VALID';
--Index Status before reorg
SELECT OBJECT_NAME, OBJECT_TYPE,status
FROM USER_OBJECTS
where object_name in (select index_name from user_indexes where table_name='HISTORYMESSAGE');
--DROP THE INTERIM TABLE
DROP TABLE NIMBUS.HISTORYMESSAGE_REORG;
Provide below grant in Prod:
GRANT SELECT ON NIMBUS.HISTORYMESSAGE TO NIMBUS_READ;
GRANT DELETE, INSERT, SELECT, UPDATE ON NIMBUS.HISTORYMESSAGE
TO NIMBUS_RW;
GRANT READ ON NIMBUS.HISTORYMESSAGE TO VS3090;
________________________________________________________________
--HISTORYMESSAGE script in ST6
DROP TABLE NIMBUS.HISTORYMESSAGE CASCADE CONSTRAINTS;
CREATE TABLE NIMBUS.HISTORYMESSAGE
(
ID
NUMBER NOT NULL,
ACTIVITYNAME VARCHAR2(255 BYTE),
DATE0 TIMESTAMP(6),
EXECUTEDATETIME TIMESTAMP(6),
HOSTNAME VARCHAR2(255 BYTE),
MANUAL
VARCHAR2(255 BYTE),
NOOP NUMBER,
RETRYCOUNT NUMBER,
SERVICEORDERNUMBER VARCHAR2(255 BYTE),
STATUS VARCHAR2(255 BYTE),
SYSTEMNAME VARCHAR2(255 BYTE),
TASKDEFINITIONID VARCHAR2(255 BYTE),
TASKID VARCHAR2(255 BYTE),
TASKNAME VARCHAR2(255 BYTE),
UID0 VARCHAR2(255 BYTE),
SERVICEORDERERROR_ID NUMBER,
SERVICEORDERVERSION_ID NUMBER,
VERSION
NUMBER,
BANNUMBER VARCHAR2(255 BYTE),
SUBSCRIPTION_ID NUMBER,
CIRCUITID VARCHAR2(255 BYTE),
CREATEDBY VARCHAR2(64 BYTE),
INTERNALUSEONLY NUMBER,
STATUSMESSAGE VARCHAR2(1000 BYTE),
VERSIONNUMBER VARCHAR2(64 BYTE),
ORDERACTIONREFERENCENUMBER VARCHAR2(64 BYTE),
EXTERNALORDERNUMBER VARCHAR2(32 BYTE),
PRODUCTTYPE VARCHAR2(32 BYTE),
SUPPLEMENTAL LOG GROUP GGS_67874 (ID) ALWAYS,
SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,
SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS,
SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS
)
TABLESPACE JBBOS_DAT
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
CREATE UNIQUE INDEX NIMBUS.HISTORYMESSAGE_PK ON NIMBUS.HISTORYMESSAGE
(ID)
TABLESPACE JBBOS_DAT
PCTFREE 20
INITRANS 10
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
GLOBAL PARTITION BY HASH (ID) (
PARTITION ID_01
TABLESPACE JBBOS_IDX,
PARTITION ID_02
TABLESPACE JBBOS_IDX,
PARTITION ID_03
TABLESPACE JBBOS_IDX,
PARTITION ID_04
TABLESPACE JBBOS_IDX,
PARTITION ID_05
TABLESPACE JBBOS_IDX,
PARTITION ID_06
TABLESPACE JBBOS_IDX,
PARTITION ID_07
TABLESPACE JBBOS_IDX,
PARTITION ID_08
TABLESPACE JBBOS_IDX,
PARTITION ID_09
TABLESPACE JBBOS_IDX,
PARTITION ID_10
TABLESPACE JBBOS_IDX,
PARTITION ID_11
TABLESPACE JBBOS_IDX,
PARTITION ID_12
TABLESPACE JBBOS_IDX,
PARTITION ID_13
TABLESPACE JBBOS_IDX,
PARTITION ID_14
TABLESPACE JBBOS_IDX,
PARTITION ID_15
TABLESPACE JBBOS_IDX,
PARTITION ID_16
TABLESPACE JBBOS_IDX
);
CREATE INDEX NIMBUS.I_HSTRMSG_BANNUMBER ON NIMBUS.HISTORYMESSAGE
(BANNUMBER)
TABLESPACE JBBOS_DAT
PCTFREE 20
INITRANS 10
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
GLOBAL PARTITION BY HASH (BANNUMBER) (
PARTITION I_HSTRMSG_BANNUMBER_01
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_BANNUMBER_02
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_BANNUMBER_03
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_BANNUMBER_04
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_BANNUMBER_05
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_BANNUMBER_06
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_BANNUMBER_07
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_BANNUMBER_08
TABLESPACE JBBOS_IDX
);
CREATE INDEX NIMBUS.I_HSTRMSG_PRODUCTTYPE ON NIMBUS.HISTORYMESSAGE
(PRODUCTTYPE)
LOGGING
TABLESPACE JBBOS_DAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);
CREATE INDEX NIMBUS.I_HSTRMSG_SOERRID ON NIMBUS.HISTORYMESSAGE
(SERVICEORDERERROR_ID)
TABLESPACE JBBOS_DAT
PCTFREE 20
INITRANS 10
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
GLOBAL PARTITION BY HASH (SERVICEORDERERROR_ID) (
PARTITION I_HSTRMSG_SOERRID_01
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOERRID_02
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOERRID_03
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOERRID_04
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOERRID_05
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOERRID_06
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOERRID_07
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOERRID_08
TABLESPACE JBBOS_IDX
);
CREATE INDEX NIMBUS.I_HSTRMSG_SONUM ON NIMBUS.HISTORYMESSAGE
(SERVICEORDERNUMBER)
TABLESPACE JBBOS_DAT
PCTFREE 20
INITRANS 10
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
GLOBAL PARTITION BY HASH (SERVICEORDERNUMBER) (
PARTITION I_HSTRMSG_SONUM_01
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SONUM_02
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SONUM_03
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SONUM_04
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SONUM_05
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SONUM_06
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SONUM_07
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SONUM_08
TABLESPACE JBBOS_IDX
);
CREATE INDEX NIMBUS.I_HSTRMSG_SOVID ON NIMBUS.HISTORYMESSAGE
(SERVICEORDERVERSION_ID)
TABLESPACE JBBOS_DAT
PCTFREE 20
INITRANS 10
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
GLOBAL PARTITION BY HASH (SERVICEORDERVERSION_ID) (
PARTITION I_HSTRMSG_SOVID_01
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOVID_02
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOVID_03
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOVID_04
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOVID_05
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOVID_06
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOVID_07
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_SOVID_08
TABLESPACE JBBOS_IDX
);
CREATE INDEX NIMBUS.I_HSTRMSG_TASKID ON NIMBUS.HISTORYMESSAGE
(TASKID)
TABLESPACE JBBOS_DAT
PCTFREE 20
INITRANS 10
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
GLOBAL PARTITION BY HASH (TASKID) (
PARTITION I_HSTRMSG_TASKID_01
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_TASKID_02
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_TASKID_03
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_TASKID_04
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_TASKID_05
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_TASKID_06
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_TASKID_07
TABLESPACE JBBOS_IDX,
PARTITION I_HSTRMSG_TASKID_08
TABLESPACE JBBOS_IDX
);
GRANT SELECT ON NIMBUS.HISTORYMESSAGE TO APPLICATION_USER;
select
table_name,round(((blocks*8)/1024/1024),2) "size
(gb)" ,
round(((num_rows*avg_row_len/1024))/1024/1024,2) "actual_data (gb)",
round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2) "wasted_space
(gb)",
round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) "reclaimable space %",
partitioned
from
dba_tables
where table_name='HISTORYMESSAGE' and
(round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;
No comments:
Post a Comment