Oracle 统计用户下表的数据量

亦凉 2022-06-08 01:36 226阅读 0赞

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

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

OWNER TABLE\_NAME     PART\_COL ROW\_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, 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;  
/