Convert Partitioned Index to Regular Index

From: David Barbour <david.barbour1_at_gmail.com>
Date: Mon, 26 Dec 2016 07:50:16 -0600
Message-ID: <CAFH+ifeiccOPwbad3tcH790V7NQZ7Cs7aXmGsCvhuP8Z9aEw7Q_at_mail.gmail.com>

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"

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 26 2016 - 14:50:16 CET