1z0-062 Oracle Database 12c: Installation and Administration – Module 10: Data Manipulation Language

1. DML Atomicity Part1A

So one of the downsides to being a DBA is that it’s a really, really good idea, if not necessity, that you also should probably know most, if not all, of the developers job as well. There’s a couple of reasons for that. For whatever reason, we tend to put, maybe I shouldn’t even say it, but we tend to put DBAs at a little bit of a higher level above the developers. Not that we don’t sit kind of in our own little offices somewhere, or in our own little cubes somewhere. And the developers kind of are usually a lower floor. I don’t know if they’re at a lower floor someplace, I’m just kidding about that. But the developers tend to come to us when they have problems with the systems. And they should, because we usually are the people who know how to fix the system. And I’m not saying that they’re the ones who break the system, they’re certainly not, because that’s up to the users, usually.

Just kidding. But we do have to know an awful lot. We are the troubleshooters, for one thing, and we do have to know an awful lot about the system. And part of knowing an awful lot about the system is that we have to know how to develop as well as how to administrate the Oracle database system. And that is just the way that it has always been. And it’s not going to change. Code is going to come to you sometimes, and you may have to, to a slight extent, troubleshoot it. I’m not suggesting that you go out and learn Java anytime soon.

Actually, I am suggesting that because it’s probably good for you to learn Java. It’s a great language to know, and it’s kind of at the core of developing in the Oracle system. But I do strongly suggest that you do learn at least the structured query language, SQL. I call it SQL programming language. Most people call it SQL, but for whatever reason, when I learned it, my instructor and my friends called it SQL. So I will call it SQL. Please forgive me if you call it SQL. So I think it’s a good idea for you to learn the sequel programming language. It’s not a full blown programming language by any means. It’s a fairly easy programming language. You will probably need it at some point in your career, if not all the time in your career.

I also, if you do want to learn a full blown programming language and you really want to round out your expertise in Oracle, I really strongly suggest that you learn PL, SQL, Oracle’s built in programming language. And I also really suggest that you learn Java. And Java is a complete full blown programming language, a complete object oriented programming language, because it comes up all the time within the Oracle database system. It has for many years at least. It has been solidly built into the Oracle database system since at least Oracle nine I, and it was even around and a good a strong part of Oracle before that. But now, of course, as you probably know, Oracle bought Sun Microsystems, the company that developed Java, and now Oracle owns Java. So we are seeing Java show up all over the place. You won’t see Java on your exams should you choose to take it.

You will see Java objects and seeing how Java interplays within the database system and its memory. And we’ve already talked about that just a little bit, and that’s all that you really have to know. But however, you do have to know an awful lot about SQL for this particular exam. And you do have to know an awful lot about SQL for your life as a DBA, chances are. So I need to take you through the SQL programming language. It’s not tough, don’t worry about it if you’ve never done any programming in your life. This is a great introduction to programming.

It’s very easy code to write and it’s just something you need to know. And you don’t need to know all of the SQL programming language for purposes of this course. Or I think for your job as a DBA, although I do strongly suggest for your job as a DBA that you do learn the sequel programming language inside and out. There are several great books out there on the sequel programming language. I always suggest the book Sequel for mere mortals. Great easy to read book as one of the ways that I learned. It’s been around for a long time because I’ve been around for a long time and I used it to learn sequel way back in the day. So that’s a good book to use. And I want to take you through the major parts of the sequel programming language and the parts that you may well encounter on the exam.

2. DML Atomicity Part1B

Oracle, as I said, is a transactional system. That is, it is a series of events that occur and end in either a commit, which means they all save, or a rollback, which means typically none of them save. So if our customer is interested in whether that customers package made it to San Francisco, and we know that the package must be shipped by air, let’s say from Miami to Orlando and then from Orlando to San Francisco, we’ve got two transactions there or two events that are occurring. We’ve got Miami to Orlando. Fine. And we’ve got Orlando to San Francisco. The whole thing has to happen before the user is happy. That is, before the user gets their package. So moving the package from Miami to Orlando is great, but it’s only half of a transaction. It’s getting the transaction from Orlando to San Francisco that matters to our user. Because if our user calls and says, I never got my package, us saying, well, it made it from Miami to Orlando, that is not going to make our user happy. In fact, it might make our user matter if we say something like that.

We care about the fact that our user cares about the fact also that the package made it to San Francisco. So the whole thing has to occur before we want to commit. And by committing, we’re basically saying, user is happy. Package got to user. If the package didn’t get to the user, if the package only got to Orlando, we might want to roll back and find out what happened, find out why the package did not make it to San Francisco. Transactions work as a package of functions. They don’t just insert by the row, necessarily. The whole thing has to happen, chances are, or the whole thing has to be rolled back. Oracle is a transactional database system. That’s why a series of inserts doesn’t do any good until that commit happens. And when writing code, the last keyword you’d place in, say, a series of inserts or maybe a series of updates or a series of deletes would be the keyword commit.

And if one of those transactions or one of those inserts, let’s say, doesn’t happen, let’s say, for instance, package has to go this is kind of crazy, but let’s say it has to go from Miami to Orlando to Nashville to Dallas to San Francisco. That’s a whole lot of package moving. But let’s just say that’s the way it’s got to work. That’s the system. It doesn’t matter that it makes it from point A to point B to point C to point D, unless it makes it to point final E, which is San Francisco. That’s the only thing which is making our customer happy and probably the only thing which gets us our money. All must occur. That’s the idea behind a transaction. If it all doesn’t happen, then it doesn’t matter that any of it, any of whatever came before happened. It’s the idea behind transactions.

It’s possible, by the way, to take Oracle out of transaction mode so that each step records itself to the database. That’s typically something you would never do, because it is important to have transactions within the database because life works in transactions. The whole thing’s got to happen. For everything to be successful, the commitment has to happen. None of it should roll back. Okay? Typically, also, we want things to occur in an acid format. That’s an acronym for four words atomicity, consistency, isolation, and durability. Let’s talk about all that atomicity. This is what I was just talking about. It doesn’t matter that the package gets to Dallas. Dallas doesn’t help us. That just makes the user mad, because the user didn’t get their package in San Francisco. So there must be a complete transaction. That’s atomicity. It’s one item. It’s one complete thing. In order for us to be successful, any partial completion is not a success. Getting to Nashville or to Dallas doesn’t help us. Atomicity means that the whole thing must succeed or the whole thing does fail.

Atomicity is the first element of a transactional system. It all must succeed. If only part of it succeeds, then the whole thing fails. Consistency. The entire thing must be identical from the beginning to the end. In other words, the package must move along. In my example maybe this isn’t the best example, but the package must move along. The package stops or changes in some way, then we’re inconsistent. There has to be some kind of consistency within the database. If the consistency doesn’t occur, I mean, if the book changes into, say, a gallon of milk, then that transaction has failed. What happens within the transaction must remain consistent. The package must move from city to city to city. Forward, forward. It can’t, in the middle, say, turn around backward and come back to Miami. What’s occurring must be consistent. That’s the C in acid isolation. This is easier to understand in technical terms. This is why the user who initiates the transaction can see the transaction, but no one else can. We don’t want other users to see what process the transaction is in.

We don’t want sales to see that the transaction is complete. Or who even cares if the package got to Dallas? Say that Accounts Receivable is looking at this. Customer service may be able to see it, and we may set things up that way. But Purchasing doesn’t need to see it, or collections doesn’t need to see it until the package gets to San Francisco because at that point, they know there’s been a successful sale. So in transactions, only the person initiating the transaction can see it until the entire transaction commits. So what does this mean to Oracle? Well, let’s say you and I work in a company. I’ve initialized the transaction packages to move. And typically, by the way, these transactions occur in milliseconds. But it’s easier to think of it as a package moving across the country. So I’ve initialized the transaction. The transaction is the package moving from step to step to whatever to San Francisco.

That transaction occurs in isolation. And this really happens in the database system. What I see is the package moving from step to step to step. My data will actually look different than your data. Until commitment happens, your data won’t show the package moving. I can see the transaction. You can’t because commitment hasn’t happened. And this is assuming commitment doesn’t happen until San Francisco. I guess typically commitment would happen at Orlando, commitment would happen at Nashville, et cetera.

But if the commitment doesn’t happen in my fictitious example, until the package doesn’t get until the package gets to San Francisco, no one but me, because I’m the person who initialized caused the transaction to occur, no one but me can see it. Here’s the issue. Never forget this. In a transactional system, data can look different to different users. This can be a problem because my table may look very, very different from your table. What’s even worse is, say the package is a book or something like that, and it’s the last one. I may ship a book off to San Francisco. And if this transaction doesn’t commit for a few daysvwhen the package arrives in San Francisco, you may the next day send that same book off to New York. So that’s a serious problem. So you don’t want transactions occurring over the course of days. They need to happen over the course of probably milliseconds. The commitment needs to happen fast. So although it does happen in isolation, this is actually true.

My database may look entirely different from your database because the commit hasn’t happened yet. I’m still looking at basically temporary tables. You may be looking at the hard drive tables, or maybe they’re stored in Ram, but you know what I’m talking about. Acid may cause tables to look differently. So be careful. This is an issue in a transactional system. Okay, then. Lastly, durability, which means if the system goes down, if somebody trips over the plug or whatever, you can’t lose. Data has to be there despite failure.

Data has to be there despite emergency. And Oracle has a lot of ways to make sure that you don’t lose data. We’ve already talked about redo logs, and there are many other ways to make sure that you don’t lose data. We’ll talk about that in a later section when we talk about backup and recovery. So these are the different elements of a system that meets the acid test. So is the acid test something that must exist in a database? Absolutely not. Is it something that should exist within an enterprise level database? Absolutely. An enterprise meets the Oracle meets that criteria. All of these things should be met to have an enterprise level system. What about undo? There are many times when we may want to undo data. How about my simple example, which is kind of elementary, but what happens when the package doesn’t make it to San Francisco? Do we want to undo the entire transaction? Well, maybe that may be something that has to happen.

We may just want to erase everything that’s gone by. We may want to wait until the package comes back from Dallas or wherever it got stopped. But there needs to be some method to undo what’s happened. And when would we want this? Well, for instance, and almost entirely on rollbacks. And fortunately for us, Oracle has a builtin system to do this. If commit is not reached, or at least Save point is not reached, because SavePoint is kind of a partial commit, then Oracle will roll back and then it’s a question of what do we want to do about the rollback? Chances are we want to just forget about that data. But there are some other times where we might want to care about undo. One of them that I talked about, and we’ll call that read consistency of DML and let’s say it’s read Inconsistency. Remember, I just sent the book to San Francisco. Five minutes later someone may send it to, to New York. Now we’ve got a problem. Who wins the battle? Did the book get to San Francisco or does it get to New York whose order gets canceled? There has to be some consistency within the DML, within the insert update and delete to the database because we need reality in the database. Something really happened. The book did hopefully get to San Francisco or the book did get to New York. We can only charge one customer theoretically.

So there has to be some consistency. So we’ve got to undo one of those transactions. In our case probably New York, and maybe in our case maybe San Francisco, because the book didn’t really get to San Francisco in our example. So maybe New York is the one we want to keep. But there has to be some way to undo the transaction that fails. Instance recovery. That’s when the database crashes. Basically the instance is that current instance of the running program. So if it fails, we may need to undo some data, maybe some bad data got in, who knows. And then lastly a thing called Flashback. Flashback is an interesting feature of Oracle that allows us to see the database as it existed sometime in the past. So maybe some corruption happened or maybe just somebody wants to look at the database as it was one day ago. And that functionality is built into Oracle. It can actually create the illusion of existing as it did at some prior point in time. We will talk about flashback in a later section and I’ll show you how to do it. Lastly, let’s talk about the redo function. We have talked a good bit about redo function. The redo function so far the redo function typically will occur automatically at some loss of the database system. And here’s, typically this is a dumb example, but here’s a typical example that I think kind of explains it. So you’re in the middle of transaction. Whatever is happening, you’re inserting into the database, inserting new orders, new rows into the database. And then my thing here, somebody trips over the power cord. I know you’ve got power backups, you’ve got UPS’s all over the servers, so that can’t really happen. But whatever. Let’s say we lose power because somebody trips over the cord.

Fortunately, every bit of DML that occurs to the database is recorded. Deletes and updates are recorded to logs. And those logs are able to recreate everything that happened, even though the transaction may not have committed. So if something is lost, say because we lost power or because the server exploded, who knows? Who knows what happened? But if some failure occurs, Oracle keeps a diary, and Oracle can go back and step by step recreate everything that happened. So transactions, for instance, cannot be lost. Data theoretically cannot be lost. And I know you may be thinking, well, wait a second, this is what backups are for. Yeah, I know.

And there are lots of backups, lots of backup systems that are available within Oracle. I said that a couple of minutes ago, and we will talk about them. But the wonderful thing about redo logs is they happen automatically. When Oracle logs back in, when it comes back on, when power starts again and it mounts, it checks to see what’s going on. Hey, what was going on when I lost power or whatever, when I lost the hard drive, whatever. When that happens, it checks its checkpoint and it says, am I consistent? Was all of my data recorded properly?

Or was I in the middle of something? Was I in the middle of writing to the hard drive? And did it commit? And if not, should it be rolled back? And what, what’s going on here? Let me check the redo logs. Was I right in the middle of something? And if it finds that it was, it immediately and automatically begins to restore itself and goes over those transactions again to see if they should be committed or rolled back. So the redo process is automatically built into the Oracle database system. And I’ll show you how it works later. That’s as much as I’ll say about it now, but it’s important that this section to know how redo works. All right, more of this stuff I said we’ll save till later. We’ll do that for now. Thank you.