1z0-082 Oracle Database Administration – Helpful queries to explore Oracle DB architecture

1. oracle data dictionary concept

Hello everyone and welcome in this new chapter. In this chapter we will talk about many things and we will take helpful query that will help you a lot. Now in this lecture we will talk about Oracle data dictionary. I assume that you have a big information about this subject, but I just want to add new information for you. So now the Oracle data dictionary, it is a meta database about the database. Like for example, you need to know how many objects you have, how many tables, how many views, how many indexes. So it is a metadata about the database. The data dictionary is the structures in tables and views. So we have a collections of tables and views is maintained by Oracle database server. So nothing to do. So the Oracle data dictionary maintained by the server and is owned by SIS user should never modify direct using SQL. So don’t try to do any transactions on dictionary tables or views. Now this is the structures for the Oracle dictionary.

I have CDB underscore, DBA underscore, all underscore, user underscore. I assume that you have the most of information about this. But anyway, CDB actually this is all the objects in the container database which is the route across all PDBs. So you can see all the objects in the container and also in the blogable databases. Actually the CDB underscore only releases twelve C and 18 C and in the future releases. Now regarding DBA underscore, all objects in a container or BDB according to the login. And I will show you that in the practice I have all underscore and these are objects accessible by the current user. Now regarding user underscore, all objects owned by current user, I assume that you have a lot of information about this, but maybe CDB underscore it is anew for you.

Okay. Now a very important note, all underscore and user underscore available to any user. So if you create any user, they will have access already in the dictionary that starts with all underscore or user underscore. Now regarding CBD underscore and DBA underscore, it is restricted to database accounts and the user that have SysDB privileges or select any dictionary privilege or they have a rule called select catalog underscore rule. So this is the main important information that I want you to know about Oracle Data dictionary. Now let’s go to the exercises. First thing I have, my virtual machine is up and running. You see, this is my virtual machine. I will log in. So it is up and running and I already connected to Oracle user. You can see here Oracle user. Now what I will do, I will open the SQL developer. This is my SQL developer from the client machine. You can do these exercises from client or you can open the SQL developer from the server, it’s up to you.

But in this exercise I will open my SQL developer which is exist in my client machine. So now this is the SQL developer. I already connected as SIS user. You can see the connection here sys user. So if I go here and I make right click properties, you can see that sys and this is the password. I connect sys as sysdba, the hostname sys. com and this is the port and this is the service name. Remember that my server is up and running and also the database is opened. Okay, very nice. So now let’s do some exercises. So the first command I will make surecon underscore name I will execute this command. You can see now that I am on the CDB underscore root. I am on the root database. Now I will make select conid comma name open mode from video sign PDB. Actually this is a dictionary view that it will show you all the bloggabill database that you have.

So the container ID, the name and the open mode. I will execute this statement and you will find that I have a container two, which is the seed bluggable database and I have container ID three, which is orclpdb and the status is mounted. Now I want to open this bloggle database and this is very important.

In order to see the objects in this subbuggable, we should open it. So I will make alter bloggable database orclpdb open. So I will open this bloggable database. I will execute this command and it will show you now the result that vloggable database orclpdbaltered now I will execute this statement. Select owner comma table name comma conid from CDB underscore tables order by one and two. So actually this query, it will show you all the tables in the container and also in the Blogger bill databases. Why? Because I have here CDB underscores. So this means all the routes and all the blogger databases. I will execute this statement and you can see now this is the result. If you make now count rows you will find a huge number. For example 4273 rows.

Now for example, you can see here two rows for the same table, for the same owner, but one for container ID three and one for container ID one. So this means that this query, it will show you the tables in all the containers. Very nice. Now remember that if I close this blogger database you will not see this number. Why? Because it should be opened now I will prove that for you. I will make count rows, you will find 4273. If I make now close and I execute this comment, you will find that bloggerbill database altered. I will execute this statement again. And if you make now count rows, you can see that 2134. So in order to see the objects, the Blogger bell database should be open. So now it will show you only the tables in the container ID one, which is the root database. So I will open it and I will execute the comment and I will execute this statement again and it’s working fine, 100%. Okay, now let’s do for example another query.

Now I will make count of the tables per container. So, I will make select conid comma count table name from CDB underscore tables group by container ID. I will execute this. And you can see now that the container ID one, which is the row database. I have this count of tables in a blogger database. Three, I have this count of tables. So now we understand the CDB underscore. So we could have CDB underscore views, indexes, whatever. So, this is the concept of CDB underscores. Now let’s continue. Now I will make select count table name from DBA underscore tables. Actually this, it will show you all the tables or all the count of tables in the container database only. Why? Because currently I am on the container database, the root container.

Now, if you make sure name, you will find that I am in the root. So this means that this query will show you only the tables in the root. Okay, I will execute. You will find that I have 2134. Now, what I will do now, I will make alter session set container orclpdb I will execute this statement. I will make now short name. So now I am on the container database or Rclpdb. So now when I make this query, select count table name from DB underscore tables. It will show you all the tables in the container database or the Blogger database which is called orclpdb because currently I am on the Blogger bell database, I will execute this, you can find now 2139.

So remember, so when I make DBA underscore all objects in a container or PDB according to the login that you have. Okay, very nice. Now I will pack again and I will do finally, I will make alter session set container equals CDBE dollar sign root. I will pack again to the root and I will make sure comm name. And you can see now that I am on the root database. So in this lecture we understand what is the meaning of CDB underscore and DBA underscore all underscore user underscore. It’s very simple and I am sure that you have this information. Thank you for listening and see you in the next video.

2. Common uses VS local users part 1

Hello everyone and welcome. In the previous lecture we understand the concept of oracle data dictionary. In this lecture we will talk about very important topic which is common users versus local users. And this is very important in oracle twelve C and above releases. You have to understand this concept very well, okay? So try to understand everything in this lecture. So now first thing, what is the common user? A CDB common user is a database account that is created in the root container and is inherited by all bloggabil database in the container database including future bloggable database. So this means that I could have a user common between all the container and the bloggable database. For example, like sys user and system user. We will understand this more in the practice, don’t worry. Okay. Now a common user cannot have the same as any local user across all the bloggabill databases. So this node is very important, okay? Oracle supplied administrative accounts, such sys user and system user are common user. You can create a common user but you need user defined debriefix. For example c hash hash. For example, you can create a common user called c hash hash arid. Actually the user defined the prefix can be defined in a database parameter called common underscore user underscore prefix and we will see that in the example.

Now, what is the local user? It’s very simple. A local user is a database user created in a specific blogger build database. For example, the HR user in the bloggabel database called orclpdb is a local user. Now, a very important note, you can create same local account and password in more than pluggable database. Now for example, you could see for example a user called HR exists in a bloggable database PDB one. And also you could find HR user exists in a bloggable database two. And also HR user exists in the bloggable database BDB three.

So this is our three separate local users. Okay, very nice. And this concept is very important because maybe you will create a nice application in a specific bloggle database. You create schemas and tables and users inside a blogger database and you create a nice application that depends on this blogger database. So you can clone it and make another bloggle database cloning from the first one and give it to another customer for example. So this is very important. So we can create same users in the blogger bill databases. Okay, very nice. So in the next lecture we will take some practice to understand more the common users and local users. Thank you for listening and see you in the next video.

3. Common uses VS local users part 2

Hello everyone. In the previous lecture we took an overview to understand what is the common user and the local user. Today we will do some practice and you will understand the concept very well. So first thing I log in in my virtual machine as Oracle user and the database is up and running. I opened locally the SQL developer and I connected sys as sysdba. Now I will execute the following statements. First thing I will make sure on name I will execute. You can see now I am in the root database. Now I will execute the following statement select username comma common comma container ID from CDB underscore users order by username. So I have a dictionary table called CDB underscore users. This will show you all the users in the container and debugable database.

This column is the username. This column to tell you if this user is common or no. And I have the container ID. I will execute this statement and you can see this is the result. For example, I have a user name called animus and it is a common. Why? Because it is exist in container three and one. For example, I have a user called CIS and the Sys user also is a common user exists in the container three and container one. Okay, very nice. Now let’s see the user HR. Actually the user HR is not a common user, it is a local user and exists only in the container ID three, which is the orclpdb that’s it. It’s very simple. So we have a common user. These common users are shared between all the bloggle database. Even if you create a new blogger databases in the future, also it will be a common user. Okay, in the next query we will see the username that are common.

So I will make select distinct username from CDB underscore users where common equal yes, I will execute and you can see this is the result. I will make count rows. You can see that I have 35 rows. Now in the next query I will make select username comma common comma container ID from CDB underscore users where common equal no order by username. So in this query I will show all the local users in all the blogger bill databases. I will execute this and you can see that I have a user called HR in container number three and I have a user called PD admin in container number three. So I have only two local users which exist in the container ID three. Okay, very nice. Now in the previous lecture I told you that you can create a common user, but you need a user defined debriefix like c hash hash. So how we can know that? It’s very simple.

I will go to the SQL developer and I will execute this command show parameter common underscore user underscore prefix. I will execute this command and it will show you that I have a database parameter called common and the score user and the score prefix. And the value should be c hash. This means if you want to create any common user, it should start by c hash. Now we will understand a lot of details about the parameters, but not now we will have a full chapter talking about the parameters. Okay, very nice. So now if I try to make create user tone identify by tone it will give me error. Why? Because if I want to create because if I want to create a common user I should be on the container and I should use the prefix which is c hash.

I will execute this command and you can see now invalid common user or role name. So I will make now create usercash hash tone identify by welcome. So in this case it will not give me any error. I will execute. You can see now usercash hash tone created. Now if I make now select distinct username from CDB and the score users where common equal. Yes. I will find this user as a common user. I will execute. And if you can go here, you will find that I have a username called CT one and it is a common user. Why? Because the word condition was common equal. Yes. Now we will understand what is the local user. It’s very simple. Now I will make alter session set container equal orclpdb I will move to the blogger able database called orclpdb. I will execute this session altered. I will show conname. You can see orclpdb. And now if I make now create user had it identified by had it. So this user is a local user only for this debugger bill which is rclpdb, I will execute this user had created.

And now I approve that for you. I will make now select username common ID from CDB underscore users where common equal no order by username. I will execute this. And you can see now that I have a user called and this user is not common and exist in the bloggable database three. So in this lecture we understand what is the meaning of common user and what is the meaning of local user. Thank you for listening and see you in the next video.