共计 1800 个字符，预计需要花费 5 分钟才能阅读完成。
自动写代码机器人，免费开通
怎么在 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;
/看完上述内容，你们掌握怎么在 Oracle 中对用户中表的数据量进行统计的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
向 AI 问一下细节
正文完
