READ-ONLY PDB users in Oracle 23ai - Secure Multitenancy

Oracle 23ai has introduced a new feature, READ-ONLY PDB user to improve security, developer productivity and performance. This helps multi-tenant environment where data access is critical.

The READ-ONLY user cannot perform any DDL or DML activities.

Create a Read-Only PDB User: To create a Read-Only PDB use the new READ ONLY clause in the CREATE USER statement.

Connect to PDB user
SQL> ALTER SESSION SET CONTAINER = my_pdb;

Create readonly “hr_user”
SQL> CREATE USER hr_user IDENTIFIED BY passwordxxx READ ONLY;

Grant create session to hr_user
SQL> GRANT CREATE SESSION TO hr_user;

Note that the “hr_user” cannot be able to perform below tasks

• User cannot run INSERT, DELETE, UPDATE or MERGE.
• User Cannot create or modify tables, indexs, views or procedures
• User cannot change roles or privileges
• User cannot modify session-level settings

When you try any of the above user will receive “ORA-28194: Can perform read operations only " error.

Run below view to see the user is read-only or not
SQL> SELECT username, read_only from dba_users where username='HR_USER';
USERNAME      READ_ONLY
--------------------  -----------------
HR_USER           YES

SQL> Connect hr_user/paswordxxx;
Connected.

SQL> CREATE TABLE employee_test (emp_id number, emp_name varchar2(50));
*
ERROR at line 1:
ORA-28194: Can perform read operations only

SQL> DELETE FROM employee;
*
ERROR at line 1:
ORA-28194: Can perform read operations only

Note that READ-ONLY users can execute PL/SQL if it doesn’t have any DDL or DML.
The below procedure rev_salary has update statement and cannot perform the operation.

 
SQL> exec REV_SALARY;
ERROR at line 1:
ORA-28194: Can perform read operations only
ORA-06512: at "HR_USER.REV_SALARY", line 3
ORA-06512: at line 1

The READ-ONLY user can run a SELECT query without any issues.

SQL> SELECT emp_id, emp_name from employee;
EMP_ID EMP_NAME
-------------- ----------------------------
1 test_user1
2 test_user2
3 test_user3
SQL>

The Read-Only PDB Users provide a clean way to enforce non-modifiability of users at the database level. This helps with read intensive applications, as these users restricted to only SELECT and users cannot perform any DDL or DML activities.

Thanks & Regards,

https://oracleracexpert.com  

Webinar: Strengthen Your Oracle Database 23c Security

Join us for an informative session focused on the latest security enhancements in Oracle Database 23c. We’ll explore key features such as improved authentication methods, encryption, and SQL Firewall, and demonstrate how these innovations help protect your data and strengthen your defenses against evolving threats.

Date & Time : 

Sept 19th  , 2025 8:00 AM – 9:00 AM Pacific Time (GMT-07:00 | San Francisco)

 
This session is ideal for:
Database Administrators (DBAs)
IT Security Professionals
Oracle Architects and Developers
Who are looking to enhance database security practices using Oracle 23c's new capabilities.

Topics covered in this webinar include:

• SQL Firewall
• Database Auditing Enhancements
• Authentication & Authorization Updates
• Data Encryption
• Autonomous Database Security Features
• And other key innovations in Oracle 23c

How to Register
Please send an email to: SatishbabuGunukula@gmail.com to register and receive webinar access details.

Join the Webinar

Click here to join the Meeting 
Click here to view the Presentation 

RMAN-03009 - RMAN Backup Failure After Applying DB RU Patch: A Resolution Guide

After applying a Database Release Update (DB RU) patch, we encountered an RMAN backup failure. The error messages provided were as follows:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 08/09/2025 14:54:45
ORA-01403: no data found

Issue: Failure While Registering Database
When attempting to register the database with RMAN, the following output was observed:

RMAN> register database;
database registered in recovery catalog
Creating and using snapshot control file for resync
starting full resync of recovery catalog
Control file used records for BACKUP REDOLOG = 6880
Control file used records for DELETED OBJECT = 6544
Control file used records for BACKUP SET = 2720
Control file used records for ARCHIVED LOG = 2384
Control file used records for LOG HISTORY = 2336
Control file used records for BACKUP DATAFILE = 2288
Control file used records for RMAN STATUS = 2256
Control file used records for BACKUP PIECE = 2096
Control file used records for BACKUP SPFILE = 524
Resync in progress: 11000 RMAN OUTPUTrecords resynced
RMAN Command Id : 2025-08-11T14:58:25
RMAN Command Id : 2025-08-11T14:58:25
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMANCAT.RLH_F1) violated - parent key
not found
RMAN Client Diagnostic Trace file :
/oracle/diag/clients/user_oracle/RMAN_3026693161_110/trace/ora_
rman_37312_1.trc
RMAN Server Diagnostic Trace file :
/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_37319.trc

Root Cause: Recovery Catalog Not Current
Upon attempting to connect to the RMAN catalog, we received the following message:

$ rman catalog rmancatusr@CATDB

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 09 01:38:51 2025
Version 19.28.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

recovery catalog database Password:
connected to recovery catalog database
PL/SQL package RMANCAT.DBMS_RCVCAT version 19.10.00.00. in RCVCAT database is not current
PL/SQL package RMANCAT.DBMS_RCVMAN version 19.10.00.00 in RCVCAT database is not current

This indicates that the RMAN catalog was not upgraded and was out of sync with the new database release.

Solution: Upgrade the RMAN Catalog
To resolve the issue, we needed to upgrade the RMAN catalog. The solution was to run the following command:

$ rman catalog <catalog user/passwd> @catdb
RMAN> upgrade catalog ;

The issue has been RESOLVED after upgrading the database. The user able to register database and able to run backups without any issues.

Conclusion

This issue was caused by the RMAN catalog being out of sync after applying a DB RU patch. Upgrading the catalog resolved the error, allowing successful database registration and backup operations.

Unlocking the Power of GraphQL in Oracle Database 23ai

Oracle 23ai introduces native support for GraphQL, it’s a modern API query language and helps developers by enabling efficient and flexible data access from the database. Unlike REST API’s, the GraphQL allows clients to extract the data they need by reducing over-fetching, undirecting of data with flexible data access.

By using GraphQL now developers can expose database schemas as GraphQL API’s without any custom code and access real time data securely with seamless integration using modern frontend tools.

GraphQL helps in the following areas

• Modern Web and Mobile Apps rapid development
• Data federation by coming relation data with other sources such as JSON Data
• Microservices architecture in a clean, versioned manner.

In order to use GraphQL, you must install or upgrade to Oracle 23ai and you should use Oracle Rest Data services (ORDS) 23.2 version or later. Note that SQL Developer web gives us a GraphQL editor screen and by using that user can write the queries

requests

For example 1: - Simple query

SQL> CREATE TABLE emp (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
salary number(10),
is_active BOOLEAN);


SQL> CREATE TABLE dept (
dept_id NUMBER PRIMARY KEY,
name VARCHAR2(100);


GraphQL Query:
query {
    emp {
        emp_id
        name
        salary
        is_active
    }
}

Oracle SQL equivalent query:

SQL> Select emp_id, name, salary, is_active from emp;

For example, 2: - Query filtering with arguments


GraphQL Query:

query {
    emp (emp-id:12345) {
        name
        salary
        is_active
    }
}

Oracle SQL equivalent query:

SQL> Select name, salary, is_active from emp where emp_id=12345;

Example 3: Role based access control

If you have HR user and other users, you want only HR users to see the salary info but not others. By using oracle built in security your GraphQL Schema you can have these access controls.

HR User:
query {
    emp {
        name
        salary
    }
}

Other User:
query {
    emp {
        name
    }
}

You can restrict which fields are visible depending upon the users’ roles without writing any custom logic.

Users can use the tools below to test GraphQL

1. postman with GraphQL support
2. GraphQL playground based or local
3. Apollo Studio
4. ORDS GraphQL endpoint tester

When using GraphQL, Oracle translates GraphQL into an optimized SQL Json and returns only the requested fields. The above examples demonstrate how easy it is to use GraphQL to integrate directly into the oracle stack. The Oracle 23ai enables you to build faster, less code, and deliver richer APIs directly from your database.

Exploring the New BOOLEAN Data Type in Oracle Database 23ai

Oracle Database 23ai has introduced many features, and one of the nice additional in SQL is the support for the BOOLEAN data type. The BOOLEN data types are available for many years in Pl/SQL and now it is supported as native data type. This will help developers simplify application logic. Earlier developers often had to rely on CHAR(1) or NUMBER(1) fields to simulate boolean logic in SQL

The BOOLEAN data type represents logical values such as TRUE, FALSE, and NULL.

Advantages of native BOOLEAN support in SQL:

• Improved readability: No more cryptic 'Y', 'N', or 1, 0 values. Developers can use TRUE and FALSE make code more intuitive.
• Better integration: Direct support in SQL helps use BOOLEAN values easier to use in views, constraints, triggers, and queries.
• Less error-prone: removes confusion caused by using characters or numbers to represent boolean values
• Modernization: Aligns Oracle SQL more closely with other RDBMSs such as PostgreSQL and MySQL.

How to use BOOLEAN in Oracle 23ai

1. Creating a Table with a BOOLEAN Column

SQL> CREATE TABLE emp (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
is_active BOOLEAN);

2. Inserting BOOLEAN Values

INSERT INTO emp (employee_id, name, is_active)
VALUES (101, 'Samantha', TRUE);

INSERT INTO emp (employee_id, name, is_active)
VALUES (102, 'Rex', FALSE);

3. Querying BOOLEAN Values

SELECT name FROM emp
WHERE is_active = TRUE;

Or

SELECT name FROM emp WHERE is_active;

4. Using BOOLEAN in CASE Statements

SELECT name,
CASE
WHEN is_active THEN 'Active'
WHEN NOT is_active THEN 'Inactive'
ELSE 'Unknown'
END AS status FROM emp;

Key Considerations: -

• Note that BOOLEAN values can still be NULL, but in conditions where NULL might affect the logic.
• Not all Oracle tools and connectors fully support BOOLEAN yet and it’s user responsibility to check compatibility with client libraries
• Existing old code using CHAR(1) or NUMBER(1) won’t auto-convert for BOOLEAN, user must migrate manually

The BOOLEAN data type in Oracle Database 23ai is a game changer for developers and DBAs. It improves data modeling, simplifies SQL logic, helps developer experience like other RDBMS platforms and consider adopting BOOLEAN fields wherever required.

Thanks & Regards,

https://oracleracexpert.com  

Data Recovery Advisor (DRA) in Oracle Database

I had the opportunity to work with the Data Recovery Advisor (DRA) feature in earlier versions of Oracle Database, and its great tool to automate the recovery process, reducing downtime and recovery time

In Oracle 11g, the Data recovery advisor is introduced, and this tool helps to reduce the recovery time by providing best automated repair option for the database. This tool automatically diagnoses data failures, determine best repair options, executes repairs at user request, it helps to reduce mean time to recover (MTTR).

The Data recovery advisor can help to limit damage caused by corruption as it can detect, analyze and repair failures before database process discovers it. In traditional method user manually determines the impact and repair options, in some cases users needs to determine right sequence of repair as well.

The Data recovery advisor commands are LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE and CHANGE FAILURE.

• LIST FAILURE: Lists the failures that have occurred in the database. 

        RMAN> LIST FAILURE;

• ADVISE FAILURE: Provides the best repair options for the listed failures. 

        RMAN> ADVISE FAILURE;

• REPAIR FAILURE: Executes the repair process based on the user's selection.

        RMAN> REPAIR FAILURE;

• CHANGE FAILURE: Allows users to modify the failure status.

    RMAN> CHANGE FAILURE FAILURE #1 RESOLVED;

 
Deprecation of DRA in Oracle 19c

While the Data Recovery Advisor was a valuable tool in earlier Oracle releases, it was deprecated in Oracle 19c. This means that DRA will no longer be available for use in future versions beyond Oracle 19c. Additionally, the associated RMAN commands (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE, and CHANGE FAILURE) have also been deprecated. As a result, DBAs will no longer have access to these commands in Oracle 19c and beyond, marking the end of an era for this powerful recovery tool.

Thanks & Regards,
https://oracleracexpert.com

MAX_COLUMNS parameter in Oracle Database 23ai

Before Oracle 23ai, the maximum allowed columns in a table is 1000. From Oracle 23ai you can increase this value by modifying a parameter MAX_COLUMNS up to 4096 if you have any use case. This initialization parameter can be set at system level only and in case of PDB you can limit to specific PDB. 

To use this MAX_COLUMN parameter the compatibility should be set to 23.0.0.0 or higher. To increase max allowed colums you must set the MAX_COLUMNS value to “EXTENDED”. Note that you can change MAX_COLUMNS from STANDARD to EXTENDED any time but to change the value back to STANDARD only when any table or view in the database contains 1000 or fewer columns. 

By default, the MAX_COLUMNS initialization parameter is set to STANDARD

SQL>show parameters max_columns
NAME                                TYPE      VALUE
-------------------------------- ----------- -------------------------
max_columns                      string       STANDARD

When user trying to add columns more than 1000 in a table will receive below error

ORA-01792: maximum number of columns in a table or view is 1000

You can change MAX_COLUMNS value using below command that will allow up to 4096 columns

SQL> ALTER SYSTEM set MAX_COLUMNS=EXTENDED scope=spfile;
SQL> shutdown immediate;
SQL> startup

If the database has tables with more than 1000 columns and trying to update the MAX_COLUMNS parameter value back to STANDARD, then user should receive below error

SQL> ALTER SYSTEM SET set MAX_COLUMNS =STANDARD scope=spfile;

ORA-32017: failure in updating SPFILE
ORA-60471: max_columns cannot be set to STANDARD as there are one or more objects with more than 1000 columns

The only way user can change this value by dropping the objects with more than 1000 columns.

Note that older Oracle client versions (before Oracle 23ai) do not support columns more than 1000 in a table and only Oracle 23ai clients support the 4096 column limit.

Thanks & Regards,

https://oracleracexpert.com

Tableau Server Unlicensed after activating the license

User might encounter this License issue after deactivating and activating license on Tableau Server and it shows as Expired or Ended.

Error: Unlicensed Go to the licensing page for more details

When you try to deactivate and active you might receive error

The requested licensing operation failed with error code '110: TABLEAU_ERROR_LICENSING_UNLICENSED_GENERAL'.”

When you run “tsm licenses list” it will show Creator/Explorer/Viewer as “0” (Zero)

This is a known issue and happens for Tableau server 2024.2.1 and later when the installation is not using ATR.

Workaround: We don’t have fix at this time, but we have workaround and user should follow below steps

1. Stop Tableau Server using CMD (tsm stop) or TSM Web GUI
2. Restart O/S
3. Start Tableau Server using CMD (tsm start) or TSM Web UI

Once the table server is up you can run below TMS command to confirm the license expire date is set t as expected or you can check the license on TMS Web UI

C:\> tsm licenses list

Pls refer below KB’s for more Details

Error code '101' or '100' TABLEAU_ERROR_LICENSING_INTERNAL when Activating Tableau Server | Tableau Software

Immediately after activating a Tableau Server license, it shows as "Ended"

Thanks & Regards,

https://oracleracexpert.com

DBMS_SPACE package enhancements in Oracle 23ai

The DBMS_SPACE package used to analyze segment growth and space requirements in the database. From Oracle 23ai onwards you can use this package to shrink a big file tablespace to reclaim unused space. In case if there are any unsupported objects you can identify in analyze phase.

First analyze big tablespace to see how much space can be reclaimed before performing the shrink. You need to provide tablespace name and shrink and shrink mode to perform this task.

• TS_MODE_ANALYZE – This mode analyzes the tablespace
• TS_MODE_SHRINK – This mode is default, and it shrinks tablespace to reclaim space
• TS_MODE_SHRINK_FORCE - This mode moves objects online and in case of failure it will attempt to move offline.

You should set “serveroutput” g on to show the results on the terminal
SQL> set serveroutput on;

Analyze how much space can be reclaimed using TS_MODE_ANALYZE mode

SQL> execute DBMS_SPACE.SHRINK_TABLESPACE (‘USER_TBS1’, SHRINK_MODE => DBMS_SPACE.TS_MODE_ANALYZE);
PL/SQL procedure successfully completed.

Here is the simplified version of the output

ANALYZE RESULTS
Total Movable Objects: 2
Total Movable Size(GB): 1.32
Original Datafile Size(GB): 5
Suggested Target Size(GB): 2.59
Process Time: +00 00:00:00.701704

Now you can perform the actual shrink operation to reclaim the space, note that DBMS_SPACE.TS_MODE_SHRINK is used by default if you don’t mention explicitly. If you don’t mention any TARGET_SIZE by default it will use DBMS_SPACE.TS_TARGET_MAX_SHRINK

SQL> execute DBMS_SPACE.SHRINK_TABLESPACE (‘USER_TBS1’);
PL/SQL procedure successfully completed.

Or

SQL> execute DBMS_SPACE.SHRINK_TABLESPACE (‘USER_TBS1’, SHRINK_MODE => DBMS_SPACE.TS_MODE_SHRINK);
PL/SQL procedure successfully completed.

Here is the simplified version of the output

SHRINK RESULTS
Total Moved Objects: 2
Total Moved Size(GB): 1.32
Original Datafile Size(GB): 5
New Datafile Size(GB): 1.44
Process Time: +00 00:00:21.501692
PL/SQL procedure successfully completed.

Note that you may not be able to reduce exact size that you got from analyze.
You can run below query to get the size of the tablespace.

SQL> SELECT tablespace_name, sum(bytes)/1024/1024/1024 as ”Tablespace Size (GB)”
FROM dba_data_files
GROUP BY tablespace_name;

You can run below query to check the segment size and you can add where condition for specific segment name or type

SQL> SELECT segment_name, segment_type, bytes/1024 size_kb
FROM user_segments;

DBMS_SPACE package has below subprograms and used for various operations: -

ASA_RECOMMENDATIONS Function - This function returns recommendations
SPACE_USAGE Procedure - This procedure shows the usage of data blocks under high water mark and auto segment space management
UNUSED_SPACE Procedure - This procedure shows unused space in an object
SHRINK_TABLESPACE Procedure - This procedure used to resize or analyze big file tablespace
OBJECT_DEPENDENT_SEGMENTS Function - This function returns the list of segments associated with an object
OBJECT_GROWTH_TREND Function - This function provides space usage of object at a specific point-in-time
FREE_BLOCKS Procedure - This procedure provides information about free blocks
ISDATAFILEDROPPABLE_NAME Procedure - This procedure checks datafile is droppable
CREATE_INDEX_COST Procedure - This procedure determines cost of creating an index
CREATE_TABLE_COST Procedure - This procedure determines the size of the table

The Shrink Tablespace simplifies the procedure to reclaim the free space from datafiles by organizing objects.

Thanks & Regards,

https://oracleracexpert.com