타입 변환과 DDL 생성

Oracle에서 PostgreSQL로 타입과 DDL 변환, 적재 전 테이블과 적재 후 제약조건 및 뷰와 권한 생성 순서

map_typeTYPE_RULESNUMBERtimestamp(0)DDL 세 묶음search_pathLEFT JOINCOALESCEGRANTALL_TAB_PRIVS

#핵심 요약

실습 도구는 DDL을 세 묶음으로 나눔
테이블은 적재 전, PK, UNIQUE, CHECK, 인덱스, FK는 적재 후, 시퀀스, 뷰, 권한은 마지막임

제약조건과 인덱스를 데이터를 다 넣은 뒤 만드는 이유는 행을 넣을 때마다 인덱스를 고치지 않아 적재가 빠르기 때문임

Oracle DATE는 시, 분, 초를 가지므로 기본값을 timestamp(0)으로 둠
PostgreSQL date로 바꾸면 시간이 사라짐

타입 규칙은 계산용 map_type()과 화면 표시용 TYPE_RULES 두 곳에 있음
규칙을 고칠 때는 둘 다 고쳐야 함

#개념과 동작 원리

#타입 매핑

Oracle	PostgreSQL	실습 예
NUMBER(p,0), p ≤ 4 / ≤ 9 / ≤ 18 / > 18	smallint / integer / bigint / numeric(p)	CUST_ID NUMBER(10,0) → bigint
NUMBER(p,s)	numeric(p,s)	AMOUNT NUMBER(12,2) → numeric(12,2)
VARCHAR2(n) / CHAR(n)	varchar(n) / char(n)	CUST_NM → varchar(50), GENDER → char(1)
DATE	timestamp(0)	시간 보존
CLOB / BLOB	text / bytea	MEMO CLOB → text
TIMESTAMP(n) / WITH TIME ZONE	timestamp(n) / timestamptz	소스 스키마에 없음

정밀도 없는 NUMBER, FLOAT, NVARCHAR2 같은 다른 타입의 도구 규칙은 기재 없음

#권한 변환 규칙

Oracle	PostgreSQL
SELECT / INSERT / UPDATE / DELETE / REFERENCES ON 테이블, 뷰	같은 이름의 GRANT와 스키마 `USAGE` PostgreSQL은 스키마에도 권한이 필요함
SELECT ON 시퀀스	`usage, select on sequence`
EXECUTE ON 프로시저, 함수	PL/SQL을 변환한 뒤 `grant execute on routine` 확인할 것으로 알리고 6단계 점검에서 마무리
PostgreSQL에 없는 역할에 준 권한	생략하고 경고 역할을 먼저 만들어야 함

실습에서는 이 규칙을 위해 Oracle ALL_TAB_PRIVS를 읽음
실습에서는 객체 권한 9건 가운데 8건이 GRANT로 만들어지고 스키마 USAGE 2건이 더해졌으며, 남은 1건은 프로시저 EXECUTE였음

#필수 명령어 또는 SQL

#① 테이블(적재 전)

PostgreSQL (대상 DB 소유자)객체 또는 설정을 변경함

create schema if not exists <SCHEMA>;

create table <SCHEMA>.cust (
    cust_id              bigint not null,
    cust_nm              varchar(50) not null,
    gender               char(1),
    tel                  varchar(20),
    join_dt              timestamp(0),
    grade_cd             varchar(2)
);

create table <SCHEMA>.orders (
    order_id             bigint not null,
    cust_id              bigint,
    order_dt             timestamp(0),
    amount               numeric(12,2),
    status               varchar(10),
    memo                 text
);

결과 해석: 개별 실행 출력은 기재 없음

#② 제약조건, 인덱스(적재 후)

PostgreSQL (대상 DB 소유자)객체 또는 설정을 변경함

alter table <SCHEMA>.cust add constraint cust_pk primary key (cust_id);
alter table <SCHEMA>.orders add constraint orders_pk primary key (order_id);
alter table <SCHEMA>.orders add constraint orders_cust_fk foreign key (cust_id) references <SCHEMA>.cust (cust_id);

결과 해석: 참조되는 PK를 먼저 만들고 FK를 나중에 만듦

#③ 시퀀스, 뷰, 권한(마지막, 일부)

PostgreSQL (대상 DB 소유자)객체 또는 설정을 변경함

set search_path to <SCHEMA>, public;
create sequence <SCHEMA>.orders_seq start with 1000001 increment by 1;
create or replace view <SCHEMA>.v_cust_sales as
SELECT c.cust_id, c.cust_nm, COALESCE(SUM(o.amount), 0) AS total_amt
FROM cust c
  LEFT JOIN orders o ON c.cust_id = o.cust_id
GROUP BY c.cust_id, c.cust_nm;
grant usage on schema <SCHEMA> to <USERNAME>;
grant select, insert, update on <SCHEMA>.orders to <USERNAME>;
grant usage, select on sequence <SCHEMA>.orders_seq to <USERNAME>;
grant select on <SCHEMA>.v_cust_sales to <REPORT_USER>;

결과 해석: 시퀀스는 Oracle의 현재값부터 시작함

#사용 조건과 주의사항

뷰 생성에서 relation "cust" does not exist가 났음
Oracle 뷰 본문에는 테이블 이름에 스키마가 없고 PostgreSQL은 search_path(기본 public)에서만 찾기 때문임
실습에서는 ③ 묶음 첫 줄에 set search_path를 둠

시간이 없는 컬럼만 있는 스키마일 때만 DATE를 date로 고름

실습에서는 이 도구를 AI에게 고쳐 달라고 할 때 "어느 파일, 어느 함수나 키, 무엇을, 건드리지 말 것"을 함께 적으라고 했음

실습의 첫 화면 캡처는 권한 이행을 넣기 전 버전이라 묶음 이름과 경고 수가 지금 버전과 다르다고 수업 자료가 밝혔음