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

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 ;

/