最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
Oracle 统计用户下表的数据量实现脚本
时间:2022-06-29 09:27:57 编辑:袖梨 来源:一聚教程网
要想统计用户下所有表的数据量,可以查看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; end; /
相关文章
- 无畏契约夜市什么时候开放-无畏契约夜市多久开启一次 07-07
- PS通道混合器怎么用?应用PS通道混合器快速改变季节教程 07-07
- 无畏契约夜市怎么打开-无畏契约夜市可以在手机上开吗 07-07
- 以太坊怎么购买?国内以太坊交易详细教程与交易平台盘点 07-07
- 龙魂旅人阵容怎么搭配-龙魂旅人阵容搭配推荐 07-06
- 绝区零自选五星选哪个-绝区零自选五星选择攻略 07-06