使用过程导出表，导出分区!

导出表的过程：

/* Formatted on 2009/07/29 09:35 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE henry."EXPORT_TABLE" (
i_table IN VARCHAR2,
flag OUT NUMBER
)
IS
h1 NUMBER;
-- TYPE t_parts IS RECORD (partition_date varchar2(2000),
-- upperbound varchar2(2000));
--CURSOR cur_last_partition IS
-- SELECT SUBSTR(partition_name,LENGTH(i_table)+2,8) partition_date,
-- high_value upperbound
-- FROM user_tab_partitions
--WHERE partition_name LIKE UPPER(i_table)||'%' AND table_name = UPPER(i_table) and rownum=1
--ORDER BY partition_position ASC;
--c_partition_name CONSTANT varchar2(30) := i_table||'_YYYYMMDD';
--v_last_partition t_parts;
--v_partition_name varchar2(30);
-- v_table varchar2(500);
BEGIN
--EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="YYYYMMDD"';
-- OPEN cur_last_partition;
-- FETCH cur_last_partition INTO v_last_partition;
--CLOSE cur_last_partition;
-- Here you can make parition in advance
-- v_last_partition.partition_date := TO_DATE(v_last_partition.partition_date);
-- v_partition_name := REPLACE(c_partition_name,'YYYYMMDD',v_last_partition.partition_date);
BEGIN
h1 :=
DBMS_DATAPUMP.OPEN (operation => 'EXPORT',
job_mode => 'TABLE',
job_name => i_table || '_JOB',
VERSION => 'COMPATIBLE'
);
END;

BEGIN
DBMS_DATAPUMP.set_parallel (handle => h1, DEGREE => 1);
END;

BEGIN
DBMS_DATAPUMP.add_file (handle => h1,
filename => i_table || '.LOG',
DIRECTORY => 'EXPDIR',
filetype => 3
);
END;

BEGIN
DBMS_DATAPUMP.set_parameter (handle => h1,
NAME => 'KEEP_MASTER',
VALUE => 0
);
END;

BEGIN
DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN(''' || i_table
|| ''')'
);
END;

BEGIN
DBMS_DATAPUMP.add_file (handle => h1,
filename => i_table || '.DMP',
DIRECTORY => 'EXPDIR',
filetype => 1
);
END;

BEGIN
DBMS_DATAPUMP.set_parameter (handle => h1,
NAME => 'INCLUDE_METADATA',
VALUE => 1
);
END;

BEGIN
DBMS_DATAPUMP.set_parameter (handle => h1,
NAME => 'DATA_ACCESS_METHOD',
VALUE => 'AUTOMATIC'
);
END;

BEGIN
DBMS_DATAPUMP.set_parameter (handle => h1,
NAME => 'ESTIMATE',
VALUE => 'BLOCKS'
);
END;

BEGIN
DBMS_DATAPUMP.start_job (handle => h1,
skip_current => 0,
abort_step => 0
);
END;

BEGIN
DBMS_DATAPUMP.detach (handle => h1);
END;

write_part_log (i_table,
'NO PARTITION_NAME',
'EXPORT TABLE',
'OK',
'NO SQL'
);
flag := 0;
EXCEPTION
WHEN OTHERS
THEN
write_part_log (i_table,
'NO PARTITION_NAME',
'EXPORT TABLE',
SQLERRM,
'NO SQL'
);
flag := -1;
END export_table;
/

导出分区的过程：

/* Formatted on 2009/07/29 09:36 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE henry.export_partition (i_table IN VARCHAR2)
IS
h1 NUMBER;

TYPE t_parts IS RECORD (
partition_date VARCHAR2 (2000),
upperbound VARCHAR2 (2000)
);

CURSOR cur_last_partition
IS
SELECT SUBSTR (partition_name, LENGTH (i_table) + 2,
8) partition_date, high_value upperbound
FROM user_tab_partitions
WHERE partition_name LIKE UPPER (i_table) || '%'
AND table_name = UPPER (i_table)
AND ROWNUM = 1
ORDER BY partition_position ASC;

c_partition_name CONSTANT VARCHAR2 (30) := i_table || '_YYYYMMDD';
v_last_partition t_parts;
v_partition_name VARCHAR2 (30);
v_table VARCHAR2 (500);
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="YYYYMMDD"';

OPEN cur_last_partition;

FETCH cur_last_partition
INTO v_last_partition;

CLOSE cur_last_partition;

-- Here you can make parition in advance
v_last_partition.partition_date :=
TO_DATE (v_last_partition.partition_date);
v_partition_name :=
REPLACE (c_partition_name, 'YYYYMMDD', v_last_partition.partition_date);

BEGIN
h1 :=
DBMS_DATAPUMP.OPEN (operation => 'EXPORT',
job_mode => 'TABLE',
job_name => v_partition_name || '_JOB',
VERSION => 'COMPATIBLE'
);
END;

BEGIN
DBMS_DATAPUMP.set_parallel (handle => h1, DEGREE => 1);
END;

BEGIN
DBMS_DATAPUMP.add_file (handle => h1,
filename => v_partition_name || '.LOG',
DIRECTORY => 'EXPDIR',
filetype => 3
);
END;

BEGIN
DBMS_DATAPUMP.set_parameter (handle => h1,
NAME => 'KEEP_MASTER',
VALUE => 0
);
END;

--begin
-- dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''HENRY'')');
--end;
BEGIN
--dbms_output.put_line('IN('''||i_table||''')');
DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN(''' || i_table
|| ''')'
);
END;

BEGIN
DBMS_DATAPUMP.data_filter (handle => h1,
NAME => 'PARTITION_LIST',
VALUE => '''||v_partition_name||'''
);
END;

BEGIN
DBMS_DATAPUMP.add_file (handle => h1,
filename => v_partition_name || '.DMP',
DIRECTORY => 'EXPDIR',
filetype => 1
);
END;

BEGIN
DBMS_DATAPUMP.set_parameter (handle => h1,
NAME => 'INCLUDE_METADATA',
VALUE => 1
);
END;

BEGIN
DBMS_DATAPUMP.set_parameter (handle => h1,
NAME => 'DATA_ACCESS_METHOD',
VALUE => 'AUTOMATIC'
);
END;

BEGIN
DBMS_DATAPUMP.set_parameter (handle => h1,
NAME => 'ESTIMATE',
VALUE => 'BLOCKS'
);
END;

BEGIN
DBMS_DATAPUMP.start_job (handle => h1,
skip_current => 0,
abort_step => 0
);
END;

BEGIN
DBMS_DATAPUMP.detach (handle => h1);
END;
END export_partition;
/