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

数据库  /  管理员 发布于 6年前   151

要想统计用户下所有表的数据量，可以查看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 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;/

总结

以上所述是小编给大家介绍的Oracle 统计用户下表的数据量实现脚本，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对站的支持！