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

最近想分析下数据库的信息, 然后写了这个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

具体效果就不描述了.