CODEWITHSUNDEEP
oracle-databaseindexingdatabase-partitioning
AJ01
AJ01

Reputation: 235

Oracle Partitioning Split Partition Issue

We have a VLDB around 2TB and tables are partitioned on each date. We also have index mapping for these partitions. But for year 2019 all the data up to 01-Oct-2019 is getting into a single partition as due to a typo a single sub partition got created with a high value of 01/10/2019.

We are trying to correct this using Split Partition i.e. move data up to 10th Feb 2019 to single partition and then remove the empty partition and create single partitions for each date. This is working fine.

The issue is coming with index mapping as there are mappings already from 1st Oct to 31st Dec and we are not able to drop these. So we were trying to create mapping from 11 Feb till 30 Sep. The script for this is taking a very long time due to volume of data. And also the indexes goes into UNUSABLE state after this and when we try to rebuild it's taking ages!!

Is there any better way we can do it.

CODE SAMPLE-

CREATE TABLE My_Table (
  id             NUMBER(9,0)  NOT NULL,
  source_system  VARCHAR(20),
  eod_date                  NUMBER(9,0)  NOT NULL,
  other columns
  )
  TABLESPACE XYZ001td
  PARTITION BY LIST (source_system)
  SUBPARTITION BY RANGE (
    eod_date
  )
  (
    PARTITION p_XYZ VALUES ('XYZ')
      NOCOMPRESS
      (
        SUBPARTITION XYZ_20181227 VALUES LESS THAN (20181228) TABLESPACE XYZ_20181227_td,
        SUBPARTITION XYZ_20181228 VALUES LESS THAN (20181229) TABLESPACE XYZ_20181228_td,
        SUBPARTITION XYZ_20181229 VALUES LESS THAN (20181230) TABLESPACE XYZ_20181229_td,
        SUBPARTITION XYZ_20181230 VALUES LESS THAN (20181231) TABLESPACE XYZ_20181230_td,
        SUBPARTITION XYZ_20181231 VALUES LESS THAN (20190101) TABLESPACE XYZ_20181231_td
        **SUBPARTITION XYZ_20191001 VALUES LESS THAN (20191002) TABLESPACE XYZ_20191001_td,**
        SUBPARTITION XYZ_20191002 VALUES LESS THAN (20191003) TABLESPACE XYZ_20191002_td,
        SUBPARTITION XYZ_20191003 VALUES LESS THAN (20191004) TABLESPACE XYZ_20191003_td,
      )
   )
/

CREATE INDEX inx_my_table_01
  ON My_table (
    source_system,
    eod_date    
  )
  TABLESPACE XYZ001td
  GLOBAL PARTITION BY HASH (
    source_system,
    eod_date
  )
  (
    PARTITION XYZ_20181227
      TABLESPACE XYZ_20181227_ti
      LOGGING,
    PARTITION XYZ_20181228
      TABLESPACE XYZ_20181228_ti
      LOGGING,
    PARTITION XYZ_20181229
      TABLESPACE XYZ_20181229_ti
      LOGGING,
    PARTITION XYZ_20181230
      TABLESPACE XYZ_20181230_ti
      LOGGING,
    PARTITION XYZ_20181231
      TABLESPACE XYZ_20181231_ti
      LOGGING
  )

Upvotes: 0

Views: 431

Answers (2)

Prajesh
Prajesh

Reputation: 1

We can not drop global hash-partitioned index

ORA-14330: Cannot drop a partition of a global hash-partitioned index

Is there any way to insert intermediate Index mapping without touching existing Index mapping

Upvotes: 0

Hijesh V L
Hijesh V L

Reputation: 269

i am not sure if i completely understand the problem. But i think following steps might help

Create a temp table and copy all 2019 data to that table drop all 2019 partitions and indexes. recreate partitions and re insert the data create local indexes

Upvotes: 0

Related Questions