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
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
| create or replace
PACKAGE BODY "EPSS_ARCHIVING" AS
PROCEDURE import_call (call_id IN number) IS
h1 NUMBER;
call_id_import NUMBER := call_id ;
import_job_name varchar2(20) := 'CALL_' ||call_id_import ;
ind NUMBER;
percent_done NUMBER;
job_state VARCHAR2(30);
le ku$_LogEntry;
js ku$_JobStatus;
jd ku$_JobDesc;
sts ku$_Status;
begin
epss_archiving.create_db_link;
epss_archiving.update_sequences;
epss_archiving.disable_constraints;
begin
h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'FULL', job_name => 'IMPORT_'||call_id_import||'', version => 'COMPATIBLE');
end;
begin
dbms_datapump.set_parallel(handle => h1, degree => 1);
end;
begin
dbms_datapump.add_file(handle => h1, filename => 'IMPORT_'||call_id_import||'.LOG', directory => 'IMPDP_DIR', filetype => 3);
end;
begin
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
end;
begin
dbms_datapump.add_file(handle => h1, filename => 'EXPDAT_'||call_id_import||'_%U.DMP', directory => 'IMPDP_DIR', filetype => 1);
end;
begin
dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
end;
begin
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
end;
begin
dbms_datapump.set_parameter(handle => h1, name => 'REUSE_DATAFILES', value => 0);
end;
begin
dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);
end;
begin
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
end;
percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
if js.percent_done != percent_done
then
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;
dbms_output.put_line('Job has completed');
dbms_output.put_line('Final job state = ' || job_state);
begin
dbms_datapump.detach(handle => h1);
end;
epss_archiving.enable_constraints;
epss_archiving.drop_db_link;
end import_call;
/*PROCEDURE TO DISABLE CONSTRINATS*/
procedure disable_constraints is
cursor user_constraints_cur is select table_name, constraint_name from user_constraints where constraint_type <> 'O';
v_table_name varchar2(100);
v_constraint_name varchar2(100);
begin
for rec_user_constraints in user_constraints_cur loop
v_table_name := rec_user_constraints.table_name;
v_constraint_name := rec_user_constraints.constraint_name;
execute immediate 'alter table ' ||v_table_name|| ' disable constraint ' || v_constraint_name || '';
end loop;
end disable_constraints;
/*PROCEDURE TO ENABLE CONSTRINATS*/
procedure enable_constraints is
cursor user_constraints_cur is select table_name, constraint_name from user_constraints where constraint_type <> 'O';
v_table_name varchar2(100);
v_constraint_name varchar2(100);
begin
for rec_user_constraints in user_constraints_cur loop
v_table_name := rec_user_constraints.table_name;
v_constraint_name := rec_user_constraints.constraint_name;
execute immediate 'alter table ' ||v_table_name|| ' enable constraint ' || v_constraint_name || '';
end loop;
end enable_constraints;
procedure update_sequences is
v_sequence_name_epss varchar(100);
v_sequence_value_epss number;
v_sequence_value_epsslnx number;
v_epss_query varchar2(300);
v_epsslnx_query varchar2(300);
v_epsslnx_update_sequence varchar2(300);
v_increase_sequence_number number;
v_epss_seq_cur varchar2(300);
cursor epss_seq_cur is
select sequence_name from all_sequences@EPSSLNX_TO_EPSSDEV.REGRESS.RDBMS.DEV.US.ORACLE.COM
where sequence_owner='EPSS' ;
begin
for rec_epss_sequences in epss_seq_cur loop
v_sequence_name_epss := rec_epss_sequences.sequence_name;
v_epss_query := 'select '||v_sequence_name_epss ||'.nextval from dual@EPSSLNX_TO_EPSSDEV.REGRESS.RDBMS.DEV.US.ORACLE.COM';
v_epsslnx_query := 'select '||v_sequence_name_epss ||'.nextval from dual';
execute immediate v_epss_query into v_sequence_value_epss;
execute immediate v_epsslnx_query into v_sequence_value_epsslnx;
if v_sequence_value_epsslnx < v_sequence_value_epss
then
v_increase_sequence_number := v_sequence_value_epss - v_sequence_value_epsslnx;
v_epsslnx_update_sequence := 'alter sequence '||v_sequence_name_epss||' increment by '||v_increase_sequence_number;
execute immediate v_epsslnx_update_sequence;
v_epsslnx_update_sequence := 'select '||v_sequence_name_epss ||'.nextval from dual';
execute immediate v_epsslnx_update_sequence;
v_epsslnx_update_sequence := 'alter sequence '||v_sequence_name_epss||' increment by 1';
execute immediate v_epsslnx_update_sequence;
end if;
end loop;
end update_sequences;
procedure create_db_link is
begin
execute immediate 'CREATE DATABASE LINK EPSSLNX_TO_EPSSDEV.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO EPSS IDENTIFIED by intrasoft08 USING ''''EPSSDEV''';
end create_db_link;
procedure drop_db_link is
begin
execute immediate 'DROP DATABASE LINK EPSSLNX_TO_EPSSDEV.REGRESS.RDBMS.DEV.US.ORACLE.COM';
end drop_db_link;
end epss_archiving; |
