Avatar of gotetioracle
gotetioracle
 asked on

Adding a collumn to partition table

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
Oracle Database

Avatar of undefined
Last Comment
Swadhin Ray

8/22/2022 - Mon
Swadhin Ray

Its same as what we do for adding a column on a simple table. Try like below:

 SQL> alter table add <<column>> <<data type>>
gotetioracle

ASKER
@ To slobaray: If i want to add partions to this collumns also will it be possible.
Swadhin Ray

Yes you can..
Your help has saved me hundreds of hours of internet surfing.
fblack61
gotetioracle

ASKER
@slobaray Can u provide any reference document if you can
ASKER CERTIFIED SOLUTION
Swadhin Ray

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question