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

4. Understanding Dynamic Performance Views

Hello everyone and welcome. In this lecture we will understand the concept of dynamic performance views and many people they name it V dollar sign views because it starts by the dollar sign. The purpose of this lecture is only to understand the concept. We have thousands and thousands of video sign views, but at least we should understand what is the meaning of video sign views are a set of underlying views that are maintained by the server and accessible to the database administrator user says so actually this means that it is the same that dictionary table until now. But we have some differences and major differences are called dynamic performance views because they are continuously updated while a database is open and in use. So these views collect some information and this information are dynamic. It is always changing and we will understand this. In real example, their contents related primarily to performance. So most of these views are related to performance.

Dynamic performance views are identified by the prefix v underscore dollar sign. But we have a public synonyms for these views that have the prefix v dollar sign. So we use always the dollar sign which is public synonyms. Okay, now the dynamic performance views are used by Enterprise Manager and Oracle Trace which is the primary interface for accessing information about system performance. We will talk in details about Enterprise Manager. And this is a very nice tool in order to see details about the database. It is a very powerful tool for the database administrator. And finally, this is the most important note for the dynamic performance views. Once the innocence is started, the vdlar sign of views that read from memory are accessible. This means that I have specific views that read from memory. Views that are read data from disk require that the database be mounted. So actually this means that I have two types of video signed views.

Some of them they read from memory, some of them they need from the disk. So this means that the database should be opened or mounted. Very nice. Now let’s go to the SQL developer and do very nice exercises. First thing, my virtual machine is up and running and my database is up and running. So now I’m using my SQL developer. In my client machine I am connected as Sys user. I will make sure good name. You will find that I am on the root. I will open the database orclpdb alter the bluggable database orclpdb open. Now you will find that bloggle database already open. Okay, no problem. Now I will alter the session and I will move to this bloggable database. So alter session set container equal orclpdb I will execute this and you can see session altered. I will make sure code name to double check. So currently I am on the bloggle database orclpdb okay, now I will write this query. Actually this query is very nice.

A query. I have a video sign view called looked underscore object. In this view, I can see if I have a tables or objects that have a look. Very nice. I will make join between this and DBA objects in order to bring the object name, because this view doesn’t contain the object name. So I make join with DBA underscore objects where the object ID equal object ID. So the query will be select oracle underscore username OS underscore username locked mod object name object type from the dollar sign locked underscore objects DBA objects where the object ID equal object ID I will execute this. And you can see now that currently I don’t have any table that have look. Very nice.

Now I will make a case for a look. And you will see now that the data will be now exist in this dynamic view. Now I will make select star from HR employees. So I have a table in a schema called H R called Employees. I will execute this and you can see this is the data. I have many employees. For example, I have employees 100. His name is Stephen King and the salary is 24,000. Now I will make updated statement. Update employees set salary equal salary plus one where employees equal 100. I will execute this. You will find now one row updated. So currently this row is looked. So no other user can make any DML statement on this row. Why? Because the current session or this session, it is look this row so no one can touch it. Okay, very nice. So this is a look. Now, if I make the same query again, you will find now that I have data in this dynamic view. So I will execute. You can see that I have a user called Sys make a look on a table called Employees.

And this is a table. And we have lock mode three. This means that this is a partial look. This means that not all the table looked. We have specific rows that have a lock. Okay, very nice. So we understand now that the data inside the video that signed views will be changed always. Now I will make rollback. So I don’t want to execute the update statement. I will make rollback. I will execute roll complete. Now, if I make this query again, I will find no data. This means that I don’t have any tables that have looked. I will execute and you will find that I don’t have any data. So this is the concept of Vdolar sign views. It contain data and this data always changes. Okay, very nice. So don’t try to go deep. Now I just want you to understand the concept. Thank you for listening and see you in the next video.

5. cdb_ & dba_ when you are inside a pluggable

Hello everyone and welcome back. In this lecture we will understand a very important note regarding the dictionary tables. And this note is very important and a lot of people, they don’t understand this point. Now what I will do, I will make right a click, open a terminal. Now I will make SQL Plus as Sasdba. So now I am connected. Now I will make count of the tables across all the database, including the Blogger bill database. First thing I will make sure con name. And currently I am on the root database. So now I will write the following SQL statement. Select count one from CDB underscore tables. I will press enter. Now you will find that I have 2134 table.

But why? We should have more, right? So first thing we have to query the bloggable database to see any of the bloggle is closed or open. So I will write the following query select name comma, open mode from v Dollar sign BDBs and I’ll press Enter. You can see that I have the Orclpdb is mounted, so I need to open it. So I will make alter bloggabel database orclpdp open. I will wait a little and you will find now that bloggable database alter. Now I will execute this statement again. I will take a copy and I will paste. You will find now that I have 4273 tables across all the containers. Very nice. Now when I make now alter session, set container equal orclpdp I’ll press Enter. Sorry. I will make alter session container equal orclpdp session alter show con named on the container database orclpdb. So this is the bloggable database called Orclpdb. Now the question.

Now if I run this query, which is select count one from CDB underscore tables, do you think that it will retrieve 4273? Actually, it will not retrieve this count. It only retrieves the count for the bloggable database or Rclpdb. So I will make paste now. And you can see now the result is 2139. So this means that if you need to know the count of tables and you need to use the CDB underscore tables, you should connect it to the raw database. Because when you connect to the bloggable database and you try to make CDB underscore tables or any dictionary view, it will show you only the objects in this container.

Remember this. So now I will make alter session sit container equal, CDB dollar sign, root session altered. I will execute again and you will find that I have 4273. So in this lecture we understand a very important concept. If you need to use the CDB, just do it in the rural database and don’t do it on the bloggabill database. That’s it. It’s very simple and easy. Thank you for listening and see you in the next video.

6. query the data files

Hello everyone and welcome back. I hope that you are doing fine. In this lecture we will learn how to query the data files. But before that, I just want to tell you that I already start up my virtual machine and the database is up and running. So now I open the SQL available remotely and I am connected by CIS user. First thing I just want to remind you that the data files contains the actual user data application data metadata. So it contains the tables, rows, indexes, procedures, views, whatever. And I told you before that the data files is very important. If you lose the data files, you will lose your database. Okay, very nice. So first thing I will make showdown name and you will find now that I am on the root database or root container. Now I just want to make sure that all the bloggable database opened. I will make select name comma open mode comma con ID from the sign PDPs. I will execute this and you can see now that I have two bloggable and it’s already opened.

I care about this one which is the orclpdb. Okay, very nice. In case if the Vloggable database is not open, you can open it using this command. Very nice. Now I have a dictionary tables called CDB and the score data and the score files. This, it will show you all the data files exist in your system or in your database, including the blogger build database. So I will make select file name comma file ID comma table space name comma container ID from CDB underscore data underscore files. I will execute this and you can see now that I have four files exist in the container ID one, and also I have four files exist in the container three. And this file created automatically when we create the Oracle database, right? But we will learn in a separate chapter how to create data files.

Anyway, for example, I have a data file called user one DBF, the file ID is seven and this is related or exist in a table space name called users and the container ID is one. So we can see now that these are four files exist in the container database one, which is the root database. So we can find that I have a table space users and do system and CSOX. And we talk about this in details in the introduction chapter. So in this lecture I just want you to understand how to query the data files. Now if you go to this location, you will find it physically on the server.

So for example, I will go to use one app oracle or a data or RCL. So I will open my machine, I will go to home, I will go to other location computer one app. I will go to Oracle, then I will go to Aura data ORCL and you can see this is the files, right? You can see this is file one and this is a file and this is a file. So this is the files but I care about. Now in this query, the data files. Okay. So the data files is DBF and I have user an dose system anti socks. Okay, no problem. Don’t try to change or delete any of these files, okay? Don’t touch it. I will close this, no problem. Now I will make the same query, but I will make DBA underscore. And I told you DBA underscore, it will show you only on the current container. So, I execute this. So this query will show you all the data files in the root database. And notice that I don’t have a column called cont. So in DB underscore you will not find a column called container ID. But in the CDB underscore you will find a column container ID.

And this is logic. Okay, very nice. Now let’s do something. I will make alter session set container or Rclpdb. I will move to the blogger Bell database. I will make now show con name you will find orclpdb. Very nice. Now if you make now this query, select file name comma file ID, tablespace name from DB underscore data files or you put CDB, it will give you the same result. Why? Because it will show you only the objects in this bloggable. So now I will execute this. So you will find the data files that in the blogger you can see here. Orclashb if you make also CDB, it will give you the same result, right? Because when you are trying to make CDB underscore and you are exist in the bluggable, it will show you only in the bloggle, right? So that’s it. So in this query, we understand how to query the data files exist in the database. So we have a main table called CDB underscore data underscore files or DBA underscore data underscore files. Very nice. Now, a very important note. If I make like this, for example, control C and I make here all. Actually we don’t have a table called all underscore data underscore files. Why? Because I told you that all underscore is available for any local user. So the local user, he doesn’t need to know about the data files and no need to know about the data files. So that’s why not everything. You will find tables containing all underscore. So if you execute this, it will give you error. So it will show you that table or view doesn’t exist. So you have to understand this concept variable. Not everything contains all underscore or user underscore. That’s it. Thank you for listening and see you in the next video.