Mysql,Oracle失去表的总行数及大小

Mysql,Oracle得到表的总行数及大小

??? 今天看了一哥们的博文，http://openlok.iteye.com/blog/2021580，突然想到以前每次测试的时候总是想知道那些表数据最多，今天从网上收集了下怎么得到表的行数及大小，本文SQL从网上收集的，本人测试通过。

??? 首先是Mysql得到表行数，参考了链接：

??? http://stackoverflow.com/questions/433903/how-many-rows-in-mysql-database-table

???

SELECT table_schema, table_name, table_rows
FROM information_schema.tables
ORDER BY table_rows DESC

?? 或者这样

SELECT table_schema, table_name, table_rows
FROM information_schema.tables
where table_schema='test'
ORDER BY table_rows DESC

?? Mysql得到表大小，参考了链接：

??? http://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database

???可以这样：

SELECT table_name AS "Tables", 
round(((data_length + index_length) / 1024 / 1024), 3) "MB" 
FROM information_schema.TABLES 
WHERE table_schema = "test"
ORDER BY (data_length + index_length) DESC;

?? Mysql得到库的大小:

???

SELECT table_schema "DB Name", 
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema
order by sum(data_length + index_length) DESC;

??? 上面的SQL在Mysql 5.6.14上面测试通过。

??? 下面看Oracle怎么得到表的总行数及大小.

???? Oracle得到表的总行数，参考了链接：

???? http://www.dba-oracle.com/t_count_rows_all_tables_in_schema.htm

???? Sys用户可以这样：

????

select table_name, num_rows counter
  from dba_tables
 where owner = 'TMD'
 order by num_rows desc
 nulls last;

??? 普通用户：

???

select table_name, num_rows  counter
  from all_tables 
 where owner = 'TMD'
 order by num_rows desc
 nulls last;

??? 或者：

???

 select table_name, num_rows  counter
  from user_tables 
 order by num_rows desc
 nulls last;

??? 如果想得到一个文本汇总文件，可以写脚本，参考了链接：

???? http://www.dba-oracle.com/t_count_rows_all_tables_in_schema.htm

????

set pages 999;
col count format 999,999,999;
spool f:/saveFile/tmp/countlist.txt
select
   table_name,
   to_number(
   extractvalue(
      xmltype(
         dbms_xmlgen.getxml('select count(*) c from '||table_name))
    ,'/ROWSET/ROW/C')) count
from 
   user_tables
order by 
   table_name;
spool off;

??? 怎么得到表的大小呢？参考了链接：

??? https://community.oracle.com/thread/1113909

???? sys用户：

?????

 select segment_name,segment_type,bytes/1024/1024 MB
 from dba_segments
 where segment_type='TABLE' and OWNER = 'TMD'
 order by bytes desc;

?? 普通用户，参考了链接：

??? http://www.dba-oracle.com/t_script_oracle_table_size.htm

???

 select segment_name table_name, sum(bytes) / (1024 * 1024) table_size_meg
  from user_extents
 where segment_type = 'TABLE'
 group by segment_name;

?? 上面的代码在oracle 10g测试通过。

??? 本文中的SQL全部从网上收集，如有错误，欢迎指出，谢谢。

<span style="fon

相关文章：

你感兴趣的文章：

标签云：