오라클 테이블별 컬럼 갯수 구하는 쿼리

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

쿼리 : 오라클 테이블별 컬럼 갯수 구하는 쿼리

account_status 가 OPEN 인 유저들의 모든 테이블별 컬럼 갯수 구하는 쿼리

SQL>
set lines 200 pages 1000
col table_name for a30
col owner for a20
select owner, table_name, count(column_name)
from dba_tab_cols
where owner in (select username from dba_users where account_status='OPEN')
group by owner, table_name
order by 1
/
 
OWNER       TABLE_NAME              COUNT(COLUMN_NAME)
---------- ------------------------------ ------------------
SYS       V_$DLM_RESS                       9
SYS       V_$DLM_TRAFFIC_CONTROLLER              17
SYS       V_$DNFS_CHANNELS                  10
SYS       V_$DNFS_FILES                   4
SYS       V_$DNFS_SERVERS                   7
SYS       V_$DNFS_STATS                  26
SYS       V_$DYNAMIC_REMASTER_STATS              13
SYS       V_$EMON                      30

 

 

참조 :