troubleshooting Question

Adding a collumn to partition table

Avatar of gotetioracle
gotetioracle asked on
Oracle Database
5 Comments1 Solution588 ViewsLast Modified:
Hi ,

I have a existing partition table for which i am adding a collumn to that table.
Can i add the add the collumn as we add to normal table or will there be any special actions to be  taken.

Kindly go through the table script below:


create table REP_YIELD_DATA_SUMMARY
(
  location_id      NUMBER,
  vcn_no           VARCHAR2(50),
  effect_date      DATE,
  activity_id      NUMBER,
  resource_id      NUMBER,
  revenue_id       NUMBER,
  expense_id       NUMBER,
  product_id       NUMBER,
  revenue          NUMBER,
  expense          NUMBER,
  col1             VARCHAR2(50),
  col2             VARCHAR2(50),
  col3             VARCHAR2(50),
  col4             VARCHAR2(50),
  col5             VARCHAR2(50),
  col6             VARCHAR2(50),
  col7             VARCHAR2(50),
  col8             VARCHAR2(50),
  col9             VARCHAR2(50),
  col10            VARCHAR2(50),
  col11            VARCHAR2(50),
  col12            NUMBER,
  revenuename      VARCHAR2(200),
  activityname     VARCHAR2(200),
  resourcename     VARCHAR2(200),
  expensename      VARCHAR2(200),
  productname      VARCHAR2(100),
  location_code    VARCHAR2(10),
  exchange_rate    NUMBER,
  activity_time    NUMBER,
  activity_count   NUMBER
)
partition by range (EFFECT_DATE)
subpartition by list (LOCATION_ID)
(
  partition PARTJAN2010 values less than (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTJAN2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTJAN2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTJAN2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTJAN2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTJAN2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTFEB2010 values less than (TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTFEB2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTFEB2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTFEB2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTFEB2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTFEB2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTMAR2010 values less than (TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTMAR2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTMAR2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTMAR2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTMAR2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTMAR2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTAPR2010 values less than (TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTAPR2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTAPR2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTAPR2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTAPR2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTAPR2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTMAY2010 values less than (TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTMAY2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTMAY2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTMAY2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTMAY2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTMAY2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTJUN2010 values less than (TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTJUN2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTJUN2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTJUN2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTJUN2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTJUN2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTJUL2010 values less than (TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTJUL2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTJUL2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTJUL2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTJUL2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTJUL2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTAUG2010 values less than (TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTAUG2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTAUG2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTAUG2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTAUG2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTAUG2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTSEP2010 values less than (TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTSEP2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTSEP2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTSEP2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTSEP2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTSEP2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTOCT2010 values less than (TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTOCT2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTOCT2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTOCT2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTOCT2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTOCT2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTNOV2010 values less than (TO_DATE(' 2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTNOV2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTNOV2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTNOV2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTNOV2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTNOV2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTDEC2010 values less than (TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTDEC2010_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTDEC2010_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTDEC2010_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTDEC2010_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTDEC2010_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTJAN2011 values less than (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTJAN2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTJAN2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTJAN2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTJAN2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTJAN2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTFEB2011 values less than (TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTFEB2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTFEB2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTFEB2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTFEB2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTFEB2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTMAR2011 values less than (TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTMAR2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTMAR2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTMAR2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTMAR2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTMAR2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTAPR2011 values less than (TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTAPR2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTAPR2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTAPR2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTAPR2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTAPR2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTMAY2011 values less than (TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTMAY2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTMAY2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTMAY2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTMAY2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTMAY2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTJUN2011 values less than (TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTJUN2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTJUN2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTJUN2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTJUN2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTJUN2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTJUL2011 values less than (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTJUL2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTJUL2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTJUL2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTJUL2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTJUL2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTAUG2011 values less than (TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTAUG2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTAUG2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTAUG2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTAUG2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTAUG2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTSEP2011 values less than (TO_DATE(' 2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTSEP2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTSEP2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTSEP2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTSEP2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTSEP2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTOCT2011 values less than (TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTOCT2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTOCT2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTOCT2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTOCT2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTOCT2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTNOV2011 values less than (TO_DATE(' 2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTNOV2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTNOV2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTNOV2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTNOV2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTNOV2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTDEC2011 values less than (TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTDEC2011_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTDEC2011_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTDEC2011_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTDEC2011_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTDEC2011_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  ),
  partition PARTDEFAULT values less than (MAXVALUE)
    tablespace BRIDGES
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PARTDEFAULT_PART_NSICT values (36) tablespace BRIDGESIDX,
    subpartition PARTDEFAULT_PART_MICT values (50) tablespace BRIDGESIDX,
    subpartition PARTDEFAULT_PART_CCT values (49) tablespace BRIDGESIDX,
    subpartition PARTDEFAULT_PART_CSCT values (52) tablespace BRIDGESIDX,
    subpartition PARTDEFAULT_PART_DEFAULT values (DEFAULT) tablespace BRIDGESIDX
  )
);
-- Create/Recreate indexes
create index IDX_LOC on REP_YIELD_DATA_SUMMARY (LOCATION_ID)
  tablespace BRIDGESIDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 416M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_LOC_CODE on REP_YIELD_DATA_SUMMARY (LOCATION_CODE)
  tablespace BRIDGESIDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 480M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_RYDS_EXPENSE on REP_YIELD_DATA_SUMMARY (EXPENSENAME)
  tablespace BRIDGESIDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1088M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_RYDS_RESOURCE on REP_YIELD_DATA_SUMMARY (RESOURCENAME)
  tablespace BRIDGESIDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 560M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_RYDS_REVENUE on REP_YIELD_DATA_SUMMARY (REVENUENAME)
  tablespace BRIDGESIDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 20M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IDX_RYDS_VCN on REP_YIELD_DATA_SUMMARY (VCN_NO)
  tablespace BRIDGESIDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 584M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index REP_SUMMARY_INDX on REP_YIELD_DATA_SUMMARY (EFFECT_DATE, ACTIVITY_ID)
  tablespace BRIDGESIDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 649M
    next 1M
    minextents 1
    maxextents unlimited
  );



Regards,
GSK
ASKER CERTIFIED SOLUTION
Swadhin Ray

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros