要想统计用户下所有表的数据量,可以查看user_tables,此表里面是统计信息,当然这个可能不太准,要想非常精确,需要直接count表。下面的脚本有异常不中断,可以重复执行的特点。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
create table bk_count_tables
(
owner VARCHAR2(30),
table_name VARCHAR2(30),
part_col varchar2(100), --分区字段
row_s number,
gather_time date
);
create index ind_bct_own_table on bk_count_tables(owner,table_name);
set serveroutput on
declare
cursor c_cursor is select s.OWNER, s.TABLE_NAME, col.column_name part_col
from dba_tables s,
( select owner,
name ,
listagg(column_name, ',' ) within group ( order by null ) column_name
from ( select owner, name , column_name
from dba_part_key_columns
where owner in ( 'TEST' )
and object_type = 'TABLE'
and name not like 'BIN$%'
union all
select owner, name , column_name
from dba_subpart_key_columns
where owner in ( 'TEST' )
and object_type = 'TABLE'
and name not like 'BIN$%' )
group by owner, name ) col
where s.OWNER in ( 'TEST' )
and not regexp_like(table_name, '[0-9]{3,8}' )
and s.table_name not like '%BAK%'
and s.table_name not like '%A2K%'
and s.table_name not like 'BK%'
and s.table_name not like 'BIN%'
and s.OWNER = col.owner(+)
and s.TABLE_NAME = col. name (+)
order by s.TABLE_NAME ;
c_row c_cursor%rowtype;
t_rows number;
begin
for c_row in c_cursor loop
begin
execute immediate 'select count(*) from bk_count_tables where owner=:1 and TABLE_NAME=:2 and rownum=1'
into t_rows using c_row.OWNER,c_row.TABLE_NAME ;
if(t_rows = 0) then
execute immediate 'select count(*) from "' ||c_row.TABLE_NAME|| '"' into t_rows;
insert into bk_count_tables values (c_row.OWNER,c_row.TABLE_NAME,c_row.part_col,t_rows,sysdate);
commit ;
end if;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(c_row.OWNER|| '---' ||c_row.TABLE_NAME);
rollback ;
end ;
end loop;
end ;
/
|
总结
以上所述是小编给大家介绍的Oracle 统计用户下表的数据量实现脚本,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持!
原文链接:http://blog.csdn.net/stevendbaguo/article/details/77929915