Pass Oracle 1z0-082 Exam in First Attempt Easily

Introduction and foundations

1. Basic Introduction

Hello and welcome to this lecture, in which we will take a basic introduction. I know you already know all of the information in this lecture, but it is always a good idea to review your knowledge. So, what will we learn in this lecture? First, we will learn what data is. What is the database? What is the DBMS RDBMS? What is the table? What are the differences between SQL and PLSQL SQL statements? What is the relational database? What exactly is normalization, and what are the consequences of not having it? And finally, we will have to understand types of hosting Oracle database at Wolf. What is data? The difference between data and database is that data is facts or figures. It is pieces of information, and usually the data is unprocessed and unorganized. Now, because we have data in the database, we need a system to manage these data. We call this a DBMS. The DBMS is a database management system to store, retrieve, and modify data in the database. And because Oracle is a relational database, then we have some The application is interface between users and database. Now what is the table? The table is the basic storage unit of Nrdbms. It is a logical unit, not physical. We will talk about logical and physical in details in this course. Don't worry. Now, the table consists of rows and columns. For example, I have a table called Departments. This table contains two columns: the department ID and department name. We call the horizontal line a row. I know that you know this information. But I only review the information for you and also for me. Okay. Now to access Oracle database, we need SQL. So the SQL is a structured query language to access database. So, I have here the Oracle server and I have here a user. In order the user to access the Oracle database, he should have a username and password. So the user use a development tool like SQL Developer or SQL Plus in order to write SQL statement. So he will use the tool, he will put the selected statement. The selected statement go to the server. Oracle server will return the result back. So this is the SQL. Now we have types of SQL statements. Actually we have DML, DDL,DCL and the transaction control. The DML. Like, select, insert, update, delete and merge. Some people ask why select statement Is a part of DML data manipulation language. Because we have something called select for Update. And I described that in details in my courses in SQL and Plcal. So never mind. So we have Data manipulation language. We have data definition. Language like create, alter, drop, rename, truncate, and commit. We have also data control language like Grant and Revoke. We have a transaction control like Commit, Roll, Pack, and save point. Okay, now, SQL is not only the way to access the database, we have Plcquel. So what is the Plc equal plaque is extension told but it has a design features of programming languages. So in PL SQL you can write for loop Statement while loop. So you can write statements like other programming languages like Java. And another important note that data manipulation and the query statement of SQL are included also in the Place. And the most important note when you hear the wordplay this is belong to Oracle in Microsoft SQL Server we have similar to PS Equal, something called TSQL. Okay? Now Oracle is a relational database. So what is a relational database? A relational database stores data in a set of simple relations. So for example, I have table department and table called EMP and table dipped. I have two columns dipped and DNAme. In this table EMP I have employee number, department number Dame’s there is a relation between this table and this table which is a foreign key. So I have the department number as a foreign key refer to table depth. Okay, if I ask you the employee 77 eight two work in department Ten, can you tell me what is the name of the department ten? Simply, you will look at the table of dipped, then you will look at the dip, and you will understand that the department name is account. So this is the relational database, and we call this the normalizations. So what are the normalizations? Normalizations is a database design technique that organizes tables in a way that reduces redundancy and dependency of data by dividing large tables into smaller tables and linking them using relationships. So now you may be wondering why I have to do normalizations and what would happen if I didn't do normalizations. So, for example, if I put the department name here, in this case I have to repeat the department name with each employee. So this means that I have extra memory space. Another problem that is difficult to handle and update the database. In this example without normalizations, if I want to update the department name to another name, in this case I have to repeat the department name with each employee. Now, in the first example without normalizations so I have the department name here that repeated with each employee. So suppose that I have a specific user entering information into this table. He enters the employee number 7782.He is working in department Ten, the name of the employee. He put accounts as department name bathe entered it in small letter. He entered another employee working in the same department but he enter accounts as capital letter. So I have his data and consistency. So I cannot know what is the real name, because in the Oracle database, it is case sensitive. So accounts, small letters, nutlike accounts, capital letter. So that's why normalization is the best. So here, if I want to change the department name, Inly change it in table department with only one record. So that's it’s the problems without normalizations, extra memory space, difficult to handle and update the database data and consistency, and we have lot and a lot of problems. Now, a very important note. In some cases we need demoralizations and we use this technique for reporting purpose and for better performance. Okay? Finally, we will discuss hosting the Oracle database at Wolf Creek and AMC. We have two options: on premises and in the cloud. On premises means that the software is installed in your own environment on your own server. The second method is in the cloud, which means that Oracle will host the database and all its components. Actually, most companies are now using on-premises, but some have transferred to the cloud.

2. Oracle DB editions

Hello everyone, and welcome. In this lecture we will take brief history of Oracle database. In my opinion, this lecture is very nice and it's good to know about this information. Let’s start actually in 1977.Larry Ellison and this is the main guy of Oracle and Pop Miner landed Oates started the consultancy software development laboratories which became Relational Software, Inc. Okay. In 1983, this company Ribosome Oracle System Corporation. Then become Oracle Corporation. So the name of Oracle Corporation started in 1083.Now let's talk about the versioning of Oracle database.79, we have Oracle version two. It was the first commercially available SQL based RDBMS. In 1983 we have Oracle version three, the first relational database to run on many frames, many computers and PCs. The database was written in C, enabling the database to be ported to multiple platforms. In 1984 we have Oracle version four, and it has a feature of multivirg in read consistency.85, we have Oracle V Five, and this version supports client server computing and distributed database systems. We have Virgin Oracle V Six, proenhancement to disk input, output roll, looking scalability and backup and recovery. And also in this version, V six, introduced the first version of Plaque language. Okay, in 1982 we have Oracle Seven, introduced Plaque store the procedures and triggers. In 1997 we have Oracle Eight, and it was released the object creation database supporting many new datatypes and also supported partitioning of large tables. In 1999 we have Oracle Eight i, and the I represent Internet, so it provides native support for Internet protocols and server side support for Java. In 2001 we have Oracle Nine. I introduced Oracle Rock, enabling multiple instance to access single database. Additionally introduced Oracle XML database. So in Oracle Nine I we have the Rack and the Rack it is multi instance accessing single database. We will understand the instance and details in this course, don't worry. In 2003 we have Oracle Eleven g introduced grid computing. What is the meaning of grid computing? A computing architecture that coordinates large numbers of servers and storage toast as a single large computer. So this is the letter Gait’s mini grid computing. In 2007 we have Oracle Eleven and we have new features. In this release, we have active data, guard, secure files and exit data, and many new features. In 2013 we have Oracle Twelve C, and thesis the major changes in the Oracle database. It’s designed for cloud and it support multitenant architecture. In 2018 we have Oracle 18 C, and it is the same as twelve C supporting cloud multitenant architecture. And it has many new features. So this is the information I want you to know. Today you have this link in order to see more information about the history of Oracle database. And thank you for listening and see you later in the next video.

3. Database VS Database Instance

Hello everyone, and welcome to this informative lecture. In this lecture, we will discuss Oracle Database editions, which is very important to know. So, what we will learn in this lecture: What are the available database editions in 12C and also in 18C? And what is the purpose of each edition? A very important note. Oracle Database software can be very expensive or very cheap, even free, depending on what you are buying or using. Okay, now let's talk about the Oracle Database twelve C editions and you will find the same editions n 18 C. Now, first thing, we have Oracle Database Personal Edition, PE. Now, this edition supports single user development, includes all the components that are included in the Enterprise Edition. And the Enterprise edition. Contain everything. But we have some limitations. That the personal edition. We don't have Rack real application cluster and no Oracle management packs. Okay, so this is the personal edition. We have Olaker Database, standard edition two Se two. And this is started from release 112.12. Now, includes all the features necessary to develop workgroup and web applications, support Rack. So actually the Standard Edition is good for small and medium companies. And it's now, what is the limitations of this version or this edition? No parallel execution, no data guard, no management pack, no cloud control, and only one pluggable database allowed. Okay, this is very important. Now, note before standard edition two, we have Se and Se one, standard edition and standard edition one. And both of these editions has no Rack option. Okay, very nice. Now we have also the Oracle database enterprise edition EE. And it contains everything. And this is the most powerful edition, and we use it in a large company. So, Olaql Database Enterprise Edition provides performance, availability, scalability and security for development applications, such as a high volume online transaction processing or LTP applications, query intensive data warehouse, and demanding Internet application. Oracle database. Enterprise editions contains all the components of Oracle database. So this is the best and it's very expensive. And finally we have Oracle Database express edition XE. And this is the only Oracle version free. It is limited use, one CPU and one GPU memory. Actually, many programmers using this version or this edition for Oracle Abex. So in Oracle Abex, you can create very nice applications and you can, for example, develop it for your small company, for your own projects. So you can take some benefits from this edition. It's very nice. So, this is the editions for Oracle twelve C. You can find the same additions for 18 C. So for more information, I give you this thing for twelve C and this thing for 18 C. So thank you for listening and see you bye. In the next video.

4. Sever processes and the PGA

Hello everyone, and welcome to this informative lecture. In this lecture, we will discuss Oracle Database editions, which is very important to know. So, what we will learn in this lecture: What are the available database editions in 12C and also in 18C? And what is the purpose of each edition? A very important note. Oracle Database software can be very expensive or very cheap, even free, depending on what you are buying or using. Okay, now let's talk about the Oracle Database twelve C editions, and you will find the same editions in 18 C. First, we have Oracle Database Personal Edition, PE. This edition supports single user development and includes all the components that are included in the Enterprise Edition. No parallel execution, no data guard, no management pack, no cloud control, and only one pluggable database allowed. Okay, this is very important. Now, note before standard edition two, we have Se and Se one, standard edition and standard edition one. And both of these editions has no Rack option. Okay, very nice. Now we have also the Oracle database enterprise edition EE. And it contains everything. And this is the most powerful edition, and we use it in a large company. So, Olaql Database Enterprise Edition provides performance, availability, scalability and security for development applications, such as a high volume online transaction processing or LTP applications, query intensive data warehouse, and demanding Internet application. Oracle database. Enterprise editions contains all the components of Oracle database. So this is the best and it's very expensive. And finally we have Oracle Database express edition XE. And this is the only Oracle version free. It is limited use, one CPU and one GPU memory. Actually, many programmers using this version or this edition for Oracle Abex. So in Oracle Abex, you can create very nice applications and you can, for example, develop it for your small company, for your own projects. So you can take some benefits from this edition. It's very nice. So, this is the editions for Oracle twelve C. You can find the same additions for 18 C. So for more information, I give you this thing for twelve C and this thing for 18 C. So thank you for listening and see you bye. In the next video.

5. System Global Area Part 1

Hello everyone and welcome back. I hope that you are doing fine in this lecture. We will understand the database and the database instance. And we have to understand what is the difference between database and the database instance. And this is the core for any database administrator. So this topic is very important. So now let's start. First thing, the Oracle database consists of two major components the database storage and the Oracle instance. We said before that the database is organised collection of information. It contains collection of database files stored in a disc storage. So this is the physical, you can see it in your eyes. Okay, so it contains collection of database files. Okay, now we have many types of database files. We have nine types of database files. We have data files, we have control files, reader log files, database backups, archived logs, parameter files, password file alert log file and trace files. We will understand all these types of files, don't worry. So I just want you to take the concept in this lecture. Okay, so now we know what is the meaning of database. Now let's move to the database instance. Actually, a database instance is assets of memory structure and the processes. So the instance is memory structures and the processes that manage database files. Okay, so this is very important. So note, a database instance exists only in memory and this is very important. Okay, so now I have the main two components of Oracle database. I have Oracle database files and I have the Oracle instance. The Oracle instance manage the database files. And the Oracle instance contains two main things memory and processes. Okay, in another world, the Oracle instance is the Oracle programme or binary loaded into the server Ram. And it's created by Oracle every time you start up your database. So every time you start your database, the Oracle in a sense started again. Okay, why we do this? Because reading from memory is a lot faster compared to reading from disk. So in Oracle database we don't read the files direct. We have the Oracle instance, which is the Oracle programme loaded into the server. Nice. Actually, in the memory, I have two types of memories: SGA and PGA. We will understand this in details, don't worry. And I have two types of processes: several processes and background processes. Remember this information very well. Okay. Now, every running Oracle database is associated with at least one Oracle database instance. Now another important note. Because an instance exists in memory and database exists on disk, an instance can exist without a database and the database can exist without an instance. This is very good information. Now the last and very important note. Losing instance, no issue but losing data files. For example, losing the data. So if you lose some of data files, this means that you are losing the database. But if you lose the instance, this is not issue because you can create it again. Okay, very nice. Now we have to understand the instance more in a very simple example. Actually you can understand what is the difference between database and database instance. Like Microsoft Word and Microsoft Document. So for example, the Microsoft Word imagine it as the instance. So it is the program, but the Microsoft document is the database. So we use the Microsoft tool to read the data inside the document, right? So this is exactly the difference between a database and a database in a sense. In this example, if I lose this document, this means that I cannot see this data, right? But if I lose the Microsoft Word program, no issue because you can install it again, right, in order to open this file. So this is the most simple scenario or example to understand the difference between database and database instance. Okay, very nice. Now we have to understand the Oracle database server architecture. Actually, we have many configurations options for the Oracle server. The first one is a single innocence database. So I have a server machine. Inside this several machine I have a database innocence and a database. So the relation one to one. I have one database instance and one Oracle database. And in our course we will focus only on this example or in this configuration. Another configuration is multisingle instance database on same server. So I have a server. For example, I have two database instance and two databases. Also the relation one to one. But here I have two databases on the same server. And the third scenario is called Oracle Rock. In this scenario I have one database and I have many instance accessing this database. And this is called Rack and this provides for me high availability, scalability and high performance. And this will not cover in this course because this is a separate course. So I just want you to understand that I have three configurations options. Okay, very nice. So this is enough for today. Thank you for listening and see you in the next video.

6. System Global Area Part 2

Hello everyone and welcome back. Today we will continue talking about the database innocence, but we will start talking about the system global area. The I know that we have a lot of theory information, but this is very important in order to pass the exam and also in order to understand the practical. Okay, so what we will learn in this lecture, we will understand the system global area, the main tasks of SGA, the share pool, the database buffer cache, and also the redo log buffer. So we will understand the three parts of theca today, as I told you that the Oracle instance is a set of memory structures and processes. Okay, in the previous lecture we talked about the PGA and the server process. In this lecture, we have to understand what is the system global area. Actually, the SGA is a group of shared memory structures that contains data and control information for one database instance. So it contains subcomponents. Each component has a specific task. So the main task of SGA, maintaining internal database structures that many process access concurrently caching data books read from disc buffering, reader data before writing it to the online redo log files storing SQL execution plans. Maybe you will tell me, man, I didn't understand anything from this. Don’t worry, we will understand everything in details. So when we go deep one by one, you will understand what is the main task of SCA. Just focus with me. Okay, very nice. Now let's start by the first part of the SGA, which is the shared pool. The shared pool is the most important component of SGA. Actually, the main purpose of the sharepool iscash non user data like data dictionary. So it cache the data dictionary. What is the meaning of data dictionary? It is metadata. What is the meaning of metadata? It is data about data. For example, DBA underscore tables. So when the user is doing a select statement, Oracle has a lot of work to do. He needs to know if this table exists or not. He will know from where he knows, which is the data dictionary. So it's better to cache the data dictionary into a separate area in the system called sharepool. That's it. Okay, we cache the data dictionary into the share pool for performance. That's it. Okay, the share pool contains subcomponents, so it also contains subcomponents like the data dictionary, cash, library cash, server result, cash, and many others. No need to go into detail here. Okay, very nice. Now another task for the share pool is to store cash information about each SQL statement that is executed. Like the execution plan. Actually, it is information about the SQL statement. And it's like the best way for executing this select statement. I will give you example. For example, I have a user, a user session, he's doing select Star from depth for example. So Oracle will make execution plan information about the select statement. This means the best way to execute this selection statement. So Oracle will store the execution plan information to the shared pool. And you can see it here past SQL. So the first time the user makes this statement, we call it Hard Pass. So Oracle has a lot of things to do, okay? But now, if another user wants to execute the same selected statement, Oracle will read this information directly from the shared pool and will not need to regenerate the execution plan again. This is referred to as a soft part. Now, let's talk about the database buffer cache, which is the second component of the system global area. It is responsible for caching database user data. Okay? The buffer cache caches frequently access database data intomemory and this is lead to more performance. Note the buffer cache cache is a block of data instead of individual roles. And this is very important. And we will understand what is the meaning of data blocks because this is the smallest unit for the storage in Oracle database. Each block equals 8 KB by default. And we can change this value and we will talk about this later. All users who are connecting to the database share and access a single database buffer cache remember this. And that's why we call it systemic global area SGA. So it is a shared memory for all users, the buffer cache, including the key pool, recycle pool and many others. And no need to go deep about these sub components. Now we will talk about the redo log buffer. So you can see this part. We call it redo log buffer. Now actually it is a circular buffer that holds information about changes made to the database. So it only contains the changes made to the database, okay, designed to store redo entries. So this is the main purpose of the redo log buffer. What is the meaning of redo entries? Actually, redo entries are a small record that reflect any changes made to the database as a part of the transactions or data-based structure changes, which means DML or DDL. Okay? Note redo entries are used for database recovery when and if needed. A very important note, the redo log entries in the redo log buffer are also written on aperiodic basis to a set of files in our database storage known as redo logs files. We will understand this later. So remember that also the reader logfiles related to the reader log buffer. Now, I will give you one example. For example, I have a user session and this user making updated statement. So the changes are applied to the actual roles which are in the buffer cache. So this updated statement will be also in the database buffer cache, which we take it in the previous slide. But also Oracle will generate a redo record and store it in this area, which is the redo look buffer, which contain only the changes. So when the user make a commit, these changes will be written to the database direct. But I don't understand, man, why not to take these changes directly from the database buffer cache? So why does Oracle make the changes in the database buffer cache and also write these changes in the redo log buffer? Because this area only for the changes. So writing the changes from the redo lock buffer, it will be more faster because the database buffer cache containing huge amount of information and it is more secure to take it only from the redo log buffer. So it is for the performance. So taking the changes direct from the redo lock buffer, it will be faster than reading it from the database buffer cache because it contains many information and it will be very busy. So that's it’s this information for today. Thank you for listening and see you in the next lecture.

Hide