Partionnement de table à chaud avec DBMS_REDFINITION

**Dajon** · 13/10/2011, 10h53

Bonjour,

Je lutte depuis hier avec un script que je suis entrain de faire afin de réaliser le partitionnement à chaud de plusieurs tables.

Je suis sur:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Je dispose d'une liste de tables à partitionner. Le type de partition est toujours le même, par range de dates, par mois. Seuls la table et le nom de la colonne changent.

Du coup j'ai fait un petit script SQL*Plus générique, mais impossible de le faire fonctionner.

Voila de quoi créer un jeu de test:

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
CREATE TABLE &G_SCHEMA..&g_table 
(
  ID NUMBER NOT NULL 
, MADATE DATE NOT NULL 
, CONSTRAINT &g_table._PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);
 
 
INSERT INTO &G_SCHEMA..&g_table (ID,MADATE) VALUES (1,TO_DATE('12/10/2008 15:58:14','DD/MM/YYYY hh24:mi:ss'));
INSERT INTO &g_schema..&g_table (ID,MADATE) VALUES (2,TO_DATE('12/10/2009 15:58:27','DD/MM/YYYY hh24:mi:ss'));
 
COMMIT;
 
exit

Et voila mon script, qui est appelé par un script shell qui lui passe les bons arguments (nom du schéma, nom de la table, nom de la colonne a utiliser comme clé de partition):

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
whenever sqlerror exit failure
 
set feedback off
set verify off 
set serveroutput on size unlimited
 
define g_schema=&1;
define g_table=&2;
define g_colonne=&3;
define g_table_work=&g_table._WRK
 
Prompt Verification si partitionnement possible pour &g_schema..&g_table ...
BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('&g_schema','&g_table', dbms_redefinition.cons_use_pk);
END;
/
Prompt Verifiaction OK.
 
Prompt Création de la table de travail &g_schema..&g_table_work
 
declare
	l_cnt integer;
begin
	select count(*)
	into l_cnt
	from all_tables
	where owner = '&g_schema'
	and table_name = '&g_table_work';
 
	if l_cnt > 0 then
		execute immediate 'drop table &g_schema..&g_table_work purge';
	end if;
 
end;
/
 
declare
	ltmp varchar2(40);
	strsql varchar2(32000);
begin
	select tablespace_name
	into ltmp
	from all_tables 
	where table_name = '&g_table' 
	and owner = '&g_schema';
 
	strsql := q'#
	CREATE TABLE &g_schema..&g_table_work tablespace #'|| ltmp ||q'#
	PARTITION BY RANGE (&g_colonne) 
	(
	  PARTITION P_190001 VALUES LESS THAN (to_date('190002','YYYYMM')) NOCOMPRESS  
	)
	AS SELECT * FROM &g_schema..&g_table
	WHERE 0=1#';
 
	execute immediate strsql;
 
end;
/
 
 
 
/*
	Création des partitions sur la table de travail
	ON partition suivant la colonne passée en paramètre, par mois
*/
Prompt Creation des partitions sur la table de travail
declare
	l_min_date date;
	l_max_date date;
	l_curr_date date;
	strsql varchar2(32000);
begin
	select min(&g_colonne), max(&g_colonne) into l_min_date, l_max_date from &g_schema..&g_table;
 
	l_curr_date := l_min_date;
 
	while l_curr_date <= l_max_date loop
 
		l_curr_date := add_months(l_curr_date, 1);
 
		strsql := q'#ALTER TABLE &g_schema..&g_table_work ADD PARTITION P_#'||to_char(add_months(l_curr_date,-1),'YYYYMM')||q'# VALUES LESS THAN (to_date('#'|| to_char(l_curr_date,'YYYYMM') ||q'#','YYYYMM')) NOCOMPRESS#';
 
		execute immediate strsql;
 
	end loop;
 
	execute immediate 'ALTER TABLE &g_schema..&g_table_work ADD PARTITION P_MAX VALUES LESS THAN (MAXVALUE) NOCOMPRESS';
 
	exception 
	when others then
		dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK() || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
		raise_application_error(-20000,'Erreur fatale lors du partitionnement de &g_table. Operation annulee.');
end;
/
 
/*
	Début de la redéfinition
*/
 
alter session force parallel dml parallel 3;
alter session force parallel query parallel 3;
 
Prompt Debut de la redefinition
BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE('&g_schema', '&g_table','&g_table_work', null, dbms_redefinition.cons_use_pk);
END;
/
 
 
 -- Transfert des objets dépendants (indexes, contraintes, grant, triggers...)
Prompt Copie des objets dependants de la table
DECLARE
    num_errors PLS_INTEGER:=0;
BEGIN
	dbms_redefinition.copy_table_dependents(
		uname            => '&g_schema',
		orig_table       => '&g_table',
		int_table        => '&g_table_work',
		copy_indexes     => dbms_redefinition.cons_orig_params,
		copy_triggers    => TRUE,
		copy_constraints => TRUE,
		copy_privileges  => TRUE,
		ignore_errors    => TRUE,
		num_errors       => num_errors,
		copy_statistics  => TRUE
	);
 
	if nvl(num_errors,0) != 0 then
		dbms_output.put_line('*** num_errors='||num_errors);
		dbms_redefinition.abort_redef_table('&g_schema', '&g_table', '&g_table_work');
		dbms_output.put_line('*** Des erreurs ont ete detectees lors de la copie des objets dependants de la table.');
		dbms_output.put_line('*** Ces erreurs peuvent être visualisees via: SELECT object_name, base_table_name, ddl_txt FROM DBA_REDEFINITION_ERRORS;');
		-- raise_application_error(-20000,'Erreur fatale lors du partitionnement de &g_table. Operation annulee.');
	end if;
END;
/
 
Prompt Synchronisation table de travail 
BEGIN
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('&g_schema','&g_table', '&g_table_work');
END;
/
 
Prompt Finalisation de la redefinition
BEGIN
   DBMS_REDEFINITION.FINISH_REDEF_TABLE('&g_schema','&g_table', '&g_table_work');
END;
/
 
-- suppression de la table intermédiaire
DROP TABLE &g_schema..&g_table_work cascade constraints;
 
exit

Tout fonctionne comme prévu jusqu'au premier truc bizarre, ligne 129, après l'appel à dbms_redefinition.copy_table_dependents(). num_error n'est pas égal à 0, mais il n'y a rien dans DBA_REDEFINITION_ERRORS?

Ensuite, l'appel à DBMS_REDEFINITION.SYNC_INTERIM_TABLE() provoque une ORA-12093:

ORA-12093: table provisoire "TEST"."ADA_TEST1_WRK" non valide
ORA-06512: à "SYS.DBMS_REDEFINITION", ligne 119
ORA-06512: à "SYS.DBMS_REDEFINITION", ligne 1740
ORA-06512: à ligne 2

Si je ne fait pas l'appel à DBMS_REDEFINITION.SYNC_INTERIM_TABLE() , alors l'exception est levée sur DBMS_REDEFINITION.FINISH_REDEF_TABLE().

Je ne comprends pas vraiment, les tables ont la même structure à part les partitions, si l'on regarde la table de travail, tout est correct. Je n'ai rien vu de pertinent dans la doc ou ailleurs sur le net, dans tout les exemples que j'ai vu, tout fonctionne comme sur des roulettes...

Si quelqu'un voit mon erreur et veux bien me mettre sur la voie...

Merci

Edit: J'ai oublié de préciser que je lance ce script avec l'utilisateur SYSTEM

**Dajon** · 13/10/2011, 11h34

Et comme d'habitude, après avoir cherché des heures, et décidé de finalement poster ici, je finis par trouver le problème:

Après la copie des objets dépendants de la table à partitionner, dans le bloc qui vérifie s'il y a des erreurs, ligne 131, j'appelle

dbms_redefinition.abort_redef_table()

Du coup, je supprime les "liens" entre mes deux tables (table source et table de travail) et l'appel à la fonction doit vider DBA_REDEFINITION_ERRORS.

Du coup je ne voyais pas les erreurs, et tous les appels suivants à dbms_redefinition plantaient.

Pour info, les deux erreurs dans DBA_REDEFINITION_ERRORS sont:

ORA-01442: colonne à modifier en NOT NULL est déjà NOT NULL
01442. 00000 - "column to be modified to NOT NULL is already NOT NULL"

Il y a des jours comme ça...

Merci si vous avez jeté un oeil

Partionnement de table à chaud avec DBMS_REDFINITION

Administration Oracle

Vue hybride

Discussions similaires

Partager

Partager