Database Software

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 !

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.

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

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.

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.

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

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

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.