Je suis actuellement sur oracle 10.2.0.3 ...

J'avais la licence me permettant de partitionner mes tables volumineuses sur plusieurs tablespaces.

Il est question de migrer vers la 11g sur laquelle je n'ai plus cette possibilité de partitionnement. Je dois donc me résoudre à regrouper l'ensemble de mes partitions et sous-partitions (organisées sur des tbs séparés) en tables normales sur un seul tablespace ..

Ma question est simple et je n'ai pas trouvé vraiment la réponse sur le forum qui me permette de résoudre mon problème.

Je vous livre le code qui m'a permis de créer les tbs, les partitions , les subpartitions , les tables , les index ..

Comment dois-je procéder pour opérer le regroupement de mes partitions-subpartitions, enfin de mes tables partitionnées sur un seul tablespace ?

Merci pour toute aide ou indice ...

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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) ;