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