RBS

Posted by Roshan on Dec 11, 2024; 10:55am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/RBS-tp12838.html

Oracle 19.23

Exadata XM-10

Hello Erman,

redefinition is slow

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
                      uname => 'T24PROD',
                      orig_table => 'FBNK_PROB',
                      int_table => 'FBNK_PROB1',
                      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/


Redefined table:
CREATE TABLE "T24PROD"."FBNK_PROB1"
( "RECID" VARCHAR2(255 BYTE),
"XMLRECORD" BLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 48 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "T24PRODDATAHIS"
LOB ("XMLRECORD") STORE AS SECUREFILE "LOB_FBNK_PROB1"(
TABLESPACE "T24PRODLOB" ENABLE STORAGE IN ROW CHUNK 8192
CACHE NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
INDEX "LOBI_FBNK_PROB1" ( INITRANS 48 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ) ;
GRANT SELECT ON "T24PROD"."FBNK_PROB1" TO "T24_READ";

-- DDL for Table FBNK_PROB
--------------------------------------------------------

CREATE TABLE "T24PROD"."FBNK_PROB"
( "RECID" VARCHAR2(255 BYTE),
"XMLRECORD" BLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "T24PRODDATA"
LOB ("XMLRECORD") STORE AS SECUREFILE "LOB_FBNK_PROB"(
TABLESPACE "T24PRODDATA" ENABLE STORAGE IN ROW CHUNK 8192
CACHE NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
INDEX "LOBI_FBNK_PROB" ( INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ) ;
GRANT SELECT ON "T24PROD"."FBNK_PROB" TO "T24_READ";

Could you please advise how to optimise?

Regards,
Roshan