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
|
whenever sqlerror exit failure
set feedback off
set verify off
set serveroutput on size unlimited
set linesize 2000
set trimspool on
set wrap off
set pagesize 0
define g_schema=&1
define g_table=&2
define g_colonne=&3
define g_table_work=&g_table._WRK
define g_interval_fonction=&4
define g_interval_type=&5
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
-- Si des tables relicats de précédentes exec existent, on les supprime
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;
select count(*)
into l_cnt
from all_tables
where owner = '&g_schema'
and table_name = '&g_table._ERR';
if l_cnt > 0 then
execute immediate 'drop table &g_schema..&g_table._ERR 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)
INTERVAL (&g_interval_fonction(1,'&g_interval_type'))
(
PARTITION P_1 VALUES LESS THAN (to_date('190002','YYYYMM')) NOCOMPRESS
)
AS SELECT * FROM &g_schema..&g_table
WHERE 0=1#';
execute immediate strsql;
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;
l_e_already_not_null exception;
PRAGMA EXCEPTION_INIT(l_e_already_not_null,-1442);
l_cpt_err 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
-- On essaye de réexécuter le DDL qui a planté pour voir le type d'erreur
for i in (select ddl_txt from DBA_REDEFINITION_ERRORS) loop
begin
execute immediate i.ddl_txt;
exception
-- On ignore les erreurs de type "contrainte not null est deja not null"
when l_e_already_not_null then null;
when others then l_cpt_err := l_cpt_err + 1;
end;
end loop;
if l_cpt_err != 0 then
execute immediate 'create table &g_schema..&g_table._ERR as SELECT object_name, base_table_name, ddl_txt FROM DBA_REDEFINITION_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('*** Le code DDL ayant provoque les erreurs peut etre visualise via: SELECT * FROM &g_schema..&g_table._ERR;');
raise_application_error(-20000,'Erreur fatale lors du partitionnement de &g_table. Operation annulee.');
end if;
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 purge;
exit |
Répondre avec citation
Envoyé par elkamaro
Partager