oracle table row count 조회

migration 중 동기화가 잘되었는지 또는 table count 건수를 비교할 일이 생겼을 때 사용하면 유용할 듯하다

 

-- rows user별로  한번에 보기 (단 쿼리의 특성상 부하를 줄수 있습니다 ㅡ.ㅡ;
SET FEEDBACK OFF
set echo off
set heading off
set rowscount off
set lin 400
set pagesize 50000

spool a.sql
select 'select '''||owner||''' as col1, '''||table_name||''' as col2, count(*) as col3 from '||owner||'.'||table_name||' b UNION ALL' from dba_tables
where owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MGMT_VIEW','WMSYS','DIP','TSMSYS','ORACLE_OCM');
spool off

@a.sql

 

=> 이제 부터가 중요
vi a.sql 을 열어
처음 줄에 with data as(    를 추가한다   제일 마지막 줄에가서 UNION ALL을 지운후 다음을 추가
)
select col1 username , sum(col3)  count from data
group by col1;

예시
with data as(
select 'ARAPROD' as col1, 'HRB19T' as col2, count(*) as col3 from ARAPROD.HRB19T b UNION ALL
select 'ARAPROD' as col1, 'HRB20T' as col2, count(*) as col3 from ARAPROD.HRB20T b UNION ALL
select 'ARAPROD' as col1, 'HRB21T' as col2, count(*) as col3 from ARAPROD.HRB21T b UNION ALL
select 'ARAPROD' as col1, 'HRB22T' as col2, count(*) as col3 from ARAPROD.HRB22T b UNION ALL
select 'ARAPROD' as col1, 'HRB23T' as col2, count(*) as col3 from ARAPROD.HRB23T b)
select col1 username , sum(col3)  count from data
group by col1;

 

결과값 예시 >

USERNAME                                 SUM(COUNT)
---------------------------------------- ----------
AAA                                            3004
ARA                                        15592536
ARA1                                          10217
ARAPROD                                    19225958
ARAPROD2                                     866284
ARAPROD3                                        230
ARAPROD4                                   16493513
BRDB                                        3927512
CSMIG                                          1525
DBWORKS                                        1508
JEONG                                       5242913
JEONGA                                       791660
JEONGTEST                                  21452785
KKK                                         3748385
PERFSTAT                                     447787
REPADMIN                                          0
SEHEE                                        418943
SINU                                              0
SONG                                        5242913
TEST02                                        86949
 
20 rows selected.

-- table 별로 rows 뽑고 싶을 때
예시
with data as(
select 'ARAPROD' as col1, 'HRB19T' as col2, count(*) as col3 from ARAPROD.HRB19T b UNION ALL
select 'ARAPROD' as col1, 'HRB20T' as col2, count(*) as col3 from ARAPROD.HRB20T b UNION ALL
select 'ARAPROD' as col1, 'HRB21T' as col2, count(*) as col3 from ARAPROD.HRB21T b UNION ALL
select 'ARAPROD' as col1, 'HRB22T' as col2, count(*) as col3 from ARAPROD.HRB22T b UNION ALL
select 'ARAPROD' as col1, 'HRB23T' as col2, count(*) as col3 from ARAPROD.HRB23T b)
select * from data
order by col1;

 

 

 

 

 

***********************************************
이건 다른 방법이에요 table에 집어 넣는 방법

*********************************************

-- dbworks 유저 생성
create user dbworks identified by dbworks default tablespace users;
grant connect, resource to dbworks;

-- rows를 담을 table 생성
create table dbworks.counter(username varchar2(20), table_name varchar2(50), count number(30));

 

--query 수행
SET FEEDBACK OFF
set echo off
set heading off
set rowscount off
set lin 400
set pagesize 50000

spool a.sql
select 'insert into dbworks.counter select '''||owner||''','''||table_name||''' count(*) count from '||owner||'.'||table_name||') b ;' from dba_tables
where owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MGMT_VIEW','WMSYS','DIP','TSMSYS','ORACLE_OCM');

spool off

@a.sql
commit;

-- table 별 rows 카운트 수 --
set lin 200
col owner for a15
col table_name for a30
select * from dbworks.counter
order by username;

-- user별  총 rows--
col owner for a15
col table_name for a20
select username, sum(count) from dbworks.counter
group by username;