Oracle中遍历TB_前缀表并获取表大小的PL/SQL问题排查

错误原因及修正点 #

• 类型不匹配错误（PLS-00382）：
  游标循环变量my_table_name是游标返回的记录类型，而非单纯的字符串。直接用WHERE segment_name=my_table_name会把记录类型和字符串类型做比较，导致类型错误。正确写法是引用记录中的table_name字段：WHERE segment_name = my_table_name.table_name。

• 查询范围不匹配：
  all_tables会返回所有用户下符合条件的表，但user_segments仅包含当前登录用户的段信息。如果TB_开头的表属于其他用户，用user_segments查不到数据。建议改用dba_segments（需对应权限），同时在游标中获取表的所属用户（owner），过滤时加上owner条件，避免不同用户下同名表的混淆。

• NULL值处理：
  当表未分配段（比如新建的空表），SUM(bytes)会返回NULL，输出时可以用NVL函数将NULL转为0，避免输出混乱。

• 语法细节：
  PL/SQL块结尾需要加;，DBMS_OUTPUT.put_line语句结尾也需要加;。

修正后的完整代码（支持跨用户查询） #

DECLARE
  CURSOR my_cursor IS
    SELECT owner, table_name
    FROM all_tables
    WHERE table_name LIKE 'TB_%'
    ORDER BY owner, table_name ASC;
  my_size NUMBER;
BEGIN
  FOR rec IN my_cursor LOOP
    SELECT NVL(SUM(bytes)/1024/1024, 0) INTO my_size
    FROM dba_segments
    WHERE segment_name = rec.table_name
      AND owner = rec.owner
      AND segment_type = 'TABLE'; -- 仅查询表段，排除索引等其他段
    
    DBMS_OUTPUT.put_line(rec.owner || '.' || rec.table_name || ' : ' || my_size || ' MB');
  END LOOP;
END;
/

无DBA权限时的替代方案（仅查当前用户表） #

DECLARE
  CURSOR my_cursor IS
    SELECT table_name
    FROM user_tables -- 仅查询当前用户的表
    WHERE table_name LIKE 'TB_%'
    ORDER BY table_name ASC;
  my_size NUMBER;
BEGIN
  FOR rec IN my_cursor LOOP
    SELECT NVL(SUM(bytes)/1024/1024, 0) INTO my_size
    FROM user_segments
    WHERE segment_name = rec.table_name
      AND segment_type = 'TABLE';
    
    DBMS_OUTPUT.put_line(rec.table_name || ' : ' || my_size || ' MB');
  END LOOP;
END;
/

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