1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168
| CREATE TABLESPACE "CDR_DATA2006"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2006.dbf' SIZE
120M AUTOEXTEND ON NEXT 100M MAXSIZE 1200M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CDR_DATA2007"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2007.dbf' SIZE
1000M AUTOEXTEND ON NEXT 100M MAXSIZE 1200M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CDR_DATA2008"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2008.dbf' SIZE
1100M AUTOEXTEND ON NEXT 100M MAXSIZE 1200M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CDR_DATA2009"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2009.dbf' SIZE
500M AUTOEXTEND ON NEXT 100M MAXSIZE 1400M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CDR_DATA2010"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2010.dbf' SIZE
1M AUTOEXTEND ON NEXT 100M MAXSIZE 1200M MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CDR_DATA2011"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2011.dbf' SIZE
1M AUTOEXTEND ON NEXT 100M MAXSIZE 1200M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CDR_DATA2012"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2012.dbf' SIZE
1M AUTOEXTEND ON NEXT 100M MAXSIZE 1200M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CDR_DATA2013"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2013.dbf' SIZE
1M AUTOEXTEND ON NEXT 100M MAXSIZE 1200M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CDR_DATA2014"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2014.dbf' SIZE
1M AUTOEXTEND ON NEXT 100M MAXSIZE 1200M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CDR_DATA2015"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2015.dbf' SIZE
1M AUTOEXTEND ON NEXT 100M MAXSIZE 1200M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "CDR_DATA2016"
LOGGING
DATAFILE '/u01/app/oracle/oradata/ORAMAF/datafile/CDR_DATA2016.dbf' SIZE
1M AUTOEXTEND ON NEXT 100M MAXSIZE 1200M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create table SOLDEARCH (
CPT_GUICHET VARCHAR2(5) not null,
CPT_NUMCPT VARCHAR2(11) not null,
CPT_SENS VARCHAR2(1),
CPT_SOLDE VARCHAR2(14) ,
CPT_DATESOLDE DATE not null,
CPT_SENSCUMDEBIT VARCHAR2(1),
CPT_CUMDEBIT VARCHAR2(16),
CPT_SENSCUMCREDIT VARCHAR2(1),
CPT_CUMCREDIT VARCHAR2(16),
CPT_SENSCUMSOLDE VARCHAR2(1),
CPT_CUMSOLDE VARCHAR2(16),
CPT_DATEMOINS1 DATE,
constraint PK_soldearch primary key (CPT_GUICHET, CPT_NUMCPT,CPT_DATESOLDE )
using index
tablespace CDR_indx,
constraint FK_SOLDEARCH_REFERENCE_COMPTE foreign key (CPT_GUICHET, CPT_NUMCPT)
references COMPTE (CPT_GUICHET, CPT_NUMCPT) on delete cascade
)
PARALLEL ( DEGREE 2)
partition by range (CPT_DATESOLDE)
(partition soldearch_2006 values less than (TO_DATE('01/01/2007','DD/MM/SYYYY')) tablespace cdr_data2006,
partition soldearch_2007 values less than (TO_DATE('01/01/2008','DD/MM/SYYYY')) tablespace cdr_data2007,
partition soldearch_2008 values less than (TO_DATE('01/01/2009','DD/MM/SYYYY')) tablespace cdr_data2008,
partition soldearch_2009 values less than (TO_DATE('01/01/2010','DD/MM/SYYYY'))tablespace cdr_data2009,
partition soldearch_2010 values less than (TO_DATE('01/01/2011','DD/MM/SYYYY')) tablespace cdr_data2010,
partition soldearch_2011 values less than (TO_DATE('01/01/2012','DD/MM/SYYYY')) tablespace cdr_data2011,
partition soldearch_2012 values less than (TO_DATE('01/01/2013','DD/MM/SYYYY')) tablespace cdr_data2012,
partition soldearch_2013 values less than (TO_DATE('01/01/2014','DD/MM/SYYYY')) tablespace cdr_data2013,
partition soldearch_2014 values less than (TO_DATE('01/01/2015','DD/MM/SYYYY')) tablespace cdr_data2014,
partition soldearch_2015 values less than (TO_DATE('01/01/2016','DD/MM/SYYYY')) tablespace cdr_data2015,
partition soldearch_2016 values less than (TO_DATE('01/01/2017','DD/MM/SYYYY')) tablespace cdr_data2016)
/
create index ID_SOLDEARCH_FK on SOLDEARCH (CPT_GUICHET,CPT_NUMCPT
)
tablespace CDR_indx
LOCAL
PARALLEL (DEGREE 2) ;
create table ARCHIVE (
ARCH_GUICHET VARCHAR2(5) not null,
ARCH_CPTE VARCHAR2(11) not null,
ARCH_NUMMVT VARCHAR2(7),
ARCH_SENS VARCHAR2(1) not null,
ARCH_MONTANT VARCHAR2(14) not null,
ARCH_LIBEL VARCHAR2(31),
ARCH_complibel VARCHAR2(70),
ARCH_DVAL DATE,
ID_CODMV VARCHAR2(3) not null,
ID_CODREJ VARCHAR2(3),
ARCH_DATEBDF DATE not null,
ARCH_ANNEE VARCHAR2(4) not null,
ARCH_MOIS VARCHAR2(2) not null,
PK_IDARTICLE VARCHAR2(12) ,
constraint FK_ARCHIVE_REFERENCE_COMPTE foreign key (ARCH_GUICHET, ARCH_CPTE)
references COMPTE (CPT_GUICHET, CPT_NUMCPT),
constraint FK_ARCHIVE_REFERENCE_REJET foreign key (ID_CODREJ)
references REJET (ID_CODREJ),
constraint FK_ARCHIVE_REFERENCE_SOLDEARCH foreign key (ARCH_GUICHET,ARCH_CPTE,ARCH_DATEBDF)
references SOLDEARCH (CPT_GUICHET,CPT_NUMCPT,CPT_DATESOLDE) on delete cascade,
constraint FK_ARCHIVE_REFERENCE_MVPARIS foreign key (ID_CODMV)
references MVPARIS (ID_CODMV)
)
PARALLEL ( DEGREE 2)
partition by range (ARCH_ANNEE)
subpartition by list (ARCH_MOIS)
subpartition template
(subpartition janv values ('01'),
subpartition fev values ('02'),
subpartition mars values ('03'),
subpartition avril values ('04'),
subpartition mai values ('05'),
subpartition juin values ('06'),
subpartition juil values ('07'),
subpartition aout values ('08'),
subpartition sept values ('09'),
subpartition oct values ('10'),
subpartition nov values ('11'),
subpartition dec values ('12'))
(partition arch_2006 values less than (2007) tablespace cdr_data2006,
partition arch_2007 values less than (2008) tablespace cdr_data2007,
partition arch_2008 values less than (2009) tablespace cdr_data2008,
partition arch_2009 values less than (2010) tablespace cdr_data2009,
partition arch_2010 values less than (2011) tablespace cdr_data2010,
partition arch_2011 values less than (2012) tablespace cdr_data2011,
partition arch_2012 values less than (2013) tablespace cdr_data2012,
partition arch_2013 values less than (2014) tablespace cdr_data2013,
partition arch_2014 values less than (2015) tablespace cdr_data2014,
partition arch_2015 values less than (2016) tablespace cdr_data2015,
partition arch_2016 values less than (2017)) tablespace cdr_data2016
/
create index ID_ARCHCODMV_FK on ARCHIVE (
ID_CODMV ASC
)
tablespace CDR_indx
LOCAL
PARALLEL ( DEGREE 2)
/
create index ID_ARCHSOLDE_FK on ARCHIVE (
ARCH_GUICHET,ARCH_CPTE,ARCH_DATEBDF
)
tablespace CDR_indx
LOCAL
PARALLEL ( DEGREE 2)
/
create index ID_ARCHCODREJ_FK on ARCHIVE (
ID_CODREJ ASC
)
tablespace CDR_indx
LOCAL
PARALLEL ( DEGREE 2)
/
create index ID_ARCHCPTE_FK on ARCHIVE (
ARCH_GUICHET,
ARCH_CPTE
)
tablespace CDR_indx
LOCAL
PARALLEL ( DEGREE 2) ; |
Partager