Interview Question And Answer for autonomous database in 2026 Part 3

 

Question : How to prevent Automatic Plan Management execution plan regressions in autonomous database

Prevent Automatic Plan Management (APM) regressions in an Oracle Autonomous Database by utilizing Real-time SQL Plan Management (SPM) and SQL Tuning Sets (STS) to capture, verify, and lock high-performing execution plans. 

1. Leverage Real-time SPM

Real-time SPM operates automatically to detect and mitigate plan regressions. As the query optimizer hard-parses a query, it evaluates the performance of new plans against a reference baseline. 

• Verification: If a new plan underperforms, the database rejects it and retains the historically validated baseline plan.
• Check Status: Query the DBA_SQL_PLAN_BASELINES view, checking the NOTES column and FOREGROUND_LAST_VERIFIED timestamp to track when real-time SPM resolved an issue

    

FOREGROUND_LAST_VERIFIED: This timestamp updates immediately, proving that the Autonomous Database intercepted the query execution in real-time.

NOTES: The XML payload details exactly what comparison was conducted, the metrics analyzed, and why the regressed plan was rejected to shield your active workload from performance anomalies

Conceptual Framework of Real-Time SPM

[SQL Executed] ──> [Optimizer Generates New Plan]
                          │
                          ▼
            [Is New Plan Better Than Baseline?]
             ├── Yes ──> [Accept & Evolve Baseline]
             └── No  ──> [Reject & Force Known Good Plan] (Prevents Regression)

2. Lock Known Good Plans

If a specific SQL statement has an optimal plan, prevent APM from evolving or altering it by locking the baseline. 

• Use DBMS_SPM.ALTER_SQL_PLAN_BASELINE to fix a plan, which forces the optimizer to use it exclusively: 

sql

BEGIN
  DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
    sql_handle      => 'SYS_SQL_xxxx',
    plan_name       => 'SQL_PLAN_yyyy',
    attribute_name  => 'FIXED',
    attribute_value => 'YES'
  );
END;
/


Verify Configuration Elements
In an Autonomous ATP database, Automatic SPM is configured to AUTO or ON by default. 
OPTIMIZER_USE_SQL_PLAN_BASELINES should be set to TRUE. 
This forces the optimizer to prioritize verified, accepted baseline plans.
AUTO_SPM_EVOLVE_TASK should be set to AUTO (the default for Autonomous) or ON. 
This kicks off high-frequency evaluation tasks to instantly assess regression. 
Execute the following script to check your instance's active settings: 
sql
-- Check if the database utilizes existing plan baselines
SELECT name, value 
FROM v$parameter 
WHERE name = 'optimizer_use_sql_plan_baselines';

-- Check the Automatic SPM Task Status

SELECT parameter_name, parameter_value 
FROM dba_sql_management_config 
WHERE parameter_name = 'AUTO_SPM_EVOLVE_TASK';
If it has been manually turned off, you can re-enable it with the following block: 
sql
BEGIN
    DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'AUTO');
END;
/

3. Migrate and Import SQL Tuning Sets (STS)
When migrating to or within an Autonomous Database, guard against sudden APM instability by seeding the target database with verified historical execution plans.]
Capture: Export a SQL Tuning Set with verified execution plans from your source database.
Load: Import the STS into the Autonomous AI Database using the DBMS_SQLSET package and load those plans as permanent accepted baselines.]
4. Adjust Baseline Auto-Purge Behavior
By default, SPM automatically purges execution plans 
that have not been used over a certain period of time (typically 53 weeks)





For more details
Migrate Existing SQL Execution Plans to Autonomous AI Database to Mitigate the Risk of SQL Performance Regressions

or

To prevent manual plan management or environmental changes (like statistics updates) from causing execution plan regressions in an Oracle Autonomous Database, you must configure and lock down SQL Plan Baselines (SPM). 
This forces the optimizer to only use a verified, high-performing plan. 
Implement this step-by-step strategy to lock in stable performance:
1. Manually Load the Known-Good Plan
Capture the optimal execution plan from the shared SQL area or 
a SQL Tuning Set and manually load it as an accepted baseline. 
Manually loaded plans are immediately considered "accepted" by the optimizer, 
guaranteeing it uses this specific plan. 
sql
DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'YOUR_SQL_ID',
    plan_hash_value => 'GOOD_PLAN_HASH'
  );
END;
/

2. Lock the Baseline to Prevent Manual Alteration
Once an acceptable plan is captured and marked accepted, you must lock the baseline.
 Locking prevents the database's automatic evolve tasks or
 manual tuning interventions from accidentally replacing or overriding your approved plan.
sql
DECLARE
  l_plans_altered PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle => 'YOUR_SQL_HANDLE',
    attribute_name => 'FIXED',
    attribute_value => 'YES'
  );
END;
/
3. Disable Automatic SPM Evolve Tasks
Oracle Autonomous Databases typically run the Automatic 
SPM Evolve Task (SYS_AUTO_SPM_EVOLVE_TASK) in the background to test and 
evolve new alternative plans. 
To completely stop any automatic changes or plan evolutions globally, disable the task:
sql
BEGIN
  DBMS_SPM.set_evolve_task_parameter(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
    parameter => 'ACCEPT_PLANS',
    value => 'FALSE'
  );
END;
/
4. Verify Baseline Usage
Ensure the database is actively picking up your locked baseline by checking the plan details 
and baseline name for your query using the DBMS_XPLAN package:
sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id => 'YOUR_SQL_ID', 
  format => 'BASIC+NOTES'
));
Additional Resources
Official Documentation: Learn more about loading and fixing plan baselines in the Oracle Database Managing SQL Plan Baselines Guide.
Real-Time SPM: Understand how the Autonomous Database self-healing mechanism isolates issues on a larger scale via the Oracle Autonomous Database Real-Time SPM Guide. 

For more Details

What is automatic SQL plan management and why should you care? | optimizer

Question : How to configure and managed datasafe in oracle autonomous atp database with example and test cases

Oracle Data Safe is integrated natively into Oracle Autonomous Transaction Processing (ATP), allowing you to easily configure and manage database security. For Serverless Autonomous Databases, the underlying Data Safe service account (DS$ADMIN) is created and authorized automatically during the cloud registration process. 

This comprehensive guide details the configuration steps, management capabilities, and practical test cases using Oracle Data Safe Tools. 

---

Phase 1: Enable and Configure Data Safe

Step 1: Initial Activation (One-time Tenancy Setup)

Before registering target databases, make sure Data Safe is globally enabled in your Oracle Cloud Infrastructure (OCI) tenancy. 

1. Log in to the OCI Console.
2. Open the navigation menu and click Oracle Database, then select Data Safe.
3. Click Enable Data Safe if it isn't already turned on. 

Step 2: Register the ATP Instance 

1. From the OCI navigation menu, select Oracle Database and click Autonomous Transaction Processing.
2. Click on the display name of your specific ATP Database instance.
3. Scroll down to the More Actions menu or the Data Safe section under the Database Information tab.
4. Click Register.
5. In the confirmation dialog box, click Confirm. OCI will provision and link the background roles automatically.
   (Note: If your ATP instance sits behind a private VCN endpoint, ensure that matching Security Rules/NSGs permit traffic to and from the Data Safe Private Endpoint on port 1522). 

---

Phase 2: Manage Core Security Capabilities

Once registered, navigate back to the global Data Safe cockpit (Oracle Database -> Data Safe) to handle active management. 

• Security Assessment: Analyzes database parameters, initialization settings, and OS-level configurations against benchmarks to locate security gaps. 
• User Assessment: Identifies risk levels by mapping user accounts, roles, privileges, and connection behaviors. 
• Activity Auditing: Governs audit trails, tracks administrative actions, and records critical data operations.
• Data Discovery & Masking: Locates sensitive columns (e.g., PII, credit cards) and replaces them with realistic, fictional values for non-production environments. 

---

Phase 3: Practical Examples & Test Cases

To validate that your Data Safe implementation is working as intended, you can walk through these two baseline test scenarios.

Setup: Create Dummy Application Schema

First, execute this setup script inside your ATP instance via the built-in OCI SQL Worksheet or an external client like Oracle SQL Developer to create test data: 

sql

-- Create a mock application table containing sensitive user info
CREATE TABLE app_users (
    user_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username VARCHAR2(50),
    email_address VARCHAR2(100),
    national_id VARCHAR2(20),  
    credit_card VARCHAR2(20)
);

-- Insert sample rows
INSERT INTO app_users (username, email_address, national_id, credit_card) 
VALUES ('jdoe', 'john.doe@example.com', '123-456-7890', '4111-2222-3333-4444');

INSERT INTO app_users (username, email_address, national_id, credit_card) 
VALUES ('asmith', 'alice.s@example.com', '987-654-3210', '5555-6666-7777-8888');

COMMIT;

-- Create an overly privileged test user to trigger user risk alerts
CREATE USER test_developer IDENTIFIED BY "Temporary_Pass123##";
GRANT CONNECT, RESOURCE, DBA TO test_developer; 

Test Case 1: Discovering & Masking Sensitive PII Data 

• Objective: Confirm Data Safe's ability to scan schema tables, flag protected data classes, and mask them. 
• Execution Steps:
  1. Navigate to Data Safe -> Data Discovery inside the OCI Console.
  2. Start a new discovery job, select your registered ATP database, and specify the schema where app_users resides.
  3. Run the scan. Review the results; Data Safe will flag credit_card and national_id automatically.
  4. Save the finding to a Sensitive Data Model (SDM).
  5. Navigate to Data Masking, choose your new SDM, and apply default transformations (e.g., Credit Card Masking Format).
  6. Run the Masking Job against a non-production clone of your database. 
• Verification: Query the target table in your database clone:
  sql

  SELECT credit_card, national_id FROM app_users;
  

  Result: The original PII numbers will now display as scrambled, generic values matching the predefined masking formats. 

---

Test Case 2: User Risk Assessment & Privileged Actions

• Objective: Verify that risky schema privileges are tracked and recorded in the management plane.
• Execution Steps:
  1. Go to Data Safe -> User Assessment.
  2. Select your ATP database and trigger an immediate Refresh Assessment scan.
  3. Open the resulting interactive dashboard and drill down into the High Risk Users section. 
• Verification: Look for the test_developer user created in the setup phase. Data Safe will accurately flag this account as High Risk due to the DBA role grant, highlighting exactly which structural privileges can be revoked to enforce least privilege principles.

---

Step 2: Manage and Configure Data Safe Features

Once active, click the Data Safe link on your ATP console page to transition directly into the Oracle Data Safe Security Center.

1. Turn on Activity Auditing & Alerts 

• Inside Data Safe, navigate to Activity Auditing > Audit Profiles.
• Choose your ATP target database and click Calculate Audit Volume to review current database logs.
• Click Update Audit Policies and enable recommended baselines, such as the Critical Database Activity policy or Center for Internet Security (CIS) Recommendations.
• Click Start Audit Trail to begin pulling database logs into Data Safe for analysis. 

2. Run a Security Assessment 

• Navigate to Security Assessment from the side menu.
• Click Assess Now to check your database configuration against standard security best practices.
• Review the results categorized by risk level (High, Medium, Low, or Pass)

Question : How to configure and managed auto index in oracle autonomous atp database with example and test cases

In Oracle Autonomous Transaction Processing (ATP), the automatic indexing feature monitors application workloads and automatically creates, validates, and tunes indexes to maximize query performance. 

By default, automatic indexing is disabled in Autonomous Database instances. You manage it entirely using the DBMS_AUTO_INDEX PL/SQL package. 

---

1. Configuration & Management

You configure the feature using the DBMS_AUTO_INDEX.CONFIGURE procedure. 

Setting the Operational Mode

• Implement (Fully On): Creates indexes, tests them, and builds them as visible structures if they improve performance.
  sql

  EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');
  

  Report Only (Monitor): Evaluates the workload and generates recommendations, but leaves newly created auto-indexes as invisible structures so the optimizer won't use them.
• sql

  EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'REPORT ONLY');
  

  Off: Disables background index creation completely.
• sql

  EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'OFF');
  

  
  

Restricting Access (Inclusion and Exclusion Lists)

You can choose specific schemas or tables to include or exclude from the auto-indexing task. 

sql

-- Exclude specific schema (e.g., HR)
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', allow => FALSE);

-- Include specific schema (Only HR will use auto-indexing)
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', allow => TRUE);

-- Disable auto-indexes on a specific table
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE', 'HR.EMPLOYEES', allow => FALSE);

Retention Policies

Auto-indexes that are not used over a specific window of time are cleared automatically. You can alter the retention period (default is 373 days) using: 

sql

-- Remove unused auto-indexes after 90 days
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');

2. Comprehensive Test Case Example

Follow this realistic workflow using the Oracle SQL Worksheet or desktop client to see how the background process picks up performance tuning opportunities.

Step A: Setup a Test Table and Data

Create a dummy table with enough distinct data points so the database engine can differentiate between a full table scan and an index range scan.

sql

-- Create a test table
CREATE TABLE test_auto_idx (
    id NUMBER,
    val VARCHAR2(100),
    created_date DATE
);

-- Insert 100,000 mock records
INSERT INTO test_auto_idx
SELECT 
    rownum, 
    DBMS_RANDOM.STRING('A', 20), 
    SYSDATE - DBMS_RANDOM.VALUE(1, 365)
FROM dual
CONNECT BY rownum <= 100000;

COMMIT;

-- Gather table statistics (Crucial step for Auto Indexing to work)
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_AUTO_IDX');

Step B: Activate Auto Indexing

Turn the engine into active enforcement mode. 

sql

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');

Step C: Simulate User Queries (Workload)

The auto-index task listens for repeating query structures that suffer from suboptimal access paths. Run a query containing a highly selective filter criteria multiple times. 

sql

-- Run this query a few times to create historical footprint in the cursor cache
SELECT * FROM test_auto_idx WHERE val = 'AABBCC';
SELECT * FROM test_auto_idx WHERE val = 'XYZ123';
SELECT * FROM test_auto_idx WHERE val = 'TESTING';

(Note: Oracle ignores one-off ad-hoc statements to protect system resources, so queries must appear multiple times to qualify as an active application workload). 

Step D: Background Processing Window

Oracle executes the validation process as an asynchronous online background job approximately every 15 minutes. During this window: 

1. It identifies candidate columns based on the cursor cache.
2. It builds the candidate as an Invisible Index (prefixed with SYS_AI).
3. It performs a test execution of the query against the invisible index.
4. If it detects performance gains, it changes the structure status to Visible. If performance drops, it blacklists the statement. 

---

3. Monitoring and Verification

Once the background time windows have processed, you can verify your results through the data dictionary views.

Check for Generated Auto Indexes 

Query DBA_INDEXES using the AUTO column flag to see what the engine built: 

sql

SELECT index_name, table_name, visibility, status 
FROM dba_indexes 
WHERE auto = 'YES' AND table_name = 'TEST_AUTO_IDX';

Extract the Activity Report 

To see a log of what was evaluated, created, or rejected, extract an HTML summary report from the database engine using DBMS_AUTO_INDEX.REPORT_ACTIVITY: [

sql

SET LONG 10000000
SET PAGESIZE 0
VARIABLE r CLOB;

BEGIN
  :r := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
          activity_start => SYSDATE - 1/24, -- last hour
          section        => 'ALL',
          level          => 'ALL',
          type           => 'TEXT');
END;
/

PRINT r;



Question : how switchover work in oracle autonomous database

In an Oracle Autonomous Database, a switchover is a planned operation that reverses the roles of the primary and standby databases with zero data loss. The active primary database becomes the new standby, while the existing standby database is promoted to the primary role. 
How the Switchover Mechanism Works
Role Reversal: The cloud infrastructure gracefully stops accepting new write transactions on the primary database, flushes all remaining redo logs to the standby database, and flips their operational roles. 
Zero Data Loss: Because it is a controlled, planned event, the system guarantees that all data is fully synchronized before completing the swap. If synchronization cannot be verified, the switchover fails safely without affecting the primary instance.
Preservation of State: The operation maintains the lifecycle state of your primary database. For example, if your primary database is stopped when you trigger the switchover, the old primary will transition to a stopped standby, and the new primary will boot into a stopped state. 
Application Continuity: Database connection strings automatically route client traffic to the new primary database instance, requiring no changes to application connection details.
Common Use Cases
Planned Maintenance: Shifting production loads away from an infrastructure component before scheduled system updates.
DR Testing & Compliance: Routinely testing disaster recovery readiness and application failover procedures for corporate audits. 
How to Initiate a Switchover
You can trigger a switchover using the Oracle Cloud Infrastructure (OCI) Console, the OCI CLI, or through automated frameworks like OCI Full Stack Disaster Recovery
Via OCI Console:
Navigate to the Autonomous Database Details page of your database.
Note for Cross-Region deployments: You must initiate the switchover from the remote Standby database page. For local deployments, it can be initiated from the primary database page. 
Under the Disaster Recovery (or Autonomous Data Guard) section, locate your peer database, click the actions menu, and select Switchover. 
Enter the name of the peer database to confirm the action and click Confirm. 
The Lifecycle State will display Updating until the swap finishes, at which point the new primary status returns to Available

Question : How vector search work in oracle autonomous database

Oracle Autonomous Database implements vector search natively through Oracle AI Vector Search, a foundational feature introduced in Oracle Database 23ai. Instead of requiring a separate, specialized vector database, Oracle allows you to store, index, and query vector embeddings natively alongside standard relational data using standard SQL.
Here is a breakdown of how the entire process works, from data ingestion to similarity querying.
1. Native Data Storage (VECTOR Data Type)
Oracle introduces a native VECTOR data type. This allows you to store vector embeddings directly as columns within your standard relational tables.
Vectors represent mathematical multi-dimensional arrays of numbers.
They capture the semantic meaning and context of unstructured data like text, images, or audio
sql
CREATE TABLE product_documentation (
    doc_id INT,
    doc_text CLOB,
    doc_vector VECTOR
);
2. Embedding Generation (Vectorization)
To convert text or files into vectors, Oracle Autonomous Database integrates directly with machine learning models:
In-Database Models: You can load pre-trained ONNX models (e.g., from Hugging Face) directly into the database using DBMS_VECTOR.load_onnx_model.
External APIs: It can make REST calls to external providers like OCI Generative AI, OpenAI, or Cohere to generate embeddings on the fly.
SQL Functions: The VECTOR_EMBEDDING() SQL function generates embeddings dynamically during data insertion or updating
3. High-Performance Indexing
While Oracle can execute exact similarity matching using a full table scan, large datasets require indexing to maintain low latency. Oracle Autonomous Database provides two advanced Approximate Nearest Neighbor (ANN) indexing techniques: 
Inverted File Flat (IVF): A neighbor partition index built on disk, utilizing the regular database buffer cache. It is highly space-efficient. 
Hierarchical Navigable Small World (HNSW): An in-memory graph-based index built fully within the SGA (VECTOR_MEMORY_SIZE). It delivers ultra-fast search performance at the cost of higher memory consumption
4. Similarity Querying via SQL
When an application executes a search, the query text is first converted into a search vector. Oracle then uses mathematical distance metrics to calculate the proximity between the search vector and the vectors stored in the table. 
Common native metrics include: 
Cosine Similarity (Default metric for most text embedding models)
Euclidean Distance (and squared Euclidean distance) 
sql
-- Query example searching for semantic matches
SELECT doc_id, doc_text 
FROM product_documentation
ORDER BY VECTOR_DISTANCE(doc_vector, :search_embedding, COSINE)
FETCH FIRST 5 ROWS ONLY;



5. Hybrid Search Capabilities
One of the major architectural benefits of Oracle's approach is multimodel query integration. In a single SQL statement, developers can seamlessly combine: [1, 2, 3, 4, 5]
Semantic Searches: Via vector distances.
Keyword Searches: Using traditional Oracle Text transactional indexing.
Relational Filters: Restricting results by standard column data (e.g., WHERE customer_id = 123 or WHERE price < 50). 
6. Enterprise Security and Availability
Because vector data is treated as a "first-class" data type, it immediately inherits Oracle's robust enterprise features without additional operational overhead: ,]
Security: Data access is protected out-of-the-box by Oracle Database Vault, Virtual Private Database (VPD), and standard grant systems.
Scale and Performance: Scale-out architecture is achieved natively through Oracle RAC, Exadata smart scans, and Oracle Globally Distributed Database (Sharding).
Disaster Recovery: Automated via Active Data Guard and GoldenGate. []
This native implementation effectively eliminates the need to synchronize and
 manage data pipelines between an operational database and a standalone vector database