Creating User Profiles in a Pluggable Oracle Database

Описание: Learn how to create user profiles in a Pluggable Oracle Database and resolve common errors like ORA-65040 and ORA-28014 with our comprehensive guide.
---
This video is based on the question https://stackoverflow.com/q/62750882/ asked by the user 'Nitin Singhal' ( https://stackoverflow.com/u/11896664/ ) and on the answer https://stackoverflow.com/a/62751213/ provided by the user 'Roberto Hernandez' ( https://stackoverflow.com/u/13755538/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to create user profile in pluggable oracle db

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

If you're working with Oracle databases, specifically in a pluggable architecture, you might encounter some challenges when it comes to creating user profiles and managing users. One such issue arises when you attempt to create a user profile in a pluggable database, only to be met with the error ORA-65040: operation not allowed from within a pluggable database. In this post, we'll explore how to properly create a user profile within a pluggable Oracle database and resolve issues related to user management.

Understanding the Problem

When working with Oracle's multitenant architecture, it’s essential to understand the different types of databases involved:

Container Database (CDB): This is the main database that contains pluggable databases.

Pluggable Database (PDB): Individual databases that share the resources of the CDB but maintain their own data and settings.

The error ORA-65040 typically indicates an attempt to perform an operation that isn't allowed within the context of a pluggable database. For example, trying to create profiles directly within a PDB can lead to complications.

Your Query

You attempted to create a profile using the following command:

[[See Video to Reveal this Text or Code Snippet]]

This resulted in the ORA-65040 error, causing frustration. Additionally, when trying to drop a user pp that was created with the profile, you encountered another issue:

[[See Video to Reveal this Text or Code Snippet]]

This led to the error ORA-28014: cannot drop administrative users.

The Solution

To successfully create user profiles and manage users in a pluggable Oracle database, follow these steps:

1. Creating a User Profile in a Container Database

To create a profile that applies to all pluggable databases within the container, use the following command while connected to the CDB:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Parameters:

PASSWORD_REUSE_MAX: Sets the maximum number of times a password can be reused.

PASSWORD_REUSE_TIME: Defines a time period during which the password cannot be reused.

2. Creating a User Profile for the Current PDB Only

If you want to create a profile that is limited to just one pluggable database, ensure you are in that specific PDB and run:

[[See Video to Reveal this Text or Code Snippet]]

Important Note:

Ensure you're connected to the correct container. Use ALTER SESSION SET CONTAINER = your_pdb_name; to switch.

3. Dropping an Administrative User

To drop a user like pp, you need administrative permissions. You can accomplish this by altering your session settings:

[[See Video to Reveal this Text or Code Snippet]]

Warning:

Be cautious when dropping users, especially administrative ones, as they may be essential for Oracle's internal processes or applications within your pluggable database.

Conclusion

Working with user profiles in a pluggable Oracle database can be straightforward if you understand the context and commands required. By following the steps above, you'll overcome the common errors associated with user management within Oracle's multitenant architecture. Always remember to perform these operations with careful consideration to avoid disrupting essential components of your database.

For further reading, refer to Oracle’s documentation or additional guides on managing users and profiles in Oracle databases.