오라클 내의 전체 테이블 또는 컬럼을 조회하려면 SYS내의 VIEW를 이용하면됩니다.
1. ALL_TABLES
- 로그인 된 계정의 권한으로 접근할 수 있는 모든 테이블들
- 예 ) 테이블명에 "테스트"를 포함한 테이블 검색 :
SELECT * FROM ALL_TABLES WHERE LIKE '%테스트%';
2. USER_TABLES
- 로그인 된 계정이 소유하고 있는 테이블들
SELECT * FROM ALL_TABLES WHERE OWNER = '로그인된계정' 과 같다.
3. ALL_TAB_COLUMNS
- 로그인 된 계정의 권한으로 접근할 수 있는 모든 테이블 내의 컬럼들
- 예 ) 컬럼명에 "테스트"를 포함한 컬럼 검색
SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%테스트%'
4. USER_TAB_COLUMNS
- 로그인된 계정이 소유하고 있는 테이블들
SELECT * FROM ALL_TAB_COLUMNS WHERE OWNER = '로그인된계정' 과 같다.
위 테이블들을 이용해서 여러 응용이 가능합니다.
그런데..... 속도는 느려요...ㅋㅋ
그대로 일일이 노가다 해서 찾는거 보다는 좋겠죠?
응용 - 1 ) 데이터베이스 전체 데이터 수 구하기 - 아래의 쿼리로 조회된 값들을 이어서 쿼리문을 만들 수 있습니다.
[쿼리문 만들기 위한 조회]
SELECT 'SELECT (' FROM DUAL UNION ALL
SELECT '(SELECT count(*) cnt FROM '||TABLE_NAME||') + ' FROM USER_TABLES UNION ALL
SELECT '0) TOTAL_COUNT FROM DUAL; ' FROM DUAL;
[조회된 결과값으로 쿼리만들기]
SELECT (
(SELECT count(*) cnt FROM T_CNKC_INTG_PRSN_INFO01L1_0329) +
(SELECT count(*) cnt FROM T_CNKC_INTG_DPRT01M1_B) +
...
0) TOTAL_COUNT FROM DUAL;
응용 - 2 ) 데이터베이스 전체 컬럼에서 원하는 데이터 검색 - 아래의 쿼리로 조회된 값들을 이어서 쿼리문을 만들 수 있습니다.
[쿼리문 만들기 위한 조회]
SELECT 'SELECT count('||COLUMN_NAME||') cnt, '''||TABLE_NAME||'.'||COLUMN_NAME||''' target, TO_CHAR('||COLUMN_NAME||') contents FROM '||TABLE_NAME||' WHERE TO_CHAR('||COLUMN_NAME||') like ''%'||'찾을내용'||'%''
GROUP BY '||COLUMN_NAME||'
UNION ALL' as query FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME like '%RTUR_ID%'
ORDER BY TABLE_NAME,COLUMN_ID
[조회된 결과값으로 쿼리만들기]
SELECT count(RTUR_ID) cnt, 'T_CNKC_INTG_USER01H1.RTUR_ID' target, TO_CHAR(RTUR_ID) contents FROM T_CNKC_INTG_USER01H1 WHERE TO_CHAR(RTUR_ID) like '%찾을내용%'
GROUP BY RTUR_ID
UNION ALL
SELECT count(RTUR_ID) cnt, 'T_CNKC_INTG_USER01H1_1114.RTUR_ID' target, TO_CHAR(RTUR_ID) contents FROM T_CNKC_INTG_USER01H1_1114 WHERE TO_CHAR(RTUR_ID) like '%찾을내용%'
GROUP BY RTUR_ID
UNION ALL
SELECT count(RTUR_ID) cnt, 'T_CNKC_INTG_USER01M1.RTUR_ID' target, TO_CHAR(RTUR_ID) contents FROM T_CNKC_INTG_USER01M1 WHERE TO_CHAR(RTUR_ID) like '%찾을내용%'
GROUP BY RTUR_ID
UNION ALL
...
마지막 UNION ALL 삭제
1. 전체 테이블 조회
SELECT * FROM ALL_TABLES;
2. 전체 데이터 수 조회
① 번 쿼리 실행(결과가 쿼리 형태로 출력됨)
SELECT 'SELECT (' FROM DUAL UNION ALL SELECT '(SELECT count(*) cnt FROM '||TABLE_NAME||') + ' FROM USER_TABLES UNION ALL SELECT '0) TOTAL_COUNT FROM DUAL; ' FROM DUAL;
② ①번 쿼리의 결과를 쿼리로써 실행
3. DB 사용용량 확인
select sum(bytes)/1024/1024/1024 from dba_segments; (GB) select sum(bytes) from dba_segments;
4. DB 전체 용량 확인
select sum(bytes) from dba_data_files;
현재 진행하는 프로젝트는 솔루션을 가지고 작업을 하는데요.
테이블이 엄청나게 많더라구요.
테이블 수를 보고 깜짝 놀랐는데...
도대체 테이블 수가 몇 개나 되는지 궁금한데 어떻게 알 수 있을까요?
select object_name from
user_objects where object_type = 'TABLE'; -- 테이블 목록
select object_name from user_objects where object_type = ‘SEQUENCE’; -- 시퀀스 목록
이 쿼리를 사용하면 테이블 목록을 가져올 수 있답니다.
테이블 개수가 224개나 된다니... 정말 엄청나네요~ ㄷㄷㄷ
테이블의 갯수가 점점 많아지다보면 어떠한 기능을 만들때 어떤 테이블을 써야하는지 어떤 테이블을 조인해야하는지 모르는 경우가 많습니다. 이럴때 활용할 수 있는 방법이 전체 테이블 목록을 보면서 찾는법, 코멘트를 찾는법 컬럼을 찾는법등이 있습니다. 오늘은 이러한 방법에 대해 소개해드리려 합니다.
전체 테이블 목록 조회
--전체 테이블 목록 조회 select * from all_all_tables select * from dba_tables select * from all_objects where object_type = 'TABLE' --접속 계정 테이블 조회 select * from tabs select * from user_objects where object_type = 'TABLE' select * from user_tables전체 테이블 조회는 권한이 있을경우에 실행시킬 수 있으며 권한이 없다면 접속계정 테이블 조회를 하시면 됩니다. 결과는 위와 같습니다.
전체 테이블 코멘트 조회
select * from all_tab_comments --전체 테이블 코멘트 조회 select * from user_tab_comments --접속계정 전체 테이블 코멘트 조회어떤 테이블을 써야할지 모를경우 가장 활용도가 높은 방법인 테이블의 설명(코멘트)를 확인하는 방법입니다. where절에 Like를 사용하여 원하는 코멘트를 넣어 찾는 방법이 가장 좋습니다.
전체 컬럼 조회
select * from cols --전체 컬럼조회 select * from all_tab_columns --전체 테이블 컬럼 조회 select * from user_tab_columns --접속계정 테이블 컬럼 조회특정 컬럼을 아는 경우 잘 사용할 수 있는 방법입니다. 특히 조인해야할때 특정 컬럼을 키로 조인해야할때 그 컬럼을 쓰는 테이블을 확인할때 많이 사용합니다. where절에 coloumn_name = [컬럼명]을 사용하시면 그 컬럼을 사용하고 있는 테이블을 확인하실 수 있습니다.
전체 컬럼 코멘트 조회
select * from all_col_comments --전체 컬럼 코멘트 조회 select * from user_col_comments --접속계정 컬럼 코멘트 조회테이블에도 코멘트가 있듯이 컬럼에도 코멘트가 있습니다. 어떤컬럼을 사용해야할지 모르겠다면 컬럼의 코멘트를 조회해보는것도 좋은 방법입니다. where절에 원하는 코멘트를 입력해서 조회하여 컬럼이나 테이블을 찾는 방법을 많이 사용합니다.