Oracle 统计用户下表的数据量实现脚本

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

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 统计用户下表的数据量实现脚本，希望对大家有所帮助