要想統(tǒng)計用戶下所有表的數(shù)據(jù)量，可以查看user_tables，此表里面是統(tǒng)計信息，當(dāng)然這個可能不太準(zhǔn)，要想非常精確，需要直接count表。下面的腳本有異常不中斷，可以重復(fù)執(zhí)行的特點。
|
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),--分區(qū)字段 row_s number, gather_time date);create index ind_bct_own_table on bk_count_tables(owner,table_name);set serveroutput ondeclarecursor 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;/ |
總結(jié)
以上所述是小編給大家介紹的Oracle 統(tǒng)計用戶下表的數(shù)據(jù)量實現(xiàn)腳本，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對服務(wù)器之家網(wǎng)站的支持！
原文鏈接：http://blog.csdn.net/stevendbaguo/article/details/77929915
