Fixing ORA-00942 and ORA-65096 in Oracle with .NET: A Complete Step-by-Step Solution
Introduction
If you are working with Oracle Database 12c or above and trying to connect it with your .NET Core or ASP.NET project, you might face errors like ORA-00942: table or view does not exist or ORA-65096: invalid common user or role name. These are very common when working with the multitenant architecture introduced in Oracle 12c, which includes Container Databases (CDBs) and Pluggable Databases (PDBs).
In this article, we’ll go step-by-step through how this issue happens, how we diagnosed it, and how we fixed it completely. All connection strings, SQL commands, and examples use demo credentials.
1. The Initial Error in C#
Oracle.ManagedDataAccess.Client.OracleException: ORA-00942: table or view does not exist at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()
The error appeared even though the connection was successful and the same query worked perfectly in SQL Developer. The C# code looked like this:
public List<Dictionary<string, object>> ExecuteSelect(string query)
{
var results = new List<Dictionary<string, object>>();
using (var conn = new OracleConnection(_connectionString))
{
conn.Open();
using (var cmd = new OracleCommand("SELECT * FROM COUNTRY_MASTER", conn))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var row = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
row[reader.GetName(i)] = reader.IsDBNull(i) ? null : reader.GetValue(i);
results.Add(row);
}
}
}
return results;
}
So why did it fail in C# but work in SQL Developer? The answer lies in Oracle’s schema management and connection architecture.
2. Understanding the Root Cause
Oracle Database 12c introduced the concept of multitenant architecture — a single container database (CDB) that can contain multiple pluggable databases (PDBs).
- CDB (Container Database): The root database that stores metadata and system information.
- PDB (Pluggable Database): User databases where application data actually resides.
If you connect to CDB$ROOT and try to create a user like:
CREATE USER FFI_USER IDENTIFIED BY ffi123;
you’ll get this error:
ORA-65096: invalid common user or role name
because normal users can only be created inside a PDB, not the root container.
3. Checking Current Container
We checked which container we were connected to:
SHOW CON_NAME;
Output:
CON_NAME --------- CDB$ROOT
That confirmed the issue — we were inside the root container, not the PDB.
4. Checking Available Pluggable Databases
Next, we listed available PDBs:
SHOW PDBS;
Output:
CON_ID CON_NAME OPEN MODE RESTRICTED ------ ---------- ---------- ---------- 2 PDB$SEED READ ONLY NO 3 XEPDB1 READ WRITE NO
Oracle XE automatically includes a default pluggable database named XEPDB1. That’s the database we needed to use.
5. Connecting to the Correct PDB (XEPDB1)
In SQL Developer, we created a new connection using:
- Username: SYSTEM
- Password: systemdemo123
- Service Name: XEPDB1
- Host: localhost
- Port: 1521
After connecting, we ran again:
SHOW CON_NAME;
Output:
CON_NAME --------- XEPDB1
Now we were in the right container!
6. Creating a Dedicated User for the Application
Once connected to XEPDB1 as SYSTEM, we created a separate user schema for the .NET app:
CREATE USER FFI_USER IDENTIFIED BY ffi123 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS; GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE SYNONYM, CREATE PROCEDURE, CREATE TRIGGER TO FFI_USER; GRANT CONNECT, RESOURCE TO FFI_USER; GRANT UNLIMITED TABLESPACE TO FFI_USER;
✅ The user was created successfully — no ORA-65096 error anymore.
7. Verifying the User
We confirmed the user was created and active:
SELECT username, account_status FROM dba_users WHERE username = 'FFI_USER';
Output:
USERNAME ACCOUNT_STATUS ---------- ---------------- FFI_USER OPEN
8. Updating the Connection String in .NET
In appsettings.json, we used the following connection string:
{
"ConnectionStrings": {
"OrclConnection": "User Id=FFI_USER;Password=ffi123;
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=XEPDB1)));"
}
}
Now our .NET application connected directly to the XEPDB1 PDB under the FFI_USER schema.
9. Root Causes and Solutions Summary
| Error | Cause | Solution |
|---|---|---|
| ORA-00942: table or view does not exist | Connected user didn’t have privileges or wasn’t in the right schema. | Use schema-qualified names or create user inside correct PDB. |
| ORA-65096: invalid common user or role name | Tried creating a user in CDB$ROOT instead of a PDB. | Connect to XEPDB1 and create user there. |
| ORA-65011: Pluggable database does not exist | Specified a PDB that doesn’t exist. | Use SHOW PDBS to check available PDBs. |
10. Verification in C#
Final working code in C#:
using (var conn = new OracleConnection(_connectionString))
{
conn.Open();
Console.WriteLine("Connected to: " + conn.DataSource);
using (var cmd = new OracleCommand("SELECT * FROM COUNTRY_MASTER", conn))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["COUNTRY_NAME"]);
}
}
}
Output:
Connected to: XEPDB1 INDIA UNITED STATES ...
11. Lessons Learned
- Always verify your current container using
SHOW CON_NAME. - Create users inside your PDB, not the root container.
- Use a dedicated schema for your application instead of SYSTEM.
- For Oracle XE, XEPDB1 is the default PDB.
Conclusion
This guide demonstrated how to fix both ORA-00942 and ORA-65096 errors by understanding Oracle’s container architecture and properly configuring the user and connection string.
By ensuring your .NET app connects to the correct PDB and uses a dedicated schema, you can avoid permission issues and achieve a clean, secure, and stable Oracle connection.
Demo Credentials Used: SYSTEM/systemdemo123, FFI_USER/ffi123 (for example only).