跳到正文
DEV Community· Abdulrawof Totakhil·· 20 小時前AI 評分24

為甚麼 Oracle Database 19c 用戶會在查詢中消失:瞭解 CDB_USERS 與 DBA_USERS

Why Oracle Database Users Disappear: Understanding CDB_USERS and DBA_USERS in Oracle 19c

AI 導讀

在 Oracle Database 19c 多租戶架構中,連線至 CDB$ROOT 查詢 DBA_USERS 時,位於 PDB 的本地用戶可能不會顯示,這不代表帳戶已被刪除或未建立。DBA 可先確認目前容器及可用 PDB,再從 root 查詢 CDB_USERS 的 CON_ID、COMMON 和帳戶狀態,以定位用戶;結果亦受字典檢視權限及 CONTAINER_DATA 設定影響。

正文

A Practical DBA Troubleshooting Guide for Local Users, Common Users, CDBs and PDBs

Technology: Oracle Database 19c

Environment: Oracle Multitenant Architecture

Technical Focus: Database User Management, SQL, Troubleshooting and Security


When you manage Oracle Database 19c, you might see that a database user does not appear when you query DBA_USERS.

At first, this result might make you think the database user is missing.

In Oracle multitenant architecture, this outcome does not always mean the user was deleted or never created.

The account could live inside a Pluggable Database (PDB), while you are connected to the Container Database root, CDB$ROOT.

Knowing this difference matters for database administration, application access control, troubleshooting and security.

1. Understanding Oracle Multitenant Architecture

Oracle Database 19c runs an architecture.

In this design, a Container Database (CDB) can hold Pluggable Databases (PDBs).


Oracle Database 19c

+-- CDB$ROOT

|   |

|   +-- Common users and CDB administration

|

+-- PDB$SEED

|   |

|   +-- Template for creating PDBs

|

+-- LABPDB

|

+-- Application schemas

+-- Local database users

+-- Application tables

+-- Database objects

CDB$ROOT is the main container of the CDB.

CDB$ROOT stores Oracle system. Handles database-wide administration tasks.

A Pluggable Database is a database container that holds application schemas, user accounts and data.

Each PDB can keep its local user accounts.

PDB$SEED is a system template used when you create PDBs.

PDB$SEED is usually read-only.

A database session works inside a current container.

The container you are connected to determines which database users, schemas and objects you can see.

If you are connected to the CDB root, do not assume that querying DBA_USERS will show all users in every PDB.

2. Understanding Common Users and Local Users

Oracle setups separate common users from local users.

A common user can live in containers within its defined scope.

In a CDB, common users usually get the C## prefix unless you change the naming rule.

Common users can get privileges that apply across containers, based on how the privileges were granted.

A common user does not automatically own all database objects.

A local user belongs to one PDB.

For example, the user LAB_READER may exist in LABPDB but not in CDB$ROOT.

You can. Manage a local user inside its PDB, if you have the right admin rights.

Oracle gives you a COMMON column in its user dictionary views.

Common | Meaning |

|--------|---------|

| YES Common user |

| NO Local user |

For example:


SELECT username,

common,

account_status

FROM dba_users

WHERE username = 'LAB_READER';

If you run the query in the right PDB, a local user will normally have:


COMMON = NO

3. The Problem: User Not Found in DBA_USERS

Consider a DBA checking whether an application account exists.

The DBA connects to the CDB root and executes:


SELECT username,

account_status

FROM dba_users

WHERE 'LAB_READER';

Illustrative result:


no rows selected

Conclusion should not be made immediately that the user has been deleted.

The database session may be connected to the container.

The next step should be to confirm where the current session is connected.

4. Step 1. Identify the Current Container

Oracle SQL*Plus provides a command to identify the current container.


SHOW CON_NAME;

Example output:


CON_NAME

------------------------------

CDB$ROOT

Result indicates that the current session is connected to the CDB root.

Alternatively, use:


SELECT SYS_CONTEXT(

'USERENV',

'CON_NAME'

) AS current_container

FROM dual;

Query can be useful in tools that do not support SQL*Plus SHOW commands.

Why should DBAs check this first?

Because a query may return information depending on the current container.

Before troubleshooting missing users or objects, confirm the current container.

Simple step can prevent account recreation, privilege changes and confusion.

5. Step 2. Check Available PDBs

From an administrative session in the CDB root, execute:


SHOW PDBS;

Displays the PDBs known to the database, together with their open mode.

A SQL alternative is:


SELECT con_id,

name,

open_mode

FROM v$containers

ORDER BY con_id;

output:


CON_ID  NAME       OPEN_MODE

------  --------   ----------

1       CDB$ROOT   READ WRITE

2       PDB$SEED   READ ONLY

3       LABPDB     READ WRITE

The CON_ID column identifies the database container.

Typical identifiers are:

  • 1: CDB root

  • 2: PDB seed

  • 3 and higher: PDBs and supported container types

An important point is that the container ID must be obtained from the database rather than assumed.

For example, LABPDB is shown as container 3 here for demonstration.

Your environment may use another container name or ID.

6. Step 3. Locate the User Using CDB_USERS

The dictionary view is useful for investigating database users across containers.

From the CDB root, a privileged session can execute:


SELECT con_id,

username,

common,

account_status

FROM cdb_users

WHERE username = 'LAB_READER'

ORDER BY con_id;

Illustrative result:


CON_ID  USERNAME     COMMON  ACCOUNT_STATUS

------  -----------  ------  --------------

3       LAB_READER   NO      OPEN

What does this result mean?

Result indicates that:

  1. The account exists in container ID 3.

  2. It is an user (COMMON = NO).

  3. Its account status is OPEN.

Result demonstrates why the account did not appear in the earlier root-level DBA_USERS query.

The account was associated with a PDB than the root.

security consideration

Oracle restricts access to data dictionary views.

A common user querying CDB_USERS from the root may see the containers allowed by its privileges and CONTAINER_DATA configuration.

Therefore, a missing row in CDB_USERS is not always proof that a user does not exist.

If the expected account's absent, verify:

  • The database sessions current container.

  • Dictionary query privileges.

  • Applicable container-data visibility.

  • Whether the relevant PDB is open and accessible.

  • Whether the query uses the username.

For this reason, it is helpful to perform troubleshooting using an authorized administrative account.

Do not grant privileges simply to make a troubleshooting query succeed.

7. Step 4. Verify the User Inside Its PDB

After you have found the PDB, connect directly to that PDB or change to it with an account that has the needed privileges.

Example:


ALTER SESSION SET CONTAINER = LABPDB;

This change to the session is administrative. Needs the right privileges.

Confirm the session:


SHOW CON_NAME;

output:


CON_NAME

------------------------------

LABPDB

Now execute:


SELECT username,

account_status,

common

FROM dba_users

WHERE username = 'LAB_READER';

Expected example output:


USERNAME     ACCOUNT_STATUS  COMMON

-----------  --------------  ------

LAB_READER   OPEN            NO

The user becomes visible because the session is now connected to the PDB that holds the account.

lesson

DBA_USERS is not malfunctioning.

The query result depends on the container.

When fixing user accounts in Oracle 19c, always check if the account is local to a PDB.

8. Reproducible Laboratory Demonstration

This example lets administrators see how things work in a test environment.

Warning: This section has a command that changes the database. Use it in a lab that is meant for testing. Do not create accounts or change containers in production without the approval.

Step 1. Connect to the lab PDB

With an admin session, switch to the lab PDB:


ALTER SESSION SET CONTAINER = LABPDB;

Verify the current container:


SHOW CON_NAME;

Step 2. Create a schema‑only user

To demo without storing a password in the article, create a schema-only account:


CREATE USER LAB_READER NO AUTHENTICATION;

A schema- account does not have a normal password and cannot log in by itself.

The schema‑only account is useful because we want to show user metadata, not test authentication.

The USER command requires the appropriate administrative privilege.

Step 3. Verify the account in the PDB


SELECT username,

account_status,

authentication_type,

common

FROM dba_users

WHERE 'LAB_READER';

Check that the account appears.

For a schema- account, the AUTHENTICATION_TYPE column should identify NONE.

The account should also be marked as local.

Step 4. Switch to the root


ALTER SESSION SET CONTAINER = CDB$ROOT;

Step 5. Query DBA_USERS


SELECT username,

account_status

FROM dba_users

WHERE 'LAB_READER';

For this local-only account, the expected result in the root is:


no rows selected

Step 6. Query CDB_USERS


SELECT con_id,

username,

common

FROM cdb_users

WHERE username = 'LAB_READER';

With sufficient dictionary access and container visibility, this query should identify the PDB that holds the account.

The exact output depends on the environment, user privileges and container configuration.

Step 7. Record the result

Capture the SQL outputs. Compare the two views.

The difference demonstrates the importance of understanding container context in an environment.

Any cleanup, such as dropping the laboratory account, should be performed after confirming that it is the intended disposable test account and that cleanup is authorized.

9. DBA_USERS vs CDB_USERS. Key Differences

The following table summarizes the important differences.

| Feature DBA_USERS | CDB_USERS |

|---|---|---|

| User information | Current container | multiple containers |

| CON_ID column | No | Yes |

Local users in other PDBs | Not displayed | Visible when permitted |

| Current container matters | Yes | Yes |

Dictionary privileges required | Yes | Yes |

| Suitable for Container‑level account checks | CDB‑wide account investigation |

When should DBA_USERS be used?

Use DBA_USERS to check account information inside the container that is currently connected to your database session.

Examples include:

  • Confirm whether a user exists in a PDB using DBA_USERS.

  • Check an accounts status with DBA_USERS.

  • Review authentication configuration via DBA_USERS.

  • Investigate local application accounts using DBA_USERS.

When should CDB_USERS be used?

Use CDB_USERS to identify user accounts across containers from the CDB root when the session has the visibility.

Examples include:

  • Find which PDB contains an username using CDB_USERS.

  • Compare account metadata across PDBs with CDB_USERS.

  • Review common users through CDB_USERS.

  • Support multitenant account inventory work with CDB_USERS.

10. Other Reasons a Database User Might Not Appear

Container context is a cause of missing-user confusion, but it is not the only possible explanation.

Incorrect username spelling

Oracle normally stores user identifiers in uppercase.

For example:


CREATE USER lab_reader NO AUTHENTICATION;

The name is generally stored as:


LAB_READER

A search that uses lowercase text may return no rows if the comparison is case-sensitive.

A diagnostic query can use:


SELECT username

FROM dba_users

WHERE UPPER(username) LIKE '%LAB%';

Be careful with quoted, case- user identifiers because applying UPPER() is not equivalent to an exact search.

Connected to the PDB service

A client may connect using an incorrect service name.

This can place the session in a PDB from the intended one.

Verify the container after connecting.

Account. Is locked

A locked account still exists in the data dictionary.

To review its status:


SELECT username,

account_status

FROM dba_users

WHERE 'LAB_READER';

An account marked LOCKED or EXPIRED is not the same as a missing account.

Avoid unlocking or changing authentication settings until the access requirement has been reviewed and approved.

dictionary privileges

A user may not have permission to query DBA_USERS or CDB_USERS.

The database may raise a privilege‑related error than displaying the expected information.

The solution is to use an account or request appropriately scoped dictionary access.

Broad roles such as DBA should not be granted merely to run a query.

Restricted container visibility

when a user can query CDB_USERS, the result may not contain rows from every PDB.

Oracles container-data security rules influence visibility.

A complete investigation may require an administrator to review those settings.

11. Security and Banking Database Considerations

Correctly identifying a users container is especially important in enterprise environments.

Database accounts should be managed according to the principle of privilege.

This means granting users the access necessary for their responsibilities.

In an environment, account reviews should also consider whether the privilege is local or common.

Some practical recommendations include:

  1. Identify the PDB before making account changes.

  2. Verify the accounts existence and status before recreating it.

  3. Avoid granting roles or system privileges.

  4. Confirm whether required privileges apply locally or across containers.

  5. Review account changes through authorized change‑management processes.

  6. Maintain audit records for administrative activity.

  7. Avoid sharing database usernames, credentials, connection details or infrastructure identifiers publicly.

When preparing technical articles, use fictional account names and laboratory environments.

The objective is to explain Oracle behavior without disclosing operational information.

12. SQL Troubleshooting Checklist

The following read‑only checks provide a starting point for investigating missing user accounts.

Check the container


SELECT SYS_CONTEXT(

'USERENV',

'CON_NAME'

) AS current_container

FROM dual;

Check whether the target account exists in the container


SELECT username,

account_status,

common

FROM dba_users

WHERE username = 'LAB_READER';

Search across containers

Run from the root with the required privileges:


SELECT con_id,

username,

Here is the text that the user provided:


account_status,

FROM cdb_users

WHERE username = 'LAB_READER'

ORDER BY con_id;


Map container IDs to container names


SELECT con_id,

name,

open_mode

FROM v$containers

ORDER BY con_id;

Confirm the session user


SELECT SYS_CONTEXT(

'USERENV',

'SESSION_USER'

) AS session_user

FROM dual;

Together, these checks together help figure out where the session is connected, whether the account is visible and which container should be examined next.

13. Suggested Original Screenshots

To give technical proof, a practical article should show screenshots from real Oracle SQL execution.

Screenshot 1. Current container

Display the result of SHOW CON_NAME or the equivalent SQL query.

Screenshot 2. Root DBA_USERS result

Display the result of searching for the account from CDB$ROOT.

Screenshot 3. CDB_USERS result

Display the account with its CON_ID value.

Screenshot 4. PDB DBA_USERS result

Display the local account found from the relevant PDB.

Screenshot 5. Comparison

If useful, include a concise table explaining the different results.

All screenshots should be laboratory execution evidence. Example outputs printed in this guide are illustrative. Are not substitutes for original screenshots.

14. Lessons Learned

This type of issue shows useful Oracle DBA principles.

First, always check the container.

A database user may exist in a PDB when a query against DBA_USERS in the root returns no rows.

Second, understand the purpose of each view.

DBA_USERS is useful for the container, while CDB_USERS supports container‑aware investigations from the root.

Third, understand common users.

A local account does not automatically exist in every PDB or in the root.

Fourth, verify visibility and privileges.

Access to dictionary information is controlled by Oracle security rules.

Fifth, avoid account changes.

Before creating a replacement user or granting privileges, establish whether the original user already exists in another container.

These principles can reduce troubleshooting errors. Support more consistent database administration.

Oracle Database 19c multitenant architecture introduces a distinction between database-wide administration and container‑specific user management.

When a database account appears to be missing, the first troubleshooting step should be to identify the container.

A local user may not appear in DBA_USERS when the DBA is connected to CDB$ROOT, even though the account exists in a PDB.

With privileges and container visibility, CDB_USERS can help identify the container associated with that account.

The main takeaway is simple:

Before concluding that an Oracle Database user does not exist, verify the container and investigate the account using the appropriate data dictionary views.

This approach supports troubleshooting, effective account administration and safer management of Oracle multitenant databases.

Official Oracle Documentation

1. Oracle Database 19c. Multitenant Architecture Overview

https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/overview-of-the-multitenant-architecture.html

2. Oracle Database 19c. Creating User Accounts

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/creating-user-accounts.html

3. Oracle Database 19c. CDB Views

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/cdb_-views.html

4. Oracle Database 19c. DBA_USERS Reference

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_USERS.html

5. Oracle Database 19c. Monitoring CDBs and PDBs

https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/viewing-information-about-cdbs-and-pdbs-with-sql-plus.html

6. Oracle Database 19c. Schema- Accounts

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/schema-only-accounts.html

Technical reference prepared with AI assistance. The laboratory outputs shown are illustrative unless separately documented by execution evidence. This document should not be represented as authored content for a program that prohibits AI‑generated submissions.

來源:DEV Community · dev.to