-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathexp_big_table.sql
120 lines (104 loc) · 23.4 KB
/
exp_big_table.sql
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
CREATE OR REPLACE PROCEDURE exp_big_table IS
l_dp_handle PLS_INTEGER; --datapump job handle
l_job_name VARCHAR2(30); --name for datapump job
l_dumpfile VARCHAR2(30); --name of dump file
l_logfile VARCHAR2(30); --name of log file
l_dpdir VARCHAR2(30); --name of datapump directory object
e_start_job1 EXCEPTION;
e_start_job2 EXCEPTION;
PRAGMA EXCEPTION_INIT(e_start_job1, -31626); --failed datapump events can leave master table behind
PRAGMA EXCEPTION_INIT(e_start_job2, -31634); --failed datapump events can leave session behind
BEGIN
l_job_name := 'exp_big_table';
l_dumpfile := l_job_name || '.dmp';
l_logfile := l_job_name || '.log';
l_dpdir := 'DATA_PUMP_DIR';
BEGIN -- open job
l_dp_handle := dbms_datapump.open(operation => 'EXPORT',
job_mode => 'TABLE', --> needed for skipping tables
job_name => 'dp_schema',
version => '10.0.0');
EXCEPTION
WHEN e_start_job1 THEN
DECLARE
l_table_name VARCHAR2(30);
BEGIN
SELECT nvl(MAX(table_name), 'x')
INTO l_table_name
FROM user_tables
WHERE table_name = l_job_name;
IF l_table_name != 'x'
THEN
dbms_output.put_line('Datapump Master Table ' || l_job_name ||
' exists.');
END IF;
END;
RAISE;
WHEN e_start_job2 THEN
dbms_output.put_line( 'Check for existing data pump session.');
RAISE;
WHEN OTHERS THEN
raise_application_error(-20000,
'Error when opening job: ' || SQLERRM);
END;
BEGIN -- add dump file
dbms_datapump.add_file(handle => l_dp_handle,
filename => l_dumpfile,
directory => l_dpdir,
filesize => '1G');
EXCEPTION
WHEN OTHERS THEN
dbms_datapump.detach(handle => l_dp_handle);
raise_application_error(-20010,
'Error when adding dump file: ' || SQLERRM);
END;
BEGIN -- add log file
dbms_datapump.add_file(handle => l_dp_handle,
filename => l_logfile,
directory => l_dpdir,
filetype => dbms_datapump.ku$_file_type_log_file);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20020,
'Error when adding log file: ' || SQLERRM);
END;
BEGIN -- specify schema
dbms_datapump.metadata_filter(handle => l_dp_handle,
NAME => 'SCHEMA_EXPR',
VALUE => '=''SCOTT''');
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20030,
'Error when adding metadata filter: ' ||
SQLERRM);
END;
BEGIN -- specify tables
dbms_datapump.metadata_filter(handle => l_dp_handle,
NAME => 'NAME_EXPR',
VALUE => 'IN (''HWC_FND_LOGINS'',''EMP'')'
);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20032,
'Error when adding metadata filter: ' ||
SQLERRM);
END;
BEGIN -- start job
dbms_datapump.start_job(handle => l_dp_handle);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20040,
'Error when starting job: ' || SQLERRM);
END;
BEGIN -- detach
dbms_datapump.detach(handle => l_dp_handle);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20050, SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Main exception: ' || SQLERRM);
raise;
END exp_big_table;
/