mysql 查询用户下所有表_统计用户下每个表rows和size及数据库中常用统计查询

1.统计用户下每个表的行数

----先创建一张表用于存放表名及该表的行数

create table table_rows(table_name

varchar(100),count_rows number);

----统计数据脚本：

declare

v_table varchar(100);

v_num   number;

v_sql   varchar(500);

cursor c1 is

select table_name from user_tables;

begin

DBMS_OUTPUT.ENABLE(buffer_size => null);  ----表示输出buffer不受限制

open c1;

loop

fetch c1

into v_table;

if c1%found then

v_sql := 'select count(*) from "' || v_table || '"';

execute immediate v_sql

into v_num;

dbms_output.put_line('table_name：' || v_table ||

'        count_rows：' ||

v_num);

insert into table_rows values (v_table, v_num);

else

exit;

end if;

end

loop;

commit;

end;

SELECT * FROM table_rows order by

count_rows desc;

2.统计用户下每个表的大小

select segment_name, bytes/1024

"size(Kb)"

from user_segments

where segment_type = 'TABLE';

3.在一个表中查看size和rows

select t1.table_name, t1.count_rows,

t2.bytes / 1024 "size(Kb)"

from table_rows t1, user_segments t2

where t1.table_name = t2.segment_name

order by count_rows desc;

4.附数据库下常用的统计记录

----统计所有表空间(空间名及大小，剩余大小、使用率)

Select

a.Tablespace_Name "ts_name",

Round(a.Bytes / 1024 / 1024 ) "total(M)",

Round(b.Bytes / 1024 / 1024 )

"free(M)",

Round(((a.Bytes - b.Bytes) / a.Bytes) * 100, 2)||'%'

"used_percent"

From (Select Tablespace_Name, Sum(Bytes) Bytes

From Dba_Data_Files

Group By Tablespace_Name) a,

(Select Tablespace_Name, Sum(Bytes) Bytes, Max(Bytes) Largest

From Dba_Free_Space

Group By Tablespace_Name) b

Where a.Tablespace_Name =

b.Tablespace_Name

And Round(((a.Bytes - b.Bytes) / a.Bytes) * 100, 2) >= 0

Union All

SELECT a.tablespace_name

"ts_name",

a.BYTES/ 1024 / 1024

"total(M)",

(a.bytes - nvl(b.bytes, 0))/ 1024 / 1024

"free(M)",

ROUND(nvl(b.bytes, 0)/a.BYTES*100,2) || '%' "used_percent"

FROM (SELECT tablespace_name, SUM (bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM (bytes_cached)

bytes FROM v$temp_extent_pool GROUP BY tablespace_name) b

WHERE

a.tablespace_name = b.tablespace_name(+)

And a.tablespace_name like 'TEMP%'

And ROUND(nvl(b.bytes, 0)/a.BYTES*100,2) >= 0;

ts_name

total(M)

free(M)

used_percent

SYSAUX

600

31

94.91%

UNDOTBS1

100

82

18%

USERS

8

2

80%

SYSTEM

760

5

99.31%

EXAMPLE

346

36

89.58%

TEMP

29

1

96.55%

----查看表空间中物理文件的名称及大小

SELECT tablespace_name, file_id, file_name,

round(bytes / (1024 * 1024), 0) total_space

FROM dba_data_files

ORDER BY tablespace_name;

TABLESPACE_NAME

FILE_ID

FILE_NAME

TOTAL_SPACE

EXAMPLE

5

/u01/app/oracle/oradata/orcl/example01.dbf

346

SYSAUX

2

/u01/app/oracle/oradata/orcl/sysaux01.dbf

600

SYSTEM

1

/u01/app/oracle/oradata/orcl/system01.dbf

760

UNDOTBS1

3

/u01/app/oracle/oradata/orcl/undotbs01.dbf

100

USERS

4

/u01/app/oracle/oradata/orcl/users01.dbf

8

----统计用户下有多少对象(触发器、表、索引、存储过程、函数、序列、包、JOB等等)

SELECT

object_type, status, COUNT(*) count#

FROM user_objects

GROUP BY

object_type, status;

----

SELECT owner, object_type, status, COUNT(*)

count#

FROM all_objects

where owner = 'SYS'

GROUP BY owner, object_type, status;

----统计表空间下的所有对象(对象的用户及占用空间大小)

SELECT owner,

segment_name,SUM(bytes)/1024/1024 From dba_segments

WHERE tablespace_name = 'USERS'

GROUP BY owner,segment_name

ORDER BY SUM(bytes) desc;

----数据文件resize可回收空间

select /*+ ordered use_hash(a,c) */  'alter database datafile

'''||a.file_name||''' resize '   ||round

(a.filesize - (a.filesize - c.hwmsize-100)

*0.8)||'M;',  a.filesize,  c.hwmsize

from(select file_id,file_name,round(bytes/1024/1024) filesize from

dba_data_files) a,(

select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents

group by file_id) c

where a.file_id = c.file_id  and

a.filesize - c.hwmsize > 100;

----分析表，刷新统计信息

analyze table scott.t1 compute statistics;