오라클에서 테이블의 구조와 메타데이터를 파악할 수 있는 쿼리, 알고 계세요?
이번 시간에는 'EMP' 테이블을 대상으로 다양한 정보를 조회할 수 있는 쿼리를 통해 테이블의 스키마, 컬럼, 암호화, 제약 조건 등의 메타데이터를 확인해 보도록 하겠습니다. 살펴보시지요~
('마지막 DDL 작업 시간', '컬럼의 세부 정보', '암호화된 컬럼', '기본 키' 등)
EMP 테이블의 마지막 DDL 시간 확인
'EMP' 테이블에 마지막으로 DDL(Data Definition Language) 작업이 언제 수행되었는지 확인하려면 다음 쿼리를 사용합니다.
SELECT last_ddl_time
FROM sys.user_objects
WHERE object_name = 'EMP'
AND object_type = 'TABLE';
- 결과: 테이블에 구조적 변경(예: 생성, 변경, 삭제)이 마지막으로 이루어진 시간을 보여줍니다.
EMP 테이블 정보 조회
'EMP' 테이블의 기본 메타데이터(파티션 여부, 저장 공간 정보 등)를 조회하는 쿼리입니다.
SELECT t.table_name, user AS owner, t.cluster_name, t.partitioned, t.iot_type, t.tablespace_name, t.last_analyzed,
ROUND(t.num_rows) num_rows, t.temporary, t.table_type, t.table_type_owner, t.nested,
DECODE(NVL(tablespace_name, 'x') || UPPER(partitioned) || NVL(iot_type, 'x') || TO_CHAR(pct_free),
'xNOx0', 'YES', 'NO') is_External, t.dropped, t.initial_extent
FROM sys.user_all_tables t
WHERE t.table_name = 'EMP';
- 결과:
- 테이블이 파티션되어 있는지, 테이블의 소유자, 행 수, 테이블이 외부 테이블인지 여부 등을 확인할 수 있습니다.
last_analyzed필드를 통해 마지막으로 통계 분석이 수행된 시간을 확인할 수 있습니다.
객체 생성 정보 조회
'EMP' 테이블의 생성일, 객체 ID 등의 기본 정보를 조회할 수 있습니다.
SELECT created, last_ddl_time, object_id, status
FROM sys.user_objects
WHERE object_name = 'EMP'
AND object_type = 'TABLE';
- 결과: 테이블의 생성일, 객체 ID, 현재 상태(유효/무효 상태)를 보여줍니다.
컬럼 메타데이터 조회
'EMP' 테이블의 컬럼별 상세 정보를 조회하는 쿼리입니다. 데이터 타입, 길이, NULL 가능 여부, 기본값 등을 확인할 수 있습니다.
SELECT cols.column_id, cols.column_name AS Name, nullable, data_type AS Type,
DECODE(data_type, 'CHAR', char_length, 'VARCHAR', char_length, 'VARCHAR2', char_length,
'NCHAR', char_length, 'NVARCHAR', char_length, 'NVARCHAR2', char_length, NULL) nchar_length,
DECODE(data_type, 'NUMBER', data_precision + data_scale, data_length ) Length,
data_precision, data_scale, data_length dlength, data_default, comments, DATA_TYPE_MOD,
cols.CHAR_USED, INITCAP(histogram) histogram, num_distinct
FROM sys.user_col_comments coms, sys.user_tab_columns cols
WHERE coms.table_name = cols.table_name
AND coms.column_name = cols.column_name
AND cols.table_name = 'EMP'
ORDER BY column_id;
- 결과: 각 컬럼의 이름, 데이터 타입, 길이, 기본값, 컬럼에 대한 설명 등을 제공합니다. 이 정보는 테이블의 구조를 정확하게 파악하는 데 유용합니다.
암호화된 컬럼 정보
암호화된 컬럼이 있는 테이블에서는 다음 쿼리를 사용하여 암호화 알고리즘 등의 정보를 조회할 수 있습니다.
SELECT column_name, encryption_alg, salt
FROM sys.user_encrypted_columns
WHERE table_name = 'EMP';
- 결과: 'EMP' 테이블에 암호화된 컬럼이 있다면, 해당 컬럼의 이름, 암호화 알고리즘, Salt 적용 여부를 보여줍니다.
제약 조건 정보 조회
'EMP' 테이블에 적용된 제약 조건(예: 기본 키) 이름을 조회하는 쿼리입니다.
SELECT CN.NAME
FROM SYS.CDEF$ C, SYS.CON$ CN, SYS.OBJ$ O, SYS.USER$ U
WHERE C.Type# = 2
AND C.CON# = CN.CON#
AND C.OBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND U.NAME = 'USER_NAME'
AND O.NAME = 'EMP';
- 결과: 제약 조건 이름을 보여주며, 테이블의 무결성을 유지하는 데 중요한 정보를 제공합니다.
기본 키 컬럼 정보 조회
'EMP' 테이블의 기본 키로 설정된 컬럼을 확인하려면 다음 쿼리를 사용합니다.
SELECT c1.column_name, c1.position
FROM SYS.DBA_CONS_COLUMNS C1
WHERE C1.table_name = 'EMP'
AND C1.constraint_name = 'PK_EMP'
AND C1.owner = 'USER_NAME'
ORDER BY 2;
- 결과: 'EMP' 테이블의 기본 키를 구성하는 컬럼과 그 순서를 확인할 수 있습니다.
테이블 설명 조회
테이블 자체에 대한 설명을 조회하려면 다음 쿼리를 사용합니다.
SELECT comments
FROM sys.user_tab_comments
WHERE table_name = 'EMP';
- 결과: 'EMP' 테이블에 대해 추가된 설명을 확인할 수 있습니다. 주로 테이블의 목적이나 특징을 설명하는 데 사용됩니다.