oracle database 26ai commands

Question : How to create user and grant necessary privilege

 To create a user in Oracle AI Database 26ai, you must first switch from the Container Database (CDB) root to a Pluggable Database (PDB). Standard practice restricts direct local user creation in the CDB root to prevent ORA-65096 errors. 

Step-by-Step SQL Execution

1. Connect to the Database

Open terminal or command prompt, start SQL*Plus, and log in as an administrator: 

sql

sqlplus / as sysdba

2. Switch to Your Pluggable Database (PDB) 

Identify your available PDBs using SHOW PDBS; and point your session to your target PDB (e.g., FREEPDB1 for the free edition): 

sql

ALTER SESSION SET CONTAINER = FREEPDB1;

3. Execute the CREATE USER Command 

Run the standard syntax to create your local user: 

sql

CREATE USER application_user IDENTIFIED BY StrongPassword26ai 
DEFAULT TABLESPACE users 
QUOTA UNLIMITED ON users;

4. Grant Essential Privileges

A newly created user has an empty privilege domain and cannot log in. You must grant connection and development roles: 

sql

GRANT CREATE SESSION, DB_DEVELOPER_ROLE TO application_user;

(Note: Oracle 26ai highly optimizes database provisioning through the comprehensive DB_DEVELOPER_ROLE.) 

---

Alternative: Create a Read-Only User 

Oracle 26ai provides a dedicated feature allowing you to define a user as read-only right at the moment of creation. This is perfect for reporting tools or data analysts: 

sql

CREATE USER analytical_user IDENTIFIED BY ReadOnlyPassword26ai READ ONLY;
GRANT CREATE SESSION TO analytical_user;

UIf you ever need to change this user to read-write capabilities down the line, execute: 

sql

ALTER USER analytical_user READ WRITE;


=======================

CREATE USER C##rag_user IDENTIFIED BY "SecurePassword2026#";
GRANT CONNECT, RESOURCE, DB_DEVELOPER_ROLE TO C##rag_user;
ALTER USER C##rag_user QUOTA UNLIMITED ON USERS;
CONNECT C##rag_user/SecurePassword2026#;



Question : ORA-51962: The vector memory area is out of space for the current container.


This error occurs when your Oracle Database lacks the memory allocation required 
to process or store new vector data in the current container. 
To fix this, either drop unused vector memory objects or 
increase the VECTOR_MEMORY_SIZE initialization parameter. 
The Solution: Increase Vector Memory Size
Connect to your database (e.g., as SYSDBA in CDB$ROOT).
Set a new parameter size for the Vector Memory Area using the following SQL statement 
(adjust the 512M to your available system RAM):
sql
ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 512M SCOPE = SPFILE;
Restart the database for the changes to apply:
sql
SHUTDOWN IMMEDIATE;
STARTUP;

Basic Command

SELECT banner_full FROM v$version;
SELECT sys_context('USERENV', 'CON_NAME') AS current_container FROM dual;
CREATE TABLE local_ai_test1 (id NUMBER PRIMARY KEY,embedding VECTOR(3, FLOAT32))
INSERT INTO local_ai_test1 VALUES (1, '[0.12, -0.43, 0.89]');
SELECT * FROM local_ai_test;




Question : How to configure oracle database 26ai for AI with example and test cases



The definitive command to interact with Generative AI in the Oracle AI Database 26ai is SELECT AI. This feature integrates Large Language Models (LLMs) right into the database engine, transforming natural language prompts directly into operational database tasks. [1, 2, 3]
The command relies on the DBMS_CLOUD_AI package to establish AI profiles and enforce data boundaries. [1, 2]
Command Syntax
sql
SELECT AI action natural_language_prompt;
Core Actions Supported
runsql (Default): Converts the text prompt to SQL, executes it, and displays the rows.
showsql: Outputs the AI-generated SQL query syntax without executing it.
narrate: Runs the query and returns a conversational summary of the data findings.
chat: Processes conversational prompts or general questions using the LLM. 
End-to-End Implementation Example
Step 1: Grant Permissions & Outbound Access
Run this block as a database administrator (ADMIN) to authorize the user schema and set network access control list (ACL) rules for your provider. [1, 2]
sql

CREATE USER HR_USER IDENTIFIED BY "SecurePassword2026#";
GRANT CREATE SESSION,CONNECT, RESOURCE, DB_DEVELOPER_ROLE to HR_USER;

GRANT EXECUTE ON DBMS_CLOUD_AI TO HR_USER;
GRANT EXECUTE ON DBMS_CLOUD TO HR_USER;

-- Grant execution permission to your developer user
GRANT EXECUTE ON DBMS_CLOUD_AI TO HR_USER;

-- Configure safe outbound HTTP access to the provider (OpenAI used as an example)
BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host           => 'api.openai.com',
        ace            => xs$ace_type(
                            privilege_list => xs$name_list('http'),
                            principal_name => 'HR_USER',
                            principal_type => xs_acl.ptype_db
                          )
    );
END;
/
Step 2: Establish the Secret Credential & AI Profile 
Log into your application schema (HR_USER) to securely register your API token
 and map the metadata boundaries for the LLM. 
sql
-- Securely store the API key inside an encrypted database credential
EXEC DBMS_CLOUD.CREATE_CREDENTIAL('AI_API_CRED', 'OPENAI', 'your_secret_api_key_here');

eg
EXEC DBMS_CLOUD.CREATE_CREDENTIAL('AI_API_CRED', 'OPENAI', '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');

-- Create a specialized profile specifying exactly what tables the AI may analyze
BEGIN
    DBMS_CLOUD_AI.create_profile(
        profile_name => 'HR_ASSISTANT',
        attributes   => '{"provider": "openai", 
                          "credential_name": "AI_API_CRED", 
                          "object_list": [{"owner": "HR_USER", "name": "EMPLOYEES"}, 
                                          {"owner": "HR_USER", "name": "DEPARTMENTS"}]}'
    );
END;
/
Step 3: Activate the Target Profile in Your Session 
Before running natural language commands, you must declare which profile is active in your current session context. [1, 2]
sql
EXEC DBMS_CLOUD_AI.set_profile('HR_ASSISTANT');
Test Cases & Behavioral Verification
Test Case 1: Verify SQL Translation Accuracy (showsql)
Intent: Confirm that the AI translates business concepts into valid Oracle SQL without executing data changes.
Command:
sql
SELECT AI showsql who are the top 3 highest-earning employees in the company;
Expected AI Output:
sql
SELECT first_name, last_name, salary 
FROM HR_USER.EMPLOYEES 
ORDER BY salary DESC 
FETCH FIRST 3 ROWS ONLY;

Test Case 2: Validate Data Retrieval & Execution (runsql)
Intent: Verify execution efficiency when pulling concrete answers instantly using the default action.
Command:
sql
SELECT AI calculate the average salary for the IT department;
Expected AI Output:
text
AVERAGE_SALARY
--------------
      115000.50

Test Case 3: Review Conversational Syntheses (narrate)
Intent: Evaluate if the LLM cleanly aggregates raw metrics into an easy-to-read qualitative overview.
Command:
sql
SELECT AI narrate how many employees work in each department;
Expected AI Output:
text
The company currently employs a total of 150 team members. The IT department 
has the highest head count with 65 workers, followed closely by Sales with 50, 
and Human Resources with 35.

Test Case 4: General Knowledge Validation (chat)
Intent: Verify that the general conversational interface operates accurately without querying underlying tables.
Command:
sql
SELECT AI chat explain the primary difference between a primary key and a unique constraint;
Expected AI Output:
text
A Primary Key uniquely identifies each row in a table and strictly prohibits NULL values. 
A Unique Constraint also enforces uniqueness across columns, but it does allow NULL values 
unless paired with a NOT NULL constraint.

Fallback Option for Stateless/Web Applications
The shorthand SELECT AI works within stateful environments like SQL*Plus and SQLcl. If you are testing or writing applications inside RESTful environments, Oracle APEX, or stateless microservices, call the functional generator equivalent instead: [1, 2, 3]
sql
SELECT DBMS_CLOUD_AI.GENERATE(
    prompt       => 'how many employees are there?',
    profile_name => 'HR_ASSISTANT',
    action       => 'runsql'
) FROM DUAL;



or


Question : Where and How to Get Your OpenAI API Key for oracle autonomous database example and used cases WHERE AND HOW TO GET your_api_key_here

Phase 1: Where and How to Get Your OpenAI API Key
Sign Up or Log In: Go directly to the OpenAI Platform Settings. Register a new developer account or log in with your credentials. [1, 2, 3]
Access API Keys Section: Once logged into your dashboard, locate and click on API Keys in the left-hand menu sidebar. [1]
Generate the Key: Click the green Create new secret key button. 
Name and Save: Give your key a recognizable name (e.g., oracle_select_ai). Click Generate key and copy the string immediately. Store it securely in a password manager; OpenAI will never show the full key value to you again. [1, 2, 3, 4]
Add Billing Credits: You must visit the Billing section and add a payment method to buy usage credits (starting with a $5 minimum). The API key will return errors and fail to run inside Oracle without pre-funded credits. [1, 2]
Phase 2: Configuration Example in Oracle Autonomous Database
Once you possess your token, use a DBA tool or the Oracle Database Actions SQL Worksheet to register your credential and set up your Large Language Model (LLM) integration profile. [1]
Step 1: Grant Access and Configure Network ACL 
Run as ADMIN to enable connectivity to OpenAI and grant execution rights to your schema user. [1, 2]
sql
-- ACL Setup
BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(host => 'api.openai.com', lower_port => 443, upper_port => 443, ace => xs$ace_type(privilege_list => xs$name_list('http'), principal_name => 'YOUR_SCHEMA_USER', principal_type => xs_acl.ptype_db)); END; /
-- Grant Privileges
GRANT EXECUTE ON DBMS_CLOUD_AI TO YOUR_SCHEMA_USER; GRANT EXECUTE ON DBMS_CLOUD TO YOUR_SCHEMA_USER;
Step 2: Create the Database Credential
Securely store your API key in the database, replacing 'your_api_token' with your actual key. [, 2, 3]
sql
BEGIN DBMS_CLOUD.CREATE_CREDENTIAL (credential_name => 'OPENAI_CRED', username => 'OPENAI', password => 'your_api_token'); END; /
Step 3: Create and Set the AI Profile 
Define the AI profile to use GPT-4o, specifying the tables the model can access. 
sql
BEGIN DBMS_CLOUD_AI.CREATE_PROFILE(profile_name => 'OPENAI_PROFILE', attributes => '{"provider": "openai", "model": "gpt-4o", "credential_name": "OPENAI_CRED", "object_list": [{"owner": "YOUR_SCHEMA_USER", "name": "YOUR_TABLE"}]}'); END; /
EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_PROFILE');
Phase 3: Core Use Cases (Select AI)
Natural Language to SQL: Ask questions in plain English to get query results.
SELECT AI how many customers live in San Francisco; 
Show Generated SQL: Review the SQL generated by the model before executing.
SELECT AI showsql top 3 products by sales; 
Narrate/Explain: Request conversational summaries of data analysis.
SELECT AI narrate analyze sales trends for 2025; 
Chat: Engage in direct, conversational chat with the AI.
SELECT AI chat explain the difference between a view and a materialized view;