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

最新推荐文章于 2021-04-03 12:18:18 发布

henrybai

最新推荐文章于 2021-04-03 12:18:18 发布

阅读量795

点赞数

CC 4.0 BY-SA版权

文章标签： table date filter parallel include file

本文链接：https://blog.youkuaiyun.com/henrybai/article/details/4389405

本文介绍了一种在Oracle数据库中导出表及其分区的方法，包括创建存储过程以实现表和特定分区的数据导出，使用DBMS_DATAPUMP包进行高效的数据处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

导出表的过程：

/* 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.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;