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

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

2020-03-01 13:18深圳gg Oracle

这篇文章给大家分享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;

									/