Oracle 统计用户下表的数据量

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

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

OWNER TABLE_NAME      PART_COL  ROW_S     GATHER_TIME
LCA F_AMRETIREORDER     CREATE_TIME   9973    2017/9/12 10:35:07
LCA MENU_LOG            CREATE_TIME   9908156   2017/9/12 10:31:26
LCA     PUB_LOGS           9881080   2017/9/12 10:31:21
LCA FT_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;
/