Oracle 使用DBMS_DATAPUMP进行归档

我有一个归档历史数据的要求，那就是从一些超过36个月的表中删除数据，但只有一个子集的数据，为了参数，让我们称它们为没有被占用的客户的报价。我们目前有54个表将从中删除数据，尽管这是一个动态列表。作为数据删除的一部分，我们需要备份要删除的数据，而不是完整的表，以防需要恢复任何报价。我无法弄清楚如何使用DBMS_DATAPUMP仅从表中导出有限的数据，最多5000个引号。所以，我所做的是创建临时表，这些临时表是源表定义的副本，将要删除的数据复制到这些临时表，然后使用DBMS_DATAPUMP导出临时表，然后删除数据和临时表。这是一种有效的方法，我是否会因为采用这种方法而错过或失去一些东西？无论如何有没有避免使用临时表？

创建临时表代码为

PROCEDURE build_temporary_tables(p_report_file       IN UTL_FILE.FILE_TYPE,
                                  p_report_filename   IN VARCHAR2,
                                  p_current_timestamp IN TIMESTAMP) IS

 v_DML_cmd        VARCHAR2(500);

 v_temp_table_count NUMBER(15) ;
 
 BEGIN

   -- Create a set of empty temporary tables that are otherwise identical to the 
   -- tables identified for data removal  
   UTL_FILE.PUT_LINE( p_report_file, 'Build Temporary Tables started');
   v_temp_table_count := 0 ;
   
   FOR i IN (SELECT table_name FROM archive_table_list) 
   LOOP        
       v_DML_cmd  := 'CREATE TABLE ABC.' ||i.table_name || '_T' || 
                       ' AS (SELECT * FROM ' || i.table_name || ' WHERE 1=2 )' ;
       EXECUTE IMMEDIATE v_DML_cmd ; 
       v_temp_table_count := v_temp_table_count + 1 ;
              
   END LOOP;

   UTL_FILE.PUT_LINE(p_report_file, '  Number of temp tables created  '||
                                        TRIM(TO_CHAR(v_temp_table_count, '999999999')));   
   UTL_FILE.NEW_LINE(p_report_file, 1 );

 EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           RAISE;
           
 END build_temporary_tables;
复制

然后使用动态SQL用插入命令填充临时表

存档代码看起来像这样

 PROCEDURE archive_quotes(p_report_file       IN UTL_FILE.FILE_TYPE,
                          p_archive_directory IN VARCHAR2,
                          p_current_timestamp IN TIMESTAMP,
                          p_archive_filename  IN VARCHAR2) IS
  
  ind NUMBER;              -- Loop index
  h1 NUMBER;               -- Data Pump job handle
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
  v_job_state varchar2(4000);
  v_table_list    VARCHAR2(3000); 
   
   
 BEGIN 
 
   -- Use utility DBMS_DATAPUMP to copy all the data to be deleted to a dump file
   -- A report log is also created  
   SELECT listagg(''''||table_name||'_T'||'''', ',')
        WITHIN GROUP
         (ORDER BY table_name)   
     INTO v_table_list
     FROM archive_table_list ;

  -- Create a (user-named) Data Pump job to do a schema export.
  h1 := DBMS_DATAPUMP.OPEN(operation => 'EXPORT',
                              job_mode  => 'TABLE',
                              job_name  => 'SYS_EXPORT_ARCHIVE_'|| TRIM(TO_CHAR(archive_job_seq.NEXTVAL, '0000')),
                              version   => 'COMPATIBLE');                    

 -- Specify a single dump file for the job (using the handle just returned)
 -- and a directory object, which must already be defined and accessible
 -- to the user running this procedure.

  DBMS_DATAPUMP.ADD_FILE(handle    => h1,
                            filename   => p_archive_filename||'.dmp',
                            filetype   => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,
                            directory  => p_archive_directory,
                            reusefile  => 1);
                             
  DBMS_DATAPUMP.ADD_FILE(handle    => h1,
                             filename  => p_archive_filename||'.log',
                             filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
                             directory => p_archive_directory,
                             reusefile => 1);                             

  -- A metadata filter is used to specify the schema that will be exported.
  DBMS_DATAPUMP.METADATA_FILTER(handle  => h1,
                                     name    => 'SCHEMA_EXPR',
                                     value   => 'IN(''ABC'')');

  DBMS_DATAPUMP.METADATA_FILTER(handle      => h1,
                                     name        => 'NAME_LIST',
                                     value       => v_table_list,                                     
                                     object_type => 'TABLE') ;                                     
                                     
  DBMS_DATAPUMP.SET_PARAMETER(handle      => h1,
                                  name        =>'INCLUDE_METADATA',
                                  value       => 1);    
                                  
  DBMS_DATAPUMP.SET_PARAMETER(handle      => h1,
                                  name        =>'DATA_ACCESS_METHOD',
                                  value       =>'AUTOMATIC');                                     
   
  -- Start the job. An exception will be generated if something is not set up
  -- properly. 

  DBMS_DATAPUMP.START_JOB(h1);

  DBMS_DATAPUMP.WAIT_FOR_JOB(h1, v_job_state);
  
  DBMS_DATAPUMP.DETACH(h1) ;

  DBMS_OUTPUT.PUT_LINE(v_job_state);
  
 EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           RAISE; 
           
 END archive_quotes ;
复制

而不是DBMS_DATAPUMP，我会使用外部表的数据泵驱动程序。有了这个，你可以卸载查询的结果!

create table t as 
  select level c1, mod ( level, 13 ) c2, sysdate c3
  from   dual
  connect by level <= 100;
  
create table ext ( 
  c1, c2, c3
) organization external (
  type oracle_datapump  
  default directory data_pump_dir
  location ( 'ext.dmp' )
) as
  select * from t
  where  c2 = 0;
  
select * from ext;

C1     C2   C3                     
   13     0 04-FEB-2020 14:40:07    
   26     0 04-FEB-2020 14:40:07    
   39     0 04-FEB-2020 14:40:07    
   52     0 04-FEB-2020 14:40:07    
   65     0 04-FEB-2020 14:40:07    
   78     0 04-FEB-2020 14:40:07    
   91     0 04-FEB-2020 14:40:07 
复制

这样，如果您要查询数据，数据将保持可访问状态。而不必先将其加载到临时表中。

您可以将创建的文件添加到单个外部表中，因此您只有一个地方可以查询所有内容。但是从外部表中选择会读取它指向的所有文件。所以这可能会很慢。

12.2用分区的外部表解决了这个问题。从19c开始，您可以使用混合分区表-一个表，其中每个分区可以是数据库服务器上的常规行数据或文件。

注意: 尽管这使用了数据泵驱动程序，但文件与数据泵导出/导入文件不兼容。所以你不能用它来读取你现有的文件 :(