Gain in-depth knowledge for passing your exam with Exam-Labs 1z0-071: Oracle Database SQL certification video training course. The most trusted and reliable name for studying and passing with VCE files which include Oracle 1z0-071 practice test questions and answers, study guide and exam practice test questions. Unlike any other 1z0-071: Oracle Database SQL video training course for your certification exam.

DBA Info - DBA_TABLESPACES

1. DBA_TABLESPACES

DBA table spaces give information about the tablespaces configured in the database during the installation. Oracle creates some table spaces by default, so these table spaces hold all the data that currently exists in the database. Let's run a select statement from DBA, highlighting Table Spaces. Choose it. Run it. So this gives information about all the tablespaces that currently exist in the database. For example, the system Sys Table Spaces holds all the data related to Oracle's internal functioning. DBA table spaces.

SELECT - Selecting Specific Data

1. Selecting Particular Columns

If needed, we can select the columns that we want. We don't always need to select all the columns from a table. We can also select the columns in any order we want want.For example, I can select status first and then table space name. Even though the Table Space Name Column appears first in the actual object, the order of the columns stored in a table doesn't matter as long as we choose what columns we want. But it matters when we do a select statement; then it will bring the results according to how the columns are stored in a table. Now, let's look at the examples. Choose table space underscore name status from the list of DB underscore table spaces. Let me highlight and run the statement so it brings me the Table Space Name Column and Status Column. If needed, I can select the status column first and then the table space name even though the status column is stored after the table space.

2. WHERE Clause – Basics

Earlier, we saw how to select just the columns that we wanted. Now let us filter the rows. This can be done by using the where class syntax, followed by the condition expression. The result will contain the rows that meet the condition. For example, if I select STAR from DBA underscore rows, it selects all the columns and all the data from DBA underscore roles. But what if I want to see only a subsetof data where the column role contains the values connect? To accomplish this, I can choose a star from the DBA underscore roles where row equals to connect. This will bring me to the rows where the value of the column row is connected. And this is a character comparison. So for character comparisons, single quotes should be used, and it is case sensitive.Now let's look at some examples. Okay, I'll run selectstar from the DBA underscore roles. I am highlighting it, and I am running it. So this lists me all the data in DB underscore roles. Now I want to see just the rows where the value of the row column is connected, and this part is case sensitive.So if I do this one where the case is different,

DBA Info - Oracle Database User Architecture

1. Oracle Database User Architecture

Remember the flight example. Let us visit this page to learn about Oracle. Now let me call this plane a plane. See how creative I am! And technically, I am. I can say all the parts of the plane belong to the plane. I can refer its left wing as plane dot left wing,right wing as plane dot right wing rudder as plane rudder. Like that. I can refer to other parts too. It's kind of like the pattern owner object name. The important thing is, who is the owner? Plane is the owner. Now let us see who has the right to use those objects. I mean the controls. The pilot has the rights. The pilot can use those objects. The pilot can take off from the plane, fly the plane, and land the plane. The pilot is the primary authority on that plane. Now, the pilot doesn't need to do all the administrative functions on a plane. There are flight attendants who could do some of those administrative functions. They can control the overhead compartments, interior lightswitches, attend to the passengers' needs, etc. So they are next to the pilot in terms of authority. And remember, they still operate on the objects owned by the plane. And then there are passengers. They bring their own belongings, and each one rides on their belongings. Assume Jack is a passenger with a laptop and carryon luggage. How do we refer to them? We can refer to those as Jackdot laptops and Jackdot baggage. Then there's Jill, who brought a tablet computer and carryon luggage. How do we refer to her possessions? We can refer to those as Jill tablets and Jill baggage. Passenger Mickey did not bring anything. Let us now compare this to the world of Oracledatabase, which we claim owns all of the important objects required for the plane to function properly. There is a user inside every Oracle database called Sys. This user wants all the Oracle-related internal objects. Then who is the pilot? Remember, pilot is not the name of a person. Right, it's a privilege. We may have a pilot for today's flight. And there will be another one coming in as the pilot for tomorrow's flying of the same plane. So piloting is a privilege. The equivalent of it in an Oracle database is the Systba privilege. Any user who has been granted with CISDBA privilege can start and stop the database. Generally, the senior members of the database administrator team within a company will have CIS DBA privileges. Then the database administrator team may have junior members who would do the regular database administration tasks. They may not have access to internal objects like Sysdba, but they can assist with routine database administration tasks. These people are like the flight attendants. Then there will be regular database users who use the database. They are like the passengers andlike the passengers, Jack and Jill. Some can own their own objects such as tables, indexes, et cetera. And like Mickey, some will not own any objects at all. The users who want objects are called schemas, while the users who don't want anything are called just users. I simplified the architecture so that it will be easy for you to understand if you are a beginner. Of course, as with every simplification, there will be some differences between the analogy and the real product. For example, in the analogy, a plane is a machine and not a person. If we strictly compare it to the SIS user database, then Sys should be a rigid entity. However, Sys is also a database user. It is very common in a company for a database administrator to login as the user says with the privilege saying DBA.

2. Schema Vs User

So let us do a quick recap and compare an Oracle user with an Oracle schema. Both start as Oracle users. Then one user goes and creates objects such as tables, indexes, sequences, procedures, packages, triggers, et cetera. And this user can now be called a schema. The users who do not own any objects are called just Oracle users.

3. How objects are stored?

Now let's look at the third aspect of RDS security, the encryption. first, the encryption in transit. Now, to encrypt data in transit, VA uses SSL or TLS connections, that is, SSL certificates. To encrypt the data in transit, the clients must use the AWS root CA, which is a certified authority or certification authority. And that means when you connect over SSL, you have to download the SSL certificate from AWS and use that when you connect to your database instance. Now, the process of enforcing SSL is slightly different depending on which database engine you use. If you're using SQL Server or PostgreSQL, you simply set the parameter RDS force equal to one in the parameter group. And remember that this is a static parameter. So you must manually reboot your instance for the parameter changes to take effect. and we have discussed this in the parameter group lecture. That static parameter change requires a manual reboot. Automatic reboots will not apply the static parameters. You must manually reboot the instance. So you have your database, and it has the root certificate from the certification authority, and you set the parameter group parameter RDS force equal to one. If you're using SQL Server or Postgres, SQL If you're using MySQL or MariaDB, you simply use the Alto statement to require SSL for a particular user. And if you're using Oracle, you add the SSL option in the DB instance option group, okay? Not the parameter group, the option group. And this is how you enforce SSL. And to connect using SSL, what you do is simply download the certificate and use that certificate to connect to your database instance. So you provide the SSL trust certificate; that is, you download it first from AWS, and then you provide that certificate while making the connection to the database. Clients download the certificate, and they use that certificate when connecting to the database. And if you've enabled SSL on your database instance, connecting without passing the SSL certificate will almost certainly result in an error, right? Let's look at some examples of SSL connections in various database engines. So in Postgres SQL, you use this kind of syntax: SSL rules equal to the path to the certificate file, and you set the SSL mode to either verify CA or verify for. If you're using MySQL, you use this particular syntax. So SSL mode is set to verify identity. And if you're using Mariah DB, then SSL mode is set to required, okay? And for older versions of MySQL and MariaDB, you use the SSL Verify Server Search option instead of the SSL Mode option. Now, from the exam point of view, you should keep this in the back of your mind. You don't have to memorise it, but keep in mind that the way you connect over SSL in different database engines differs, and the options available vary slightly. All right, now let's look at the Audius encryption at rest. So RDS supports the AES 256 encryption algorithm, and this is managed through the KmsService, the key management service of AWS. This has the potential to encrypt both the masters and the read replicas. And we're going to talk about these replicas later in this section. And you have to enable encryption when you create your instance and not later on. RDS also supports what is called TDE, or Transparent Data Encryption. And this is something that is available with SQL Server and Oracle database engines. Okay, so TDE is a native encryption feature available with SQL Server and Oracle. And you can also use that along with the RDS encryption at rest. and you enable TDE using the option groups. In Oracle, you have the TDE option, and in SQL Server, you have a transparent data encryption option. And you can enable this in the option groups of the particular database engine. And you can use TDE along with the RDS encryption, as I said before. But remember that using both of these together is not a great idea. You can use one because there might be a slight performance hit if you use TDE along with the RDS encryption address. Just remember that using both of them together is not a great idea, but you can still use it if your use case requires it. All right, that's about it. Let's continue to the next lecture.

4. Accessing the Objects

in a high-level overview. Generally, the creator of an object is also the owner of that object. There are exceptions to this, about which we will talk later. The owners have all the rights to the objects they want. If needed, they can grant access to their objects to other users. For example, if user one wants table one, the user can be granted access to table one by user one. And if needed, user two can grant access.

Classification of SQL Commands

1. Classification of SQL Commands

Equal commands are classified into four categories. They are data manipulation languages, also known as DML. Data Definition Language, also known as DDL Data Control Language, also known as Transaction Control Language, also known as TCL, Data manipulation. language contains select, insert, update, delete, and merge. Select is used to read or select data from one or more tables. Insert is used to insert rows into a table. Update is used to modify arrows or rows of a table. Delete is used to delete one or more rows from a table. Merge is used to do multiple operations in a single statement. I will explain this later. Data Definition Language contains create, alter, drop, rename, truncate, and comment statements. Create is used to create objects such as tables. Alter is used to alter objects, such as adding a column to a table. Drop is used to drop an object, such as dropping an index. Rename is used to rename an object. Truncate is used to delete all the rows of a table. Comment is used to add comments about an object. Data Control Language contains grant and revocation statements. Grant is used to grant privileges and roles to a single user or role. Revoke is used to revoke privileges from a user or a role. Commit is used to confirm and close a transaction. A rollback is used to roll back a transaction fully. The same points are used to partially roll back a transaction. So, if you master these commands, you'll be well on your way to becoming an expert in more than just sequel development.

Pay a fraction of the cost to study with Exam-Labs 1z0-071: Oracle Database SQL certification video training course. Passing the certification exams have never been easier. With the complete self-paced exam prep solution including 1z0-071: Oracle Database SQL certification video training course, practice test questions and answers, exam practice test questions and study guide, you have nothing to worry about for your next certification exam.

Hide