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

7. query the temps files

Hello everyone and welcome. In the previous lecture we understand how to query the data files. In this lecture we will understand how to query the temp files. I just want to tell you a very important note. Don’t try to go deep about this subject. In this lecture only I need you to know how to write the query for data files and temp files and whatever. We will understand the concept of all these in details in the next chapters. So just relax, okay? Very nice. So first thing, I already opened my virtual machine and my database is up and running. I will minimize this. I open the SQL developer in my client machine and I am connected as sysdba. Now in this lecture we will learn how to query the temp files. Actually a temp file is a file that is a part of an Oracle database.

Temp files are used with a temporary table space. Now to refresh your information, temporary tablespace are used for special operations, particularly for sorting data results on disk and for hash join in SQL. For example, for SQL with millions of roads returned, the Sort operation is too large for the Ram area and must occur on disk. So the temporary table space is where this take place. So we understand what is the purpose of this table space. And this table space is using temp files. So now let’s do some exercises. First thing I will make showdown name. You can see that now I am on the container database CDB underscore root. I will make select name open mode con ID from visual understand PDBs. To make sure that all the blogger is open, I have no issue.

Now I will write this sequel which is select file name, file ID template space name conid from CDB underscore temp files. I will execute this and you can see that I have two temporary file temp one DBF and the file ID is one and it is related to tablespace temp as I told you. And it is in the container ID one. Okay. And also I have another temp file for the container ID three, which is the orclpdb. And also it is related to a temp table space. So this means that one file is enough for each database or a blogger build database. So I just want you to focus in this lecture about query the temp files. So I have one temp file in the root and I have one temp file in the pluggable. Very nice. Now if I do the same query, but I change CDB to DBA, it will show you only the root database.

I will execute this, it will show you only the object in the root because currently I am in the route. But now if I make alter session sit container equal orclpdb and I make surgeon name. Now if I make select file name, file ID tablespace name from DB and the Scottish files, it will show you the files that exist in the Blogger database or Rclpdb. Because I am currently on this bloggle, I will execute this and you will find this is the result. So you can see that or clash or Rclpdb. So that’s it.

So in this picture we understand how to query the temp file and we understand what is the purpose of the temporary table space. Don’t worry, it will be a full chapter to manage the table space and how we can create table spaces and data files and Tim files. So the purpose only to query and to know some dictionary tables. Thank you for listening and see you in the next video.

8. V$DATABASE / v$version / v$instance

Hello everyone and welcome in this lecture we will query the database name and the database version and the an instance name. Okay, very nice. But before that we have to review a very important information. I told you that Oracle database twelve C and above releases like 18 C and 19 c. In the future we have multi tenant database. So we have a container database called CDB and it is like a basket only. Okay, very nice. So every container database has the following containers. Exactly one CDB route. So I have a root container and a seed bloggable database called PDB underscore seed like this one. So this connected to the root and also zero or more user created PDBs.

So I have a basket called CDB inside that I have a root inside the route I have the bloggable databases. So for example the container ID for the basket three DB we can give it ID zero root one, the seed two and the sales DB for example three and this is four. So this is the concept. Okay, very nice. So now let’s back to the exercise. I already have my virtual machine up and running and I am connected as SIS user. First thing I will make sure con name you will find now that I am in the roots database. Now I will make this a query. Select name comma CDB comma conid comma open mode from visual or sign database. So this view I can know what is the name of the container database or my main database. I will execute this and you can see that my database name is ORCL which is the container database.

And we choose this name in the setup. Very nice. So the name is ORCL CDBS which is mean it is a container database. The con ID is zero and the open mode is read and write. Another way to query the database name, we can make show parameter DB underscore name. I will execute this and you can see now that DB underscore name is ORCL. Very nice. Okay, now we will make this query in order to see the Oracle database version. It’s a very simple query. Select banner from video sign version. I will execute this and you can see oracle database 18 C enterprise edition release eighteen zero zero production. Very nice. Now how to query the innocence name.

And remember that I told you the innocence name equal database name if we are using a single database innocence. So I will make select star from video or sign innocence and you will see a lot of information about the innocence. So I have the innocence number one and the innocence name is ORCL. You can also see helpful information like the host name, the virgin. Very nice. Another way to show the innocence name is to use show parameter instance underscore name. I will execute this and you can see now that the innocence name is ORCL. So in this we understand how to query the container database name and the database version and the instance which is the memory. Thank you for listening and see you in the next video.

9. V$CONTAINERS / cdb_PDBS

Hello everyone. In this lecture we will understand two things. First thing we will understand what is the video sign containers and also we will understand the CDB underscore PDBs. OK, now let’s start by the video sign containers. Actually video are signed. Containers display information about bloggable databases and the route associated with the current instance. Let’s do some exercise. I will make first show conname. You will find now that I am on the root database. Now I will execute this statement. Select conid name open mode from the dollar sign containers. I will execute this and you will find that I will have the root and also all the bloggable databases. So I have a container ID one, which is the root and the open mod is read write. I have also container two which is the seed bloggable and read only I have three. I have a bloggable database called Orclpdb and it is read and write.

So the doers and containers show you the root and all the pluggable. Now I will make alter session set container orclpdb I will execute this session altered. Now, if I execute the same statement, I only see now the blogger bill. Because currently now I am on the blogger bill. I will execute this statement. You can see that I only see container ID three and it is Orclpdb. Let’s back again to the root alter session set container equals CDB underscore root show con name. So now I am again on the root database. Now I have a dictionary table called CDB underscore PDBs.

I will make select Pied PDB name status from CDB underscore PDBs. I will execute this and you can see that I have two boggles three and two and the status is normal. So actually what is the meaning of the status? First thing we could have a status called new. And the new mean that the bloggable database has never been opened since it was created. I have a normal like this. The bloggable database is ready to be used unblocked. The bloggable database has been unblocked. The only operation that can be performed on it is drop bloggable database and we will see that in a full chapter we have a status called relocating. And this means the database is in the process of being relocated to a different container database. We have a status called relocated.

This means the blogger based database has been relocated to different CDB. So the first one in progress and the second one it is relocated to a different container database. That’s it. So this means that this dictionary table, it will show you all the blogger bell database you have in your database. But if you move now you make alter session sit container equal orclpdb I will execute. Then I will make showcase name. Now, if you execute this statement, it will show you only the bloggabel which is Orclpdb. Because I am currently a login, I will execute this and you will find blog abilities three. And also, if you put here DBA underscore, it will give you the same result. So in this lecture, we understand two things. The video are sign containers and the CDB underscore PDBs. So each one has specific information. Thank you for listening and see you in the next video.

10. V$DATAFILE

Hello everyone. In this lecture we will understand two things. First thing we will understand what is the video sign containers and also we will understand the CDB underscore PDBs. OK, now let’s start by the video sign containers. Actually video are signed. Containers display information about bloggable databases and the route associated with the current instance. Let’s do some exercise. I will make first show conname. You will find now that I am on the root database. Now I will execute this statement. Select conid name open mode from the dollar sign containers. I will execute this and you will find that I will have the root and also all the bloggable databases.

So I have a container ID one, which is the root and the open mod is read write. I have also container two which is the seed bloggable and read only I have three. I have a bloggable database called Orclpdb and it is read and write. So the doers and containers show you the root and all the pluggable. Now I will make alter session set container orclpdb I will execute this session altered. Now, if I execute the same statement, I only see now the blogger bill. Because currently now I am on the blogger bill. I will execute this statement. You can see that I only see container ID three and it is Orclpdb. Let’s back again to the root alter session set container equals CDB underscore root show con name. So now I am again on the root database. Now I have a dictionary table called CDB underscore PDBs. I will make select Pdbid PDB name status from CDB underscore PDBs. I will execute this and you can see that I have two bloggles three and two and the status is normal. So actually what is the meaning of the status? First thing we could have a status called new.

And the new mean that the bloggable database has never been opened since it was created. I have a normal like this. The bloggable database is ready to be used unblocked. The bloggable database has been unblocked. The only operation that can be performed on it is drop bloggable database and we will see that in a full chapter we have a status called relocating. And this means the database is in the process of being relocated to a different container database. We have a status called relocated. This means the blogger based database has been relocated to different CDB.

So the first one in progress and the second one it is relocated to a different container database. That’s it. So this means that this dictionary table, it will show you all the blogger bell database you have in your database. But if you move now you make alter session sit container equal orclpdb I will execute. Then I will make showcase name. Now, if you execute this statement, it will show you only the bloggabel which is Orclpdb. Because I am currently a login, I will execute this and you will find bloggabilities three. And also, if you put here DBA underscore, it will give you the same result. So in this lecture, we understand two things. The video are sign containers and the CDB underscore PDBs. So each one has specific information. Thank you for listening and see you in the next video.