Learn DB Concepts with me...

With the release of Oracle 23c AI, Oracle has introduced a suite of new features designed to empower DBAs and developers. One of the most revolutionary additions is JSON Relational Duality, which offers unprecedented flexibility in handling data by allowing users to access the same data as both relational and JSON, seamlessly switching between formats without duplication or additional processing.

This article explores JSON Relational Duality and demonstrates how it simplifies data management for applications that need to handle both structured and unstructured data. 

JSON Relational Duality in Oracle 23c AI allows the same data to be represented as both relational and JSON simultaneously. This feature is particularly valuable for applications where structured relational data needs to be accessed as unstructured JSON data—and vice versa. 

For example, a company with a traditional relational structure may want to integrate with a modern application that uses JSON-based APIs. JSON Relational Duality bridges this gap, enabling applications to leverage both formats without complex transformations or performance compromises.

In the past, handling both relational and JSON data often meant duplicating data or creating intricate ETL processes to convert between formats. JSON Relational Duality eliminates these challenges by allowing a single, consistent view of data that works natively with both JSON and relational formats.

- Reduced Complexity: You avoid complex ETL processes when transforming data formats.

- Enhanced Flexibility: Developers can work with the format best suited to their application needs.

- Improved Performance: Access data in the format you need without extra processing, reducing load on the system.

To demonstrate JSON Relational Duality, let’s use an example of a user, 'user', who manages employee data in a relational structure but needs to provide a JSON format for API consumption.

Suppose we have a table 'employees' with the following schema:

SQL

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    department VARCHAR2(50),

    salary NUMBER

);

Step 1: Enabling JSON Relational Duality

With Oracle 23c AI, we can use a 'DUPLICATE JSON' clause to enable duality, allowing 'employees' to be queried as JSON without changing the schema.

SQL

ALTER TABLE employees ENABLE JSON RELATIONAL DUALITY;

Step 2: Querying Data in JSON Format

Now, 'user' can retrieve the same data in JSON format using a simple query:

SQL

SELECT JSON_OBJECT(*) 

FROM employees 

WHERE department = 'Sales';

The query above will output each row as a JSON object, making it compatible with any API or application that requires JSON.

Step 3: Using JSON Data with Relational Functions

JSON Relational Duality allows JSON data to be used in SQL operations as if it were relational. This means that the 'user' can join, filter, and manipulate JSON data using SQL.

For example, let’s filter employees by salary and project it as JSON:

SQL

SELECT JSON_OBJECT(*) 

FROM employees 

WHERE salary > 70000;

Use Case: Integrating Relational Data with a JSON-Based Application

Imagine the 'user' is tasked with integrating an Oracle database with a cloud-based application that consumes JSON via REST API. JSON Relational Duality simplifies this process. Instead of transforming data into JSON on the fly or storing duplicates, the 'user' can directly query relational data in JSON format and feed it into the API, streamlining integration.

Example API Call with JSON Duality Data

Using JSON output from Oracle, the 'user' can now make API calls without extra data transformation:

JSON:

{

    "employee_id": 101,

    "first_name": "John",

    "last_name": "Doe",

    "department": "Sales",

    "salary": 75000

}

JSON Relational Duality in Complex Queries

Let’s explore a more complex query in which the 'user' needs to get department-wise average salaries, outputting the results in JSON format.

SQL

SELECT JSON_OBJECT(

    'department' VALUE department,

    'average_salary' VALUE AVG(salary)

) AS department_summary

FROM employees

GROUP BY department;

This query enables a modern, JSON-based interface to summarize data traditionally stored in relational format, making it accessible to front-end applications that prefer JSON.

SEO-Optimized Benefits of JSON Relational Duality

For DBAs, developers, and system architects, JSON Relational Duality in Oracle 23c AI is a keyword-rich feature that aligns with trends in hybrid database management and Oracle 23c AI JSON integration. It emphasizes dual-format data handling and native JSON support in relational databases, making it ideal for applications with mixed data needs. Key benefits like performance optimization, reduced data duplication, and simplified data management address frequently searched queries, ensuring that Oracle 23c AI remains at the forefront of modern database management.

Summary: Embracing the Duality Advantage in Oracle 23c AI

Oracle 23c AI’s JSON Relational Duality is a groundbreaking feature, particularly for DBAs like 'user', who must manage complex data structures efficiently. With JSON Relational Duality, Oracle offers flexibility without sacrificing performance, enabling seamless data integration for applications that demand both structured and unstructured data.

For anyone managing databases in hybrid environments, JSON Relational Duality is a powerful tool that reduces workload, enhances performance, and provides a competitive edge in the data-driven landscape.

Invoke SQL*Plus:

First, open SQL*Plus to begin the process.

Query V$SESSION to Identify the Session:

Use the following query to retrieve the session details. This will list all active sessions with their respective SID, SERIAL#, STATUS, SCHEMANAME, and PROGRAM.

SELECT SID, SERIAL#, STATUS, SCHEMANAME, PROGRAM FROM v$session;

The SID (Session Identifier) and SERIAL# values of the Oracle session to be killed can then be identified from this output.

Execute the ALTER SYSTEM Command:

Substitute the identified SID and SERIAL# values and issue the alter system kill session command.

ALTER SYSTEM KILL SESSION 'sid,serial#';

Handling Sessions Marked for Kill:

Sometimes, Oracle is not able to kill the session immediately with the alter system kill session command alone. Upon issuing the command, the session may be 'marked for kill' and will be terminated as soon as possible.

Forcing Session Termination:

In the case where a session is 'marked for kill' and not terminated immediately, you can force the termination by adding the immediate keyword:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Verify Termination:

To ensure that the session has been terminated, re-query the V$SESSION dynamic performance view:

SELECT SID, SERIAL#, STATUS, SCHEMANAME, PROGRAM FROM v$session WHERE SID = 'sid' AND SERIAL# = 'serial#';

The value of the STATUS column will be 'ACTIVE' when the session is making a SQL call and 'INACTIVE' if it is not.

Confirm PMON Cleanup:

After the Process Monitor (PMON) has cleaned up the session, the row will be removed from V$SESSION. Re-query to confirm:

SELECT SID, SERIAL#, STATUS, SCHEMANAME, PROGRAM FROM v$session WHERE SID = 'sid' AND SERIAL# = 'serial#';

Handling RAC Environments:

In a Real Application Clusters (RAC) environment, you can optionally specify the INST_ID, which is shown when querying the GV$SESSION view. This allows you to kill a session on a different RAC node.

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

By following these detailed steps, you can efficiently identify and terminate a session using SQL*Plus, ensuring minimal disruption and maintaining database integrity.

In database management, efficiently handling idle sessions is essential to maintaining performance and resource availability. Oracle Resource Manager is a powerful tool, often underutilized, that offers granular control over how sessions consume resources. This post explores how to leverage Oracle Resource Manager to monitor and automatically terminate idle sessions, complete with in-depth, practical examples.

Oracle Resource Manager allows DBAs to define and enforce resource limits on user sessions based on specific conditions, such as idle time, CPU usage, and session priority. This level of control can be particularly useful in environments with high session volumes, such as transactional systems or shared database infrastructures, where idle sessions can prevent other active sessions from connecting.

To illustrate, let's walk through a scenario where we have a transactional database with frequent user connections. Our goal is to manage idle sessions, terminating any session that remains idle for over an hour. 

A resource plan acts as a blueprint, defining how Oracle should handle session resources. We’ll create a resource plan named `Session_Management_Plan` to automatically switch idle sessions to a termination state.

SQL:

BEGIN

    DBMS_RESOURCE_MANAGER.create_pending_area();

    DBMS_RESOURCE_MANAGER.create_plan(

        plan    => 'Session_Management_Plan',

        comment => 'Resource Plan to handle idle sessions exceeding 1 hour'

    );

    DBMS_RESOURCE_MANAGER.submit_pending_area();

END;

/

Here, we set up a simple resource plan structure to act as a container for directives. The `create_pending_area` function places the configuration in a pending state until it is finalized and submitted.

In Oracle Resource Manager, consumer groups classify sessions based on their resource needs. Here, we create two consumer groups: `ACTIVE_SESSIONS` for sessions with standard activity and `IDLE_TERMINATION` for sessions that exceed the idle limit.

SQL:

BEGIN

    DBMS_RESOURCE_MANAGER.create_pending_area();

    DBMS_RESOURCE_MANAGER.create_consumer_group(

        consumer_group => 'ACTIVE_SESSIONS',

        comment        => 'Group for actively monitored sessions'

    );

    DBMS_RESOURCE_MANAGER.create_consumer_group(

        consumer_group => 'IDLE_TERMINATION',

        comment        => 'Group for sessions to be terminated if idle too long'

    );

    DBMS_RESOURCE_MANAGER.submit_pending_area();

END;

/

These consumer groups allow us to specify different rules and behaviors based on session status.

Directives define the rules for each consumer group within a plan. We’ll set a directive to monitor sessions within `ACTIVE_SESSIONS`, moving idle sessions to the `IDLE_TERMINATION` group if they remain idle for over an hour.

SQL:

BEGIN

    DBMS_RESOURCE_MANAGER.create_pending_area();

    DBMS_RESOURCE_MANAGER.create_plan_directive(

        plan             => 'Session_Management_Plan',

        group_or_subplan => 'ACTIVE_SESSIONS',

        comment          => 'Standard sessions with monitoring for idle state',

        switch_time      => 3600, -- 1 hour idle limit (3600 seconds)

        switch_group     => 'IDLE_TERMINATION'

    );

    DBMS_RESOURCE_MANAGER.create_plan_directive(

        plan             => 'Session_Management_Plan',

        group_or_subplan => 'IDLE_TERMINATION',

        comment          => 'Group for terminating sessions idle over 1 hour',

        cpu_p1           => 0, -- No CPU allocation as sessions are idle

        max_idle_blocker_time => 3600 -- Terminate after one hour idle

    );

    DBMS_RESOURCE_MANAGER.submit_pending_area();

END;

/

- `switch_time` specifies the idle duration threshold (3600 seconds or 1 hour).

- `switch_group` moves sessions from `ACTIVE_SESSIONS` to `IDLE_TERMINATION` once they exceed the idle time.

- `cpu_p1` is set to zero for `IDLE_TERMINATION` to prevent idle sessions from consuming CPU.

- `max_idle_blocker_time` limits the maximum time for idle sessions in the `IDLE_TERMINATION` group, ensuring termination.

With the resource plan and directives set, we activate `Session_Management_Plan` to enforce these rules on the database.

SQL:

ALTER SYSTEM SET resource_manager_plan = 'Session_Management_Plan';

By activating this plan, Oracle will apply our specified rules for monitoring idle sessions and automatically terminate them after an hour of inactivity.

To ensure the plan is working as expected, monitor session activity by checking which sessions are in `ACTIVE_SESSIONS` versus `IDLE_TERMINATION`.

SQL:

SELECT username, 

       session_id, 

       status, 

       last_call_et AS idle_seconds, 

       consumer_group

FROM   v$session

WHERE  consumer_group IN ('ACTIVE_SESSIONS', 'IDLE_TERMINATION')

ORDER BY idle_seconds DESC;

The `last_call_et` column shows idle time in seconds, while the `consumer_group` column lets you see whether the session is active or set for termination. This query provides visibility into session status, letting you track how effectively idle sessions are managed.

1. Testing in Non-Production: Resource Manager settings should be tested in non-production environments first to ensure they align with your workload and that CPU or memory-intensive processes are unaffected by idle session policies.

2. Avoid Overly Aggressive Termination Policies: While terminating idle sessions frees up resources, be cautious with overly aggressive thresholds that could disrupt user sessions critical for business operations.

3. Regularly Review Session Activity: Use `DBA_HIST_ACTIVE_SESS_HISTORY` or `DBA_HIST_RESOURCE_PLAN_DIRECTIVE` views to analyze historical session activity and refine thresholds as needed.

Oracle Resource Manager offers a structured, policy-based way to manage session resources, making it easier for DBAs to maintain optimal database performance. By automatically handling idle sessions, DBAs can prevent resource bottlenecks and maximize availability for active sessions. With careful setup and monitoring, Resource Manager becomes an invaluable tool in any Oracle DBA’s toolkit.

References: Oracle Database Administrator’s Guide, Oracle 19c Documentation, Oracle Database Resource Manager Concepts and Usage.

In database environments, maintaining optimal performance requires careful management of user sessions, especially idle ones. Idle sessions can consume resources and lead to unnecessary wait times, especially when the maximum number of sessions is reached. In this post, we’ll explore how to identify idle sessions, create a configuration to manage them, and set up an automatic process to terminate idle sessions that last longer than one hour.

Understanding Idle Sessions in Oracle Database

An idle session is a session where the user is connected but not actively executing SQL statements. While Oracle can handle many sessions simultaneously, excessive idle sessions can eventually strain resources. By managing and terminating these sessions, DBAs can optimize resource usage, particularly in environments where there’s a limit on concurrent connections.

Oracle Profiles allows DBAs to control session-level resource usage, including limiting the amount of idle time.

Step 1: Create a Profile with an Idle Time Limit

To set an idle timeout of one hour (60 minutes), we’ll create a profile with a `IDLE_TIME` resource limit. 

SQL: 

CREATE PROFILE user_idle_limit_profile

LIMIT

    IDLE_TIME 60; -- 60 minutes

Here, `IDLE_TIME` represents the maximum allowed idle time for a session, in minutes. Once this threshold is exceeded, Oracle will automatically disconnect the session.

Step 2: Assign the Profile to Users

To enforce the idle timeout, assign the profile to users who require monitoring:

SQL: 

ALTER USER john_doe PROFILE user_idle_limit_profile;

ALTER USER jane_smith PROFILE user_idle_limit_profile;

All users assigned to `user_idle_limit_profile` will be disconnected automatically if idle for more than one hour. Keep in mind that an `IDLE_TIME` of zero means there is no idle limit.

 Note: Sessions terminated this way will receive an ORA-02396 warning message, indicating that they have been disconnected due to inactivity.

Step 3: Verifying Profile Application

You can verify if the profile was applied correctly by checking the `DBA_USERS` view:

SQL: 

SELECT username, profile 

FROM dba_users 

WHERE profile = 'USER_IDLE_LIMIT_PROFILE';

This confirms that the intended users are bound by the idle session limit.

Oracle’s Resource Manager offers more control. It allows you to create rules that monitor and act on specific session conditions, including idle sessions.

Step 1: Create a Resource Plan

First, create a resource plan that specifies the action to take when a session remains idle for too long. Here’s an example:

SQL: 

BEGIN

    DBMS_RESOURCE_MANAGER.create_pending_area();

    DBMS_RESOURCE_MANAGER.create_plan(

        plan    => 'idle_session_plan',

        comment => 'Resource plan to terminate idle sessions'

    );

    DBMS_RESOURCE_MANAGER.create_plan_directive(

        plan             => 'idle_session_plan',

        group_or_subplan => 'OTHER_GROUPS',

        comment          => 'Terminate sessions idle for more than 60 minutes',

        switch_for_call  => 'TRUE',

        switch_time      => 3600, -- 3600 seconds (1 hour)

        switch_group     => 'KILL_SESSION'

    );

    DBMS_RESOURCE_MANAGER.submit_pending_area();

END;

/

In this example, the `switch_time` parameter is set to 3600 seconds (1 hour). If a session remains idle for this duration, Oracle will automatically move it to the `KILL_SESSION` group, where it’s terminated.

Step 2: Activate the Resource Plan

Once the resource plan is defined, activate it to enforce the rules:

SQL: 

ALTER SYSTEM SET resource_manager_plan = 'idle_session_plan';

With this plan active, sessions that remain idle for over an hour are automatically terminated, reducing unnecessary resource consumption.

Step 3: Verifying Active Resource Plan

To check which resource plan is active, you can use:

SQL: 

SELECT name, active

FROM v$rsrc_plan

WHERE is_top_plan = 'TRUE';

This will confirm that `idle_session_plan` is the active resource management plan.

Regularly reviewing the sessions terminated by the profile or resource manager helps ensure that active users are unaffected. You can monitor terminated sessions by querying Oracle’s session and resource views:

SQL: 

SELECT sid, serial#, username, status, last_call_et

FROM v$session

WHERE status = 'INACTIVE' 

  AND last_call_et > 3600; -- Sessions idle for more than 1 hour

The `last_call_et` column records the idle duration in seconds. This query allows you to keep track of all sessions that have been idle for over an hour.

1. Set Realistic Limits: Configure idle timeouts that reflect actual user behavior. For instance, if users typically require 30-minute breaks, a 60-minute idle timeout may be ideal.

2. Monitor Frequently: As with any automated process, regularly monitor your configuration to ensure that critical sessions are not mistakenly terminated.

3. Communicate with Users: Inform users about session policies, especially if implementing a more aggressive timeout.

By setting up automated processes to manage idle sessions, Oracle DBAs can improve resource utilization, ensure a smooth user experience, and optimize system performance. This proactive approach helps avoid potential bottlenecks, allowing DBAs to focus on higher-impact tasks.

1. Remove remap if you don't want.
2. Add ENCRYPTION_PASSWORD=IF_ANY