如何在PL/SQL中一次性从所有模式的ALL_OBJECTS查询全部内容

最直接的方法：利用ALL_OBJECTS的OWNER字段过滤 #

ALL_OBJECTS视图本身就包含了你有权限访问的所有模式（OWNER字段）的对象，无需重复执行查询，直接通过OWNER条件指定目标模式即可：

SELECT owner AS schema_name, object_name, object_type
FROM ALL_OBJECTS
WHERE owner IN ('A', 'B', 'C', 'D') -- 替换为你的目标模式列表
AND object_name LIKE '%something%'; -- 替换为你的搜索关键词

若要搜索所有可访问模式，去掉WHERE owner IN (...)条件即可。

动态SQL生成（适用于复杂查询场景） #

如果需要针对每个模式执行更复杂的逻辑，可以先生成包含所有模式的查询语句，再一次性执行：

-- 生成合并查询语句
SELECT 'SELECT ''' || owner || ''' AS schema_name, object_name, object_type FROM ALL_OBJECTS WHERE owner = ''' || owner || ''' AND object_name LIKE ''%something%''' || ' UNION ALL'
FROM ALL_USERS
WHERE owner IN ('A', 'B', 'C', 'D'); -- 指定目标模式

将生成的结果复制后，去掉最后一行的UNION ALL，执行完整SQL即可得到合并结果。

PL/SQL批量执行（支持灵活输出或存储） #

如果需要自定义结果处理逻辑，可通过PL/SQL块循环执行：

1. 输出到控制台

SET SERVEROUTPUT ON;
DECLARE
    CURSOR c_target_schemas IS
        SELECT username FROM ALL_USERS WHERE username IN ('A', 'B', 'C', 'D');
BEGIN
    FOR rec IN c_target_schemas LOOP
        DBMS_OUTPUT.PUT_LINE('=== 模式: ' || rec.username || ' ===');
        FOR obj_rec IN (
            SELECT object_name, object_type 
            FROM ALL_OBJECTS 
            WHERE owner = rec.username AND object_name LIKE '%something%'
        ) LOOP
            DBMS_OUTPUT.PUT_LINE(obj_rec.object_name || ' (' || obj_rec.object_type || ')');
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
END;
/

2. 存储到临时表

-- 创建临时表存储结果
CREATE GLOBAL TEMPORARY TABLE temp_search_results (
    schema_name VARCHAR2(30),
    object_name VARCHAR2(128),
    object_type VARCHAR2(19)
) ON COMMIT PRESERVE ROWS;

-- 批量插入结果
DECLARE
    CURSOR c_target_schemas IS
        SELECT username FROM ALL_USERS WHERE username IN ('A', 'B', 'C', 'D');
BEGIN
    FOR rec IN c_target_schemas LOOP
        INSERT INTO temp_search_results
        SELECT rec.username, object_name, object_type 
        FROM ALL_OBJECTS 
        WHERE owner = rec.username AND object_name LIKE '%something%';
    END LOOP;
END;
/

-- 查询最终结果
SELECT * FROM temp_search_results;

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