Good Morning. Also Merry Christmas and Happy New Year for those to whom it
may apply.
Running Oracle EE 11.2.0.3 on RHEL 6.8
We are upgrading our SAP system and there are several tables that are not
partitioned but do have one or more global hash-partitioned indexes.
The upgrade process does some work during an 'uptime' phase, but it's
stopping when it reaches these indexes. In order to proceed with the
upgrade, we need to convert them back to regular indexes. Because of the
activity on these indexes, we need to keep the table/indexes available.
Is dbms_redefinition the way to go with this? Or is there
another/different/better approach?
Here are the current DDL statements for one of the tables involved with the
associated primary key separately described. The index has 6 partitions.
SQL> select dbms_metadata.get_ddl('TABLE','ARFCSSTATE','SAPR3') from dual;
DBMS_METADATA.GET_DDL('TABLE','ARFCSSTATE','SAPR3')
------------------------------------------------------------
--------------------
CREATE TABLE "SAPR3"."ARFCSSTATE"
( "ARFCIPID" VARCHAR2(8) DEFAULT ' ',
"ARFCPID" VARCHAR2(4) DEFAULT ' ',
"ARFCTIME" VARCHAR2(8) DEFAULT ' ',
"ARFCTIDCNT" VARCHAR2(4) DEFAULT ' ',
"ARFCDEST" VARCHAR2(32) DEFAULT ' ',
"ARFCLUWCNT" VARCHAR2(8) DEFAULT ' ',
"ARFCSTATE" VARCHAR2(8) DEFAULT ' ' NOT NULL ENABLE,
"ARFCFNAM" VARCHAR2(30) DEFAULT ' ' NOT NULL ENABLE,
"ARFCRETURN" VARCHAR2(1) DEFAULT ' ' NOT NULL ENABLE,
"ARFCUZEIT" VARCHAR2(6) DEFAULT '000000' NOT NULL ENABLE,
"ARFCDATUM" VARCHAR2(8) DEFAULT '00000000' NOT NULL ENABLE,
"ARFCUSER" VARCHAR2(12) DEFAULT ' ' NOT NULL ENABLE,
"ARFCRETRYS" VARCHAR2(4) DEFAULT '0000' NOT NULL ENABLE,
"ARFCTCODE" VARCHAR2(20) DEFAULT ' ' NOT NULL ENABLE,
"ARFCRHOST" VARCHAR2(8) DEFAULT ' ' NOT NULL ENABLE,
"ARFCMSG" VARCHAR2(50) DEFAULT ' ' NOT NULL ENABLE,
"ARFCRESERV" VARCHAR2(255) DEFAULT ' ' NOT NULL ENABLE,
"HASH" RAW(40),
CONSTRAINT "ARFCSSTATE0" PRIMARY KEY ("ARFCIPID", "ARFCPID",
"ARFCTIME", "ARFC
TIDCNT", "ARFCDEST", "ARFCLUWCNT")
USING INDEX PCTFREE 1 INITRANS 50 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 2
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
<(214)%20748-3645>
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSAPBTABI" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 50 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
<(214)%20748-3645>
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSAPBTABD" ENABLE ROW MOVEMENT
1* select dbms_metadata.get_ddl('INDEX','ARFCSSTATE~0','SAPR3') from dual
SQL> /
DBMS_METADATA.GET_DDL('INDEX','ARFCSSTATE~0','SAPR3')
------------------------------------------------------------
--------------------
CREATE UNIQUE INDEX "SAPR3"."ARFCSSTATE~0" ON "SAPR3"."ARFCSSTATE"
("ARFCIPID"
, "ARFCPID", "ARFCTIME", "ARFCTIDCNT", "ARFCDEST", "ARFCLUWCNT")
PCTFREE 1 INITRANS 50 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 2
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
<(214)%20748-3645>
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSAPBTABI"
