无SELECT_CATALOG_ROLE权限，能否用ALL_TAB_COLUMNS生成含其他模式的Oracle DDL？

可以实现，但有个关键前提：你只能生成自己有访问权限的其他所有者对象的DDL——ALL开头的数据字典视图仅展示当前用户有权限读取的对象，看不到无权限的对象。

下面是具体的实现思路和示例代码：

1. 生成表结构DDL #

利用ALL_TABLES、ALL_TAB_COLUMNS、ALL_TAB_COMMENTS、ALL_COL_COMMENTS这些视图拼接表定义：

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
    v_ddl CLOB;
BEGIN
    FOR tbl IN (SELECT owner, table_name FROM all_tables WHERE owner <> USER ORDER BY owner, table_name) LOOP
        -- 初始化表DDL
        v_ddl := 'CREATE TABLE "' || tbl.owner || '"."' || tbl.table_name || '" (';
        
        -- 拼接列定义
        FOR col IN (
            SELECT column_name, data_type, data_length, data_precision, data_scale, nullable, char_used
            FROM all_tab_columns
            WHERE owner = tbl.owner AND table_name = tbl.table_name
            ORDER BY column_id
        ) LOOP
            v_ddl := v_ddl || CHR(10) || '    "' || col.column_name || '" ' || col.data_type;
            
            -- 处理数据类型的长度/精度
            IF col.data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR') THEN
                v_ddl := v_ddl || '(' || col.data_length || CASE WHEN col.char_used = 'C' THEN ' CHAR' ELSE '' END || ')';
            ELSIF col.data_type = 'NUMBER' THEN
                IF col.data_precision IS NOT NULL THEN
                    v_ddl := v_ddl || '(' || col.data_precision || CASE WHEN col.data_scale > 0 THEN ',' || col.data_scale ELSE '' END || ')';
                END IF;
            END IF;
            
            -- 处理非空约束
            IF col.nullable = 'N' THEN
                v_ddl := v_ddl || ' NOT NULL';
            END IF;
            
            -- 添加列分隔符（最后一列不加）
            SELECT CASE WHEN MAX(column_id) = col.column_id THEN '' ELSE ',' END
            INTO v_ddl
            FROM all_tab_columns
            WHERE owner = tbl.owner AND table_name = tbl.table_name;
        END LOOP;
        
        -- 结束表定义
        v_ddl := v_ddl || CHR(10) || ');';
        
        -- 添加表注释
        FOR comm IN (SELECT comments FROM all_tab_comments WHERE owner = tbl.owner AND table_name = tbl.table_name AND comments IS NOT NULL) LOOP
            v_ddl := v_ddl || CHR(10) || 'COMMENT ON TABLE "' || tbl.owner || '"."' || tbl.table_name || '" IS ''' || REPLACE(comm.comments, '''', '''''') || ''';';
        END LOOP;
        
        -- 添加列注释
        FOR col_comm IN (SELECT column_name, comments FROM all_col_comments WHERE owner = tbl.owner AND table_name = tbl.table_name AND comments IS NOT NULL) LOOP
            v_ddl := v_ddl || CHR(10) || 'COMMENT ON COLUMN "' || tbl.owner || '"."' || tbl.table_name || '"."' || col_comm.column_name || '" IS ''' || REPLACE(col_comm.comments, '''', '''''') || ''';';
        END LOOP;
        
        -- 输出DDL
        DBMS_OUTPUT.PUT_LINE(v_ddl);
        DBMS_OUTPUT.PUT_LINE(CHR(10) || '----------------------------------------' || CHR(10));
    END LOOP;
END;
/

2. 生成约束DDL #

通过ALL_CONSTRAINTS和ALL_CONS_COLUMNS生成主键、唯一约束、外键、检查约束：

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
    v_ddl VARCHAR2(4000);
BEGIN
    FOR cons IN (
        SELECT owner, constraint_name, table_name, constraint_type, r_owner, r_constraint_name
        FROM all_constraints
        WHERE owner <> USER AND constraint_type IN ('P', 'U', 'R', 'C')
        ORDER BY owner, table_name, constraint_type
    ) LOOP
        -- 主键/唯一约束
        IF cons.constraint_type IN ('P', 'U') THEN
            v_ddl := 'ALTER TABLE "' || cons.owner || '"."' || cons.table_name || '" ADD CONSTRAINT "' || cons.constraint_name || '" ' ||
                     CASE cons.constraint_type WHEN 'P' THEN 'PRIMARY KEY' WHEN 'U' THEN 'UNIQUE' END || ' (';
            
            -- 拼接约束列
            FOR col IN (SELECT column_name FROM all_cons_columns WHERE owner = cons.owner AND constraint_name = cons.constraint_name ORDER BY position) LOOP
                v_ddl := v_ddl || '"' || col.column_name || '",';
            END LOOP;
            
            -- 移除最后一个逗号
            v_ddl := RTRIM(v_ddl, ',') || ');';
        END IF;
        
        -- 外键约束（仅能生成你有权限访问的被引用表的外键）
        IF cons.constraint_type = 'R' THEN
            v_ddl := 'ALTER TABLE "' || cons.owner || '"."' || cons.table_name || '" ADD CONSTRAINT "' || cons.constraint_name || '" FOREIGN KEY (';
            
            -- 拼接外键列
            FOR col IN (SELECT column_name FROM all_cons_columns WHERE owner = cons.owner AND constraint_name = cons.constraint_name ORDER BY position) LOOP
                v_ddl := v_ddl || '"' || col.column_name || '",';
            END LOOP;
            
            v_ddl := RTRIM(v_ddl, ',') || ') REFERENCES "' || cons.r_owner || '"."' || 
                     (SELECT table_name FROM all_constraints WHERE owner = cons.r_owner AND constraint_name = cons.r_constraint_name) || '" (';
            
            -- 拼接被引用列
            FOR ref_col IN (SELECT column_name FROM all_cons_columns WHERE owner = cons.r_owner AND constraint_name = cons.r_constraint_name ORDER BY position) LOOP
                v_ddl := v_ddl || '"' || ref_col.column_name || '",';
            END LOOP;
            
            v_ddl := RTRIM(v_ddl, ',') || ');';
        END IF;
        
        -- 检查约束
        IF cons.constraint_type = 'C' THEN
            v_ddl := 'ALTER TABLE "' || cons.owner || '"."' || cons.table_name || '" ADD CONSTRAINT "' || cons.constraint_name || '" CHECK (' ||
                     (SELECT search_condition FROM all_constraints WHERE owner = cons.owner AND constraint_name = cons.constraint_name) || ');';
        END IF;
        
        DBMS_OUTPUT.PUT_LINE(v_ddl);
        DBMS_OUTPUT.PUT_LINE(CHR(10) || '----------------------------------------' || CHR(10));
    END LOOP;
END;
/

3. 生成索引DDL #

利用ALL_INDEXES和ALL_IND_COLUMNS生成普通索引：

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
    v_ddl VARCHAR2(4000);
BEGIN
    FOR idx IN (
        SELECT owner, index_name, table_name, uniqueness
        FROM all_indexes
        WHERE owner <> USER AND index_type = 'NORMAL' AND generated = 'N' -- 排除系统生成的索引（比如主键自动创建的索引）
        ORDER BY owner, table_name, index_name
    ) LOOP
        v_ddl := 'CREATE ' || idx.uniqueness || ' INDEX "' || idx.owner || '"."' || idx.index_name || '" ON "' || idx.owner || '"."' || idx.table_name || '" (';
        
        -- 拼接索引列
        FOR col IN (SELECT column_name, column_position, descend FROM all_ind_columns WHERE owner = idx.owner AND index_name = idx.index_name ORDER BY column_position) LOOP
            v_ddl := v_ddl || '"' || col.column_name || '" ' || col.descend || ',';
        END LOOP;
        
        v_ddl := RTRIM(v_ddl, ',') || ');';
        
        DBMS_OUTPUT.PUT_LINE(v_ddl);
        DBMS_OUTPUT.PUT_LINE(CHR(10) || '----------------------------------------' || CHR(10));
    END LOOP;
END;
/

关键局限性说明 #

1. 权限限制：只能获取你有SELECT权限的其他所有者对象的信息，无权限的对象在ALL视图中完全不可见。
2. 信息不全：ALL视图无法提供对象的完整存储参数（比如表的PCT_FREE、INITIAL_EXTENT，索引的存储子句等），如果需要这些参数，可能需要额外的权限或联系DBA。
3. 依赖问题：外键、触发器等依赖对象如果无权限访问，无法生成完整的关联DDL，重建时需要手动调整。
4. 特殊对象支持有限：序列、存储过程、触发器等对象可以通过ALL_SEQUENCES、ALL_PROCEDURES、ALL_TRIGGERS生成，但部分细节（比如触发器的完整代码）可能在ALL视图中无法获取完整内容。

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