oracle to postgresql ORA-00943 오류, Oracle 테이블 대소문자와 스키마 확인 방법

Oracle to PostgreSQL Migration

oracle_fdw ORA-00943 오류, Oracle 테이블 대소문자와 스키마 확인 방법

Oracle에서 PostgreSQL로 마이그레이션하는 과정에서 oracle_fdw 외래 테이블은 생성되지만 조회 시 ORA-00943: table or view does not exist 오류가 발생할 수 있다. 이 경우 대부분 Oracle 쪽 스키마명과 테이블명을 PostgreSQL 외래 테이블 옵션에 정확히 지정하지 않아 발생한다.

오류 상황 정리

PostgreSQL 15 환경에서 oracle_fdw 확장을 설치하고 Oracle 12c 데이터베이스에 연결한 뒤, 외래 서버와 사용자 매핑까지 정상 생성된 상태라면 연결 자체는 어느 정도 준비된 상태로 볼 수 있다.

문제는 외래 테이블 생성까지는 성공하지만 실제 조회 시점에 Oracle 테이블을 찾지 못한다는 점이다. 이때 다음과 같은 오류가 발생한다.

ERROR: Oracle table "public"."emp" for foreign table "test" does not exist or does not allow read access
DETAIL: ORA-00943: table or view does not exist.
HINT: Oracle table names are case sensitive (normally all uppercase).

이 메시지에서 핵심은 "public"."emp"다. PostgreSQL의 public 스키마를 Oracle 스키마처럼 지정했기 때문에, Oracle 입장에서는 PUBLIC.EMP 또는 대소문자가 반영된 public.emp 객체를 찾으려 한다.

핵심 요약 외래 테이블 생성 성공은 Oracle 실제 테이블 존재 검증을 완료했다는 의미가 아니다.
조회 시점에 Oracle 테이블 접근이 발생하면서 오류가 드러날 수 있다.
options(schema 'public', table 'emp')에서 schema는 PostgreSQL schema가 아니라 Oracle schema다.
Oracle에서 따옴표 없이 만든 객체명은 보통 대문자로 저장된다.

가장 흔한 원인: Oracle 스키마명을 public으로 잘못 지정

PostgreSQL에서 기본 스키마가 public이라서 외래 테이블 옵션에도 schema 'public'을 넣는 경우가 많다. 하지만 oracle_fdw의 schema 옵션은 PostgreSQL의 스키마가 아니라 Oracle 쪽 owner 또는 schema를 의미한다.

예를 들어 Oracle 테이블이 PROPONEST_SAAS.TEST1이라면 외래 테이블 옵션도 Oracle 기준으로 작성해야 한다. PostgreSQL 안에서 외래 테이블을 어느 스키마에 만들지는 CREATE FOREIGN TABLE의 이름 앞에 붙이는 PostgreSQL 스키마로 결정한다.

구분	의미	예시
PostgreSQL 외래 테이블 위치	PostgreSQL 안에서 외래 테이블이 생성될 스키마	public.test1
options(schema ...)	Oracle 쪽 테이블 owner 또는 schema	PROPONEST_SAAS
options(table ...)	Oracle 쪽 실제 테이블명	TEST1
user mapping	Oracle에 접속할 계정 정보	오라클유저아이디

해결 예시: Oracle 객체명을 대문자로 지정

Oracle에서 다음처럼 큰따옴표 없이 테이블을 만들었다면 실제 객체명은 보통 대문자로 저장된다.

CREATE TABLE test1 (
    empno NUMBER
);

이 경우 Oracle 내부에서는 TEST1로 저장된다. 따라서 PostgreSQL의 외래 테이블 옵션에도 대문자로 지정하는 것이 안전하다.

CREATE FOREIGN TABLE public.test1 (
    empno numeric
)
SERVER ora
OPTIONS (
    schema 'PROPONEST_SAAS',
    table 'TEST1'
);

만약 Oracle 테이블이 EMP이고 owner가 PROPONEST_SAAS라면 다음과 같이 작성한다.

CREATE FOREIGN TABLE public.emp (
    empno numeric
)
SERVER ora
OPTIONS (
    schema 'PROPONEST_SAAS',
    table 'EMP'
);

운영 환경에서는 schema 'public', table 'emp'처럼 PostgreSQL 기준으로 작성하지 말고, Oracle의 실제 owner와 object name을 기준으로 작성해야 한다.

Oracle에서 실제 스키마와 테이블명 확인하기

먼저 Oracle에 직접 접속해서 테이블 owner와 table name을 확인해야 한다. 같은 이름의 테이블이라도 어떤 owner에 있는지에 따라 접근 방식이 달라진다.

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

특정 문자열이 포함된 테이블을 찾고 싶다면 다음처럼 조회할 수 있다.

SELECT owner, table_name
FROM all_tables
WHERE table_name LIKE '%TEST%';

현재 Oracle 접속 계정이 해당 테이블을 직접 조회할 수 있는지도 확인해야 한다.

SELECT *
FROM PROPONEST_SAAS.TEST1
WHERE ROWNUM <= 10;

위 쿼리가 Oracle에서 실패한다면 PostgreSQL의 oracle_fdw에서도 조회할 수 없다. 이 경우 PostgreSQL 설정 문제가 아니라 Oracle 권한 또는 객체명 문제를 먼저 해결해야 한다.

권한 문제도 함께 확인해야 한다

오류 메시지에는 “table does not exist or does not allow read access”라는 표현이 함께 나온다. 즉 실제로 테이블이 없을 수도 있고, 테이블은 있지만 Oracle 접속 계정에 SELECT 권한이 없을 수도 있다.

create user mapping에 지정한 Oracle 계정이 테이블 owner가 아니라면, Oracle에서 해당 계정에 명시적으로 조회 권한을 부여해야 한다.

GRANT SELECT ON PROPONEST_SAAS.TEST1 TO ORACLE_USER_ID;

특히 role을 통해 부여된 권한은 외부 접속이나 특정 실행 환경에서 기대한 방식으로 동작하지 않을 수 있으므로, 마이그레이션 작업용 계정에는 필요한 테이블에 대해 직접 권한을 부여하는 방식이 명확하다.

확인 항목	점검 방법
Oracle 테이블 존재 여부	all_tables에서 owner와 table_name을 확인한다.
Oracle SELECT 가능 여부	user mapping에 사용한 Oracle 계정으로 직접 SELECT를 수행한다.
대소문자	따옴표 없이 만든 Oracle 객체는 보통 대문자로 지정한다.
schema 옵션	PostgreSQL 스키마가 아니라 Oracle owner를 입력한다.
table 옵션	Oracle의 실제 table_name을 입력한다.

user mapping은 어떻게 이해해야 하나

create user mapping은 PostgreSQL 사용자와 Oracle 접속 계정을 연결하는 설정이다. 여기서 FOR 뒤에는 PostgreSQL에서 외래 테이블을 조회할 사용자명을 넣고, OPTIONS(user ..., password ...)에는 Oracle 계정 정보를 넣는다.

CREATE USER MAPPING FOR pg_user
SERVER ora
OPTIONS (
    user 'ORACLE_USER_ID',
    password 'ORACLE_PASSWORD'
);

예를 들어 PostgreSQL에서 postgres 사용자로 조회할 예정이라면 다음과 같은 구조가 된다.

GRANT USAGE ON FOREIGN SERVER ora TO postgres;

CREATE USER MAPPING FOR postgres
SERVER ora
OPTIONS (
    user 'PROPONEST_SAAS',
    password 'oracle_password'
);

이때 Oracle 계정 PROPONEST_SAAS가 자기 소유의 TEST1 테이블을 조회한다면 별도 권한 부여가 필요 없을 수 있다. 반대로 다른 owner의 테이블을 조회한다면 Oracle 쪽에서 SELECT 권한이 필요하다.

올바른 생성 순서 예시

아래는 Oracle owner가 PROPONEST_SAAS, Oracle 테이블명이 TEST1, PostgreSQL 사용자명이 postgres인 경우의 기본 예시다.

CREATE EXTENSION oracle_fdw;

CREATE SERVER ora
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (
    dbserver '//000.00.00.236:1521/orcl'
);

GRANT USAGE ON FOREIGN SERVER ora TO postgres;

CREATE USER MAPPING FOR postgres
SERVER ora
OPTIONS (
    user 'PROPONEST_SAAS',
    password 'oracle_password'
);

CREATE FOREIGN TABLE public.test1 (
    empno numeric
)
SERVER ora
OPTIONS (
    schema 'PROPONEST_SAAS',
    table 'TEST1'
);

SELECT *
FROM public.test1;

PostgreSQL에서 외래 테이블 이름은 public.test1처럼 소문자로 만들어도 된다. 중요한 것은 OPTIONS 안의 schema와 table 값이 Oracle 기준이라는 점이다.

IMPORT FOREIGN SCHEMA를 사용하는 방법

수동으로 CREATE FOREIGN TABLE을 작성하다 보면 컬럼 타입, 테이블명, 대소문자, owner를 잘못 지정하기 쉽다. Oracle에 있는 테이블을 PostgreSQL 외래 테이블로 가져올 때는 IMPORT FOREIGN SCHEMA를 쓰는 방법도 검토할 수 있다.

IMPORT FOREIGN SCHEMA "PROPONEST_SAAS"
LIMIT TO ("TEST1")
FROM SERVER ora
INTO public;

이 방식은 Oracle 쪽 스키마에서 테이블 정의를 읽어 PostgreSQL 외래 테이블을 생성하므로, 수동 작성보다 실수를 줄일 수 있다. 다만 Oracle 객체명과 권한 문제는 여전히 동일하게 적용된다.

IMPORT FOREIGN SCHEMA를 쓰면 좋은 경우 테이블 수가 많다.
컬럼 타입을 일일이 맞추기 어렵다.
대소문자와 컬럼 정의 실수를 줄이고 싶다.
마이그레이션 전 Oracle 테이블 구조를 빠르게 확인하고 싶다.

대소문자 규칙을 정확히 이해하기

Oracle과 PostgreSQL은 식별자 대소문자 처리 방식이 다르다. Oracle은 큰따옴표 없이 만든 객체명을 대문자로 저장하고, PostgreSQL은 큰따옴표 없이 만든 객체명을 소문자로 접는다.

DBMS	생성 SQL	실제 저장 이름
Oracle	CREATE TABLE test1 (...)	TEST1
Oracle	CREATE TABLE "test1" (...)	test1
PostgreSQL	CREATE TABLE test1 (...)	test1
PostgreSQL	CREATE TABLE "TEST1" (...)	TEST1

따라서 Oracle에서 일반적으로 생성한 테이블이라면 oracle_fdw 옵션에는 대문자 owner와 대문자 table명을 넣는 것이 기본이다.

환경변수 설정에서 점검할 부분

현재 오류는 Oracle 라이브러리 로딩 실패라기보다 Oracle 객체 조회 문제에 가깝다. 그래도 운영 환경에서는 환경변수 오타와 라이브러리 경로도 정리해두는 것이 좋다.

export PG_HOME=/usr/pgsql-15
export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$PG_HOME/bin:$ORACLE_HOME/bin:$PATH

제시된 설정에는 $LB_LIBRARY_PATH처럼 보이는 오타 가능성이 있다. 일반적으로는 $LD_LIBRARY_PATH를 사용한다. 또한 PATH에는 실행 파일 경로를 넣고, 라이브러리 경로는 LD_LIBRARY_PATH에 두는 식으로 분리하는 것이 명확하다.

라이브러리 경로 문제가 있으면 보통 extension 로딩이나 Oracle 접속 단계에서 다른 오류가 발생한다. 현재처럼 외래 테이블 조회 시 ORA-00943이 나온다면 우선 Oracle owner, table name, SELECT 권한을 확인하는 것이 맞다.

자주 하는 실수

oracle_fdw를 처음 사용할 때 가장 많이 헷갈리는 부분은 PostgreSQL의 스키마와 Oracle의 스키마를 같은 의미로 생각하는 것이다. 외래 테이블은 PostgreSQL에 만들어지지만 실제 데이터는 Oracle에 있으므로, 원격 객체를 가리키는 옵션은 Oracle 기준으로 작성해야 한다.

실수	문제점	수정 방향
schema 'public' 사용	Oracle에 PUBLIC owner의 테이블이 있다고 가정하게 된다.	Oracle 실제 owner인 PROPONEST_SAAS 등을 사용한다.
table 'emp' 사용	Oracle의 일반 테이블명은 보통 EMP로 저장된다.	table 'EMP'처럼 대문자로 지정한다.
Oracle 권한 미확인	테이블은 있어도 user mapping 계정이 읽지 못할 수 있다.	Oracle에서 직접 SELECT 테스트를 한다.
PostgreSQL 사용자와 Oracle 사용자를 혼동	user mapping의 FOR와 OPTIONS user 의미를 반대로 이해할 수 있다.	FOR는 PostgreSQL 사용자, OPTIONS user는 Oracle 사용자로 이해한다.

점검 순서

같은 오류가 반복된다면 다음 순서대로 확인하는 것이 가장 빠르다. 연결 자체가 되는지보다, Oracle에서 실제로 어떤 이름과 권한으로 테이블을 볼 수 있는지가 핵심이다.

• Oracle에 user mapping에 입력한 계정으로 직접 접속한다.
• SELECT owner, table_name FROM all_tables로 실제 owner와 table명을 확인한다.
• Oracle에서 SELECT * FROM OWNER.TABLE_NAME이 되는지 확인한다.
• PostgreSQL 외래 테이블의 options(schema ...)에 Oracle owner를 대문자로 입력한다.
• options(table ...)에 Oracle table_name을 대문자로 입력한다.
• 필요하면 IMPORT FOREIGN SCHEMA로 외래 테이블을 자동 생성한다.

결론

이 사례의 핵심 원인은 oracle_fdw가 PostgreSQL의 public.emp가 아니라 Oracle의 public.emp를 찾으려고 했다는 점이다. Oracle 쪽에 실제로 PUBLIC.EMP가 없거나 해당 계정이 읽을 수 없다면 ORA-00943 오류가 발생한다.

Oracle에서 큰따옴표 없이 만든 테이블은 보통 대문자로 저장되므로, schema 'PROPONEST_SAAS', table 'TEST1'처럼 Oracle 실제 owner와 table명을 기준으로 지정해야 한다.

관리자 입장에서 가장 안전한 대응은 Oracle에서 먼저 user mapping 계정으로 직접 SELECT를 확인한 뒤, PostgreSQL의 foreign table 옵션을 Oracle 기준으로 다시 만드는 것이다. 테이블 수가 많다면 IMPORT FOREIGN SCHEMA를 활용하면 수동 지정 실수를 줄일 수 있다.