RBS

[Roshan]

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

[ErmanArslansOracleBlog]

*At the moment, I can't check what you are changing in the definition of that table with this redefinition action.
Please state the reason that you do this for, and what do you want to change in the definition.

*Also we may need to monitor it and see what are the waits, and at what stages is time wasted?

Helpful MOS documents:

Online Redefinition Progress Monitoring view V$ONLINE_REDEF (Doc ID 2185967.1)

[Roshan]

I am doing this activity because the percentage free space on one tablespace for the table is 8%. That's why I had to move the table to another tablespace.

I had a look at your doc. It fits my case.

http://ermanarslan.blogspot.com/2021/08/exadata-x8m-2-pca-x8-2-part-3-migration_13.html

It was helpful after implementing the steps. completed in 40 mins against 12 hours!

Thanks