oracle统计表中的用户个数,Oracle 统计用户下表的数据量

Oracle 统计用户下表的数据量

博主：guogang83 发表时间：2017-09-11 10:10:48 浏览量：452

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

/