oracle分组统计和全量统计,Oracle 统计用户下表的数据量

左手的ㄟ右手 2022-10-21 01:08 60阅读 0赞

要想统计用户下所有表的数据量，可以查看user\_tables，此表里面是统计信息，当然这个可能不太准，要想非常精确，需要直接count表。下面的脚本有异常不中断，可以重复执行的特点。

所属用户 表名 分区字段 数据量 收集时间

OWNER TABLE\_NAME PART\_COLROW\_S GATHER\_TIME

LCAF\_AMRETIREORDER CREATE\_TIME 9973 2017/9/12 10:35:07

LCAMENU\_LOG CREATE\_TIME 9908156 2017/9/12 10:31:26

LCA PUB\_LOGS 9881080 2017/9/12 10:31:21

LCAFT\_ACTROLE CODE 9663 2017/9/12 10:34:46

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,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; /