1z0-082 Oracle Database Administration – Database Startup, shutdown and connections Part 2

16. connecting using tnsnames

Hello everyone and welcome back. In the previous lecture we understand what is the meaning of easy connect. In this lecture we will learn how to connect using the Tnsnames aura. So we will go to the home other location computer u one app Oracle products 18 DB one and you will go to network and I will go to admin. Then you will find T in its name or I will open. You can see now that I have entry called ORCL. Now what I will do, I will close this one and I will open a terminal. And now I will connect SQL plus as Sysdba. I am corrected. Now I want to teach you how to connect using TNS name entry. So again if I open the file you will find entry called ORCL. And this is the name of entry for the T in its name. And this entry pointing to a service called Oracle. com. And this service pointing to a database called Oracle. Very nice. So now I will close. Now I will do the following comment. I will make connect sys. Then I will put the password welcome at. Now I will put ORCL.

That’s it. So in this way Oracle will understand that after the at is T in its name entry, and also this needs the listener to be up and running. I will press Enter and you will find now that I have the same error that I should log in using as sysdba or sysoper. So I will make now again connect sys welcome at or class sysdba. And you can see now that I am connected. Now I want to login using the system user. And remember that the system user, it is the same of sys user, but he has listed privileges. So now I just want to change the password for the system user. I will make alter user system identity five by welcome and I will press Enter. So I changed the password for system to Welcome. Now I will make connect system welcome at ORCL. Now no need to put as this DBA.

I will press enter. And now I am connected. Very nice. Now I want to teach you something important. Now I will go to the file admin here, I will open it. And now I will take this. I will make copy. Now I will make another entry. But for the database called Orclpdb. So I will make the entry is Orclpdp and the service name will be Orclpdp. So this means that I have another entry. It’s called Orclpdb pointing to the service name Orclpdb. com, which is pointing to a blogger bill database called Orclpdb. So now I will make save and I will close this one. Now if I go here and I make now connect system welcome at Orclpdb you will find a message database not open. So in order to open to this bloggerable database, this blogger bell database should be opened, right? So I will make connect sys as sysdba. I will put the password. I will make now alter bloggabil database orclpdp I will open it and you will find

now the message is bloggabill database alter. Now if I try to do this now again connect system welcome at Orclpdb, it will work without any issue. So I will take it copy. I will paste it here. I will paste it here. And you can see now that I am connected, I will make now showcon name. So you can see now I am on the bloggabel database called Orclpdb. Very nice. Now what I will do, I will do something very nice. Now remember that I am on the server. So this means that I am connecting using the client style. So I will open this file. I will take from here to here. I don’t need the listener local. I will describe this for you in detail. So the listener ORCL don’t touch it. So I will take ORCL and orclpdb I will take it as copy. Now I will go to my original machine. I will make right click and I will make a new. Then I will make text documents. I will open it. I will paste this. I will close save.

Now I will rename it to tsunamis aura. It will give me this message. I will click yes. So you can see now that I created a file called Tnsnames Aura. Very nice. Now I will open the SQL developer that exists in my machine. I will go to Tools preferences. Then I will go to database advance. I will go to the TNS name dot directory. I will go browse and I will select it from the desktop. I will press select and okay, now I will make a new connection. I will put the user system for example the password welcome. And I will select the connection type as TNS and I will select now for example orclpdb I will test the connection and you can see it is success 100%. Now what I will do, I will do something very nice. Now I will go to my desktop original, I will make rightclick. And I will open it with north bad. Okay. And I will change this for example to P for example had it. I just want to prove to you that this name is only entry. You can name it whatever you like. So this connection Hallett is pointing at the end at this database called Orclpdb. com. So now I will close it and I will save it. Now if I go to the SQL developer and I try to make a new connection. I will go to the TNS. Then I will choose harder. Now I will put system and the password is welcome. Now if I make test the connection will be success. But this is only alias. Okay, very nice. So in this lecture we learn how to connect using the t in its names dutch Aura. So thank you for listening and see you next time in the next video.

17. unlock the hr schema in the pluggable database

Hello everyone and welcome. In the previous lecture we understand how we can connect using the t in its name entry. In this lecture we will unlock a user called HR and this user exists in the blogger database called Orclpgb. If you remember we when we do the installations for Oracle database, we make checkbox for install sample schema. So if you didn’t put this checkbox, this means you will not find this user. But anyway, the lecture for today is only to show you how to unlock the user HR if you didn’t find this user, no problem. Okay, very nice. So now I log in as Oracle as user. You can see here I will make right click open terminal. I will make SQL plus as sysdba. I will make now show PDPs. You will find that I have two bloggle database.

The first one is Seed, the second one is Orclpdb. And the status for this bloggable is read write. So it is opened. Now I want to move to this bloggable database. I will write the following command alter session set container equal orclpdp and I will press Enter. I will make now show con name and you will find now that I am on the container database. Orclpdb I will make now the following query to make sure that I have the user HR. So I will make select user name from all users and I will press Enter. You can find that I have 37 rows, so I have a username called HR.

So now I will execute the following command. In order to unlock the user HR, I will make alter user HR account unlock and I will press Enter. So you will find now useralter. Now I will change the password for the user HR to PHR. So I will make alter user HR identify by HR and I will press Enter. Wow, very nice.

So now I will try to connect using the HR user. So I will make connect HR HR at orclpdp and I will press Enter. So I am connected. Now we have many sample tables for the user HR. And I am sure that you know these tables because I describe that in details in my SQL calls. But anyway you can make for example select table name from user tables and you can see that I have a table called regions. I have a table called countries, locations, departments, jobs, employees, job history.

Now I will try to login using the HR remotely from SQL developer. So I will open my SQL developer in my client machine or my original machine. I will make a new connection. I will put the user HR, the password HR and I will put the hostname test. com the port the same the service name which is the bloggable database. It will porclpdb. Now I will make test connection. It is success. Now I will connect. But I have to give a connection name. So I will make HR connection. Now I will try to make connect and I already log in using the HR I will click here. I will go to the tables and you can find many tables. For example employees table you can see this is the employees table. If you go here for example and make select store from employees and try to execute you will find this is the data. Wow. Wow. So we learned a lot of things in this lecture. So thank you for listening and see you in the next video.

18. saving and runing scripts

Hello everyone and welcome. In this lecture we will learn something very nice. How to save and run scripts using the SQL Plus. So first thing I log in as Oracle user, I will make rightclick and I will open a terminal. Now first thing you have to know that when you make for example PWD, this means that I am currently on this path which is home Oracle. Now I will connect using SQL Plus. So I will make SQL plus as Sysdba. Very nice. Now I will do a sample query. For example, select one from dual. Select one from dual. I’ll press enter. So this is the result. Suppose now that I have this query and I want to save it. So it’s very simple. I will make save. I will name the query. For example test SQL and I will press Enter. So you can find now created file tist SQL.

But where can I find this script? It’s very simple. You will find it in the same location which is Home Oracle. I will go to home. I will go to the other location computer. You will go to the home then Oracle. And you will find that test SQL. So it’s very simple. Right now I will pack again to the SQL Plus. And I will now make the following comment. It’s very simple. I will make at test SQL. So I will run the script. I will press enter. So I already run the script and everything is working fine. Okay, very nice. I will close this one and I will close this one. And now I will open a terminal and I will make CD desktop. So now currently I am on the desktop. So now when I make SQL plus as Sisdba, I will write the following query. For example, select an instance underscore name from visual sign an instance.

So the NSS name is ORCL. Now I want to save this query. I will make save test SQL. Now it will save it in the desktop. Why? Because in the previous command I make change directory to desktop. I’ll press Enter and you can see it now here. Right. Very nice. So now if I make at test dot SQL, I run the script and everything working fine 100%. Now another way I will make exit. I will run the SQL Plus and at the same time I will run the script. So I can do the following command. SQL plus for example system welcome at ORCL then I will make space. Then I will make at test SQL. I will press Enter and wow. Wow. Working fine 100%. So if you are outside the SQL Plus, you can invoke the SQL Plus like this. You will put user password, then TNS name, entry, then space.

Then you will mention the script name. Okay, very nice. Now suppose that you want to connect using Sys user. It will be the same. But we have symmetric. We will make I will make Exit. Now I will make SQL plus Sys welcome at ORCL as says DBA. Then you will make at Test SQL and I’ll press Enter and it’s working fine. So in this lecture we learn how to save a script and how to run a script and we learn how to run the script from the SQL and how to run the SQL script from outside the SQL by invoking the SQL bus. So thank you for listening and see you in the next video.