데이터 사전 조회 범위 (CDB_, DBA_, ALL_, USER_, V$)

#핵심 요약

데이터 딕셔너리는 데이터베이스의 메타데이터임
SYS가 소유하고 데이터베이스가 자동으로 갱신하며, 사용자가 직접 고치면 안 됨

딕셔너리 뷰는 테이블이 아니라 뷰임
예를 들어 SELECT text FROM dba_views WHERE view_name = 'USER_TABLES'로 USER_TABLES가 어떤 기초 테이블로 만들어졌는지 볼 수 있음

V$로 시작하는 뷰는 성능 관련 정보를 보여 주는 동적 성능 뷰임

전체 딕셔너리 목록은 SELECT * FROM dictionary, 어떤 컬럼이 어느 테이블에 있는지는 SELECT table_name FROM user_tab_columns WHERE column_name = '<COLUMN>'으로 찾음

뷰 이름 접두사가 조회 범위를 정함
CDB_(CDB 전체) > DBA_(한 컨테이너 전체) > ALL_(조회 권한이 있는 것) > USER_(내 소유만) 순으로 좁아짐

어떤 뷰가 있는지 모르면 DICTIONARY(동의어 DICT)와 DICT_COLUMNS로 찾음

DBA_ 뷰에서 ORA-00942: table or view does not exist가 나면 이름보다 권한을 먼저 의심함

V$ 뷰는 컨트롤 파일과 메모리에서, DBA_ 뷰는 데이터 딕셔너리에서 정보를 가져옴

#개념과 동작 원리

#데이터 딕셔너리

정보는 기초(base) 테이블에 저장되고 사용자는 그 위의 뷰로 조회함
실습에서는 뷰가 1,000개가 넘는다고 적었음

공식 문서: 딕셔너리 정보는 데이터베이스만 쓰고 바꿔야 함
어떤 사용자도 SYS 스키마의 행이나 객체를 바꾸면 안 되며, 바꾸면 데이터베이스 동작에 영구적인 피해를 줄 수 있음

실습 기록: 기초 테이블도 권한이 있으면 조회할 수 있음
다만 정규화되어 있고 숫자 코드가 많아 사람이 읽기 어려워 뷰를 씀

#접두사와 범위

실습 기록: 상위 접두사 뷰가 하위 뷰를 행과 열 모두에서 포함하는 경우가 많음
USER_TABLES에는 OWNER 열이 없고 ALL_TABLES에는 있음

실습 기록: 모든 접두사가 짝을 이루지는 않음
예를 들어 DBA_LOCK은 있지만 ALL_LOCK은 없음

실습 기록: CDB_와 DBA_ 뷰는 SYSDBA 권한, SELECT ANY DICTIONARY 권한, SELECT_CATALOG_ROLE 역할 중 하나가 있거나 직접 부여받아야 조회할 수 있음

실습 기록: CDB 루트에서 CDB_ 뷰 결과는 그 사용자의 CONTAINER_DATA 속성에 따라 달라짐
PDB 안에서 CDB_ 뷰를 조회하면 대응하는 DBA_ 뷰 범위만 보임

실습 기록: 접두사만 다르고 이름이 같은 뷰들은 같은 기초 테이블을 보고 범위만 다름

#권한이 없을 때의 오류

권한 없는 딕셔너리 뷰를 조회하면 "권한이 없다"가 아니라 ORA-00942(그런 객체가 없음)가 남
실습에서는 객체가 있다는 사실 자체도 정보라서 이렇게 설계됐다고 설명함
같은 오류의 원인은 뷰 이름 오타, 권한 부족, 뷰가 아직 만들어지지 않음(CDB 수동 생성에서 카탈로그 스크립트를 빠뜨린 경우) 세 가지가 있음

#V$ 뷰와 DBA_ 뷰

실습 기록: 성능 분석에서 한 번 조회보다 간격을 두고 두 번 조회해 차이를 보는 이유가 읽기 일관성이 없어서임

실습 기록: MOUNT 상태에서 데이터 파일 이름을 바꾸면, 열린 뒤 V$DATAFILE과 DBA_DATA_FILES 모두 새 이름을 보여 줌
컨트롤 파일이 기준이고 딕셔너리가 따라오므로 수동으로 맞출 필요가 없음

실습 기록: 데이터베이스가 열리지 않는 장애에서는 V$DATAFILE, V$DATAFILE_HEADER 같은 V$ 뷰가 유일한 정보원임

#필수 명령어 또는 SQL

#필요한 뷰 찾기

SELECT COUNT(*) FROM dictionary;

SELECT table_name, SUBSTR(comments, 1, 60) comments
  FROM dictionary
 WHERE table_name LIKE '%TABLESPACE%';

SELECT column_name, SUBSTR(comments, 1, 50) comments
  FROM dict_columns
 WHERE table_name = 'DBA_TABLESPACES';

결과 해석:

실습 기록: DICTIONARY에서 %SEQUENCE%를 찾으면 USER_SEQUENCES, ALL_SEQUENCES, DBA_SEQUENCES, CDB_SEQUENCES와 V$, GV$ 계열 뷰가 나왔음

#소유자와 기초 테이블

SELECT owner, object_type, COUNT(*)
  FROM dba_objects
 WHERE object_name = 'DBA_TABLES'
 GROUP BY owner, object_type;

SELECT COUNT(*) FROM sys.tab$;

결과 해석:

설명: 첫 조회의 소유자는 SYS임
둘째 조회처럼 기초 테이블도 권한이 있으면 조회됨
결과 값은 기재 없음

#접두사별 범위 비교

SELECT table_name FROM user_tables ORDER BY table_name;
SELECT COUNT(*) FROM all_tables;
SELECT COUNT(*) FROM dba_tables;

결과 해석:

설명: ALL_TABLES는 USER_TABLES보다 많음
일반 사용자의 DBA_TABLES 조회는 ORA-00942로 끝남

GRANT SELECT ON system.help TO <USERNAME>;

SELECT owner, table_name FROM all_tables WHERE table_name = 'HELP';

REVOKE SELECT ON system.help FROM <USERNAME>;

결과 해석:

설명: 권한을 준 순간 소유 관계는 그대로인데 ALL_TABLES에 그 테이블이 나타남

실습 확인 문제 기록: SELECT ANY TABLE만 받은 사용자는 다른 스키마 테이블은 조회했지만 DBA_TABLES 조회에서 ORA-00942가 났음
실습에서는 다음 문제로 SELECT_CATALOG_ROLE을 부여한 뒤 다시 조회하게 했고 결과는 적지 않았음

#V$ 뷰와 DBA_ 뷰 비교

SELECT ts#, name FROM v$tablespace ORDER BY ts#;
SELECT tablespace_name, extent_management, status FROM dba_tablespaces;

SELECT v.file#, v.name v_name, d.file_name d_name
  FROM v$datafile v, dba_data_files d
 WHERE v.file# = d.file_id
 ORDER BY v.file#;

SELECT * FROM v$fixed_table WHERE LOWER(name) LIKE '%datafile%';

결과 해석:

설명: 두 테이블스페이스 뷰는 같은 대상을 가리키지만 열 구성이 다름
조인한 데이터 파일 이름은 정확히 같음
V$FIXED_TABLE은 동적 성능 뷰 목록임

#사용 조건과 주의사항

실습 9.6절 접두사 비교 실습은 확인 포인트에서 ⑥ 단계(USER_TABLES에 OWNER 열이 없음)를 언급하지만 해당 단계 명령이 빠져 있음

실습 9.5절의 NOMOUNT 비교 실습(SHUTDOWN과 STARTUP NOMOUNT 포함)은 데이터베이스 기동과 종료 범위라 여기서 다루지 않음

실습의 PDB 이름 표기가 섞여 있음