Pass Oracle 1z0-062 Exam in First Attempt Easily

Module 1: Database Concepts And Tools

1. Instructor And Course introduction

Hi, everybody. Welcome to this course on Oracle 12 C database administration. My name is Brent Rose. I have been an Oracle DBA for about a little bit more than 20 years. I have also been an OracleDBA DBA instructor for that long. And I have travelled throughout the country, in fact, throughout the world, teaching the Oracle DBA system since somewhere around version eight. I. And I am very happy to be presenting to you this database course on the Oracle Twelve C system, the latest version of Oracle as of the time of this recording. So, in this class, we will hopefully prepare you for the Oracle database administration exam for twelve C. I also hope that we will be able to prepare you to work as an Oracle database administrator in the 12 C system. So welcome to the course. I hope you enjoy it.

2. DatabaseConcepts And Tools Part1A

Hi everyone, and welcome to this course, which is on database administration in particular. This course is tied to Exam Number 1, Z-62, which is the Oracle Certification Exam, which is the first Certification Exam for Database Administrators in the Oracle Database System. In particular. In this course, we are dealing with the latest iteration of the database system as of the time of this recording. And that, for me, is now Oracle Twelve C. So Oracle has been through many iterations; obviously, it's been through twelve different iterations as I record this. Twelve C has been out for, well, almost a couple of years now, and it is performing quite well, very well. In fact, there's a claim by Oracle, and it'sprobably a true claim, that oracle is the fastestand most robust database system that's in existence today. And that seems to be true. Everybody seems to bear out that claim, and it seems to be true in the testing that's done on Oracle. Other database manufacturers, of course, also make that claim. But it seems to me—it seems to me like the independent tests seem to bear out that Oracle is the fastest and most robust database system out there, especially when you run it multiplatform natively. In general, I believe Oracle releases on Linux first. If you run Oracle on Linux, I think you will be very happy. It also releases after running for a very short time on Linux in release candidate mode. It is released in Windows shortly after. I have tended to run it on windows for most of my professional life. I don't know why. I'm just a Windows guy. I like windows. I'm comfortable with windows. Oracle runs very fast on Windows. I think if you run Oracle on Windows, you're going to be extremely happy with the way that it runs. I have absolutely no complaints whatsoever. I'm not saying that Windows is the greatest operating system out there. I just happen to know how to run Windows, how to deal with Windows, and I like Oracle on Windows. I like the idea of a very graphical user interface. So I think you should not be at all afraid of running Oracle on Windows if that's what your business calls for or that's what you want to do. Personally, I find that after the initial Oracle distributions are released on Linux and Windows, it soon follows with releases on several different Unix distributions and the Macintosh. So, once Oracle was released, it was released on just about every possible platform you could think of. It's not so true anymore. Now there are several different platforms that Oracle is released on, but you will find that of all the major platforms that are out there, Oracle will run on just about anything, including all the major distributions of Linux and the latest versions of Windows. And like I said, Oracle will even run on the Mac. Oracle even comes with its own built-in operating system; should you choose to run Oracle that way, it's got its own native version of Linux built into it, so you can actually run Oracle on its own. I have never actually done that myself. I've always run it on some other operating system; like I said, almost always Windows, but very often also Linux or Unix. I think you will be very happy, especially if you're migrating from another database system to Oracle. I think you'll be very happy with the way that Oracle runs. It is a very quick database system. Even if you're running a very large database, you'll be happy with the way that it runs on Oracle. In fact, if you're coming from another database system, for instance, something like SQL Server, where you weren't running a gigantic database, and now you've moved into an environment where you're running a database that's maybe terabytes worth of data, then Oracle is probably the way to go. There is a point at which certain other database systems tend to run down or slow down. Oracle seems not to do that. Oracle appears to run fantastically in terabyte-scale environments, and I have run in terabyte-scale database systems. In fact, I think, and I can't swear to this, but outside the US government, I have worked for a private company that has the largest database system in the world. I won't swear to it. That was their claim. I think they're telling the truth. So I have really worked in some very, very large database system environments. And every time that I do, in particular with one database system that claims to be the largest outside the US government, that database system runs on Oracle. And every time that I have worked on a very large database system, it's always been on Oracle. It seems like Oracle just does great with massive amounts of data. I have no problem with DB 2; I have no problem with SQL Server for small databases; I have no problem with Access. I like all the database systems that are out there. But I think Oracle is certainly one of the best, most powerful, and most robust systems that are out there. And if you're just coming to Oracle from another system, or if Oracle is your first database system, I think you're going to be very happy with what you see as we go through this course. So the idea behind this course is not onlydo we want to give you the basics ofhow to start on a career as a DBA. how to work as a DBA. Maybe you've come to us because you want some sort of refresher course. You've worked as a DBA for Oracle or some other system for a while. And now you want to get back to basics. See if there's anything you might have missed along the way. Great. We're going to cover all aspects of the Oracle database and the beginnings of working as a DBA in the Oracle system. And then our other goal, of course, is to get you past exam number one, z-zero-62, which is the first of the two database certification exams that are being offered in the Oracle Twelve C system. Okay, let's go back to the beginning, and let's go back to the relational database system, which was created by Dr. Ted Cod, e. F. Cod, who was a developer at IBM, who was a database developer at IBM. and in 1970 he published a paper. And as I recall, the paper was something that had a very long title. It had something to do with the concepts of relational database systems. A little bit longer than that, because it has a subtitle. But that's the idea. The idea is the concept of a relational database system. All major database systems in the world today operate on the same concept. and that concept is that data is somehow related to other data. It is perfectly fine, if you want to, to keep your data in a non-relational database system. And I'll give you the classic example that I always give, and that classic example is Excel. Excel is a great tool. And it's a great tool in which to put your data because it allows you to—it has cells, it has rows, and it has columns. The intersection of the rows and columns,of course, is what we call sells. The same way, in a large database system, the data goes into those cells. Excel is easy to use. It's easy to make changes to the system or to the data in the system. But Excel is a little bit different than a relational database system. And the reason why it's different is because even though we do have tables, that is, tables of columns and rows, there is no way for the columns and rows of each table to communicate with or relate to one another. That's what we call a flat file system. A flat file system consists of unrelated tables. It's great to store data on an Excel system. You can even do things like perform math on the data in an Excel system. You can add columns and rows. You can cross-check things. It's great. It's a great thing to have. But the only difference conceptually between Excel and a relational database system is that in a relational database system, the tables can relate to one another. Here's what I mean by that. Okay, so let's suppose we come up with an Excel spreadsheet, and in that Excel spreadsheet we store the names and telephone numbers of all of our friends. That is great. We can do that. We can put the name in the first column, the first name in the first column, the last name in the second column, and then the third column can be a list of all the telephone numbers. and that's fine. There is nothing wrong with that. Except these days, people often have more than one telephone number. In fact, almost everyone does. A lot of people that I know just have their cellphone number, and that's the only phone that they have. and that will work in our system. Because our system has a column of first name,a column of last name, and a column oftheir phone number, we can easily look up theirphone number, no problem, in Excel. But that's not very many people that I know because just about everyone I know has at least their cell phone number and their work number. So that's two numbers that we store. Also an awful lot of people still, most peoplehave a home phone number that's number three. Some people have a couple of work numbers, not very many, but some people have a couple of work numbers, maybe the direct line to their office and the mainline to the company, plus maybe a third phone line, which is to a secretary or someone like that. And then maybe they have a fax number—that's another telephone number that people may have. And we can store all these numbers in an Excel spreadsheet. That's no problem. We can have a home phone number, a cell phone number, a work phone number, a work two phone number, and a fax number. And who knows? Maybe people have other phone numbers. Aside from that, people nowadays can have anywhere from zero to an theoretically infinite number of phone numbers. We don't know how many phone numbers we have, but let's say person number one in the first row of our spreadsheet has five phone numbers. Person number two, the second row of our spreadsheet, has one phone number. So the first person is taking up several columns—five columns—of phone numbers. The second person only has one column worth of phone numbers. So, if you know anything about data storage, which I'm sure you do, you're watching this video. If one row takes up five columns, the second row doesn't just take up one column. The second row takes up all five columns. One column has some data in it for their phone number, and four columns are nothing but wasted space. This is not what we want. We want no wasted space because, as a general rule, a small database is a fast database. A fast database is a small database. So we want things to be as fast as they can be, which means we want things to be as small as they can be. Wasted space is not small. Wasted space is a great way for your database to become large. So we want to keep things as small in terms of rows as they can be. If all of our rows are together, or just say, "Follow my hands for a second," or just say "This way," then great. But if we start adding extra data onto one person's row, suddenly all of those rows have to be larger. They have to be wider. This is not what we want. Suddenly we become larger, and larger is slower. Databases need to be fast, so we need some other system. We can't just assume that because the largest row has 100 phone numbers, they take up space in every single row for 100 phone numbers when really all we need is space for one phone number. Along comes Dr. Tedcott, and his idea is: don't use the flat file. Don't use just one Excel spreadsheet. And I know, by the way, that Excel can link spreadsheets to other spreadsheets, and it does have a limited capability for that. But never mind that. For now, let's just talk about general tables within Excel. We can't link them together, so that doesn't help us. But we need some way to not waste space. Rule number one of databases: don't waste space. Database rule number two is smaller. Faster. Faster is smaller. So we got two rules here that we'vealready violated with Excel no knocking excel. Excel is great. It has its places, and what it does is fantastic.

3. DatabaseConcepts And Tools Part1B

But sometimes, especially when we grow larger as a company, we're going to need a more efficient and faster way of handling our data. So how about we do this? In our first table, we're going to have name, first name, last name, and that's it. Let's just have that on our first table. In our second table, we're going to have two columns. One is a list of all of the phone numbers. That's the phone numbers of all of our friends, customers, or whatever is on our list in our first table. And we're going to have a list of numbers; we're just going to have a number column. We'll also put a number column in our first table. So all of our friends, customers, or whatever have a number. And the second table will have two columns. One is a list of numbers, and the second is a list of phone numbers. just a long list of phone numbers. The first friend or customer is customer number one at the first table. The next customer is customer number two, then customer number three, and so on up to infinity. Possibly over in the list of phone numbers, though we're not going to number them that way. Here's what we're going to do. Customer number one from the first table is numbered one. We have a series of phone numbers in the second table, somewhere. They don't have to be in any particular order; they're just a list of phone numbers. and they have a number attached to them too. That phone is a collection. Maybe at least one of those phone numbers is going to have a number attached to it, and that number is going to be one. Let's call our first customer, John, the customer. He's got a number. We'll call it a primary key. His primary key in the number column is one. Let's look at our list of phone numbers in the second table and see how many of them have a column that is one. All of those ones are going to match John, the customer. The two tables relate to one another. John the Customer matches between zero and an infinite number of phone numbers. And how do they match? Because those rows in table number two have the number one, the side of the phone number, All of the phone numbers with thenumber one belong to John the customer. Second customer, Jane the customer She has a number two as her primary key. In the second table, we've got a couple of phonenumbers that have a number two beside of them. We relate those to Jane, the customer. So all the twos in the second table belong to Jane's third customer series of number three. in table number two. fourth customer series 40 to any number of fours That's how we're going to relate the two tables together. And we might have other tables that use the same connection method, maybe a series of credit card numbers. John, the customer, has two credit cards that he uses with us. We're going to store that in Table Number Three. How do we know that those rows with that credit card information credit card number, expiration date, address) are tied to that credit card? How do we know that this row relates to John, the customer? because it's got a number one in a column. We're going to call that, by the way, the foreign key. All the primary keys are going to be unique. They can't have more than one copy of themselves. They can't be more than customer number one onlyjohn the customer is customer number one only Jane. The customer is customer number two. Because we can't get their data mixed upin table number two and table number three. We may have several number ones in table number two because, by all means, John, we may have several number ones in table number three. That's no problem. All those rows relate to John. All the twos in table number two and table number three relate to Jane. This is the concept of the relational database management system, the RDBMS. And all this was invented or created by Dr. Ted Cod at IBM, and it was described in a paper that he published in 1970. And so we have several other databasesystems available to us other than Oracle,Microsoft Access, I talked about, IBM's DBtwo I mentioned, and Microsoft SQL Server. And then, of course, there's the one that we'll deal with in this course, Oracle. The current version number for Oracle is 12.C, which means there have been eleven more before it. And that's right. Oracle keeps a fairly simple numbering system. They started with number one and they have now grownto number twelve, starting at about version number five. Don't quote me on that because I don't remember exactly, but I think it was at number five they started adding letters. For instance, a very popular versionof Oracle was Oracle nine I. I stood for Internet so that the database could be used in Internet applications. It was easy with Oracle 9i to place your data on the Internet. Those capabilities still exist in Oracle 12 G. Excuse me, in database version ten and eleven, they used the letter G, ten G, eleven G. That stood for grid, for a grid computing model. The C in "twelve C" stands for "cloud," meaning that one of the great features about this version, version twelve of Oracle, is that it can be used on the cloud, and a number of servers can be linked together on the cloud. The database can be run on the cloud. So let's talk a little bit more about Oracle Twelve C. But before we do that, I wanted to mention that we've got a couple of flat file systems out there. We discussed Corrals, Quattro, and Pro, which are all excellent flat file systems, but Oracle is not one of them. It's a full relational database management system. And what makes it different is that it has a series of tables that link together and are related together. Are those tables flat files? Absolutely. They are columns and rows. But columns and rows in tables are nothing more than flat files. We link those flat files together by keys, with primary keys in one file and foreign keys in one or more other files, in one or more other tables. That's the gist of it. That's how the relational database management system works. That's how all major database systems work today. primary keys in one table being linked to foreign keys in one or more other tables. That's the concept. We will always have our data in tables, that is, in rows and columns. And remember, as I said earlier, the intersection of the row and the column is called the field. It's also known as the cell or the way. Way back in the old days, it was called the Tuple Tuple. Now we'll mostly refer to the cell. Non-programmers will refer to it as the field. If you want to call it the field, that's perfectly fine. I'll say that I'll use that term throughout this course quite a bit. But remember, that's the intersection of the column and the row; that's going to be the cell or the field thing. And I don't have a better definition than just to say that things in the Oracle database are called objects. Beginning at about version eight, Oracle began to market itself as an object-oriented database system. If you're familiar with the Java programming language or the C Plus Plus programming language, you're familiar with the term "object-oriented." Things are now based on objects in the Oracle database system. And like the tables that we've talked about, which are stored in Oracle, there are types of objects. There are lots more objects in the database system. In this course, we'll talk about indexes and views and stored procedures, which are chunks of code. These are all now objects stored within Oracle. These are the kinds of things. For instance, you, as a DBA, will create tables. Chances are, the developers will do it. However, in most organizations, in fact, in every single organisation where I've ever worked, the DBA was in charge of both table creation and table dropping, that is, table elimination. The structure of the table, for whatever reason, is always where I've worked. It's the DBA's job; I know in some organisations it's the developer's job. and that makes some sense. It's just generally not done that way. So we'll learn all about tables, how to create them, and how to alter them. Views and stored procedures Views are ways of seeing parts of a table (or tables) and ways of looking at the data. We'll talk about that. Stored procedures—that's a developer concept, because that's chunks of code, like I said. So that's something that you as a DBA will be less concerned with. and we won't spend much time with that. But my point is, all of those different structures, all of those different concepts, are all considered objects in the oracle. So objects are just concepts. But they're all different concepts that interact together to present data to the user. They store data, and they present data. That's what the database system is all about.

4. Database Concepts And Tools Part2

So as a DBA, you'll have a couple of tools available to you. A couple of them are built into the database, or at least they come with the database. You may be running the database on your home machine while you practise for this course. Practice for the exam, or just practise for your job. and that's a good idea. There are a couple of ways, by the way, that you can run Oracle in practise mode. I tend to do that, and actually I'm running this course in what you might call a practise mode. I guess it turns out that actually all of the Oracle database software is completely free and available to you for learning purposes, and there is no restriction on it. You can have it permanently. You can install it on your home machine or wherever you want to, and you do not have to pay for it. You can download a completely unrestricted copy. This is not a 30-day copy. You can download the enterprise software completely for free. Oracle is not the cheapest software in the world. So for learning purposes, this is quite a deal. And it runs on, like I said before, just about any platform. So if you want to, you want to try running this even on your home machine, and you might be thinking, "Oh my gosh, my computer is a couple of years old right now, and maybe if I can afford to buy a new computer or something like that, I'll do that." I'll run it on my machine at home. You would be surprised, actually. Well, for one thing, there are a couple of ways that you can run Oracle, and that's probably not going to be a problem for you, but you would be surprised. Oracle is actually not a very heavy install on your machine. If you have a relatively new computer, such as a Windows computer, this should work. You can probably run Oracle on your machine even in enterprise mode. There are a couple of different versions of Oracle; there's a standard mode and an enterprise mode. There are even a couple of what you might call, I don't know, "practise modes" that are just for developers that developers can just run their software against and see how it runs. Those are all available to you, and you can download them all at the Oracle website. I tend to run in enterprise mode. That's the full version of Oracle, the same version that multinational corporations run to store their data. You can download a copy of that. It's free, it's unlimited, and as long as you're just using it for learning purposes, there is a licence available to you and you may want to do that. As a matter of fact, it might be a good idea. As we go through some of the things we're going to do in this course, you may consider downloading either the standard or the enterprise version. As a matter of fact, let's say you're running Windows at home, or the Mac, or whatever you're running at home, and you want to take a look at the Linux version, one of the Linux distributions that runs Oracle, because maybe that's what you're running at work. That's no problem. They're actually virtual machines that are available on the Oracle Website. So you can download, say, a Solaris virtual machine onto Windows, load Oracle into that virtual machine, and run the virtual machine on your home machine while running Oracle within, say, a Unix environment on your Windows machine to simulate, say, your work environment. All of those types of downloads are available on the Oracle Website. They are all free. They are all available to you to use for learning and training purposes. and they're fairly easy to install. The Windows Enterprise version, if you want to run that directly on your machine, is a straightforward install. It's a straight runsetup exe on Windows, and you let it run. And we are going to use a few different developer training modules as part of our database. All of that is available on the Oracle Website for free. As a matter of fact, if you install the enterprise version on your local PC, a lot of that will install for you. So it's very easy to learn Oracle. At least they make it as easy as they possibly can for you to learn Oracle. Most of the developer tools are available to you for free, at least the ones that are available from Oracle. So let's take a look at a couple of those developer tools. One of the ones that I like to use is SQL Plus. SQL Plus is a command-line tool, and it has been around since, I think, something like version five. Don't quote me on it, but it's been around for something like version five of Oracle, and you can kind of tell because it's a command-line tool, so it's not meant to be run in any graphical environment. If you're running Windows, the Mac, or something like that, it will run in your graphical environment. In fact, that's the way that you'll run it. For example, on Windows, you'll run it from the Command Prompt in Windows. So if you want to take a look at it here, I took a screenshot of SQL Plus. You may think it looks like a mess as it's running on my screen because there's all this data all over the place. It's really not. It's actually a pretty simple screenshot. The problem is it's not formatted very well. But then I didn't set up my SQL Plus environment to format this data that's been output here. SQL Plus can be formatted in a variety of ways within Windows. If you look at the bottom of the screen, you'll see the command prompt for SQL Plus running in a command window in Windows, and you can see that it has SQL greater than that. That's what the command prompt looks like for SQL Plus. And above that, there is just a bunch of data. It's basically a data dump from one of the tables. It's not formatted well, but what you're seeing is a bunch of table columns from the Employees table, which is one of the sample tables that's available to you for training purposes. And then below that is the data that's returned. Like I said, this is running on the actual full-enterprise version of Oracle. I've got it running and installed on a simple PC. It's just a normal home PC, and Oracle runs extremely well and very fast. And it did not slow down my machine at all. So this is SQL Plus. You may not be all that excited about running a command line, window, type of command prompt, or type of software, but this software has been around for a long time. As I previously stated, it has existed since Oracle Eight. Don't quote me about Oracle Five. So it has been around for a long time. It is very, very easy to use. It is very, very easy to see what's going on. There's nothing happening behind some graphical environment, and it has a few nifty little features, but nothing so much that it would cluster what's actually happening. I believe that the vast majority of Oracle DBAs still use it at some point in their careers. I use SQL Plus all the time. SQL Plus is just one of the major tools in my back pocket as a DVA. I don't know if it's really my primary tool. I think I tend to come from a Windows background. That's sort of my background. I started out as a Windows programmer, so I kind of like graphical environments. So I don't know if this is the tool that I use most of the time, but I can tell you what: this is a tool that I use an awful lot of the time. And you should get used to it. You should play around with it a little bit. I'll show you a lot of things on it. But you should get used to this tool because, like I said, it's easy to use if you just want to check out your database, check out some of the tables, see if the tables are reporting correctly, and see if the database is running correctly. Start up the database. Shut down the database. Some of the very basic things that you'll do with an Oracle database, you can do with SQL Plus. SQL Plus is a great tool. When I'm working in Oracle, I can tell you that SQL Plus is always running. I've always had it available to me so that I can run some commands. I may have a big graphical user interface going on; I may do a lot of my work there. In fact, I tend to use third-party graphical user interfaces. So those aren't free, but I work better in a lot of those. They have a lot of conveniences, even when I'm running one of those. an expensive third-party graphical user interface. I've got SQL Plus running because SQL Plus takes all the graphics out of it. I don't have to worry about all the buttons and all the whistles and drop-down menus and all of that. It's just basic good old SQL plus. When I run something in SQL Plus, I don't have to worry about my graphical user interface getting in my way. I know that the results that are coming from the database really are the results that are coming from the database. So take a look at SQL Plus and get logged into it, use it, and get familiar with it. It's something I'm sure you're going to want to use in your professional career. Another free user interface from Oracle is Oracle Developer. The great thing about Oracle Developer is that it's completely graphical. In fact, it's written in Java. And as you know, Oracle now owns the Java programming language. Oracle bought Sun a few years ago, and when it bought Sun, it bought Java, of course, and all the tools that went along with Java. Oracle SQL Developer was one of the tools that Oracle wrote completely in Java. So it runs on any platform—any platform that has a Java virtual machine, which I think is probably any platform you'll ever work on. And it's a pretty easy-to-use platform. As you can see on the left window, we're looking at all the different object types that are available within this particular database. This is the same database that I was looking at before. This is the database that I downloaded for free, installed for free, and that's available for users for training purposes. for education purposes. I created the little table that's open right now that has my table ID, first name, and last name. We'll find out how to create tables. very simple thing to do in Oracle. Oracle Sequel Developer is a great tool to be used within Oracle, and it comes with the database. When you install the database, you will get SQLPlus, and you will get Oracle SQL Developer. In Windows. You just click the Start button, look for the Oracle set of applications, and you'll find both of those tools available to you. So whether you like the command-line interface, which I like because it's simple and you can see what's going on under the hood, or whether you like the graphical user interface, which I also like because it's much easier to use, although you can't necessarily tell what's going on under the hood, Those two tools will be available to you from Oracle. Another tool that will be available to you, although it's not installed by default, is Oracle Enterprise Manager. You can download it for free for training purposes, and at work, you'll almost certainly have it available to you as Oracle Enterprise Manager. Oracle Enterprise Manager is for administering the database. It's not so much involved with data, it's for performance tuning and things like that. And that's another tool that you should take a look at before you take the exam or before you go back to work. And it also performs a number of functions graphically that you could also perform within SQL Plus. But they're good things to know to be able to do graphically, because it's just a lot quicker and easier to do them that way. We'll talk about that later. Throughout this course, we're going to talk about things like tuning the database and creating the objects. All of those things can be done within these graphical tools, but it's good to know how to do them by hand. So as we go through this course, we're going to do all of those things by hand, or at least a little bit of it. There's a separate course, for instance, on performance tuning, so we won't do much of that. But we will need to know, for instance, how to create the objects, how to drop objects, and a lot of the things that you as a DBA will need to do, maybe on a daily basis. Now, as I said, there are also private tools available to you other than the ones that Oracle has developed for you. One of my favourites is Toad. been around for years and years. started out as a tool for Oracle application developers. That's where the name Toad comes from. It is not just for application developers. It's not a tool just for developers. It's also useful for DBAs and other Oracle database administrators. In fact, there are even different versions of it. I think the version that I show here on the screen is the actual developer version, because I use it a lot as a developer. It's not free because it's put out by a third-party developer. There are versions for Toad, for developers, for DBAs, and for other people. I think there are something like ten different versions, all very similar, but ten different versions of Toad. Now, I really highly recommend Toad, especially if you'll be doing some development work. It's great for DBAs as well, because it's a good graphical way to look at your database. It's really handy for developers. So if you plan on writing any SQL code or PL SQL code, it's good for that. Okay, so that's a general overview of what relational database management systems are and what Oracle is. We've got a look at some of the tools that are available within Oracle. We're well on our way to learning about Oracle itself, what it is, and passing that certification exam. Thanks.

Hide

Module 2: Memory Structure

1. Memory Structure Part1A

So there are several different definitions of a database and several different ideas of what a database is. If I had to come up with the idea of what a database is off the top of my head, I'm not sure how I would define it. But I can tell you exactly how Oracle defines it. And Oracle says that a database is a collection of physical files related to data storage. That's the database. Now, I know you may be thinking about the logical files or all the data as it exists in memory or within the files or something like that. Okay, I'm with you. I'm good with that. That's a good definition. And I'm fine with that definition. But all I can tell you is Oracle's definition. And by Oracle's definition, a database is the physical files related to data storage. So if you happen to come across that on the exam, don't think that you will. If you do end up taking the exam, I don't think you'll see that exact question on the exam. But if you do, it's going to be multiple choice. and the answer is going to be just these physical files related to data storage—that's the database. just the physical files. That's how Oracle defines the database: just the physical files. So if you do happen to see that question, you'll get it right. That's the database. Let's go ahead and talk about some of the other terms that are available within Oracle. And they're not unusual terms, by the way. These terms are pretty industry wide.I think the reason why they are industry-wide is because Oracle sort of came up with these terms. Oracle is a database model that has been around since the beginning, since the days when the idea of a database on the computer just came to exist. And if you hear some of these terms from other database systems, know that a lot of them were invented by Oracle Corporation. And some of the earlier folks who worked within Oracle and were developing some of these concepts—some of them are a little bit strange too—but a lot of them, strange or not, come from Oracle. Consider another software company, such as Microsoft. Microsoft, a great software company, makes everything from the world's most popular word processor to the Xbox. That is a wide product range for a company that calls itself that, and I think they're right about this, a software company. They are basically a software company, and that's what they do. They have an incredibly wide range of software products, and they also have some hardware products. They make an Xbox, and they make joysticks. Microsoft makes a lot of different products. That's one of the reasons why they are the largest computer product company in the world. The second largest software company Microsoft calls itself a software company, too, and they are the largest software company in the world. The second largest software company in the world is Oracle. And they really, although they do have a very large product line, mostly acquired products like, say, Sun Microsystems or other companies along that line. And they've acquired many, many—I don't know how many companies Oracle has acquired over the years, but they have acquired many companies. But nevertheless, Oracle is a data company. They really have one product, certainly one primary product, and that is their database system. It isn't, as of the time I'm recording this, the only database system that they have because they also have the MySQL or MySQL Database product that they give away largely because it was free software before they obtained it. But they really have one product, and that's the Oracle database system. It's their flagship product, and it's really kind of, in a way, their only major product. And it's been around now for twelve versions, and they started with version one and have given major version changes from one through twelve. There are no skips; there was no such thing as Oracle 2007. It's Oracle One, Oracle Two, all the way up to version twelve. And what's interesting, and I won't stray too far from the topic, is that we'll be back to definitions in less than a second. What's interesting is that they don't have a whole lot of minor versions. I mean, you don't see that there's no version 11.3.65. There is no such thing; there is no 11.6, and there is no 11. And you can take that back; going back generations of this software, going back generations, you're going to see that there is a 9.19.2, and actually it's not just that it will be 9.1.01, something like that, four digits; Oracle will always carry out to four digits. So, for example, in this case, you'll have 12.1.0.1, because the product line has always had only four digits. As of the time of me recording this, they're at 12.2. And that's where they usually stop; that's 12.2.0; don't quote me on that because I don't remember the sub numbers, but I know their version is 12.2. Historically, there's never been a version that's gone to zero three.What happens is they'll release the major version 12.01, something like that, and then there will be a major upgrade that will happen within a couple of months, 12.1.0 .1.And that's what happened, and that happened shortly after version twelve was released. Now we're at that 12.2-release version. That's the current release of Oracle Twelve C, and I would be very surprised if there were another subversion of Oracle Twelve that generally does not happen within the Oracle system. They do not have a whole lot of releases, not even major subreleases. There may be some minor releases here and there, but this is a very stable product. Yes, there are some web updates that will be released here and there, minor patches here and there, but no major patches will happen. And the software is just solid, so don't expect to see a whole lot of that happening. If you've got version 12.2 installed right now, that's probably, except for, like I say, the minor patches, about as far as it will go until sometime in 2345 years from now.Probably not that long. They don't have that long of a life cycle. Five years, I think, is about the longest life cycle. I've seen a version of Oracle Go, but in terms of major patches, that's probably where we'll see it. 12.2 is probably the last major patch we'll see until a couple of years from now, when we'll move on to Oracle 13. Anyway, sorry, back to what I was talking about. Some of the terminology within Oracle has another one of those terms, and this is a term you'll see throughout the database industry: schema scheme. The schema is the collection of database objects that relate to a particular user. Defining a user in Oracle is a little bit tough. The user is generally the collection of all the objects that are within—I don't know how to say it—a login. I mean, when you log into Oracle, you're logging into a schema. If you log out, then log in as some other user, you're logging into a different schema. Almost all types of objects, tables, sequences, and all of those types of things are all within a schema. That is, they all belong to a single user, not necessarily a human being. You could be logging in as the sisdba, one of Oracle's most powerful users. The Sisdba has almost all the privileges that are available within Oracle. The SysDBA schema may not be that powerful, though, because the schema may contain only, say, a couple of tables. Turns out the SISDBA schema has a whole lot of views and a whole lot of use for the data dictionary, which we'll talk about later. So it's a large schema in terms of the number of objects that it owns, but that's because it owns a lot of views. But it may not have any tables that are useful within your database. I mean, you, maybe as a DBA within your company, may have created, I don't know, 20510 tables. That doesn't sound all that optimised to have a thousand tables. But it's very possible. I've worked in companies that certainly had well over 1000 tables; in fact, they had thousands of tables. And that schema—maybe there is just a central schema that owns all those tables, that owns all the views, that owns all the sequences. That's possible. That's very possible. So you might consider that to be a large schema. But the idea behind the schema is that it is a collection of objects. and normally the schema equates to a login. When one logs into the computer, one is logging into that user's schema, that user's collection of objects. It's entirely possible for you, for instance, to share your login's schema with others so that others have access to the objects within that schema. very possible and very common. In fact, it's even possible for somebody to make an object available to the entire public, including all the schemas. So all that's possible But that's the concept of a schema. The schema is a collection of all the objects, and it usually equates to a login for a user. And here I've listed that it would include, amongst other things, tables, indexes, sequences, views, and stored procedures; and it could have a whole lot more than that because there are a whole lot more objects other than that little, tiny list within the Oracle database system, the Oracle instance. So when we talk about computers, and we talk about, say, an instance of Word, an instance of Microsoft Word, what do we mean? We usually think of one active version, one active copy of Microsoft Word. So we may have one document open and one copy of Word on a computer. It could be the same thing with Microsoft Access. We're running database number one in one copy of Access and database number two in a second copy of Access. Those are instances. Instances of Access, instances of excel. Whatever. Oracle is the same way. Running an instance of Oracle means that you're running one copy, which, although it's not a great term when it comes to Oracle, means you're running one Oracle database. So that's an instance. It's all the memory structures, I stayhere on the slide, all the memorystructures and processes that run in Ram. That's the Oracle instance. So how, in Oracle terminology, is that different than the database? The database is what runs, basically, not inside RAM but physically on the database itself. The database is physical. The instance is what runs in Ram. So we've got what's on the hard drive, the database, and we've got what runs in memory. So these aren't necessarily the common terms for these things, but these are the terms that Oracle uses. The database itself sits on the hard drive, and everything that runs once the database is running is the instance. All the memory structures, all the processes, the CPU, and everything—everything that's not running on the hard drive, everything that doesn't sit physically on the hard drive—is the Oracle instance. You may have several Oracle instances running on one computer. In fact, that's pretty common. You may have several Oracle instances running on several different computers and tie them all together into one big database system. So it's important that we try and make the terminology clear. And this is the terminology as Oracle defines it. Other database systems may have completely different terminology, but this is Oracle's term for the instance. An instance of Microsoft Word may mean something entirely different to you, but the Oracle instance means not the running program but the portion of the programme that runs in memory. The user process is something we'll talk about here in this section and also in the next section. We need to talk about this bit. For instance. Some parts of Oracle are running. like in memory. a little bit on the hard drive. but also in memory. because Oracle does several things. as you would suspect. It does several things all at once. Several different processes are in memory. Several different processes in Ram. It divides RAM up into several sections, and it's a little bit complicated. It's not just a question of, "We're running a little bit of the database in RAM now." It divides up the ram into several different things that do several different things. And we need to look at all of those because it's important for you to know those things—not just for your work life but very important for the purposes of passing the exam. So, let's talk about the user process. This is most likely Ram's portion. If you've got enough memory on the computer, it's RAM. I realise some of this may dump over onto something like what we would call a swap file. That is what happens when you run out of RAM and need to borrow a little of the hard drive to act as RAM. Windows calls that the swap file.

Hide