오라클에서 스키마 정보를 추출하는 방법

오라클에서 스키마 정보를 추출하는 방법은 여러 가지가 있습니다. 일반적으로 사용하는 방법은 다음과 같습니다.
✅ 사용자(스키마) 목록 확인하기

-- 전체 사용자(스키마) 목록 조회
SELECT username FROM dba_users;

✅ 특정 스키마의 테이블 목록 추출

-- 현재 접속한 사용자 스키마의 테이블 조회
SELECT table_name FROM user_tables;

-- 특정 사용자(스키마)의 테이블 조회 (DBA권한 필요)
SELECT table_name FROM all_tables WHERE owner = '스키마명';

✅ 특정 스키마의 컬럼 정보 추출

-- 특정 테이블의 컬럼 목록과 데이터타입 등 정보 조회
SELECT column_name, data_type, data_length, nullable
  FROM all_tab_columns
 WHERE owner = '스키마명'
   AND table_name = '테이블명';

✅ 특정 스키마의 제약조건 조회하기

-- 특정 스키마의 제약조건 조회
SELECT constraint_name, constraint_type, table_name
  FROM all_constraints
 WHERE owner = '스키마명';

제약조건 타입은 다음과 같습니다

• P: Primary Key
• R: Foreign Key
• U: Unique
• C: Check

✅ 인덱스 정보 추출하기

-- 특정 스키마의 인덱스 정보 조회
SELECT index_name, table_name, uniqueness
  FROM all_indexes
 WHERE owner = '스키마명';

✅ 오브젝트 타입별 목록 추출하기

-- 스키마의 오브젝트 타입별 목록 조회 (테이블, 뷰, 시퀀스, 패키지 등)
SELECT object_name, object_type
  FROM all_objects
 WHERE owner = '스키마명';

✅ 스키마 구조 전체를 DDL 형태로 추출하기 (DBMS_METADATA 이용)
Oracle의 DBMS_METADATA 패키지를 활용하여 테이블 및 기타 오브젝트의 DDL 문장을 추출할 수 있습니다.

-- 특정 테이블 DDL 추출 예시
SELECT DBMS_METADATA.GET_DDL('TABLE', '테이블명', '스키마명') FROM dual;

-- 특정 스키마 전체의 테이블 DDL 추출 (예시)
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner)
  FROM all_tables
 WHERE owner = '스키마명';

참고: DDL 추출 시 권한에 따라 ORA-31603 오류가 발생할 수 있으며, 이때는 EXECUTE_CATALOG_ROLE 권한이 필요합니다.

✅ 데이터 펌프(expdp, impdp)를 활용한 스키마 추출(덤프파일 생성)
 - OS 단에서 Oracle 데이터 펌프를 사용하여 특정 스키마 전체를 덤프파일로 추출할 수 있습니다.
 - directory는 Oracle에 미리 등록된 디렉터리명입니다. (예: DATA_PUMP_DIR)

expdp userid=system/password schemas=스키마명 directory=디렉터리명 dumpfile=스키마명.dmp logfile=스키마명.log

 

오라클 - ORA-31603 에러