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;