반응형
https://docs.oracle.com/en/database/oracle/oracle-database/26/nfcoa/co_general.html
Oracle AI Database New Features
This book describes the new features in Oracle AI Database 26ai.
docs.oracle.com
이번에는 OCI Autonomous AI Database에서 실행 했음
OCI Autonomous AI Database 생성
1.생성 화면 접근
햄버거 메뉴 → Oracle Database → Autonomous Database → Create Autonomous Database
2. 기본 정보 입력
-Compartment: root
-Display name: selectai-lab
-Database name: selectailab
3. Provisioning (약 5분~10분 소요)
4. Database Actions로 접속
우측 상단 "Database actions" -> "SQL" -> 로그인하면 브라우저 안에 SQL 워크시트 화면
5.AI Test용 계정 생성
CREATE USER veclab IDENTIFIED BY "Adb2026Lab#Xyz"
DEFAULT TABLESPACE DATA
QUOTA UNLIMITED ON DATA;
GRANT DB_DEVELOPER_ROLE TO veclab;
GRANT EXECUTE ON DBMS_CLOUD_AI TO veclab;
GRANT EXECUTE ON DBMS_CLOUD TO veclab;
6. ORDS 활성화
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'VECLAB',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'veclab',
p_auto_rest_auth => TRUE
);
COMMIT;
END;
/
7.실습 Table 생성
우측상단 Logout -> VECLAB 로그인 -> 실행
--원본 문서 테이블 생성
CREATE TABLE lab_raw_docs (
doc_id NUMBER,
doc_title VARCHAR2(100),
doc_text CLOB
);
INSERT INTO lab_raw_docs VALUES (
1, 'Oracle DB Guide',
'Oracle Data Guard is Oracle''s primary high availability and disaster recovery solution. It maintains one or more standby databases that are transactionally consistent copies of a production database. When the production database becomes unavailable, Data Guard can switch any standby to the production role, minimizing downtime. Active Data Guard extends this capability by allowing the standby database to be opened for read-only queries and reporting while apply continues in real time.'
);
INSERT INTO lab_raw_docs VALUES (
2, 'RAC Guide',
'Oracle Real Application Clusters allows multiple database instances to access a single shared database concurrently. Each instance runs on a separate server node, and the Cache Fusion technology synchronizes data blocks across instances using a high-speed interconnect. RAC primarily addresses high availability at the instance level, whereas Data Guard protects against complete site failures by maintaining physically or logically separate copies of the database.'
);
INSERT INTO lab_raw_docs VALUES (
3, 'RMAN Guide',
'Recovery Manager, commonly known as RMAN, is Oracle''s built-in utility for backup and recovery operations. It can perform full or incremental backups, and supports block change tracking to speed up incremental backups. RMAN backups can be validated for corruption, and Oracle recommends periodically testing restore procedures to ensure that recovery is possible when needed.'
);
INSERT INTO lab_raw_docs VALUES (
4, 'ASM Guide',
'Automatic Storage Management, or ASM, is Oracle''s built-in volume manager and file system designed specifically for database files. ASM stripes and mirrors data across multiple disks automatically, removing the need for a separate logical volume manager. ASM disk groups can be configured with different redundancy levels to protect against disk failures.'
);
INSERT INTO lab_raw_docs VALUES (
5, 'GoldenGate Guide',
'Oracle GoldenGate provides real-time data replication and integration between heterogeneous database systems. Unlike Data Guard, which typically replicates an entire database, GoldenGate can replicate selected tables or transactions, and supports replication between databases from different vendors. This makes GoldenGate a common choice for zero-downtime migrations and active-active replication topologies.'
);
COMMIT;
--청크 테이블 생성 및 결과 확인
CREATE TABLE lab_doc_chunks (
doc_id NUMBER,
chunk_id NUMBER,
chunk_text VARCHAR2(4000)
);
INSERT INTO lab_doc_chunks (doc_id, chunk_id, chunk_text)
SELECT
dt.doc_id,
JSON_VALUE(ct.column_value, '$.chunk_id' RETURNING NUMBER),
JSON_VALUE(ct.column_value, '$.chunk_data')
FROM lab_raw_docs dt,
DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS(
dt.doc_text,
JSON('{"by":"words", "max":"30", "overlap":"8", "normalize":"all"}')
) ct;
COMMIT;
SELECT doc_id, COUNT(*) AS chunk_count
FROM lab_doc_chunks
GROUP BY doc_id
ORDER BY doc_id;
8.OCI 인증 설정
OCI 콘솔 → Identity & Security → Domains → 도메인 클릭 → Dynamic Groups → Create Dynamic Group
9. Policies
-Name: selectai-adb-genai-policy
-Description: 아무 내용 (예: Allow ADB to use Generative AI)
allow dynamic-group selectai-adb-dyngroup to manage generative-ai-family in tenancy
Select AI Test
Resource Principal 활성화 (ADMIN으로 접속)
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(username => 'VECLAB');
END;
/
--결과 확인
SELECT owner, credential_name FROM dba_credentials WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL';-- AI 프로파일 생성 및 Test (VECLAB으로 재접속)
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'LAB_OCI_RP_PROFILE',
attributes => '{
"provider": "oci",
"credential_name": "OCI$RESOURCE_PRINCIPAL",
"object_list": [
{"owner": "VECLAB", "name": "lab_raw_docs"},
{"owner": "VECLAB", "name": "lab_doc_chunks"}
],
"model": "cohere.command-a-03-2025",
"oci_apiformat": "COHERE"
}'
);
END;
/
--결과 확인
SELECT profile_name, status FROM user_cloud_ai_profiles WHERE profile_name = 'LAB_OCI_RP_PROFILE';
/*
중요: 상단 실행 버튼(▶)옆에 드롭다운 화살표가 있음
'스크립트 실행'(또는 F5)을 선택해야 두 문장이 같은 세션에서 순서대로 실행
커서를 두고 Ctrl+Enter로 한 문장씩 실행하면 실패 함.
*/
BEGIN
DBMS_CLOUD_AI.SET_PROFILE(profile_name => 'LAB_OCI_RP_PROFILE');
END;
/
SELECT AI showsql 'How many chunks does each document have?';
--runsql/narrate/chat Test
BEGIN
DBMS_CLOUD_AI.SET_PROFILE(profile_name => 'LAB_OCI_RP_PROFILE');
END;
/
--showsql : 영문 / 한글
SELECT AI showsql 'How many chunks does each document have?';
SELECT AI showsql '각 문서마다 청크가 몇 개씩 있어?';
--runsql (기본값) : 영문 / 한글
SELECT AI 'Which document has the most chunks?';
SELECT AI '가장 청크가 많은 문서는 뭐야?';
--narrate : 영문 / 한글
SELECT AI narrate 'How many chunks does each document have?';
SELECT AI narrate '각 문서마다 청크가 몇 개씩 있어?';
--chat : 영문 / 한글
SELECT AI chat 'What is Retrieval-Augmented Generation in simple terms?';
SELECT AI chat '검색증강생성(RAG)이 뭔지 3줄로 설명해줘';
showsql에서 영문 vs 한글 미세 차이
두 SQL이 거의 100% 동일하지만, 한글 질문 쪽에만 ORDER BY lrd."DOC_ID"가 추가 됨
'각 문서마다'라는 한글 표현이 LLM에게 "순서대로 정리해서"라는 느낌으로 전달 된것으로 추측 됨
반응형