查看: 1517|回复: 0

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

发表于 2017-10-10 08:00:04
句号论坛

要想统计用户下所有表的数据量,可以查看user_tables,此表里面是统计信息,当然这个可能不太准,要想非常精确,需要直接count表。下面的脚本有异常不中断,可以重复执行的特点。

  1. create table bk_count_tables
  2. (
  3. owner VARCHAR2(30),
  4. table_name VARCHAR2(30),
  5. part_col varchar2(100),--分区字段
  6. row_s number,
  7. gather_time date
  8. );
  9. create index ind_bct_own_table on bk_count_tables(owner,table_name);
  10. set serveroutput on
  11. declare
  12. cursor c_cursor is select s.OWNER, s.TABLE_NAME, col.column_name part_col
  13. from dba_tables s,
  14. (select owner,
  15. name,
  16. listagg(column_name, ',') within group(order by null) column_name
  17. from (select owner, name, column_name
  18. from dba_part_key_columns
  19. where owner in ('TEST')
  20. and object_type = 'TABLE'
  21. and name not like 'BIN$%'
  22. union all
  23. select owner, name, column_name
  24. from dba_subpart_key_columns
  25. where owner in ('TEST')
  26. and object_type = 'TABLE'
  27. and name not like 'BIN$%')
  28. group by owner, name) col
  29. where s.OWNER in ('TEST')
  30. and not regexp_like(table_name, '[0-9]{3,8}')
  31. and s.table_name not like '%BAK%'
  32. and s.table_name not like '%A2K%'
  33. and s.table_name not like 'BK%'
  34. and s.table_name not like 'BIN%'
  35. and s.OWNER = col.owner(+)
  36. and s.TABLE_NAME = col.name(+)
  37. order by s.TABLE_NAME ;
  38. c_row c_cursor%rowtype;
  39. t_rows number;
  40. begin
  41. for c_row in c_cursor loop
  42. begin
  43. execute immediate 'select count(*) from bk_count_tables where owner=:1 and TABLE_NAME=:2 and rownum=1'
  44. into t_rows using c_row.OWNER,c_row.TABLE_NAME ;
  45. if(t_rows = 0) then
  46. execute immediate 'select count(*) from "'||c_row.TABLE_NAME||'"' into t_rows;
  47. insert into bk_count_tables values(c_row.OWNER,c_row.TABLE_NAME,c_row.part_col,t_rows,sysdate);
  48. commit;
  49. end if;
  50. EXCEPTION
  51. WHEN OTHERS THEN
  52. DBMS_OUTPUT.PUT_LINE(c_row.OWNER||'---'||c_row.TABLE_NAME);
  53. rollback;
  54. end;
  55. end loop;
  56. end;
  57. /
复制代码

总结

以上所述是小编给大家介绍的Oracle 统计用户下表的数据量实现脚本,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对程序员之家网站的支持!



太阳http代理AD
回复

使用道具 举报

关闭

站长推荐上一条 /1 下一条