常用sql---表记录数和占用空间统计

最新推荐文章于 2022-04-23 07:21:37 发布

Hungry+Foolish

最新推荐文章于 2022-04-23 07:21:37 发布

阅读量173

点赞数

本文链接：https://blog.csdn.net/u013165852/article/details/103238932

版权

1.每张表的记录数和占用空间

select owner as 用户名,
       table_name as 表名,
       num_rows as 记录数,
       ROUND(t.NUM_ROWS * t.AVG_ROW_LEN / 1024 / 1024, 2) "占用空间（M）"
  from all_tables t
 where OWNER NOT IN
       ('SYS', 'SYSTEM', 'OUTLN', 'DIP', 'ORACLE_OCM', 'DBSNMP', 'APPQOSSYS',
        'WMSYS', 'EXFSYS', 'CTXSYS', 'XDB', 'ANONYMOUS', 'ORDSYS', 'ORDDATA',
        'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'MDSYS', 'OLAPSYS', 'MDDATA',
        'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR', 'SYSMAN',
        'MGMT_VIEW', 'FLOWS_FILES', 'APEX_PUBLIC_USER', 'APEX_030200',
        'OWBSYS', 'OWBSYS_AUDIT', 'SCOTT', 'HR', 'OE', 'IX', 'SH', 'PM', 'BI',
        'XS$NULL');

2.整个库的数据条目数和占用空间　

select COUNT(TABLE_NAME) AS "表数量（个）",
       sum(t.NUM_ROWS) / 10000 as "表数据条目记录总数（万）",
       ROUND(sum(t.NUM_ROWS * t.AVG_ROW_LEN / 1024 / 1024 / 1024), 2) "占用空间（GB）"
  from all_tables t
 where OWNER NOT IN
       ('SYS', 'SYSTEM', 'OUTLN', 'DIP', 'ORACLE_OCM', 'DBSNMP', 'APPQOSSYS',
        'WMSYS', 'EXFSYS', 'CTXSYS', 'XDB', 'ANONYMOUS', 'ORDSYS', 'ORDDATA',
        'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'MDSYS', 'OLAPSYS', 'MDDATA',
        'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR', 'SYSMAN',
        'MGMT_VIEW', 'FLOWS_FILES', 'APEX_PUBLIC_USER', 'APEX_030200',
        'OWBSYS', 'OWBSYS_AUDIT', 'SCOTT', 'HR', 'OE', 'IX', 'SH', 'PM', 'BI',
        'XS$NULL');