Oracle 26ai 설치 (docker) 및 graph 데이터 생성 조회 예제

이전에 19c 를 도커로 설치했었다.

Docker 를 이용한 오라클DB 설치 ( 19c )

Docker 를 이용한 오라클DB 설치 ( 19c )

동일하게 또 하면 잘된다. 26ai 이미지로만 바꿔서 실행하자.

 

1,2,3 은 동일하게 실행 후,

 

# 이미지 pull
docker pull container-registry.oracle.com/database/free:latest
# 정상 다운로드 확인
docker images | grep 'database/free'
# 본인의 서버 cpu 아키텍처에 맞는지 확인
docker image inspect container-registry.oracle.com/database/free:latest --format '{{.Os}}/{{.Architecture}}'
# 볼륨 생성 확인
docker volume create oracle26ai-oradata
docker volume ls | grep oracle26ai-oradata
# 오라클 컨테이너 실행( 서비스 포트, 암호설정을 본인에게 맞게 수정 )
docker run -d --name oracle26ai-free   --restart unless-stopped   -p 39831:1521   -p 39832:5500   -e ORACLE_PWD='Oracle26ai_StrongPwd1'   -e ORACLE_CHARACTERSET=AL32UTF8   -v oracle26ai-oradata:/opt/oracle/oradata   container-registry.oracle.com/database/free:latest
# 정상 로그 확인 ( READY )
docker logs -f oracle26ai-free
docker ps --format 'table {{.Names}}\t{{.Status}}\t{{.Ports}}

 

정상 설치가 되었으면, 컨테이너 안쪽에 접속 하여 확인하자.

docker exec -it oracle26ai-free bash
sqlplus sys/Oracle26ai_StrongPwd1@localhost:1521/FREEPDB1 as sysdba

 

필요한 사용자를 생성하자.

특히, 이번 오라클 버전에서는 GraphDB 쓰려고 하니 아래와 같이 생성에서 권한을 모두 부여한다.

DROP USER graphuser CASCADE;

CREATE USER graphuser IDENTIFIED BY "GraphUser_StrongPassword_2026!"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION TO graphuser;
GRANT CREATE TABLE TO graphuser;
GRANT CREATE VIEW TO graphuser;
GRANT CREATE SEQUENCE TO graphuser;
GRANT CREATE PROCEDURE TO graphuser;
GRANT CREATE TRIGGER TO graphuser;
GRANT CREATE TYPE TO graphuser;
GRANT CREATE SYNONYM TO graphuser;

GRANT CREATE PROPERTY GRAPH TO graphuser;
GRANT ALTER ANY PROPERTY GRAPH TO graphuser;
GRANT DROP ANY PROPERTY GRAPH TO graphuser;

 

이제 샘플로 그래프db를 생성해 봅니다.

DROP PROPERTY GRAPH sales_graph;

DROP TABLE purchases PURGE;
DROP TABLE products PURGE;
DROP TABLE customers PURGE;

CREATE TABLE customers (
  customer_id NUMBER PRIMARY KEY,
  name        VARCHAR2(100)
);

CREATE TABLE products (
  product_id NUMBER PRIMARY KEY,
  name       VARCHAR2(100)
);

CREATE TABLE purchases (
  purchase_id NUMBER PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  product_id  NUMBER NOT NULL,
  amount      NUMBER,
  CONSTRAINT fk_purchases_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id),
  CONSTRAINT fk_purchases_product
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
);

INSERT INTO customers VALUES (1, 'Kim');
INSERT INTO customers VALUES (2, 'Lee');

INSERT INTO products VALUES (101, 'Oracle 26ai');
INSERT INTO products VALUES (102, 'Graph DB Book');

INSERT INTO purchases VALUES (1001, 1, 101, 50000);
INSERT INTO purchases VALUES (1002, 1, 102, 30000);
INSERT INTO purchases VALUES (1003, 2, 101, 70000);

COMMIT;

CREATE OR REPLACE PROPERTY GRAPH sales_graph
  VERTEX TABLES (
    customers
      KEY (customer_id)
      LABEL customer
      PROPERTIES (customer_id, name),

    products
      KEY (product_id)
      LABEL product
      PROPERTIES (product_id, name)
  )
  EDGE TABLES (
    purchases
      KEY (purchase_id)
      SOURCE KEY (customer_id) REFERENCES customers (customer_id)
      DESTINATION KEY (product_id) REFERENCES products (product_id)
      LABEL bought
      PROPERTIES (purchase_id, amount)
  );
  
-- 생성확인
SELECT graph_name
FROM user_property_graphs;

-- 메타데이터 확인
SELECT graph_name, element_name, element_kind
FROM user_pg_elements
WHERE graph_name = 'SALES_GRAPH'
ORDER BY element_kind, element_name;

-- 조회
SELECT *
FROM GRAPH_TABLE (
  sales_graph
  MATCH (c IS customer)-[b IS bought]->(p IS product)
  COLUMNS (
    c.name AS customer_name,
    p.name AS product_name,
    b.amount AS amount
  )
);