如何从Oracle的dbms_metadata.fetch_ddl输出中移除闪回归档语句

问题描述 #

执行dbms_metadata.fetch_ddl或dbms_metadata.get_ddl生成表的DDL时，输出会额外包含ALTER TABLE "SCHEMA"."TABLE" FLASHBACK ARCHIVE "ARCHIVE_NAME"语句，希望通过DBMS_METADATA自带的配置移除这条语句，尽量避免用正则表达式处理。已经尝试调整set_transform_parameter的所有可见选项，但均无法生效。

另外发现一个特殊现象：即使将SQLTERMINATOR参数设为true，这条FLASHBACK ARCHIVE的ALTER语句末尾也不会添加分号。

解决方案 #

可以通过设置FLASHBACK_ARCHIVE这个transform参数来禁用该语句的生成，在你的脚本中添加一行配置即可：

dbms_metadata.set_transform_param (ddl_handle, 'FLASHBACK_ARCHIVE', false);

修改后的完整脚本 #

set SERVEROUTPUT ON

declare
  metadata_handle number;
  transform_handle number;
  ddl_handle number;
  result_array sys.ku$_ddls;

begin

  metadata_handle := dbms_metadata.open('TABLE');
--  transform_handle := dbms_metadata.add_transform(metadata_handle, 'MODIFY');
--  dbms_metadata.set_remap_param(transform_handle, 'REMAP_NAME', 'TABLE1', 'TABLE2');
--  dbms_metadata.set_remap_param(transform_handle, 'REMAP_SCHEMA', 'X', 'Y');
  ddl_handle := dbms_metadata.add_transform(metadata_handle, 'DDL');
  dbms_metadata.set_transform_param (ddl_handle, 'SQLTERMINATOR', true);
  dbms_metadata.set_transform_param (ddl_handle, 'PRETTY', true);
  dbms_metadata.set_transform_param (ddl_handle, 'SEGMENT_ATTRIBUTES', false);
  dbms_metadata.set_transform_param (ddl_handle, 'STORAGE', false);
  dbms_metadata.set_transform_param (ddl_handle, 'TABLESPACE', false);
  dbms_metadata.set_transform_param (ddl_handle, 'PARTITIONING', false);
  dbms_metadata.set_transform_param (ddl_handle, 'CONSTRAINTS', false);
  -- 添加这一行禁用FLASHBACK ARCHIVE语句
  dbms_metadata.set_transform_param (ddl_handle, 'FLASHBACK_ARCHIVE', false);
  dbms_metadata.set_filter(metadata_handle, 'SCHEMA', 'SCHEMA_NAME');


  loop
    result_array := dbms_metadata.fetch_ddl(metadata_handle);
    exit when result_array is null;
      for i in result_array.first..result_array.last loop
      dbms_output.put_line(result_array(i).ddltext);
    end loop;
  end loop; 
  dbms_metadata.close(metadata_handle);
end;
/

关于SQLTERMINATOR的补充说明 #

这条FLASHBACK ARCHIVE的ALTER语句属于DBMS_METADATA生成的独立DDL条目，默认逻辑下不会为这类附加语句自动添加终止符。不过添加上述FLASHBACK_ARCHIVE=false的参数后，这条语句会被直接移除，也就无需再处理终止符的问题了。

内容的提问来源于stack exchange，提问作者Josh Reynolds