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
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
@ To slobaray: If i want to add partions to this collumns also will it be possible.
Yes you can..
ASKER
@slobaray Can u provide any reference document if you can
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SQL> alter table add <<column>> <<data type>>