如何配置DBMS_METADATA让GET_DDL生成带修改后缀的对象名以避免命名冲突？

嘿，你提到的这个需求——给DBMS_METADATA生成的DDL对象名加后缀避免同 schema 冲突，其实DBMS_METADATA本身并没有提供像rename_expr这种一键重命名的内置参数，但我们可以通过手动处理生成的DDL文本来实现，这里给你两种实用的方案：

方案一：基于现有代码快速修改（直接替换对象名） #

你可以在循环输出DDL的时候，用字符串替换（或正则替换）把原对象名改成带后缀的版本。比如我们给所有对象加_NEW后缀，修改你现有代码的循环部分即可：

DECLARE
    l_suffix VARCHAR2(20) := '_NEW'; -- 定义你想要的后缀
BEGIN -- MAIN
    -- 保留你原来的DBMS_METADATA参数设置
    dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE);
    dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'EMIT_SCHEMA', false);

    FOR t IN (
        -- 保留你原来的查询逻辑不变
        select object_name, dbms_metadata.get_ddl(object_type, object_name, owner) AS gen_ddl
        from
        (
            select
                owner,
                object_name,
                decode(object_type,
                    'DATABASE LINK',      'DB_LINK',
                    'JOB',                'PROCOBJ',
                    'RULE SET',           'PROCOBJ',
                    'RULE',               'PROCOBJ',
                    'EVALUATION CONTEXT', 'PROCOBJ',
                    'CREDENTIAL',         'PROCOBJ',
                    'CHAIN',              'PROCOBJ',
                    'PROGRAM',            'PROCOBJ',
                    'PACKAGE',            'PACKAGE_SPEC',
                    'PACKAGE BODY',       'PACKAGE_BODY',
                    'TYPE',               'TYPE_SPEC',
                    'TYPE BODY',          'TYPE_BODY',
                    'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',
                    'QUEUE',              'AQ_QUEUE',
                    'JAVA CLASS',         'JAVA_CLASS',
                    'JAVA TYPE',          'JAVA_TYPE',
                    'JAVA SOURCE',        'JAVA_SOURCE',
                    'JAVA RESOURCE',      'JAVA_RESOURCE',
                    'XML SCHEMA',         'XMLSCHEMA',
                    object_type
                ) object_type
            from all_objects
            where owner in ('PAYTRAS')
            and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
                'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
            and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
            and (owner, object_name) not in (select owner, table_name from all_nested_tables)
            and (owner, object_name) not in (select owner, table_name from all_tables where iot_type = 'IOT_OVERFLOW')
        )
        order by owner, object_type, object_name
    ) LOOP
        -- 用正则替换DDL中的原对象名为带后缀的版本
        l_modified_ddl := REGEXP_REPLACE(
            t.gen_ddl,
            'CREATE\s+(\w+)\s+"?' || t.object_name || '"?',
            'CREATE \1 "' || t.object_name || l_suffix || '"',
            1, 1, 'i'
        );
        
        -- 可选：如果约束名也需要加后缀（比如PK_EMP改成PK_EMP_NEW），再追加一次替换
        l_modified_ddl := REGEXP_REPLACE(
            l_modified_ddl,
            '("?' || t.object_name || '(_\w+)?")',
            '\1' || l_suffix,
            1, 0, 'i'
        );
        
        dbms_output.put_line(t.object_name || l_suffix || '###' || l_modified_ddl);
    END LOOP;
END; -- MAIN
/

这个方案的好处是不需要额外创建对象，直接修改现有代码即可，正则表达式能适配大多数对象类型的DDL开头（比如CREATE TABLE、CREATE PACKAGE等）。

方案二：封装成可复用的自定义函数 #

如果以后需要多次使用这个功能，或者要处理更复杂的重命名规则，可以把DDL生成和重命名逻辑封装成一个函数：

CREATE OR REPLACE FUNCTION get_ddl_with_suffix(
    p_owner IN VARCHAR2,
    p_object_type IN VARCHAR2,
    p_object_name IN VARCHAR2,
    p_suffix IN VARCHAR2
) RETURN CLOB IS
    l_original_ddl CLOB;
    l_modified_ddl CLOB;
BEGIN
    -- 统一设置DBMS_METADATA转换参数
    dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE);
    dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'EMIT_SCHEMA', false);
    
    -- 获取原始DDL
    l_original_ddl := dbms_metadata.get_ddl(p_object_type, p_object_name, p_owner);
    
    -- 替换对象名
    l_modified_ddl := REGEXP_REPLACE(
        l_original_ddl,
        'CREATE\s+(\w+)\s+"?' || p_object_name || '"?',
        'CREATE \1 "' || p_object_name || p_suffix || '"',
        1, 1, 'i'
    );
    
    -- 替换关联的约束名（可选）
    l_modified_ddl := REGEXP_REPLACE(
        l_modified_ddl,
        '("?' || p_object_name || '(_\w+)?")',
        '\1' || p_suffix,
        1, 0, 'i'
    );
    
    RETURN l_modified_ddl;
END;
/

然后在你的主代码里调用这个函数即可：

DECLARE
    l_suffix VARCHAR2(20) := '_NEW';
BEGIN
    FOR t IN (
        -- 保留你原来的查询逻辑不变
        select object_name, object_type
        from
        (
            select
                owner,
                object_name,
                decode(object_type,
                    'DATABASE LINK',      'DB_LINK',
                    'JOB',                'PROCOBJ',
                    'RULE SET',           'PROCOBJ',
                    'RULE',               'PROCOBJ',
                    'EVALUATION CONTEXT', 'PROCOBJ',
                    'CREDENTIAL',         'PROCOBJ',
                    'CHAIN',              'PROCOBJ',
                    'PROGRAM',            'PROCOBJ',
                    'PACKAGE',            'PACKAGE_SPEC',
                    'PACKAGE BODY',       'PACKAGE_BODY',
                    'TYPE',               'TYPE_SPEC',
                    'TYPE BODY',          'TYPE_BODY',
                    'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',
                    'QUEUE',              'AQ_QUEUE',
                    'JAVA CLASS',         'JAVA_CLASS',
                    'JAVA TYPE',          'JAVA_TYPE',
                    'JAVA SOURCE',        'JAVA_SOURCE',
                    'JAVA RESOURCE',      'JAVA_RESOURCE',
                    'XML SCHEMA',         'XMLSCHEMA',
                    object_type
                ) object_type
            from all_objects
            where owner in ('PAYTRAS')
            and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
                'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
            and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
            and (owner, object_name) not in (select owner, table_name from all_nested_tables)
            and (owner, object_name) not in (select owner, table_name from all_tables where iot_type = 'IOT_OVERFLOW')
        )
        order by owner, object_type, object_name
    ) LOOP
        dbms_output.put_line(
            t.object_name || l_suffix || '###' || 
            get_ddl_with_suffix('PAYTRAS', t.object_type, t.object_name, l_suffix)
        );
    END LOOP;
END;
/

这个方案更灵活，你可以随时调整函数里的重命名规则，或者扩展支持前缀、自定义表达式等需求。

注意事项 #

• 不同对象类型的DDL格式可能有差异（比如触发器、视图的DDL开头），如果遇到正则匹配不到的情况，可以针对性调整正则表达式的规则。
• 如果对象名包含特殊字符（比如空格、非ASCII字符），需要修改正则表达式来适配，确保能正确匹配到对象名。

备注：内容来源于stack exchange，提问作者Chris