OracleDBAtalk: online_redefinitionLOB_Compression_F1

select OWNER,TABLE_NAME,COLUMN_NAME, segment_name from dba_lobs;

select owner,segment_name,bytes/1024/1024/1024 Gb from dba_segments where segment_name like '%LOB%' order by GB desc ;

select segment_name,bytes/1024/1024/1024 Gb from dba_segments order by GB desc;

select OWNER,TABLE_NAME,COLUMN_NAME, segment_name from dba_lobs where table_name = 'NODE_INFO_ARCHIVE_DAILY_STAGE';

select * from dba_lobs where table_name = 'NODE_INFO_ARCHIVE_DAILY_STAGE';

select sum(DBMS_LOB.GETLENGTH(RESPONSE))/1024/1024/1024 Gb from TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE;--3.88 GB

select bytes/1024/1024/1024 Gb from dba_segments where segment_name= 'SYS_LOB0000676148C00006$$';--7.918 GB

set timing on;

BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE('TOPOLOGY_RPT','NODE_INFO_ARCHIVE_DAILY_STAGE',

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 TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE_R AS SELECT * FROM TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE 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 dba_constraints where table_name ='NODE_INFO_ARCHIVE_DAILY_STAGE_R';

SELECT COUNT(*) FROM TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE_R ;

COUNT(*)

----------

--START REDEFINITION

set timing on;

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('TOPOLOGY_RPT','NODE_INFO_ARCHIVE_DAILY_STAGE', 'NODE_INFO_ARCHIVE_DAILY_STAGE_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('TOPOLOGY_RPT','NODE_INFO_ARCHIVE_DAILY_STAGE', 'NODE_INFO_ARCHIVE_DAILY_STAGE_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

Select * from DBA_REDEFINITION_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 TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE_R ;

set timing on;

SELECT COUNT(*) FROM TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE ;

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('TOPOLOGY_RPT','NODE_INFO_ARCHIVE_DAILY_STAGE','NODE_INFO_ARCHIVE_DAILY_STAGE_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 ('TOPOLOGY_RPT','NODE_INFO_ARCHIVE_DAILY_STAGE','NODE_INFO_ARCHIVE_DAILY_STAGE_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='TOPOLOGY_RPT' and object_name like '%NODE_INFO_ARCHIVE_DAILY_STAGE%';

HT_NODE_INFO_ARCHIVE_DAILY_STAGE_WL TABLE VALID

HT_NODE_INFO_ARCHIVE_DAILY_STAGE_WF TABLE VALID

HT_NODE_INFO_ARCHIVE_DAILY_STAGE_ST TABLE VALID

HT_NODE_INFO_ARCHIVE_DAILY_STAGE_RA TABLE VALID

HT_NODE_INFO_ARCHIVE_DAILY_STAGE_OM TABLE VALID

HT_NODE_INFO_ARCHIVE_DAILY_STAGE_CS TABLE VALID

HT_NODE_INFO_ARCHIVE_DAILY_STAGE TABLE VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_WLL TABLE VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_WFM TABLE PARTITION VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_WFM TABLE VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_STALE TABLE PARTITION VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_STALE TABLE VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_SAL TABLE PARTITION VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_SAL TABLE VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_R TABLE VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_RAW TABLE VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_OMS TABLE PARTITION VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_OMS TABLE VALID

NODE_INFO_ARCHIVE_DAILY_STAGE_CSINM TABLE VALID

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

order by 1,2;

A close up of a computer screen

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('TOPOLOGY_RPT') ;

Gather table stats:

exec DBMS_STATS.GATHER_TABLE_STATS('TOPOLOGY_RPT', 'NODE_INFO_ARCHIVE_DAILY_STAGE', 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='TOPOLOGY_RPT' and object_name='NODE_INFO_ARCHIVE_DAILY_STAGE';

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

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

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

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='TOPOLOGY_RPT' and table_name like 'NODE_INFO_ARCHIVE_DAILY_STAGE';

Table

Description automatically generated

select * from dba_log_group_columns where owner='TOPOLOGY_RPT' and table_name like 'NODE_INFO_ARCHIVE_DAILY_STAGE';

Text, table

Description automatically generated

--Count of indexes,triggers,synonyms and constraints

select index_name from user_indexes where table_name='NODE_INFO_ARCHIVE_DAILY_STAGE';

select trigger_name from user_triggers where table_name='NODE_INFO_ARCHIVE_DAILY_STAGE';

select constraint_name from user_constraints where table_name='NODE_INFO_ARCHIVE_DAILY_STAGE';

select synonym_name from user_synonyms where table_name='NODE_INFO_ARCHIVE_DAILY_STAGE';

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 TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE_R;

________________________________________________________________

--NODE_INFO_ARCHIVE_DAILY_STAGE script in ST6

ALTER TABLE TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE

DROP PRIMARY KEY CASCADE;

DROP TABLE TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE CASCADE CONSTRAINTS;

CREATE TABLE TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE

(

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 TOPOLOGY_RPT.I_DDRSTYP_ADDRESSINFO_ID ON TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE

(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 TOPOLOGY_RPT.I_DDRSTYP_ELEMENT ON TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE

(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 TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE ADD (

PRIMARY KEY

(CASSADDRESSLINES_ID)

USING INDEX TOPOLOGY_RPT.I_DDRSTYP_ELEMENT

ENABLE VALIDATE);

GRANT SELECT ON TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE TO APPLICATION_USER;

OracleDBAtalk

Saturday, February 15, 2025

online_redefinitionLOB_Compression_F1_NDC.docx

No comments:

Post a Comment

FIG project queries

Report Abuse