DBMS redefinition

Posted by Roshan on Feb 17, 2025; 12:26pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/DBMS-redefinition-tp12920.html

Hello Erman,

could you please advise why DBMS REDEFINITION START hangs at 71%?

SOURCE DDL:

CREATE TABLE "T24PROD"."FBNK_AC_C002"
( "RECID" VARCHAR2(255 BYTE),
"XMLRECORD" "SYS"."XMLTYPE" ,
"CHARGE_DATE" NUMBER GENERATED ALWAYS AS (CAST(EXTRACTVALUE(SYS_MAKEXML("SYS_NC00003$"),'/row/c4') AS number)) VIRTUAL
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 24 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"
XMLTYPE COLUMN "XMLRECORD" STORE AS SECUREFILE CLOB "LOB_FBNK_AC_C002"(
TABLESPACE "T24PRODLOB" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
CREATE UNIQUE INDEX "T24PROD"."FBNK_AC_C002_PK" ON "T24PROD"."FBNK_AC_C002" ("RECID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
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 "T24PRODINDEX"
PARALLEL 40 ;
ALTER TABLE "T24PROD"."FBNK_AC_C002" ADD CONSTRAINT "FBNK_AC_C002_PK" PRIMARY KEY ("RECID")
USING INDEX "T24PROD"."FBNK_AC_C002_PK" ENABLE;

CREATE INDEX "T24PROD"."NIX_FBNK_AC_C002_C4" ON "T24PROD"."FBNK_AC_C002" ("CHARGE_DATE", 1)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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 "T24PRODINDEX" ;


GRANT SELECT ON "T24PROD"."FBNK_AC_C002" TO "T24_READ";


TARGET DDL:

CREATE TABLE "T24PROD"."FBNK_AC_C002B"
( "RECID" VARCHAR2(255 BYTE),
"XMLRECORD" "SYS"."XMLTYPE"
) 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"
XMLTYPE COLUMN "XMLRECORD" STORE AS SECUREFILE CLOB "LOB_FBNK_AC_C002B"(
TABLESPACE "T24PRODLOBHIS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

No locks present on DB.
redef_output.html
Regards,
ROshan