Beyond No-Code: How Oracle AI Agent Studio Uses MCP, Governance, and AI-Unit Monitoring to Build Production-Ready Industry Agents

Recently, I was reading about Oracle AI Agent Studio 1 for Industry Applications. Initially, the no-code and low-code capability caught my attention. However, after looking more closely at the announcement, I felt that no-code development was only one part of the story.

Creating an AI agent through natural-language instructions or a visual interface may become relatively easy. The real challenge starts when we want to use that agent in a production environment.

A production agent may need to access enterprise data, connect with business applications, call different tools, and complete multiple steps. Once an agent starts performing these activities, we need to think about security, permissions, approvals, monitoring, auditing, and cost.

From my perspective as an Oracle database professional, these operational areas are more important than simply creating an agent.

An agent may have good reasoning capabilities, but it cannot be considered production-ready unless the organization can control what it accesses, understand what it is doing, and monitor how many resources it consumes.

Three areas in Oracle’s announcement particularly caught my attention:

1. Model Context Protocol support for connecting agents with approved tools
2. Governance controls for managing agent access and actions
3. AI-unit monitoring for understanding usage and operational cost

In this post, I am sharing my understanding of these capabilities and why I believe they will be important when organizations begin moving industry-specific AI agents from demonstrations into real business workflows.

No-Code Alone Does Not Make an Agent Production-Ready

No-code and low-code platforms can make AI agent development accessible to more people.

A business user may understand a particular process very well but may not have the programming knowledge required to build an agent from the beginning. Similarly, a developer may understand the technology but may not fully understand every business rule behind an industry process.

A visual and natural-language development environment can help bring these two groups together.

Teams may be able to define an agent’s role, goal, tools, policies, and working context without writing every component manually. This can reduce initial development effort and help businesses evaluate use cases more quickly.

However, creating the agent is only the first step.

Before allowing an agent to work with production applications, we need answers to several important questions:

• What data can the agent access?
• Which application functions can it use?
• Can it only read information, or can it also update records?
• Which actions require human approval?
• How will its activities be recorded?
• What happens when the agent receives an incomplete or incorrect request?
• How will the support team investigate a failed execution?
• How much AI capacity does each workflow consume?
• Who is responsible if the agent performs an incorrect action?

These are not only AI-development questions. They are also security, operations, compliance, application-support, and database-management questions.

This is why I believe that the real value of Oracle AI Agent Studio will not be measured only by how quickly an agent can be created. It will also depend on how safely and reliably that agent can operate inside an existing business process.

Understanding the Role of MCP

One of the most interesting parts of the announcement is support for Model Context Protocol, commonly called MCP.

An AI model can understand and reason about a request, but reasoning alone cannot complete a business transaction. The agent needs a controlled method to communicate with tools and applications.

For example, depending on the business process, an agent may need to:

• Retrieve a project milestone
• Check the status of an invoice
• Review customer or supplier information
• Examine a product catalogue
• Access an approved database function
• Initiate an application workflow
• Request approval from an authorized user

MCP can provide a standard method for connecting an AI agent with these tools and contextual resources.

In the case of Oracle AI Agent Studio for Industry Applications, the idea is to connect agents with approved Oracle Industry Application tools and capabilities through MCP servers. The same approved capabilities can then be reused across multiple agent use cases.

A simplified flow could work like this:

1. A user submits a business request.
2. The agent interprets the request and identifies the required objective.
3. The agent determines which approved tool is needed.
4. The tool is accessed through the MCP connection.
5. Existing permissions and business policies are applied.
6. The tool retrieves information or performs an allowed action.
7. The result is returned to the agent.
8. The agent continues the process, requests human approval, or presents the result.

In this flow, the most important word is approved.

The agent should not receive unrestricted access to every tool available in the environment. It should only be provided with the tools required for its assigned business function.

For example, a finance agent may be permitted to analyse an accounting exception and prepare a recommendation. That does not necessarily mean that the agent should be allowed to post a journal entry without human approval.

Similarly, a project-management agent may identify a possible schedule risk and suggest corrective actions. The final business decision may still need to be made by the responsible project manager.

MCP can standardize how an agent connects with a tool, but it does not automatically solve every security and governance concern.

Organizations will still need to define:

• Which tools are available to each agent
• What data those tools can access
• Which operations are read-only
• Which operations can modify business records
• What approvals are required
• How credentials and secrets are protected
• What information is recorded for auditing
• How unauthorized or failed tool calls are handled

For this reason, I would consider MCP part of the overall integration and security architecture, rather than viewing it only as a plug-and-play connection method.

Governance Must Be Included from the Beginning

Traditional enterprise applications normally operate through predefined screens, user roles, transactions, and approval paths.

AI agents introduce a different working model. An agent may interpret an objective, create a plan, select available tools, and change its next action based on the result of a previous step.

This flexibility can be useful, but it also creates additional risks.

A production agent needs clear boundaries. It should understand not only what it can do but also what it is not permitted to do.

In my view, agent governance should cover at least the following areas.

Identity

Every agent activity should have an identifiable origin.

The organization should be able to determine which user, application, service, or other agent initiated a particular workflow.

An agent should not become an anonymous method of accessing enterprise data.

Authorization

An agent should receive only the permissions required to complete its assigned work.

If a user does not have permission to access certain financial or employee data, using an AI agent should not allow that user to bypass the existing restriction.

The same least-privilege approach that we apply to database users and application accounts should also be applied to agents.

Tool Access

Each agent should receive access only to the tools required for its purpose.

For example, an agent created for customer support should not automatically receive access to financial posting or database-administration functions.

Restricting the available tools can reduce both accidental actions and security exposure.

Data Boundaries

Enterprise applications may contain financial, personal, customer, supplier, employee, or industry-regulated information.

The agent must operate within the organization’s existing data-security and privacy requirements. Natural-language access should not become a new method of bypassing application or database controls.

Human Approval

Not every process should be fully autonomous.

Low-risk and repeatable tasks may be suitable for higher levels of automation. Actions that create financial, legal, compliance, or customer impact may still need approval from an authorized person.

The approval requirement should be based on the potential impact of the action, not only on the technical capability of the agent.

Auditability

Organizations should be able to review:

• What request the agent received
• What information it accessed
• Which tools it selected
• What actions it performed
• What approvals it requested
• What errors occurred
• What final result it produced

Without this level of traceability, troubleshooting an agent workflow could become very difficult.

Exception Handling

A reliable agent should know when it cannot safely continue.

If information is missing, a tool is unavailable, or the request is outside the agent’s permitted area, it should stop and escalate the issue instead of generating an uncertain answer or attempting an unauthorized action.

Governance should not be added only after completing agent development. It should influence the agent’s design, instructions, permissions, tools, test cases, approvals, and deployment process from the beginning.

We Need to Test Agent Actions, Not Only Answers

When a normal chatbot provides an incorrect answer, the problem may remain within the conversation.

When an agent performs an incorrect action, the impact can reach the actual business application or database.

For this reason, testing an enterprise agent should cover more than the quality of its language.

A proper test plan should include several areas.

Functional Testing

Does the agent understand the request and complete the expected workflow?

Does it call the correct tools and return the intended business result?

Permission Testing

Can the agent access only the applications, tools, and records that it is authorized to use?

Testing should confirm that unauthorized requests are rejected, even when they are submitted through natural language.

Negative Testing

We should check how the agent behaves when it receives:

• Incomplete information
• Contradictory instructions
• An unauthorized request
• An unsupported request
• Incorrect or unexpected tool output
• A request outside its assigned business purpose

The agent should handle these situations safely instead of trying to complete the workflow at any cost.

Integration Testing

An agent may depend on several applications and tools.

Teams need to test what happens when one application is unavailable, a tool responds slowly, a connection fails, or an intermediate step returns unexpected information.

Regression Testing

When an agent’s instructions, tools, model, or workflow are changed, previously completed test cases should be executed again.

This can help the team identify whether a new change has affected existing behaviour.

Security Testing

Testing should also cover prompt-injection attempts, unauthorized tool calls, sensitive-data exposure, duplicate transactions, and attempts to bypass approval requirements.

Agent testing should eventually become part of normal enterprise release management. Development, testing, change approval, deployment, and production validation will continue to be necessary even when the agent itself is created using a no-code platform.

Production Monitoring Is Equally Important

Testing can reduce risk before deployment, but it cannot identify every situation that may occur in production.

After deployment, organizations need visibility into how the agent is actually operating.

Useful operational information may include:

• Total number of requests
• Successful and failed executions
• Average time required to complete a workflow
• Tool-call failures
• Number of retries
• Approval and escalation frequency
• Workflows stopped by security policies
• Resource or model consumption
• AI units consumed by each workflow
• Cost per successful business result

This information can help application-support and operations teams identify whether the agent is reliable.

For example, a workflow may appear successful because it eventually completes. However, monitoring may show that the agent repeatedly calls the same tool or retries several steps before reaching the result.

That behaviour can increase completion time and AI consumption. It may also indicate that the agent’s instructions or workflow design need improvement.

The support team should be able to trace an execution from the original request to the final result. Without that visibility, a failed multi-step agent workflow could be much harder to investigate than a normal application transaction.

Why AI-Unit Monitoring Matters

AI-agent costs may not always be easy to predict.

A simple request may require only one model interaction. A complex workflow may involve multiple reasoning steps, several tool calls, stored context, retries, and interaction with other agents.

This means that two apparently similar requests could consume different amounts of AI capacity.

AI-unit monitoring can help organizations understand how much capacity each agent and workflow is consuming.

Some practical questions that monitoring can help answer include:

• Which agents are consuming the most AI units?
• Which business processes are producing useful outcomes?
• Are repeated tool calls increasing consumption?
• Are failed workflows consuming resources without providing value?
• Is the agent using more reasoning steps than required?
• Can part of the workflow be simplified?
• What is the consumption per successful transaction?
• Should limits or alerts be introduced?
• Is the value created by the agent higher than its operational cost?

In my opinion, the last question is the most important.

The goal should not simply be to reduce AI-unit consumption. A workflow that consumes more resources may still be valuable if it completes a complex task that previously required several hours of manual effort.

At the same time, low consumption does not automatically mean that an agent is efficient. If the agent frequently fails or produces unusable results, even a low-cost execution has limited business value.

For this reason, AI consumption should be compared with business outcomes.

Organizations could measure indicators such as:

• AI units per successful workflow
• Cost per completed transaction
• Time saved for business users
• Reduction in manual activities
• Percentage of requests requiring human correction
• Number of errors prevented
• Improvement in process completion time

This is where agent operations and FinOps may begin to work together.

Traditional cloud cost management focuses on compute, storage, network, and database resources. Agentic AI introduces additional areas such as model selection, context size, reasoning depth, tool calls, memory usage, workflow duration, and retry behaviour.

AI-unit monitoring can provide technical consumption information. Organizations will still need to connect that information with business measurements to understand the agent’s actual value.

Cross-Application Workflows Increase Both Value and Complexity

Most enterprise business processes do not remain inside a single application.

A project workflow may involve project schedules, supplier information, procurement details, field issues, risk assessments, and management approvals.

A retail workflow may need product information, catalogue data, images, inventory, customer preferences, and order information.

A finance workflow may require the agent to collect information from different functions before preparing a recommendation or requesting approval.

Oracle’s announced direction includes coordinating supported agents, tools, and services across Oracle Industry Applications and Oracle Fusion Cloud Applications.

This could allow an agent to work across multiple stages of a business process instead of answering only one question.

We may also see teams of specialized agents working together. One agent could collect information, another could analyse risk, and another could prepare a recommended action.

This model has strong potential, but it also increases operational complexity.

Each handoff between agents, applications, and tools should maintain:

• User and agent identity
• Access permissions
• Business context
• Approval status
• Audit information
• Error details
• Workflow history

If these details are not maintained, troubleshooting a multi-agent process could become complicated.

Cross-application orchestration should therefore be introduced gradually, starting with clear processes that have measurable outcomes and well-defined ownership.

A Simple Way to View the Architecture

From my understanding, a production agent architecture can be viewed in six logical layers.

1. User and Business-Process Layer

This is where an employee, customer, supplier, scheduled process, or another application submits a request.

2. Agent and Orchestration Layer

The agent understands the objective, plans the required steps, selects approved tools, and coordinates with other agents when required.

3. Governance and Policy Layer

This layer applies identity, application roles, permissions, approval rules, operational boundaries, and data-access controls.

4. MCP and Tool Layer

Approved MCP servers expose specific application tools and capabilities to the agent.

5. Application and Data Layer

Oracle Industry Applications, Oracle Fusion Cloud Applications, databases, and supported OCI services provide the business functions and information required by the workflow.

6. Monitoring and Cost Layer

Execution traces, performance, failures, tool calls, usage, and AI-unit consumption are monitored for operational support, governance, and cost management.

The platform may bring several of these components together, but organizations will still need clear ownership.

Business teams, application administrators, security teams, architects, DBAs, and cloud-operations teams will all have responsibilities in a production agent environment.

What Oracle DBAs and Application Administrators Should Consider

Agentic AI is not relevant only to AI developers and data scientists.

Database administrators and application administrators may need to support the security, performance, integration, and operational requirements behind these agents.

From a DBA and production-support perspective, I believe the following areas will be important:

• Applying least-privilege database access
• Reviewing how agent-generated queries access enterprise data
• Auditing SQL and transaction activity initiated through agents
• Protecting production credentials, wallets, API keys, and secrets
• Monitoring database workload created by agent workflows
• Validating natural-language-to-SQL use cases
• Preventing unrestricted or inefficient queries
• Managing access to vector stores and retrieval data
• Reviewing retention requirements for agent memory and conversation history
• Investigating failed or unusually expensive workflows
• Separating development, testing, and production environments
• Including agent-related components in change-management processes
• Planning backup, recovery, and continuity for dependent services

An agent may provide a natural-language interface, but the underlying database and application responsibilities will not disappear.

In fact, they may become more important because an agent can coordinate several operations more quickly than a human user.

For example, if an incorrectly configured agent runs an inefficient query only once, the impact may be limited. If that agent automatically runs the same query hundreds of times as part of a frequently used workflow, the performance impact could be much greater.

DBAs should therefore be involved early when an agent will access important production databases.

A Practical Adoption Approach

Organizations do not need to begin with a highly complex, completely autonomous process.

A controlled approach may be more suitable.

Step 1: Select a Clear Use Case

Start with a well-understood process that has defined inputs, outputs, owners, and success criteria.

Step 2: Begin with Read-Only Access

Where possible, allow the first version of the agent to retrieve and analyse information without updating production records.

Step 3: Define Permissions and Tools

Provide only the data and tools required for the selected process.

Step 4: Add Human Approval

Require an authorized user to approve actions that could create a financial, legal, operational, or customer impact.

Step 5: Test Failure Scenarios

Test unauthorized requests, incorrect inputs, unavailable tools, repeated actions, and unexpected application responses.

Step 6: Enable Monitoring

Capture execution status, tool calls, errors, completion time, approvals, and AI-unit consumption.

Step 7: Compare Consumption with Business Value

Measure whether the agent is saving time, reducing manual effort, improving accuracy, or helping the business complete work faster.

Step 8: Increase Autonomy Gradually

Additional tools and actions should be introduced only after the existing workflow has demonstrated reliable behaviour.

This approach can help organizations learn how their agents behave without immediately exposing critical production processes to unnecessary risk.

Final Thoughts

The main message I took from Oracle AI Agent Studio for Industry Applications is not only that enterprises may be able to create agents using natural language or a visual interface.

The larger change is the movement toward AI agents that can work with industry context, approved enterprise tools, business applications, and organizational policies.

MCP can provide a standard way to connect agents with approved tools. Governance can define what each agent is permitted to access and perform. Testing can help validate the agent’s answers, actions, and tool usage. Production monitoring can show how reliably it operates. AI-unit monitoring can help organizations compare technical consumption with business value.

No-code may help teams create an agent more quickly. However, production readiness will depend on security, governance, integration, testing, monitoring, cost control, and human accountability.

For Oracle DBAs and application-support professionals, this change also creates a new area of responsibility. We may need to monitor not only database users and application transactions, but also the agents that are initiating queries, calling tools, and coordinating business workflows.

In my view, the successful enterprise agent will not be the one that demonstrates the highest level of autonomy. It will be the one that completes useful work while remaining secure, traceable, reliable, and cost-effective.

That is the real journey beyond no-code: moving from an interesting AI demonstration to an enterprise agent that can be responsibly trusted with industry work.

References

1. Oracle, “Oracle AI Agent Studio for Industry Applications Helps Accelerate Business Outcomes,” October 21, 2026.
2. Oracle Cloud Infrastructure Documentation, “Enterprise AI Agents are in General Availability in OCI Generative AI,” March 31, 2026.
3. Oracle, “Oracle Extends Fusion Agentic Applications with Introduction of Fusion Claw,” September 29, 2026.

 

OCI Data Safe for Oracle DBAs: Security Assessment, User Assessment and Activity Auditing

Introduction

Database security is one of those areas that can easily get pushed behind regular DBA activities such as monitoring, performance tuning, backup, patching and incident handling.

But in production environments, security needs regular attention as well.

As the number of databases and users increases, it becomes difficult to manually review every security configuration, user privilege and database activity. This is where OCI Data Safe can help.

OCI Data Safe provides a centralized way to perform security assessments, review database users and monitor database activity.

This article focuses on three areas that are particularly useful for Oracle DBAs:

• Security Assessment

• User Assessment

• Activity Auditing

The main focus here is how these capabilities can be used as part of regular DBA activities rather than treating database security as a one-time exercise.

1. Security Assessment

A database can be healthy from an availability and performance perspective while still having security gaps.

For example, a database may be running without any issues, but there could be:

• Unnecessary privileges

• Weak security configurations

• Unused database accounts

• Incorrect password policies

• Missing auditing configurations

• Security-related parameters that need attention

OCI Data Safe Security Assessment helps identify these types of security findings.

The assessment provides visibility into the security configuration of the target Oracle database and highlights areas that may require attention.

Instead of manually checking a large number of database security settings, the assessment provides a centralized view of the findings.

2. Reviewing Security Assessment Findings

One important point with security assessments is that every finding should not be treated as an immediate production change.

A recommendation may be technically correct, but the database may have a specific application dependency.

For example, changing a database parameter can potentially affect an application, integration or existing process.

A practical approach is:

Review → Validate → Plan → Implement → Reassess

For each important finding, the following points should be considered:

• What is the security concern?

• Is the finding applicable to the environment?

• Is there any application dependency?

• What is the impact of making the change?

• Does the change require downtime?

• Has the change been tested in a lower environment?

• Is approval required before implementing it?

This approach helps avoid making security changes without understanding their operational impact.

3. Security Assessment Score

OCI Data Safe provides an overall view of the database security posture.

The score can be useful for tracking improvement over time.

However, the score should not be the only thing considered during a security review.

For example, a database may have a good overall score but still have one important finding that needs immediate attention.

It is therefore better to look at the individual findings and understand their impact instead of focusing only on the final score.

The assessment can also be used as a baseline.

After remediation, another assessment can be performed to confirm whether the security posture has improved.

4. User Assessment

Database users and privileges are another important part of database security.

In large environments, database accounts can accumulate over time.

Some users may have been created for temporary activities, some may belong to old applications, and some may no longer be required.

There can also be cases where users have more privileges than they actually need.

OCI Data Safe User Assessment helps provide better visibility into database users and their security-related characteristics.

This makes it easier to identify accounts that require further review.

5. What to Review During User Assessment

Some of the areas that deserve attention during a user review include:

Inactive or unused accounts

Old accounts should be reviewed to determine whether they are still required.

If an account is no longer needed, the appropriate process can be followed to disable or remove it.

Highly privileged users

Accounts with powerful privileges should receive additional attention.

Examples include:

• SYS

• SYSTEM

• DBA-privileged accounts

• Application accounts with administrative privileges

• Service accounts with unnecessary privileges

Application accounts

Application users should generally have only the privileges required by the application.

Providing broad privileges just to avoid application errors can create unnecessary security exposure.

Old accounts

Accounts created for previous applications, projects or temporary activities can remain in the database if they are not reviewed regularly.

Periodic user assessment helps identify these accounts.

6. Principle of Least Privilege

The principle of least privilege is very important when managing database users.

A user should have only the access required to perform the assigned activity.

For example, a reporting account that only needs to read data should not have unnecessary administrative privileges.

Similarly, an application account should not be given DBA-level access simply because it makes troubleshooting easier.

From an operational perspective, excessive privileges can sometimes make things easier in the short term.

From a security perspective, they increase the potential impact if the account is compromised.

Regular user reviews help maintain better control over this.

7. Activity Auditing

Security assessment helps identify configuration-related issues.

User assessment helps understand database access.

But there is another important question:

What is actually happening inside the database?

This is where activity auditing becomes useful.

Database activity auditing provides information about activities performed against the database.

Depending on the auditing configuration and database capabilities, audit information can help answer questions such as:

• Who accessed the database?

• Which user performed the activity?

• When did the activity happen?

• Which object was accessed?

• What type of operation was performed?

• Was the operation successful or unsuccessful?

This information can be useful for both security investigations and operational troubleshooting.

8. Activity Auditing in Real DBA Scenarios

Auditing is not limited to security teams.

It can also be useful during regular DBA investigations.

Example 1: Unexpected Data Change

Suppose an application team reports that some data in a production table was changed unexpectedly.

Instead of only trying to identify the person who may have made the change, audit information can be reviewed to understand the database activity around that time.

Example 2: Unexpected Login

If an unexpected login is reported for a production database, audit records can provide additional information about the account and activity.

Example 3: Object Changes

If a database object was modified unexpectedly, audit information can help during the investigation.

Example 4: Compliance Requirements

Some environments require database activities to be monitored and retained for audit or compliance purposes.

Centralized auditing can make this process easier to manage.

9. Security Assessment, User Assessment and Auditing Together

These three areas provide different views of database security.

Capability	What it helps answer
Security Assessment	Is the database configuration secure?
User Assessment	Who has access and what privileges do they have?
Activity Auditing	What activities are taking place in the database?

Looking at them together provides a much better picture.

The approach can be viewed as:

Configuration → Access → Activity

First, understand the security configuration.

Next, review who has access.

Finally, understand what activities are being performed.

This gives a more complete security review instead of focusing on only one area.

10. A Practical DBA Review Process

A simple periodic review can be followed for production databases.

Step 1: Review the Security Assessment

Check the latest assessment results and identify important findings.

Step 2: Prioritize the findings

Not every recommendation will have the same level of importance.

Critical and high-impact findings should receive priority.

Step 3: Review database users

Look for:

• Inactive accounts

• Unused accounts

• Highly privileged users

• Excessive privileges

• Old application accounts

• Service accounts

Step 4: Review audit activity

Check whether the required database activities are being captured.

Step 5: Investigate unusual activity

Any unexpected login, privilege usage or database activity can be investigated further.

Step 6: Remediate approved findings

Apply the required changes through the organization's change management process.

Step 7: Reassess

Run another assessment after remediation and compare the results.

This creates a continuous cycle rather than a one-time security check.

11. Security Changes Should Be Planned Carefully

One area that should not be overlooked is the relationship between security and application availability.

A security recommendation may suggest changing a database configuration, privilege or authentication setting.

Before implementing it in production, the following should be considered:

• Application compatibility

• Database dependencies

• Connection configuration

• Scheduled jobs

• Monitoring tools

• Integration systems

• Existing security controls

• Change management requirements

• Rollback plan

Security improvements should not introduce unexpected production issues.

Testing the change before production implementation is always preferable where possible.

12. Building Security Into Regular DBA Activities

Database security should not be treated as an activity that happens only during an audit.

It can be included in regular DBA operations.

For example:

Daily / Weekly

• Review important database activity

• Investigate unusual access

• Monitor security-related alerts

Monthly

• Review database users

• Review privileged accounts

• Check inactive accounts

• Review important security findings

Periodic

• Perform Security Assessment

• Review User Assessment

• Review audit configuration

• Track remediation

• Reassess after major changes

The exact frequency can depend on the organization's security and compliance requirements.

13. Practical DBA Security Checklist

The following checklist can be used during a database security review:

• Review the latest Security Assessment

• Identify critical security findings

• Validate findings against the application requirements

• Review privileged database accounts

• Review inactive and unused users

• Check application and service accounts

• Review unnecessary privileges

• Verify auditing requirements

• Review unusual database activity

• Track security remediation actions

• Follow change management for production changes

• Perform reassessment after remediation

• Maintain security review evidence where required

Conclusion

OCI Data Safe provides useful capabilities for Oracle database security management.

Security Assessment helps identify configuration and security gaps.

User Assessment provides better visibility into database users and privileges.

Activity Auditing helps understand database activity and supports investigation when something unexpected happens.

For DBAs, the important part is not simply generating a security report.

The real value comes from reviewing the findings, understanding the database and application requirements, taking appropriate action and checking the results again.

A simple approach such as:

Assess → Review → Remediate → Monitor → Reassess

can help make database security part of regular DBA operations.

As Oracle environments continue to grow across OCI and other cloud platforms, having a consistent way to review database security becomes increasingly important.

OCI Data Safe can be one of the tools used to bring these security activities together and provide better visibility into the overall database security posture.

Oracle to PostgreSQL Data Migration Using CSV

Oracle to PostgreSQL Data Migration Using CSV

1. Overview

CSV-based migration is one of the simplest and most transparent ways to move table data from Oracle to PostgreSQL. It is useful when the source of data volume is manageable, when a dependency-free method is preferred, or when teams need a repeatable runbook for controlled data movement.

This document explains the end-to-end flow in a blog-friendly format. The sample object names are generic, so the steps can be reused safely without exposing internal or client-specific details.

2. High-Level Migration Flow

Step	Activity	Purpose	Main Output
1	Export from Oracle	Extract selected rows into a CSV file	CSV file
2	Transfer file	Move the export file to the PostgreSQL server securely	CSV or compressed CSV
3	Prepare PostgreSQL table	Create a matching target structure	Target table
4	Import and validate	Load data using COPY and verify results	Loaded and validated data

3. Prerequisites

• Oracle database access with read permission on the source table.

• PostgreSQL database access with permission to create tables and load data.

• Sufficient disk space on both source and target servers for the CSV file.

• Network connectivity between source and target servers for secure file transfer.

• Agreement on date filters, column mapping, data types, and expected row count.

• A rollback or cleanup plan in case validation fails.

4. Step 1: Export Data from Oracle

Start by exporting only the required columns and date range. Keeping the export focused reduces file size, improves load performance, and makes validation easier.

In SQL*Plus, CSV formatting can be enabled so that the result is written directly to a structured CSV file.

sqlplus <source_user>/<password>@<source_service_name>

SET HEADING ON

SET FEEDBACK OFF

SET TERMOUT OFF

SET MARKUP CSV ON DELIMITER ',' QUOTE ON

SPOOL /path/to/export/source_table_month.csv

SELECT column_1,

       column_2,

       event_timestamp,

       numeric_value,

       created_timestamp,

       status_code,

       group_id

FROM <source_schema>.<source_table>

WHERE event_timestamp >= TO_DATE('<start_date>', 'YYYY-MM-DD')

  AND event_timestamp <  TO_DATE('<end_date>',   'YYYY-MM-DD');

 

SPOOL OFF

EXIT;

Why this step matters

• Column-level selection avoids exporting unnecessary data.

• A date range filter makes the extract repeatable and easier to reconcile.

• CSV quoting helps preserve values that contain commas or special characters.

• The spool output becomes the migration input for PostgreSQL.

5. Optional Step: Compress the Export File

If the CSV file is large, compress it before transferring. Compression reduces transfer time and lowers the chance of network interruption during file movement.

gzip /path/to/export/source_table_month.csv

After compression, the file name will usually become source_table_month.csv.gz.

6. Step 2: Transfer the File Securely

Move the CSV or compressed CSV file from the Oracle server to the PostgreSQL server using an approved secure transfer method such as SCP, SFTP, or an enterprise file transfer tool.

scp /path/to/export/source_table_month.csv.gz <target_user>@<target_host>:/path/to/target/folder/

Recommended checks after transfer

• Confirm that the file exists on the target server.

• Compare file size between source and target.

• If checksum validation is available, compare checksum values.

• Unzip the file before loading if PostgreSQL COPY is reading the plain CSV file.

gunzip /path/to/target/folder/source_table_month.csv.gz

7. Step 3: Prepare the Target Table in PostgreSQL

Before loading the CSV file, create the target table with data types that match the source data. This step is important because Oracle and PostgreSQL data types are not always one-to-one matches.

psql -h <target_host> -U <target_user> -d <target_database>

CREATE TABLE <target_schema>.<target_table> (

    column_1           VARCHAR(100),

    column_2           VARCHAR(100),

    event_timestamp    TIMESTAMP,

    numeric_value      NUMERIC,

    created_timestamp  TIMESTAMP,

    status_code        VARCHAR(50),

    group_id           INTEGER

);

Data type mapping guidance

Oracle Type	PostgreSQL Type	Comment
VARCHAR2	VARCHAR or TEXT	Use VARCHAR when a known limit is required.
NUMBER	NUMERIC, INTEGER, BIGINT	Choose based on precision, scale, and expected values.
DATE	TIMESTAMP or DATE	Oracle DATE can include time, so validate carefully.
TIMESTAMP	TIMESTAMP	Confirm timezone handling requirements separately.

8. Step 4: Import CSV Data into PostgreSQL

PostgreSQL COPY is a fast and commonly used method to load CSV data into a table. The file path must be accessible to the PostgreSQL server process.

COPY <target_schema>.<target_table> (

    column_1,

    column_2,

    event_timestamp,

    numeric_value,

    created_timestamp,

    status_code,

    group_id

)

FROM '/path/to/target/folder/source_table_month.csv'

WITH (

    FORMAT csv,

    HEADER true,

    DELIMITER ',',

    QUOTE '"',

    ESCAPE ''''

);

Common import issues to check

• File access issue: PostgreSQL must be able to read the file path.

• Timestamp format mismatch: confirm exported timestamps match PostgreSQL expectations.

• Delimiter or quote issue: validate the CSV configuration if rows fail to load.

• Numeric conversion issue: check blank values, special characters, and precision requirements.

9. Step 5: Validate the Data Load

Validation confirms whether the migration was completed correctly. At minimum, compare row counts, sample records, timestamp boundaries, and numeric values.

-- Check total row count

SELECT COUNT(*) AS target_row_count

FROM <target_schema>.<target_table>;

-- Check timestamp range

SELECT MIN(event_timestamp) AS min_event_time,

       MAX(event_timestamp) AS max_event_time

FROM <target_schema>.<target_table>;

-- Review sample records

SELECT *

FROM <target_schema>.<target_table>

ORDER BY event_timestamp

LIMIT 10;

Validation	Expected Result	Action if Failed
Row count	Target count matches source count	Review filters, rejected rows, and duplicate load attempts.
Date range	Minimum and maximum timestamp are within expected range	Check Oracle WHERE clause and timestamp formatting.
Data sampling	Sample values match source records	Compare source and target for selected keys.
Numeric accuracy	Decimal and integer values are preserved	Review NUMERIC precision and CSV formatting.

10. Step 6: Post-Migration Cleanup

Once validation is successful, complete the housekeeping activities so that the environment remains clean and secure.

• Archive the CSV file only if required by the retention policy.

• Remove temporary files from shared paths after approval.

• Document row counts, validation of queries, and completion time.

• Apply indexes or constraints after the bulk load if that was part of the migration plan.

• Notify application or reporting teams that the dataset is ready for use.

11. Summary

This blog provides a simple and repeatable approach for moving Oracle table data to PostgreSQL using CSV files. The key success factors are a controlled export query, secure file transfer, correct target table design, reliable COPY import, and strong post-load validation.