OracleDBAtalk: Online_redefinition_reorg_steps_External_Interfacehistory

--Ensure to take backup of table EXTERNALINTERFACEHISTORY in POrod 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 = EXTERNALINTERFACEHIST_20220803_%U.dmp filesize=10G TABLES=EXTERNALINTERFACEHISTORY PARALLEL=8 LOGFILE = EXTERNALINTERFACEHIST_20220803.log COMPRESSION=ALL &

-- Ensure to copy the table script using toad. In ST6 oit 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;

col "Tablespace" for a22
col "Used GB" for 99,999,999
col "Free GB" for 99,999,999
col "Total GB" for 99,999,999i
set pages 50

select df.tablespace_name "Tablespace",
totalusedspace "Used GB",
(df.totalspace - tu.totalusedspace) "Free GB",
df.totalspace "Total GB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1073741824 ) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name order by df.tablespace_name;

TABLESPACE_NAME TOTAL USED FREE PCT

------------------------------ ---------- ---------- ---------- ----------

GGS_DATA 30 .000976563 29.9990234 .003255208

JBBOS_ARCHIVE 255 33.0126953 221.987305 12.9461550

JBBOS_DAT 4770 3147.12958 1622.87042 65.9775593

JBBOS_DAT_1 752.5 74.8552246 677.644775 9.94753815

JBBOS_IDX 1233.75 625.017944 608.732056 50.6600158

SYSAUX 30 19.2545166 10.7454834 64.1817220

SYSTEM 30 1.02416992 28.9758301 3.41389974

TEMP 120 120 0 100

TOOLS 30 .000976563 29.9990234 .003255208

UNDOTBS1 60 50.2439575 9.75604248 83.7399292

UNDOTBS2 60 41.4337769 18.5662231 69.0562948

UNDOTBS3 60 59.5176392 .482360840 99.1960653

UNDOTBS4 90 86.2666016 3.73339844 95.8517795

UNDOTBS5 60 59.5672607 .432739258 99.2787679

UNDOTBS6 60 35.0135498 24.9864502 58.3559163

USERS 30 .000976563 29.9990234 .003255208

VENUS_DAT 90 69.9528809 20.0471191 77.7254232

--Count of indexes,triggers,synonyms and constraints

set timing on;

select index_name from user_indexes where table_name='EXTERNALINTERFACEHISTORY';

select trigger_name from user_triggers where table_name='EXTERNALINTERFACEHISTORY';

select constraint_name from user_constraints where table_name='EXTERNALINTERFACEHISTORY';

select synonym_name from user_synonyms where table_name='EXTERNALINTERFACEHISTORY';

INDEX_NAME

--------------------------------------------------------------------------------

PK_EIH_BLOB

I_EIH_BLOB_TASKID

I_EIH_BLOB_ORDERNUMBER

I_EIH_BAN

SYS_IL0000526408C00011$$

5 rows selected.

no rows selected.

CONSTRAINT_NAME

--------------------------------------------------------------------------------

PK_EIH_ID

SYS_C00291276

2 rows selected.

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='EXTERNALINTERFACEHISTORY' and

(round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))

order by 4 desc;

--Gather table stats:

exec DBMS_STATS.GATHER_TABLE_STATS('NIMBUS', 'EXTERNALINTERFACEHISTORY', degree=>6, method_opt=> 'for all columns size 1', granularity => 'AUTO', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE=>TRUE);

--Check the Table size and Index size

--Table and Index size output before REORG :-

select segment_name,TABLESPACE_NAME ,segment_type,to_char( bytes/1024/1024/1024) size_gb from user_segments where segment_name = 'EXTERNALINTERFACEHISTORY' or

segment_name in (select index_name from user_indexes where table_name='EXTERNALINTERFACEHISTORY');

SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE SIZE_GB

EXTERNALINTERFACEHISTORY JBBOS_DAT TABLE 9.23828125

I_EIH_BAN JBBOS_IDX INDEX .125

I_EIH_BLOB_ORDERNUMBER JBBOS_IDX INDEX .2265625

I_EIH_BLOB_TASKID JBBOS_IDX INDEX .5

PK_EIH_BLOB JBBOS_IDX INDEX .1484375

SYS_IL0000526408C00011$$ JBBOS_DAT_1 LOBINDEX .00006103515625

--Last analyzed

SELECT LAST_ANALYZED FROM user_TABLES WHERE TABLE_NAME IN 'EXTERNALINTERFACEHISTORY';

Text

Description automatically generated with medium confidence

--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 'EXTERNALINTERFACEHISTORY'

order by 1,2;

A picture containing table

Description automatically generated

2nd Method: Redifinition:

Privileges Required for the DBMS_REDEFINITION Package

Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:

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;

BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE('NIMBUS','EXTERNALINTERFACEHISTORY',

DBMS_REDEFINITION.CONS_USE_PK);

END;

--If table is candidate then it will return PL/SQL procedure successfully completed otherwise it raises an error.

set timing on;

alter session force parallel dml parallel 8;

alter session force parallel query parallel 8;

Session altered.

Elapsed: 00:00:01.52

Session altered.

Elapsed: 00:00:00.40

set timing on;

CREATE TABLE EXTERNALINTERFACEHISTORY_REORG AS SELECT * FROM EXTERNALINTERFACEHISTORY WHERE 1=2 ;

Table created.

Elapsed: 00:00:00.52

--Not Null constraint should not be created and dropped if created.

--alter table EXTERNALINTERFACEHISTORY_REORG drop constraint SYS_C00293456

SELECT COUNT(*) FROM EXTERNALINTERFACEHISTORY_REORG ;

COUNT(*)

----------

--START REDEFINITION

set timing on;

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('NIMBUS','EXTERNALINTERFACEHISTORY', 'EXTERNALINTERFACEHISTORY_REORG');

PL/SQL procedure successfully completed.

Elapsed: 00:09:38.16

--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','EXTERNALINTERFACEHISTORY', 'EXTERNALINTERFACEHISTORY_REORG',

DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, N);

END;

PL/SQL procedure successfully completed.

Elapsed: 00:05:56.59

--CHECK FOR ERRORS

no rows selected.

Elapsed: 00:00:00.20

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.EXTERNALINTERFACEHISTORY_REORG ;

set timing on;

SELECT COUNT(*) FROM NIMBUS.EXTERNALINTERFACEHISTORY ;

COUNT(*)

----------

6696523

1 row selected.

Elapsed: 00:00:05.92

COUNT(*)

----------

6696523

1 row selected.

Elapsed: 00:00:00.17

set timing on;

EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('NIMBUS','EXTERNALINTERFACEHISTORY','EXTERNALINTERFACEHISTORY_REORG');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.61--FINISH THE REDEFINITION

The following finishes the redefinition:

set timing on;

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('NIMBUS','EXTERNALINTERFACEHISTORY','EXTERNALINTERFACEHISTORY_REORG');

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.23

‘SQL>

COLUMN OBJECT_NAME FORMAT A40

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='NIMBUS' and object_name like '%EXTERNALINTERFACEHISTORY%';

HT_EXTERNALINTERFACEHISTORY_WL TABLE VALID

HT_EXTERNALINTERFACEHISTORY_WF TABLE VALID

HT_EXTERNALINTERFACEHISTORY_ST TABLE VALID

HT_EXTERNALINTERFACEHISTORY_RA TABLE VALID

HT_EXTERNALINTERFACEHISTORY_OM TABLE VALID

HT_EXTERNALINTERFACEHISTORY_CS TABLE VALID

HT_EXTERNALINTERFACEHISTORY TABLE VALID

EXTERNALINTERFACEHISTORY_WLL TABLE VALID

EXTERNALINTERFACEHISTORY_WFM TABLE PARTITION VALID

EXTERNALINTERFACEHISTORY_WFM TABLE VALID

EXTERNALINTERFACEHISTORY_STALE TABLE PARTITION VALID

EXTERNALINTERFACEHISTORY_STALE TABLE VALID

EXTERNALINTERFACEHISTORY_SAL TABLE PARTITION VALID

EXTERNALINTERFACEHISTORY_SAL TABLE VALID

EXTERNALINTERFACEHISTORY_REORG TABLE VALID

EXTERNALINTERFACEHISTORY_RAW TABLE VALID

EXTERNALINTERFACEHISTORY_OMS TABLE PARTITION VALID

EXTERNALINTERFACEHISTORY_OMS TABLE VALID

EXTERNALINTERFACEHISTORY_CSINM TABLE VALID

EXTERNALINTERFACEHISTORY TABLE VALID

--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 'EXTERNALINTERFACEHISTORY'

order by 1,2;

Graphical user interface, text, application

Description automatically generated

--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:

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='NIMBUS' and object_name='EXTERNALINTERFACEHISTORY';

--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

(round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and table_name like 'EXTERNALINTERFACEHISTORY'

order by 4 desc; Graphical user interface, text, application, email

Description automatically generated

--Table and Index size output after REORG :-

select segment_name,TABLESPACE_NAME ,segment_type, to_char(bytes/1024/1024/1024) size_gb from user_segments where segment_name = 'EXTERNALINTERFACEHISTORY' or

segment_name in (select index_name from user_indexes where table_name='EXTERNALINTERFACEHISTORY');

SYS_IL0000673317C00011$$ JBBOS_DAT LOBINDEX .00006103515625

I_EIH_BLOB_TASKID JBBOS_IDX INDEX .3359375

EXTERNALINTERFACEHISTORY JBBOS_DAT TABLE 10.50323486328125

I_EIH_BLOB_ORDERNUMBER JBBOS_IDX INDEX .15625

I_EIH_BAN JBBOS_IDX INDEX .0859375

PK_EIH_BLOB JBBOS_IDX INDEX .1328125

--Count of indexes,triggers,synonyms and constraints

select index_name from user_indexes where table_name='EXTERNALINTERFACEHISTORY';

select trigger_name from user_triggers where table_name='EXTERNALINTERFACEHISTORY';

select constraint_name from user_constraints where table_name='EXTERNALINTERFACEHISTORY';

select synonym_name from user_synonyms where table_name='EXTERNALINTERFACEHISTORY';

INDEX_NAME

--------------------------------------------------------------------------------

SYS_IL0000673317C00011$$

I_EIH_BAN

I_EIH_BLOB_ORDERNUMBER

I_EIH_BLOB_TASKID

PK_EIH_BLOB

5 rows selected.

no rows selected.

CONSTRAINT_NAME

--------------------------------------------------------------------------------

SYS_C00291276

PK_EIH_ID

2 rows selected.

no rows selected.

--DROP THE INTERIM TABLE

DROP TABLE NIMBUS.EXTERNALINTERFACEHISTORY_REORG;

________________________________________________________________

--EXTERNALINTERFACEHISTORY script in ST6

ALTER TABLE NIMBUS.EXTERNALINTERFACEHISTORY

DROP PRIMARY KEY CASCADE;

DROP TABLE NIMBUS.EXTERNALINTERFACEHISTORY CASCADE CONSTRAINTS;

CREATE TABLE NIMBUS.EXTERNALINTERFACEHISTORY

(

ID NUMBER NOT NULL,

EMBUSMESSAGETAG VARCHAR2(255 BYTE),

EXTERNALORDERNUMBER VARCHAR2(255 BYTE),

INITIATOR NUMBER,

INTERFACETYPE NUMBER,

MESSAGETAG VARCHAR2(255 BYTE),

MESSAGETYPE VARCHAR2(255 BYTE),

SYSTEMID VARCHAR2(255 BYTE),

ORDERNUMBER VARCHAR2(255 BYTE),

ORDERVERSION VARCHAR2(255 BYTE),

REQUESTREPLY BLOB,

TASKDEFINITIONID VARCHAR2(255 BYTE),

TASKID VARCHAR2(255 BYTE),

T_TIMESTAMP TIMESTAMP(6),

WORKFLOWTASKID VARCHAR2(255 BYTE),

VERSION NUMBER,

ORDERACTIONREFERENCENUMBER VARCHAR2(255 BYTE),

BAN VARCHAR2(255 BYTE),

PRIORITY NUMBER,

ERRORCODE VARCHAR2(255 BYTE),

KEYIDENTIFIER VARCHAR2(255 BYTE)

)

LOB (REQUESTREPLY) STORE AS SECUREFILE LOB_EIH_BLOB_1507 (

TABLESPACE JBBOS_DAT_1

ENABLE STORAGE IN ROW

CHUNK 8192

COMPRESS MEDIUM

NOCACHE

LOGGING

STORAGE (

INITIAL 104K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

))

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 INDEX NIMBUS.I_EIH_BAN ON NIMBUS.EXTERNALINTERFACEHISTORY

(BAN)

LOGGING

TABLESPACE JBBOS_IDX

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_EIH_BLOB_ORDERNUMBER ON NIMBUS.EXTERNALINTERFACEHISTORY

(ORDERNUMBER)

LOGGING

TABLESPACE JBBOS_IDX

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_EIH_BLOB_TASKID ON NIMBUS.EXTERNALINTERFACEHISTORY

(TASKID)

LOGGING

TABLESPACE JBBOS_IDX

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

);

CREATE UNIQUE INDEX NIMBUS.PK_EIH_BLOB ON NIMBUS.EXTERNALINTERFACEHISTORY

(ID)

LOGGING

TABLESPACE JBBOS_IDX

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

);

CREATE OR REPLACE PUBLIC SYNONYM EXTERNALINTERFACEHISTORY FOR NIMBUS.EXTERNALINTERFACEHISTORY;

ALTER TABLE NIMBUS.EXTERNALINTERFACEHISTORY ADD (

CONSTRAINT PK_EIH_ID

PRIMARY KEY

(ID)

USING INDEX NIMBUS.PK_EIH_BLOB

ENABLE VALIDATE);

GRANT SELECT ON NIMBUS.EXTERNALINTERFACEHISTORY 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='EXTERNALINTERFACEHISTORY' and

(round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))

order by 4 desc;

OracleDBAtalk

Saturday, February 15, 2025

Online_redefinition_reorg_steps_External_Interfacehistory_ST6.docx

No comments:

Post a Comment

FIG project queries

Report Abuse