Shulou(Shulou.com)05/31 Report--

How to count the data volume of tables in users in Oracle? I believe that many inexperienced people are helpless about this. For this reason, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

To count the amount of data in all tables under the user, you can view user_tables, which is statistical information. Of course, this may not be accurate. To be very accurate, you need to directly count the table. The following script has the feature of exception not interrupting and can be executed repeatedly.

create table bk_count_tables( owner VARCHAR2(30), table_name VARCHAR2(30), part_col varchar2(100),--partition field row_s number, gather_time date);create index ind_bct_own_table on bk_count_tables(owner,table_name);set serveroutput ondeclarsor 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;/After reading the above, do you know how to count the amount of data in the user table in Oracle? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.