Oracle 查询用户下表名,表列数,表行数,表大小的SQL

oracle,查询,用户,表列,行数,大小,sql · 浏览次数 : 394

小编点评

## 数据库信息分析 SQL 以下是您的 SQL 代码的总结： ```sql SELECT x.table_name AS 表名, x.表行数, x.表列数, y.表大小 AS 表大小单位MB FROM ( SELECT b.table_name, a.num_rows AS 表行数, b.count1 AS 表列数 FROM user_tables a INNER JOIN ( SELECT table_name, count( column_name ) AS count1 FROM user_tab_columns GROUP BY table_name ) b ON a.table_name = b.table_name ORDER BY b.table_name ) x LEFT outer JOIN ( SELECT sum( tablesize ) AS 表大小, tablename AS tablename FROM ( SELECT sum( C.bytes ) / 1024 / 1024 AS tablesize, C.table_name AS tablename FROM ( SELECT A.table_name, B.bytes FROM user_lobs A, user_extents B WHERE A.segment_name = B.segment_name ) C GROUP BY C.table_name UNION ALL SELECT sum( bytes ) / 1024 / 1024 AS tablesize, segment_name AS tablename FROM user_extents WHERE segment_type = 'TABLE' GROUP BY segment_name ) GROUP BY tablename ORDER BY 1 DESC ) y ON x.table_name = y.tablename ORDER BY y.表大小 desc; ``` **总结:** * 代码首先使用 subquery 提取所有表的信息，包括表名称、行数和列数。 * 然后使用 left outer join 将所有表的信息与表大小信息进行关联。 * 代码最后按照表大小降序排列结果。 **注意:** * 代码中使用了 `order by` 子句，可能导致结果的排序顺序不按照预期。 * 您可以根据需要进行数据清理和修改。

正文

最近想分析下数据库的信息, 然后写了这个SQL. 比较lowB一些.

因为Oracle的 deferred_segment_creation 参数的影响.
很多表如果是0行,那么是不会创建extents 存储信息.
所以很多取出来的表大小信息为空. 

这也就导致了查询结果表大小为空的情况. 
为了能够准确显示表信息, 我这边进行了 left outer join的操作

SQL比较简单未进行任何优化

有多个至少两重的关联子查询用来展示结果.

具体SQL为:

SELECT
    x.table_name AS 表名,
    x.表行数,
    x.表列数,
    y.表大小 AS 表大小单位MB 
FROM
    (
    SELECT
        b.table_name,
        a.num_rows AS 表行数,
        b.count1 AS 表列数 
    FROM
        user_tables a
        INNER JOIN ( SELECT table_name, count( column_name ) AS count1 FROM user_tab_columns GROUP BY table_name ) b ON a.table_name = b.table_name 
    ORDER BY
        b.table_name 
    ) x LEFT outer
    JOIN (
    SELECT
        sum( tablesize ) AS 表大小,
        tablename 
    FROM
        (
        SELECT
            sum( C.bytes ) / 1024 / 1024 AS tablesize,
            C.table_name AS tablename 
        FROM
            ( SELECT A.table_name, B.bytes FROM user_lobs A, user_extents B WHERE A.segment_name = B.segment_name ) C 
        GROUP BY
            C.table_name UNION ALL
        SELECT
            sum( bytes ) / 1024 / 1024 AS tablesize,
            segment_name AS tablename 
        FROM
            user_extents 
        WHERE
            segment_type = 'TABLE' 
        GROUP BY
            segment_name 
        ) 
    GROUP BY
        tablename 
    ORDER BY
        1 DESC 
    ) y ON x.table_name = y.tablename 
ORDER BY
    y.表大小 desc

具体效果就不描述了.