统计用户下每个表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;

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2122711/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2122711/