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;
/