OracleDBAtalk: Online_redefinition_reorg_steps_ADDRESSINFO_ADDRESSLINESTYPE

--Ensure to take backup of table ADDRESSINFO_ADDRESSLINESTYPE 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 = ADDRESSINFO_ADDRESSLINESTYPE_20220804_%U.dmp filesize=10G TABLES=ADDRESSINFO_ADDRESSLINESTYPE PARALLEL=8 LOGFILE = ADDRESSINFO_ADDRESSLINESTYPE_20220804.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='ADDRESSINFO_ADDRESSLINESTYPE';

select trigger_name from user_triggers where table_name='ADDRESSINFO_ADDRESSLINESTYPE';

select constraint_name from user_constraints where table_name='ADDRESSINFO_ADDRESSLINESTYPE';

select synonym_name from user_synonyms where table_name='ADDRESSINFO_ADDRESSLINESTYPE';

INDEX_NAME

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

I_DDRSTYP_ADDRESSINFO_ID

I_DDRSTYP_ELEMENT

2 rows selected.

no rows selected.

CONSTRAINT_NAME

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

SYS_C00288399

1 row selected.

no rows selected

n Check if supplemental logging for GG is enabled:

select * from dba_log_groups where owner='NIMBUS' and table_name like 'ADDRESSINFO_ADDRESSLINESTYPE';

select * from dba_log_group_columns where owner='NIMBUS' and table_name like 'ADDRESSINFO_ADDRESSLINESTYPE';

Text, table

Description automatically generated

--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,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='ADDRESSINFO_ADDRESSLINESTYPE' 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', 'ADDRESSINFO_ADDRESSLINESTYPE', 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 = 'ADDRESSINFO_ADDRESSLINESTYPE' or

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

Table

Description automatically generated

--Last analyzed

SELECT LAST_ANALYZED FROM user_TABLES WHERE TABLE_NAME IN 'ADDRESSINFO_ADDRESSLINESTYPE';

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

order by 1,2;

A picture containing text

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','ADDRESSINFO_ADDRESSLINESTYPE',

DBMS_REDEFINITION.CONS_USE_PK);

END;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.23

--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 ADDRESSINFO_ADDRESSLINESTYPE_R AS SELECT * FROM ADDRESSINFO_ADDRESSLINESTYPE WHERE 1=2 ;

Table created.

Elapsed: 00:00:00.32

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

select constraint_name from user_constraints where table_name ='ADDRESSINFO_ADDRESSLINESTYPE_R'

SELECT COUNT(*) FROM ADDRESSINFO_ADDRESSLINESTYPE_R ;

COUNT(*)

----------

--START REDEFINITION

set timing on;

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('NIMBUS','ADDRESSINFO_ADDRESSLINESTYPE', 'ADDRESSINFO_ADDRESSLINESTYPE_R');

PL/SQL procedure successfully completed.

Elapsed: 00:02:39.00--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','ADDRESSINFO_ADDRESSLINESTYPE', 'ADDRESSINFO_ADDRESSLINESTYPE_R',

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

set timing on;

SELECT COUNT(*) FROM NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE ;

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','ADDRESSINFO_ADDRESSLINESTYPE','ADDRESSINFO_ADDRESSLINESTYPE_R');

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','ADDRESSINFO_ADDRESSLINESTYPE','ADDRESSINFO_ADDRESSLINESTYPE_R');

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 '%ADDRESSINFO_ADDRESSLINESTYPE%';

HT_ADDRESSINFO_ADDRESSLINESTYPE_WL TABLE VALID

HT_ADDRESSINFO_ADDRESSLINESTYPE_WF TABLE VALID

HT_ADDRESSINFO_ADDRESSLINESTYPE_ST TABLE VALID

HT_ADDRESSINFO_ADDRESSLINESTYPE_RA TABLE VALID

HT_ADDRESSINFO_ADDRESSLINESTYPE_OM TABLE VALID

HT_ADDRESSINFO_ADDRESSLINESTYPE_CS TABLE VALID

HT_ADDRESSINFO_ADDRESSLINESTYPE TABLE VALID

ADDRESSINFO_ADDRESSLINESTYPE_WLL TABLE VALID

ADDRESSINFO_ADDRESSLINESTYPE_WFM TABLE PARTITION VALID

ADDRESSINFO_ADDRESSLINESTYPE_WFM TABLE VALID

ADDRESSINFO_ADDRESSLINESTYPE_STALE TABLE PARTITION VALID

ADDRESSINFO_ADDRESSLINESTYPE_STALE TABLE VALID

ADDRESSINFO_ADDRESSLINESTYPE_SAL TABLE PARTITION VALID

ADDRESSINFO_ADDRESSLINESTYPE_SAL TABLE VALID

ADDRESSINFO_ADDRESSLINESTYPE_R TABLE VALID

ADDRESSINFO_ADDRESSLINESTYPE_RAW TABLE VALID

ADDRESSINFO_ADDRESSLINESTYPE_OMS TABLE PARTITION VALID

ADDRESSINFO_ADDRESSLINESTYPE_OMS TABLE VALID

ADDRESSINFO_ADDRESSLINESTYPE_CSINM TABLE VALID

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

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:

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

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

order by 4 desc;

Graphical user interface, application

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 = 'ADDRESSINFO_ADDRESSLINESTYPE' or

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

Table

Description automatically generated

n Check if supplemental logging for GG is enabled (Output shud be like below):

select * from dba_log_groups where owner='NIMBUS' and table_name like 'ADDRESSINFO_ADDRESSLINESTYPE';

Table

Description automatically generated

select * from dba_log_group_columns where owner='NIMBUS' and table_name like 'ADDRESSINFO_ADDRESSLINESTYPE';

Text, table

Description automatically generated

--Count of indexes,triggers,synonyms and constraints

select index_name from user_indexes where table_name='ADDRESSINFO_ADDRESSLINESTYPE';

select trigger_name from user_triggers where table_name='ADDRESSINFO_ADDRESSLINESTYPE';

select constraint_name from user_constraints where table_name='ADDRESSINFO_ADDRESSLINESTYPE';

select synonym_name from user_synonyms where table_name='ADDRESSINFO_ADDRESSLINESTYPE';

INDEX_NAME

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

I_DDRSTYP_ADDRESSINFO_ID

I_DDRSTYP_ELEMENT

2 rows selected.

no rows selected.

CONSTRAINT_NAME

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

SYS_C00288399

1 row selected.

no rows selected

--DROP THE INTERIM TABLE

DROP TABLE NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE_R;

________________________________________________________________

--ADDRESSINFO_ADDRESSLINESTYPE script in ST6

ALTER TABLE NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE

DROP PRIMARY KEY CASCADE;

DROP TABLE NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE CASCADE CONSTRAINTS;

CREATE TABLE NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE

(

ADDRESSINFO_ID NUMBER,

CASSADDRESSLINES_ID NUMBER,

SUPPLEMENTAL LOG GROUP GGS_67809 (CASSADDRESSLINES_ID,ADDRESSINFO_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 INDEX NIMBUS.I_DDRSTYP_ADDRESSINFO_ID ON NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE

(ADDRESSINFO_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 INDEX NIMBUS.I_DDRSTYP_ELEMENT ON NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE

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

);

ALTER TABLE NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE ADD (

PRIMARY KEY

(CASSADDRESSLINES_ID)

USING INDEX NIMBUS.I_DDRSTYP_ELEMENT

ENABLE VALIDATE);

GRANT SELECT ON NIMBUS.ADDRESSINFO_ADDRESSLINESTYPE TO APPLICATION_USER;

OracleDBAtalk

Saturday, February 15, 2025

Online_redefinition_reorg_steps_ADDRESSINFO_ADDRESSLINESTYPE_ST6.docx

No comments:

Post a Comment

FIG project queries

Report Abuse