如何从异地数据库服务器复制表的完整元数据（含约束、索引等）？

你的脚本用了CREATE TABLE AS SELECT（简称CTAS）语句，这种方式只能复制表的列定义、数据以及基础的列属性（比如是否允许为空），完全不会复制主键、外键、索引、表空间指定、触发器这些元数据，所以得换用以下几种方法来实现完整复制：

方法1：用DBMS_METADATA生成完整DDL（推荐） #

Oracle自带的DBMS_METADATA包可以直接从远程库导出表的全部DDL，包含所有约束、索引、表空间配置等，是最省心的方式。

步骤示例： #

-- 1. 生成并执行远程表的完整DDL
DECLARE
    v_ddl CLOB;
    v_tables VARCHAR2(100) := 'TBL1,TBL2'; -- 要复制的表名
BEGIN
    -- 循环处理每个表
    FOR tbl IN (SELECT REGEXP_SUBSTR(v_tables, '[^,]+', 1, LEVEL) AS table_name
                FROM DUAL
                CONNECT BY REGEXP_SUBSTR(v_tables, '[^,]+', 1, LEVEL) IS NOT NULL) LOOP
        -- 获取远程表的完整DDL，包含所有元数据
        v_ddl := DBMS_METADATA.GET_DDL('TABLE', tbl.table_name, 'MAINDB@MAINDB');
        
        -- 替换原schema前缀（如果本地不需要保留MAINDB这个schema）
        v_ddl := REPLACE(v_ddl, 'MAINDB.', '');
        -- 如果远程表空间在本地不存在，替换成本地表空间
        v_ddl := REPLACE(v_ddl, 'REMOTE_TABLESPACE', 'LOCAL_TABLESPACE');
        
        -- 执行DDL创建表结构
        EXECUTE IMMEDIATE v_ddl;
        DBMS_OUTPUT.PUT_LINE('已创建表 ' || tbl.table_name || ' 的完整结构');
    END LOOP;
END;
/

-- 2. 导入表数据
INSERT INTO TBL1 SELECT * FROM TBL1@MAINDB;
INSERT INTO TBL2 SELECT * FROM TBL2@MAINDB;
COMMIT;

注意事项： #

• 确保本地用户有CREATE TABLE、CREATE INDEX等DDL权限，以及远程库的查询权限
• 如果远程表依赖其他对象（比如外键关联的表），需要先复制依赖对象
• GET_DDL默认会包含原表的存储参数，若本地环境不同，需手动调整DDL中的相关配置

方法2：手动查询数据字典拼接DDL #

如果无法使用DBMS_METADATA，可以直接查询远程库的数据字典表，手动生成约束、索引的DDL。

步骤示例： #

-- 1. 创建空表结构（仅列定义，不导数据）
CREATE TABLE TBL1 AS SELECT * FROM TBL1@MAINDB WHERE 1=0;
CREATE TABLE TBL2 AS SELECT * FROM TBL2@MAINDB WHERE 1=0;

-- 2. 添加主键约束
DECLARE
    v_pk_sql VARCHAR2(2000);
BEGIN
    -- 处理TBL1的主键
    SELECT 'ALTER TABLE TBL1 ADD CONSTRAINT ' || c.constraint_name || ' PRIMARY KEY (' ||
           LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) || ')'
    INTO v_pk_sql
    FROM ALL_CONSTRAINTS@MAINDB c
    JOIN ALL_CONS_COLUMNS@MAINDB cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
    WHERE c.owner='MAINDB' AND c.table_name='TBL1' AND c.constraint_type='P'
    GROUP BY c.constraint_name;
    
    EXECUTE IMMEDIATE v_pk_sql;
    
    -- 处理TBL2的主键
    SELECT 'ALTER TABLE TBL2 ADD CONSTRAINT ' || c.constraint_name || ' PRIMARY KEY (' ||
           LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) || ')'
    INTO v_pk_sql
    FROM ALL_CONSTRAINTS@MAINDB c
    JOIN ALL_CONS_COLUMNS@MAINDB cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
    WHERE c.owner='MAINDB' AND c.table_name='TBL2' AND c.constraint_type='P'
    GROUP BY c.constraint_name;
    
    EXECUTE IMMEDIATE v_pk_sql;
END;
/

-- 3. 添加非主键索引
DECLARE
    CURSOR c_indexes IS
        SELECT i.index_name, i.table_name, 
               LISTAGG(ic.column_name, ', ') WITHIN GROUP (ORDER BY ic.column_position) AS columns,
               i.index_type
        FROM ALL_INDEXES@MAINDB i
        JOIN ALL_IND_COLUMNS@MAINDB ic ON i.owner = ic.owner AND i.index_name = ic.index_name
        WHERE i.owner='MAINDB' AND i.table_name IN ('TBL1','TBL2')
        -- 排除主键索引（已经通过约束添加）
        AND i.index_name NOT IN (SELECT constraint_name FROM ALL_CONSTRAINTS@MAINDB WHERE owner='MAINDB' AND table_name IN ('TBL1','TBL2') AND constraint_type='P')
        GROUP BY i.index_name, i.table_name, i.index_type;
    v_idx_sql VARCHAR2(2000);
BEGIN
    FOR idx IN c_indexes LOOP
        v_idx_sql := 'CREATE ' || idx.index_type || ' INDEX ' || idx.index_name || ' ON ' || idx.table_name || ' (' || idx.columns || ')';
        EXECUTE IMMEDIATE v_idx_sql;
    END LOOP;
END;
/

-- 4. 导入数据
INSERT INTO TBL1 SELECT * FROM TBL1@MAINDB;
INSERT INTO TBL2 SELECT * FROM TBL2@MAINDB;
COMMIT;

注意事项： #

• 这种方法需要手动处理所有类型的约束（外键、唯一约束、检查约束等），适合表结构简单的场景
• 外键约束需要确保关联的父表已经在本地创建，否则会执行失败

方法3：用数据泵（EXPDP/IMPDP）跨库迁移 #

如果需要复制大量表或整个schema，推荐用Oracle数据泵，通过数据库链直接跨库导出导入，能完整保留所有元数据和数据。

步骤示例： #

-- 1. 创建数据库链（如果还没有）
CREATE DATABASE LINK MAINDB 
CONNECT TO MAINDB IDENTIFIED BY your_password 
USING 'your_remote_tns_entry';

-- 2. 用IMPDP直接从远程库导入表到本地
IMPDP local_user/local_password@local_db 
NETWORK_LINK=MAINDB 
TABLES=MAINDB.TBL1, MAINDB.TBL2 
REMAP_SCHEMA=MAINDB:local_user -- 将原schema映射到本地用户
REMAP_TABLESPACE=remote_tablespace:local_tablespace; -- 将原表空间映射到本地表空间

注意事项： #

• 需要配置TNS，确保本地数据库能访问远程库
• 执行数据泵的用户需要拥有EXP_FULL_DATABASE和IMP_FULL_DATABASE角色
• 如果只需要结构不需要数据，可以添加CONTENT=METADATA_ONLY参数

内容的提问来源于stack exchange，提问作者DIPAK SHAH