Forum Stats

  • 3,853,726 Users
  • 2,264,259 Discussions
  • 7,905,436 Comments

Discussions

Common user in Oracle 12c

User_N25PC
User_N25PC Member Posts: 34 Employee
edited Nov 10, 2017 3:12AM in Multitenant

Hi,

I would like about the recommendation ( or maybe even rule ) that says that common user should not own any objects ( tables for example ).

My question is :

Can common user create objects inside the root container. ? ( is that not possible or not recommended )

The scenario would be something like this :

There are more PDBs, and there is a need for some extra data, something like summary for all PDBs, to be stored somewhere outside any particular PDB.

For that purpose, I would create a common user ( because that one has to have access to all PDBs ), which will then create some tables and put that data there. But, if that can not be the root container, then, looks like I have to create another PDB just for that purpose.

Or there is some better way for doing this ?

thx

User_N25PCEmad Al-Mousa

Best Answer

Answers

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Nov 9, 2017 10:14AM Answer ✓

    If I might be so bold to suggest leveraging a new feature in 12.2 call Multitenant Application Containers. https://oracle-base.com/articles/12c/multitenant-application-containers-12cr2

    With App Containers, you can create the objects you want and then have PDBs built off of that app container inheriting those objects. This seems to be what you are seeking.

    I make it a personal policy to create as few common users as possible and to not store any application objects in the CDB.

    Cheers,
    Brian

    User_N25PCUser_N25PC
  • User_N25PC
    User_N25PC Member Posts: 34 Employee
    edited Nov 9, 2017 10:25AM

    thx for the link and the suggestion...I have first to read and then to say is that what I need.....will come back..thx !

  • Unknown
    edited Nov 9, 2017 11:47AM
    I would like about the recommendation ( or maybe even rule ) that says that common user should not own any objects ( tables for example ).

    What 'recommendation' are you referring to? Post a link to an OFFICIAL doc that supports your statement.

    My question is :Can common user create objects inside the root container. ? ( is that not possible or not recommended )

    My answer is: what happened when you tried it?

    Often the BEST and FASTEST way to get an answer to a 'what happens if ...' question is TRY IT and see what happens.

    The root/CDB container should be used to manage the database and the PDBs.

    If you have a need to store overall management data the root/CDB is certainly the place to store it.

  • User_N25PC
    User_N25PC Member Posts: 34 Employee
    edited Nov 9, 2017 4:03PM

    https://docs.oracle.com/database/121/CNCPT/cdblogic.htm#CNCPT89250

    says "The root does not store user data. Thus, you must not add user data to the root or modify system-supplied schemas in the root. However, you can create common users and roles for database administration..."

    Managing common user in oracle 12c

    says "a common user may have a schema in each container. But in practice, the common user will probably not own any objects"

    there are also some also some other papers.....but, the point is that I am not sure have I understood them correctly...and that is why I ask for the clarification.

    - is it OK ( good practice )  that common user has own some db objects ( tables ) and is it ok that he keep them in the root container ( table could be with million of rows )  ?

    - or maybe the common user is not the right way to do what I need , but there is some better way ?

    ( to make it clear.....I need one user that has access to all PDBs and which wants to store some business data that do not belong to any particular PDB, but are covering all of them and will be then used by this user who created them  )

    thx

  • Unknown
    edited Nov 9, 2017 4:50PM
    https://docs.oracle.com/database/121/CNCPT/cdblogic.htm#CNCPT89250says "The root does not store user data. Thus, you must not add user data to the root or modify system-supplied schemas in the root.

    Correct - do NOT store 'user data' in the root container. Store it in the PDB where that user is.

    Meaning - don't try to create data in the root that will be common/shared between PDBs.

    However, you can create common users and roles for database administration..."Managing common user in oracle 12c 

    Correct - those 'common users' will have schemas in the root container. Some common users will be used to help manage one or more PDBs and some will be used to manage the CDB/root as a whole.

    As part of that 'database administration' you may want to create tables in the common user schema in the root. That is NOT 'user data' and should not be used as such.

    Rather it is, as you first suggested, 'management data' related to the overall CDB/root.

    Managing common user in oracle 12c says "a common user may have a schema in each container. But in practice, the common user will probably not own any objects"

    That is NOT an Oracle doc - it is an opinion shared by a forum volunteer in response to another thread question.

    - is it OK ( good practice ) that common user has own some db objects ( tables ) and is it ok that he keep them in the root container ( table could be with million of rows ) ?

    As the doc said the root is for managing the CDB. It should be used ONLY for that purpose.

    It should NOT be used as a data repository - why would you have a table with millions of rows?

    If you need to store/analyze/manipulate data then create a PDB and do it there. In 12.1 create the minimum tables/objects necessary in a common root schema to gather the management data you need to collect. Then if you want to store that data for further use create a batch job to migrate it to a management PDB created for that purpose.

    So MY rule of thumb (meaning my opinion)

    1. Don't create UNNECESSARY objects in the root in ANY common user schema

    2. Don't use the root to store/manipulate USER data from PDBs

    3. Don't accumulate historical management data in the root - gather your daily management data in the root but migrate that data to a management PDB for any non-immediate or analytical needs.

    ( to make it clear.....I need one user that has access to all PDBs and which wants to store some business data that do not belong to any particular PDB, but are covering all of them and will be then used by this user who created them )

    What 'business data' are you talking about?

    I would consider business data to be user data related to what the PDBs are doing. If so it should be stored in a PDB.

    Once again - the root should be used ONLY to assist in managing the CDB on a day to day basis. Any use other than that should be done in a PDB.

    User_N25PCUser_N25PC
  • User_N25PC
    User_N25PC Member Posts: 34 Employee
    edited Nov 9, 2017 5:01PM

    when I said 'business data' , I meant some kind of management data, that this common user, which is in the same time some kind of the supervisor for all PDBs inside, create and view. But, I understood that you suggest then another PDB  , for management purpose, should be created and that data should be then stored there..which sounds fine for me  ( there is no benefit for me to keep them under the root container ).....just wanted to check the options.

    thx

  • Unknown
    edited Nov 9, 2017 6:08PM

    As another responder said you really should move to 12.2 and use application containers for that.

    That is one of the objectives for Oracle introducing app containers - to allow users to group/manage a SET of PDBs as an entity but without having to use the root container to do it.

    With 12.2 you really shouldn't need the root container for much of anything except global stuff.

    When it comes to new functionality as extensive as app containers there is really no substitute for creating it in a sandbox and actually working with the examples in the docs.

    Lots of good stuff in 12.2

    User_N25PCUser_N25PC
  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy
    edited Nov 10, 2017 2:04AM

    Hi,

    Yes, sure you can create objects in the CDB$ROOT schema of a common user. Usually, common users are for the system (for administration, audit, monitoring,...) and you may need few tables for them. An example is Statspack.

    As mentioned by Brian, if those common objects are related with the application deployed in multiple PDBs, then better to use an application root in 12cR2. You have lot of possibilities with application container even to access data from other containers.

    Regards,

    Franck.

    User_N25PCUser_N25PCEmad Al-Mousa
This discussion has been closed.