Database Security is EVERYTHING!

[00:00:03]Good evening everyone. It is Ask Tom live again. Uh thank you for for joining us. Um if you've been having some drama getting into the Ask Tom platform uh whether it's just been down or whether it's demanding sign on stuff. We'll get to that shortly as to why we've been having some uh dramas. So the the fact that you're here uh pleases me immensely u because I want to make sure that basically you know we get as much fun and community content as we can. Um singer on the chat line if you can't hear me or see me. Hopefully I've got everything sorted out. Uh without further ado, let's jump into the uh session and see what we can work out.

[00:00:40]Okay. Okay, so hopefully you should still be able to see me and still be able to see my slides and still be able to hear me while I adjust everything so I can hopefully see the chat and the like.

[00:00:52]Dig it all over. Okay. Uh, getting in touch is easy. Linkree/ Connor has all my socials, etc. And my the QR code there as well, even with my ugly mug in the middle of it, should work to take you to Linkree/ Connor. Um, I've got the Conor McDera. my Twitter DMs, but I have to admit I'm becoming more and more disenchanted with Twitter or X as it's now called. Um, in terms of quality tech content, um, there's there's plenty of content on there, but quality tech content seems to be uh a dir. So, uh, I may change that to just being probably my the best way to get in touch with me is probably with LinkedIn. Um, that seems to be the place where some sanity at least still prevails. But let us continue on. [snorts] Uh if you're new to office hours, uh do not adjust your set is my way of saying don't be alarmed when you see the content generally shifted slightly off the slides. Uh this is such that when I put this into a video and throw myself in there to help explain things and wave my hands, etc., uh I won't block any important stuff on the slides.

[00:01:54]So some bits and pieces to start with.

[00:01:56]Uh first and foremost, uh I need to offer an apology.

[00:02:03]And the reason for that apology is fairly simple. Uh last month I got back from a couple of conferences. It was Thursday night my time. It was 9:00 p.m.

[00:02:13]I fired up Zoom for the office hours and I was ready to go and then I saw this.

[00:02:20]This meeting was scheduled more than 7 days ago and I realized that literally I was a week late. Uh so for anyone that tried to join office hours in June um on the correct day, uh my sincerest apologies. Uh I literally a week later came back and went, "Yep, okay, let's go." And yeah, realized I was actually a week late. So my sincerest apology for that. Um the second apology I'll make is uh the popularity of Ask Tom Live has led to some interesting anomalies.

[00:02:52]namely we're getting a sudden jump in denial of service attacks. Suddenly we've become attractive to the bots and the AI engines etc. So in the last uh week couple of weeks or so we've basically been trying to navigate our way through people trying to literally hack our site and denial of service tax etc. And as a result uh there's been some time when the site has been unavailable basically you know we've got traffic throttling on there which simply doesn't let people in so they can't do further damage. And um and the other one is interesting is um as a workaround we've also now um just temporarily hopefully put uh single sign on onto some of the pages that were being attacked most frequently by unauthor unauthenticated people. [snorts] So I know that's a little bit annoying. Um be aware that if you've already registered for any office hours when you get the email each month in terms be aware the Zoom URL is in there. So, as long as you go directly through Zoom and not try to go through our site, then you should be fine. So, oh, the chat just popped up already.

[00:03:56]Yeah. So, be be aware of that.

[00:03:58]Effectively, yeah, we've got this effectively system where we're doing our best to get back to a point where we can have all the pages open to the public.

[00:04:05]Um, as it always was. Uh, it's just sad that, you know, uh, you try to do the right thing by the community and you get some bad actors out not in our community sitting there trying to smash away at our site. But we will hopefully try and get that sorted.

[00:04:18]Lots of goodies have come out uh in the last month. Number one is uh there's a new SQL developer VS Code extension 26.2 that came out a couple of weeks ago. SQL CL also has a 26.2 come out. Um some key features there are basically um some hooks into OS in terms of improving uh the MCP server and also uh Apex Lang.

[00:04:40]That's the big one. Okay, Apex laying for Apex 26.1.

[00:04:44]26.2.1 came out of Odds. Uh, if you're on 26.1 or below, I'd encourage you to get 26.2.

[00:04:51]Uh, that's got some important security fixes. But this is the big one, the ability to actually run effectively a a streaming or HTTP version of the MP MCP server, not just on cloud, but on prem as well. Uh, Apex 2126.1 has come out as I mentioned a couple of months back, but just to let you know, there's been two patch set exceptions since then. So, head over to the apex.oracle.com, click on the patches section, and you can download the patch uh lots of bug fixes there. Huge release 26.1. So, I'd expect to see some patch set um bundles come out soon to fix um encounter bugs.

[00:05:28]Uh I put this graphic up there just to let you know that things have changed once we get into this um 20 year year of 2026 release cycle. Apex ors and SQL CL are now effectively I put there bed partners. So they will need if in terms of your organization the days of oh look I'll just sit on this version of for a long time and regularly update Apex or I'll regularly update ORS and just sit on Apex um are probably coming to an end. You can probably treat these three things as effectively being a single bundle. Now, so as the next version of Apex will come out, there will be a accompanying version of ORDS and potentially accompanying version of SQL CL and you should plan on putting all these three things along together because they're designed to work in conjunction. Now, um because of Apex is very much building applications now from flat files using Apex Lang, it's got some hooks into ors and hooks into SQL CL. So, I'd encourage you to start looking at when you're planning on upgrades, having those three things stay in alignment as you move through the rest of 2026 and 2027. So, um just be aware of that. Okay, chat line.

[00:06:39]Uh someone said, "I think one must consider SQL Plus well below SQL CL features at this point." Um I agree. Um funny enough, I actually put a blog post out the other day. People said, "Oh, what tools do you use?" And I said, I use SQL Plus, SQL CL, SQL Developer Classic, and SQL Developer VS Code. And like there's a classic thing where I simply use the the tool that's fit for purpose. Like simple as that. So um there's no need to It's funny how people get into religious debates about which one you should always use. Um I use various ones for various tools. If I'm loading a CSV, SQL Seal is my go-to. If I'm unloading the CSV, SQL Plus is a reasonably amount faster. So as I said I just pick and choose the thing that suits me best for the requirement at hand and I encourage people to do the same. But yeah in terms of functionality depth SQL CL is obviously vastly superior to SQL plus uh because it has these hooks into Apex and liquid basease and projects etc etc. This one this is an interesting one for you. We said obviously a long time ago that SQL developer classic the Java application would no longer get any updates after I think 24.3 I think the version was. Uh we have released a new version. Now when I say new version what it is is some of the Java libraries in that release have been exposed as having security issues and so we've simply upgraded those libraries. So functionality is unchanged. We simply pop this one out such that to make sure that no one's going to be impacted by security issues with various you know third party libraries etc. So if you are using SQL Developer Classic still, as I said, I still use it regularly. Uh yeah, make sure you go go grab a fresh copy.

[00:08:16]Um it has to run on JDK17. Now, um quick tip for me, I use the um Growl version of JDK17 and I find that to be I know a little bit subjectively a fair bit faster to actually start up and run SQL developer as opposed to the standard Java runtime. Anyway, food for thought.

[00:08:34]Um as I mentioned um and taking up on the on the point of the fact that we've got um you know superior functionality in SQL CL uh our Oracle skills are regularly being updated. So I encourage you to regularly pull them down to your codeex cla and client etc environments.

[00:08:52]Uh if you've got SQL CL installed very simple you just type in skills sync and it knows where to go get the skills from and it knows typically where they're going to be installed on your machine and it'll try to plunk them in the right places. So skill sync is just a one-stop command to actually re regularly refresh your Oracle skills. Um, and also I'd encourage people to head out to the GitHub repo, have a read through the Oracle database skills. Uh, if you're a database person, if you're an Apex person, have a read through the Apex skills. We want to build effectively a collective set of knowledge effectively uh you know best practices from customers such as yourselves all over the world to such that everyone ends up building great database applications founded on the knowledge of effective a global community. Okay, what else have I got popped up here? Q&A said is there a release date for 26.1 on ADB? Um, I think a blog post or email went out just recently. What you'll see is it's it's already in place rolling out. In fact, on my free ADB1, which is based in Australia, I logged into Apex and it pops up and says, "Hey, 26.1 is available. Do you want to upgrade?" So, um, I think it's actually on it's actually in progress at the moment. Um, obviously it doesn't happen to every single server all at the same time, but it has commenced. So, um, keep an eye on your on your ADB versions. It won't be too far away, I'm sure.

[00:10:18]Yeah, I said in free tier, yes, but commercial versions. Yeah, it's coming.

[00:10:21]It's coming. Uh, it makes sense to go free tier first because the I would say the risk, you know, the risk issue is far lower than people who are actually paid customers.

[00:10:34]Where you up to? Oh, so what have I been doing? Um, so in June I was at the Ascend conference. Caught up with, you know, many people there. It was great.

[00:10:42]Pro tip, if you're running a conference, this conference, they had a puppy petting zoo and um in the exhibition hall, I got to tell you, if you're a vendor and you want customers, this is the single most effective way to do it.

[00:10:54]Man, we could not get close to those little doggies. Um KCO, great event. Uh we had a, you know, huge Apex Symposium day on the Sunday. Caught up with lots of people there. It was in the middle of the World Cup. So, they did a really good job in basically setting aside time for people to actually both network but also watch the game etc. This last picture here, second last picture here I think is uh the women in technology lunch breakfast. So, that was a great initiative they do each year at Kcope where they basically invite women in the IT professionals to come um sit around.

[00:11:23]Men are invited as well um to actually just you know hear what it's like to basically some of the challenges that women face in the technology space. And literally just three or four days ago I was in India for the Sand Game Conference. Um a little bit of touristing as well the day before. This was the I think the first ACE dinner um at the Sand Game Conference. So we had about 60 aces there, ACE Associates mainly. So that was a a good fun thing.

[00:11:46]We had an event in a conference was held in university and the university put on things like dancers and choirs and you know it was a really interesting things you wouldn't normally see at a conference. Uh these are some of the volunteers involved and this is the event at Hyderabbad. So across the two venues I think we had about 1,200 people over the weekend. So yeah um the community in India is absolutely buzzing.

[00:12:11]No. So I'm just talking to my dog. You can't go outside. Just sit sit on there for the time being. What's coming up with me? Um LA O. I'll be in Latin America in August. Um hopefully I can see you there. And all these things are once again saying please support your local user groups wherever you are.

[00:12:29]Reach out to them. Be a volunteer if you can but if not maybe give a talk otherwise just head along and attend. Um that's how we grow our community. Okie dokie. What have we got to talk about tonight? We've got Aura 4068 active historical data options deep data security assertions and determinism number of extents alter table and flashback fine grained dependencies.

[00:12:49]Lots of cool cool things to talk about.

[00:12:52]Okay, let's start with number one. Aura 4068. Who's going to be brave and throw an answer in the chat before I mention as to what Aura 4068 is? If you're a developer, you've probably hit it in your life. Anybody? There's anybody there? Three, two, one. We're moving on.

[00:13:12]State change of package. Well done.

[00:13:15]Here's the question that came in.

[00:13:18]I think I understand why Aura 4 4048.

[00:13:21]No, I'm pretty sure it's 4068. I think this I should never cut and paste questions that come in. [snorts] I think I understand why Aura 4068 is necessary.

[00:13:29]But how do I avoid it? We currently catch it with an exception handler, but that doesn't seem to be a sustainable option to put in every single package.

[00:13:38]So, I'm going to present a couple of argument, a couple of things here. And one of the things I will say is the exception handlers for Ouro 468 is generally a big no no. and we'll describe why in a demo. But your best option in my experience has been good coding discipline. Now, that sounds like I'm sort of, you know, just being a school teacher and saying, "Yeah, you should do better." Um, I'll show you as to what I mean by that. And it's not too hard to do. Certainly, it's an easy thing to do when you're building applications and it's not too hard to retrofit to applications to help insulate yourself from this issue. So, let's do a demo and see how we go. I can't see my keyboard without my glasses.

[00:14:20]Okay, hopefully everyone can see that.

[00:14:22]Okay. Um, in terms of seeing out the chat line if it's not big enough. So, I'm going to create a package here called package one just with a procedure called P. And I'm have a package body that aligns with it. And the thing that generates aura 4068 is the fact that when a package maintains state and what do we mean by maintains state? It means you've got some sort of variable in there that is held for the duration of a session, not just for the duration of a call. So in this case here, for example, I've got a global variable defined in my package body, which means every time I do an operation to it, the value of that is held for the duration of the session, even after my package call finishes, unlike a local variable that would normally just evaporate once the call is complete. So we can see here basically I'm initializing my global to zero and every time I call procedure P, I add one to it. So let's give it a go. I call my package procedure once and my global got set from 0 to one. I call it again, it goes from Z one to two. That makes sense. So here's where things get a bit interesting. What happens when it's time to actually make a change to that package? So as we know, packages are code. Code changes your iterations of software. So in this case, I've actually just recompiled the package literally as the same. In fact, I actually just what did I do? put a null in there. So, I made a tiny change to the code. Didn't touch the variable, but just made a little slightly change to the source code. The reason I made some sort of change is Oracle was quite smart in ever since about Oracle 11, maybe even earlier. We look at the package and if you compile a package and the and the source code is identical. We treat it as a no. Nothing happens and so nothing has changed. But in this case, I've made some change to the code. If I go back to session one, there's my aura 4068. Now, the question is why? Well, I'm trying to run my package, but there's a new version of it that's just been loaded into the database. The reason I really have to just bomb out here is because I have no idea what the state of this variable is.

[00:16:21]It may well be the case that variable has disappeared from the code. I may have removed it. There is no way of maintaining the state of that variable that has been initialized for the duration of this session. By definition, I have to throw [snorts] it away plus any other things that are holding their state across the session. So, I spit out an Aura 4068.

[00:16:41]Not only have I raised an error because it's illegal to call this, I've now literally flushed out the state and therefore I could actually start again, so to speak. So, if I run it now, it's now actually initialized back to its opening state. So, this is really just saying I threw away any state you currently had and I've started from scratch. And so, this is the thing that people try to work around.

[00:17:05]The original question came in saying we're using exception handlers. Let me stress this is something I think you should never ever [snorts] do. This is a common workaround I see. They go I'm now worried that when I call this I might get some sort of error and they simply sticker when others then null in there.

[00:17:21]Now you don't have to have just when others. You could put a 4068. You could be quite targeted in the error. But if you're capturing that 4068 and then simply saying yep I just want to ignore that. Here's the damage you've done almost invisibly. My package state here is two. Let's go over to my other session. And once again, some other change. So, I've made another change here effectively just to change my code.

[00:17:44]If I come back to here, oh, my error's gone away. That's fantastic. Isn't that cool? No longer Aura 4068. And I can continue it running and it's all fine. Looks great. But let's look at our code. Oh, I've lost my my source code. Where's this one? My source code has a DBMS output in it.

[00:18:03]It's meant to be spitting something out.

[00:18:06]But if I come back here, where's my output?

[00:18:10]The exception when I was a null, yes, it's ignored that state violation, but as a result, my state has not been reset. So now I've got this giant mess in the fact that my application looks like it's running fine, but it's actually broken. So this is something you want to be avoiding. You don't want to be simply capturing that state violation and ignoring it because we've never had the error and therefore it thinks everything is fine even though I've blown away my state. So not a good option. So this is the first workound I would recommend. The best way to basically handle this error is not to get this error. And this is something I recommend as a a a development standard.

[00:18:51]If I'm going to have a package called pkg1 and I know it needs to reference some global variables. needs to hold some state. Have a partnering package like a sort of a companion package which stores the globals. So this package is called pkg1 blob and that's where I'm going to store my global variable.

[00:19:10]Now I create my package as before.

[00:19:12]There's the package header. There's the package body. But notice now I reference this separate package to do my global work. This gives me the benefit that there is no longer any globals defined in this package. And because this is where my logic is, this is where most of the code changes are going to happen over time. Yes, occasionally I'm probably going to maybe want to change this, maybe add more globals, etc. But for the vast majority of times when I'm just doing code maintenance, I'll just be doing it in this package here and more likely just in this package body.

[00:19:43]And therefore, the globals never get changed. And then I'm going to preserve my state. So in this case, for example, I've now got my companion package which stores the global. So in this case, I run it here. I go over here. Whoops.

[00:19:56]I changed my package body to something else. I go back to session one and it's fine. And I never lost my state because I never changed the pkg1 glob package, the one that held my globals. So, this is the advice I give to anyone building applications. The moment you have globals for a package, stick them in a different package. That's where you want to be going. Okay? And you can see it all works just fine. Let's go back to our slides.

[00:20:20]So, your best option, good discipline.

[00:20:24]The other option you can look at doing if you are using additionbased redefinition, this solves a lot of your problems because what happens is you have an existing package in version one of your application. When you're deploying code changes, you're not going to be changing that package. You're actually going to create an addition version two of your application and deploy your new package into there. And then what'll happen is effectively as people reconnect as the brand new session over time, they will simply pick up the new version of the package.

[00:20:51]Therefore, this issue simply disappears.

[00:20:54]Now, I can see that a lot of people aren't using additionbased redefinition, but if you're doing, you know, a lot of, you know, the more modern code cycles, which is continuous integration, where you're deploying code repeatedly, this is a great way, great time to look at it. A lot of people think that using EBR means I have to totally rearchitect my entire application plus everything in the stack, right? Such that I have zero downtime. I have to have cross addition triggers and addition views, etc. That's great if you really have to go that 24 by7, but there's nothing wrong with just using additionbased redefinition just to manage code evolution and leave all the rest of the stuff, you know, the same. So if you were changing tables dramatically, you would still take an outage in the normal way, but for code changes, you could use EBR. So something to think about.

[00:21:37]The second thing I want to talk about is in version 26 AI, we've made some improvements in this area. So let's have a look at some of the things you can do.

[00:21:50]So I'm going to recreate my package now.

[00:21:52]It's going to have a global variable in it, but it's also got some initialization code down here. So I do have a global variable and you know I'm not following best practice here. I haven't put it into a separate package.

[00:22:02]I've got a global variable here which is obviously going to create me some 4068 concerns and I initialize the code. And it doesn't have to be an initialization block. What I'm really doing is effectively developing an understanding in my code that I will be managing the handling of this variable. So when this variable needs to be referenced, it's my task to actually say, "Yep, I'm going to handle initializing it." Now, the database obviously doesn't know this. So if I create this, I'm selecting old server into my mail server, right? I run it and it says, "Yep, I've initialized my code." I go over to here and run PK2A. I've made a change and we're back in the same boat because I've changed my package. I get 4068.

[00:22:47]These are the ones which frustrate developers because the developer goes well hold on you know I'm committing to looking after this right it's my job it might be in here I say you know if the mail server is null then initialize it wherever it is in this package I've already sort of undertaken the effort to say if I lose track of this it's my job to reinitialize it to a working value but I'm not avoiding this. So this is what we've added to 26AI. We now have a keyword saying a package is resetable.

[00:23:14]It's really you telling the database that yep I'll be able to reset the global variables to values I require.

[00:23:21]Leave it with me. So you don't have to give me a 4068 because I'm undertaking that responsibility. So here I have I've reset and now my package is resetable and I've built the package as the original version oldmmailerver.com.

[00:23:34]I run it which initiates my initializes my state. If I come over here and I've made a change this time it's called new server.mmailal sererver.com because the package is resettable.

[00:23:46]I did indeed get a 4068 but I simply swallowed that up because I've told the database, yep, I'll look after resetting the state. And you can see it actually has flipped over to reinitialize that variable. So in 26 AI, as long as you're prepared to undertake the responsibility for doing the initialization if a 4068 happens, then the resettable keyword lets you avoid the errors. That's a pretty cool little touch.

[00:24:11]Any questions on that one? Nope. Good.

[00:24:15]How can it be 25 past the hour? Dear me, disaster.

[00:24:21]Okay. Active and historical data. This one's just some slides and a demo, so hopefully I'll be able to get through it a little bit quicker.

[00:24:29]Here's the question that came in. We currently have a large table which is holding temporal data. We use the familiar valid from to date mechanism to identify temporal ranges. We are worried about performance problems as the table grows. Thoughts on separating the data.

[00:24:47]So rather than doing a simple temporal, this is when I dragged um from effectively a customer a anonymized version of a customer I worked with in the past. And in terms of temporal data, what they had was an order date. Well, sorry about that. What they had was an order date. So every order that came in had an order date and over time eventually that that we had with we let me start again. Let me start from the top. We had customer orders. Every order that came in had an order date and that stayed for the duration of the order.

[00:25:19]And eventually once this order got to the point where it had been, you know, dealt with, managed, shipped and the customers received it and signed off on it, we will never ever touch this date again, this data again. And so there was a finalized date. So anything with a finalized date effectively was stamping this record as never to be touched again because it's now fit to be fixed in stone. [snorts] So this is the temporal version here where when you've got an order date and not a finalized date, the order is active in our system. And once a finalized date arrives, then effectively this is becomes part of the history of this table and its application. And obviously over time you end up with a fairly constant amount of active rows that just have an order date and no finalized date. and the number of finalized orders just grows at infin item over time. So what are some of the things you can do? I said 90% of the data. Some options you can consider.

[00:26:10]Number one is do nothing. And I stress this first because don't get me wrong, it's very good to do some planning and thinking as to what might happen. But there's a difference between planning that out and being convinced that you're going to have problems without being able to prove it. When you look at the kind of things that this this application's going to be doing, people are going to be coming in for orders, like giving an order ID. That's going to be relatively efficient no matter what you do because it's generally going to be a primary key lookup or a very small range scan. Or they're going to come in for things like an order date. And most typically people come in and say, "Show me all the orders that are fairly recent." It's rare in an application to say, "I need all the orders for the last 3 years." that's a customerf facing part of the application. Your administrative team, your trend analysis team might be doing that, but in such cases, you probably do want to actually scan vast amounts of the data anyway. So, the first thing you want to do is, do I actually have a problem?

[00:27:10]Second one you might want to look at is partial indexing.

[00:27:14]Assuming that people are always looking for active orders perhaps on different predicates. So rather than looking for orders based on order ID, they might be saying I want to see all the orders for a given product but only active orders.

[00:27:26]In that case they're going to say and finalized date is null. But obviously if you've got an index on the product ID column, well 90% of that index is for finalized orders. That index is huge and for queries like this isn't really going to be that useful. So one of the things you can look at doing is using functionbased indexes to partially index the data. In this case I'm simply saying when the order is active i.e. the finalized date is null then I want to bring in the product ID. If a case is false it falls through to null which means this expression will be null for all the finalized orders and nulls do not form index keys. So what you end up with is you're only indexing the active orders on product ID. Now the downside with that is what you have to do is then obviously match this expression here with your application. So your application now needs to put this case expression into your queries um to make sure it actually can match up with this index keys. Otherwise it'll just go back to doing a different index range scan.

[00:28:28]One of the nice things in 26 coming soon is partial indexing. So you'll be able to actually create an index with a wear clause. And that'll be really really cool because then you don't really have to change your application code. It will know that effectively this and this are matching predicates and therefore this this index here can be used to satisfy just this query. So that's coming up hopefully in a future release update.

[00:28:54]Safe Harbor as always. But yeah, we'll be having partial indexes where they're done declaratively, not using function based function based indexes. That's pretty cool.

[00:29:04]The second option is partitioning.

[00:29:05]Sorry, second option. There's a big three there. The third option is partitioning. And that is if you partition by finalized [clears throat] date, then what you can do is break up your finalized orders into for example yearly cycles. And therefore, your active orders are all the ones with a null finalized date will simply be less than max value. Uh nulls sort after all the date values in a partition table. So all the active values would simply slot into there. that lets you take advantage of doing some things when you say where finalized is null, the database will only ever scan these this active position here. Now, one of the things you'll probably want to do is add a enable row movement because every time if your existing application simply doesn't update to set the finalized date to a non-null value, you need to allow the database to move rows from this max value partition into another one, its appropriate year. If you don't do that then you get an error when you're trying to update the max finaliz state to not null.

[00:30:03]Similarly, if you prefer something a little bit more um well of documented you could actually once again add a um virtual column which does the same case statement. If the finalized data is not null then we call it finalize otherwise we call it active. Make that an invisible column the status column visible so it doesn't impact your existing application code and then you could actually partition on this. So now you've simply got two partitions, one for all the finalized data and one for the active data. Maybe you don't want to have it by year, etc. So there's plenty of options you could explore. Either way, once you've got partitioned tables involved, what you can do is you can do partial indexing at the partitioning level and that can be done declaratively. I can say for my final partition, this one here, I don't want indexing index segments. However, for my active one, I do want indexing segments.

[00:30:51]And the way you activate this is when you create your indexes, you simply say I want them to be created as partial and only the active partition will end up with a associated index segment. The final one will not. Once again, a way of having very small compact indexes that are appropriate purely in this case to all the active orders. And the last one which is a little bit more work but I have seen very successfully used is actually literally separate tables.

[00:31:16]You'll have an active orders table, probably just called the orders table, which has the all the columns as before, and then you'll have a finalized orders.

[00:31:24]So now your code has to manage the concept of moving data from the active um table into the finalized table when someone updates the finalized date. Now you might be wondering, well, why not just use partitioning for that? Well, there's a couple of reasons. One is you might not have a partitioning license.

[00:31:39]But the other one I've seen very successfully used is people say, "Well, once a data gets moved into my finalized table, I can append lots of other information that I might have had to normally look up through other normalized structures, etc. Once I know a a record is coming into this finalized table and I'm pretty sure it's it's basically you can think of it as being read only, it's never going to be changed again. Maybe it's a good time to go pick up other metadata associated with that order that normally I would have to go digging into other tables for. So effectively once you separate into two tables you don't have you're no longer bound by having a common structure between the two. So there are opportunities there to actually do more work as you finalize rows. Therefore making downward processing whether it's you know trend reporting analysis etc much more efficient. So there are just some ideas that I thought you might want to play with when it comes to managing effectively temporal data. Uh to manage that thing between two tables you typically have to have real-time population. So triggers need to manage it. Um, and for things like batch population, you would have an overarching view. So, if you have a batch process that says, I need to see all the orders, it's going to be a view that basically does a union all between the active orders and the finalized orders.

[00:32:48]The reason I would say these things are certainly potentials is now that if you're on 12C and above, which I certainly hope everyone is, with 12C and above, a lot of these operations, moving from a non-partition table to a partition table, effectively evolving from one table to another, uh, can all be done online with minimal disruption to service. So, it's not like if you're doing some of these mass big changes, you have to take huge outages. And that's why I mentioned back at the first I said don't have to solve these problems before they become problems.

[00:33:19]You know the planning is good but then you can actually just monitor it and if you start to see degradation then you can start looking at tackling these problems because you can do a lot of the operations online.

[00:33:30]People in the Q&A I always look at the chat.

[00:33:37]Okay. We often get all four errors with the state chains. I think that's mostly true. Uh yes, that's pretty much correct. Um effectively 4068 is your everything is valid but your state's gone. The other ones are generally basically you've compiled the application and it's for example the package is no longer valid um or for example privilege to have been lost etc. So they could be a bit slightly more um catastrophic.

[00:34:03]And what we got? What about materialized views on commit for finalized active records? um you could do that but generally if you're what people are worried about this being a problem I would imagine these tables are fairly large in size and you do need to take a lot of care when using oncommit materialized views for large tables because all you have to do is have you don't have any control over the refresh code and the risk there is if the refresh code one day decides to not be driven by the materialized view log and simply ends up popping into the table uh you could get a full table scan and that obviously destroys your commit performance. But um yeah, I'm I'm not dismissing it out of hand. Um just need to take care when using materialized views on an on commit style for large tables.

[00:34:47]Take off those.

[00:34:55]Okay. Deep data security.

[00:35:04]I saw some blog posts on deep data security. I stress this is a 26 AI feature but it seems that we have to have Entraa Enterprise Direct identity to use it. Doesn't this limit the feature to Microsoftbased customers only?

[00:35:18]The answer is no. Um and it's I I can see that a lot of the literature out there blog posts um you know YouTube videos wipe etc at the moment done by Oracle very much are yep here's how we do it with you know a an identity based system whether it's the one on OCI or Microsoft's etc and and certainly we've designed this feature in mind to have really good integration with enterprise identity architectures so it's very very useful but it is not actually essential Sure. So for those that haven't seen deep data security, I thought I'd do a little demo here in 26AI that doesn't touch anything you out in the enterprise world. So just to show that you can do it even just in the database. Uh but clearly having it associated with enterprise um useride management is useful. But let's give it a go.

[00:36:10]So I'm going to start with a very very simple table called Scott.2. As we can see there is eight employees in our table. imaginatively named it demo one to demo 8 and the first four employees are looked after by manager number two and the next four are looked after by man manager number one. Now there's typical kind of requirements that you would have for this kind of data because we could argue that anything which has someone's salary is sensitive data and so here's the requirement I want to secure it and the specific requirements have managers versus employees.

[00:36:42]Employees should only see their own record and managers should see the people that report to them, but not the salary for those people that report to them.

[00:36:51]Now, here's the weird thing. When it comes to securing database objects, obviously, you know, we've always said for the last 30 years, when you're securing access to objects, you should definitely use database roles and privileges to do that. And that still holds true. But of course, that does not reach into an object to actually control access to the data. So back in or I think Oracle 8, we said, "Oh yeah, that's fine. Database roles don't work for that particular requirement. What you need is DBMS rowle security." Now here's where you have to sort of make a bit of an apology here because in reality we used to say that then we said, "Oh no, no, no, stop using that.

[00:37:26]We've got a better option now. We've got fine grain access control." And then no, stop using that. We've got a better option now. That's called Oracle label security. I think this is what this refers to. And then like no, stop using that. We've got a better option now.

[00:37:38]It's called real application security etc etc. You know eventually customers are going come on man give us something that's concrete and is not going to change and the drama with all these things all these things work fine but typically they're PLSQL based APIs and people have been you know that's obviously generally harder to manage because it's also harder to get generate the DDL back out you know for those kind of things from the database and the other one is when you're using PL/SQL APIs sometimes get picking up information up as to what you've done from the data dictionary wasn't particularly easy. People are saying well everything else about the database security in terms of grants and whatever and privileges and object is done declarative with DD declaratively with DDL surely all these things should be done with DDL as well and so that's what we've done. So when it comes just to standard privileges the ability to for example connect to the database create tables etc that's unchanged. So I'm going to create a role called a standard database role. Nothing different there. And that's the role I'm going to use to do things like privilege control. So in terms of access on privilege levels, standard database roles are fine. However, we've now got a new kind of role called a data role. So as opposed to a role being access to objects, a data role is about access to data inside objects. So I'm going to have a data ro called employee role. I'm going to pad this out later such that I control what data an employee can see.

[00:39:09]And I'll have the same thing for a manager role.

[00:39:12]Now, here's where the link to enterprise uh identity, you know, management systems comes in. One of the nice things we've spent a lot of time on is anything which is effectively a role that's going to control access to data.

[00:39:26]We have extensions that let you map these things to the equivalent role or user account, etc. in some sort of global identification system. And the benefit of that is is it effectively lets us you know you don't have to now mimic in your database the entire identity structure that you might have already in your entire corporation. So if you've got for example something in you know Azure which says these people are defined as managers you'll be able to link that manager role to that to there such that if someone logs on and in Azure they're a manager they will effectively be mapped when they connect to the database to being a manager like that. So this is the nice thing is the users and roles etc we create in deep data security can have hooks back to your corporate enterprisewide definition of users and roles and security privileges etc which means it can all be managed there as opposed to managed in many places but I'm keeping it all just in the database.

[00:40:21]Now this is the role I want to give to my employees. So I'm going to give them some data privileges shortly but I'm also going to give them the standard role to let them actually connect to the database. This is a a role that defines everything this person will be able to do. Same for the manager.

[00:40:37]Now, what do we do to roles? We normally grant things to them. So, in this case, because we have a data role, I can now do what's called a data grant. This data grant is called employees can see their own record. And it's defined with an SQL statement. I'm saying this data grant says you're allowed to select scott.mployee employee 2. But when you do it, you're only allowed to see the rows where the employee name equals who you're logged on as. So I'm going to create some users called demo 1, demo 2, etc. Now this data grant is then given to the employee role. So my employer role has a standard role which has privileges and now a data role in the employee role now has some controls as to what I'm allowed to actually see. For the managers, it's a bit different. For the managers, they can select everything, but they can't see the salary. So, you should be able to see who your employees are, but you can't see what they earn. Same thing, Scott.

[00:41:32]2. And in this case for managers, we're doing it based on the manager column.

[00:41:36]Now, there's a lot of extension. You can do this. It doesn't just have to be their username. This aura end user context can have all sorts of stuff uh including the familiar CIS context information that people use um currently with a role level security. But having done that, let's now create some users.

[00:41:52]And we have a new thing called an end user. These end users, once again, can be hooked in or linked up to your enterprise identification system. In this case, I'm just creating them in the database. I've got an employee called demo one. I've got a manager called manager one. How do I know they're employees and managers? Well, it's based on the grants I give them. So, demo one is gets the employee role. Manager one gets the manager role. So now I've put all the pieces in place and now we can actually see deep data security in action. If I connect as demo one and do a query, I can only see my data even though I'm looks like I'm quering the entire table. I'm blissfully unaware there's any other rows in there. And if I connect as a manager, I get to see all my employees because I'm manager one, but I don't get to see their salary. And that's probably the most basic simple demonstration of deep data security I can do. There is an extensive amount of additions you can do well and above of that but you get the idea of how this thing is meant to work and it effectively becomes a superset of all those older technologies that we had before.

[00:42:58]Okay. Q&A question is uh let me bring it over here so I can what is the best way with regular user privileges not DBA to sell to sell if a given user uses database links and list them explicit links as well as hidden in views and nested views must be considered well there's only one way you'll be able to do that and that's with some DBA privileges uh we're not going to simply go and release access to what people are doing um you know to your average user. If you want to see what your your own user account is using, uh the way you probably do that is with auditing. Uh the audit trail contains when people access things using database links. But once again, the audit trail is available to DBAs.

[00:43:46]There's a reason we make things locked down to DBAs because you don't want to be letting anyone else see, you know, am I using database links because that opens up an avenue for potential attacks to remote databases. So hopefully that helps.

[00:44:02]And I assume the enterprise data ro example does not call the LDOP service.

[00:44:06]It is metadata or a placeholder. That is correct. It's not like when you create one of these things, it reaches out and says, "Hey, Azure, you know, is this valid, etc." Or, you know, whatever.

[00:44:13]It's simply a linkage between the two such that when someone comes in, if you've set up um enterprise um enterprise authentication on your database, so I I won't connect as Scott Tiger. I'll simply connect as you know Conor McDonald oracle.com. [snorts] It will say oh yeah if you've come in through this enterprise provider reach out to Entraa and say y what roles does he have? Ah if he's got these roles in charter that means he gets access to this role in the database. Hopefully that makes sense.

[00:44:49]944. Dear me. [snorts] Okay. Assertions and determinisms. This one's pretty quick.

[00:45:00]I was exploring the new assertions in 26AI and there seems to be a contradiction in the docs. One part of the doc says assertions must be deterministic. Non-deterministic function calls in assertions are not allowed. But later it says PL/SQL functions not allowed. This includes deterministic functions that do not access tables. So which is it? It's actually both. Now, it's a bit of yeah, it's it's a bit um disingenuous of me to say, oh, this is an interesting, you know, subtle thing about assertions when assertions is an absolutely brand new feature that I'm pretty confident a lot of people have maybe never even heard of. So, let's describe what assertions are, why they are just amazing, and then we'll talk about the actual problem that has been raised here.

[00:45:55]So I'm going to start with why assertions are and I'm going to try and justify this to you. The greatest step forward in data quality in relational databases in literally three decades now that's a big statement to make. Let me see if I can justify it. So I've got the employee table here. I've also got the department table. The reason I put them here is because I want to have a look.

[00:46:17]I've made a slight change to the employee table. Notice for each department I've tweaked one of the rows to be a department head. So in this case, King is the department head for department 10, Ford's the department head for department 20 and Blake is the department head for department 30. Other than that, it's just a copy of Scott.

[00:46:35]But I made a couple of changes there.

[00:46:36]And I've got the department table as well. Way back in Oracle 2, we introduced data quality. In Oracle 2, you could make a column not null. And that is a form of data quality. you're making sure that you don't have data missing and not much else happened in terms of data quality since Oracle 2 until Oracle 7 many many years later and said this Oracle 7 is still I think late '9s so 30 years ago we introduced declarative constraints so I could say yep employee number is the primary key it must be present it must be unique same with the department table then same version Oracle 7 I said yeah we can now have check constraints it makes no sense for a salary to be negative. So I had primary keys and check constraints. I had unique constraints. If the department name is present, doesn't have to be, but if it is, it has to be unique. And finally, we introduced declarative ways of bringing all these tables together. It makes no sense for an employee to have a department number that doesn't belong to my department table. So these are all great things.

[00:47:40]These are all huge advancements in data quality in Oracle 7. And you know what happened since then?

[00:47:46]nothing. This is pretty much all we had in terms of declarative data quality.

[00:47:51]Um, we were the first to have it. The other databases eventually all caught up, but yeah, nothing's really happened by in any database for 30 years really besides these. And the problem is data quality rules driven by actual business requirements are vastly more complicated than just uniqueness and and check constraints. For example, based on our employee table, this is a really trivial but sensible requirement. If each department has a department head, no one else in that department should have a salary higher than them. There is no declarative command to do that. If I look at my employee table, we can see that King has five salary 5,000, Ford salary 3,000, and Blake has a salary 2850. So no one else and at the moment it is true no one else in these tables has a salary higher than that person in the department. I can't implement it in the database. So what do we do? We do it in code. Now I'm going to do it in a trigger because a lot of people think if I'm doing data validation I should do it as close to the data as possible.

[00:48:54]Excellent idea. But it doesn't have to be a trigger. It could be a trigger. It could be a PL/SQL procedure. It could be your Java code. Could be up in Apex.

[00:49:02]Doesn't really matter where you put it.

[00:49:04]This is what we're trying to achieve.

[00:49:06]I'm saying every time something happens to a row and I insert on the employee table, what do I do? Let's go see if the person who has the job of the department head has a salary less than the incoming salary. That's illegal. And so we raise an error. So that trigger created fine.

[00:49:24]Let's give it a go. Department 20. Who's got the highest salary? The department head. 2850. So if I try insert a value of 500 into department 30, that's me as a new developer. should be fine.

[00:49:37]It blows up. This is one of the downsides of using triggers. You get this trigger is mutating error. And if you've seen this, you're familiar what it means. You cannot have a rowle trigger on a table that literally queries the table you're triggering on because the table is in the state of flux. You can't do it. Now, this is the sad things. What people often do is they go, "Okay, well, I'll take this code and put it out in the application tier."

[00:50:03]We'll see why that's a bad idea momentarily, but let's just say I'm I'm dedicated to keeping it inside the trigger. This is one option I could do.

[00:50:12]Let's not do it at row level. So, I'll comment that out. Now, this has performance implications because I no longer have access to the incoming data, which means this query is actually now table wide. Every time I insert a row, when I finish the insert, I check the entire table. For every single row where there's someone who's a department head, go do a double check to make sure no one else exists that has violates that salary. So, my trigger is now created.

[00:50:40]Let's give it a test. I insert 1,200, which is less than 28.50. That's good. I insert 3,000, which is more than 28.50 department head, and I get the correct error. So, at this point, I'm running around. I'm high-fiving my team. Yeah, we've solved the problem. Yes, I've really hurt my performance because I'm doing this checking of all the data in the table, but at least I've got data quality. Now, besides the performance impact of that, there is one little tiny issue with that approach, and that is it simply doesn't work because doesn't matter if you're deep down in the database kernel using triggers or in PL/SQL or in Java or in Apex. If you're just doing queries like this to do data of validation, the reason it doesn't work is because you can't see what's going on in other sessions. So we know that that that threshold salary was 28.50.

[00:51:30]I might in this session insert a developer at 2500 which is less than 28.50. That's all fine. But in another session a managers might be going hm the department head has underperformed this year. I'm reducing his salary from 28.50 50 to,500.

[00:51:48]Now that's allowed because that person won't be able to see my uncommitted change. Mine was allowed because I can't see the uncommitted change in the other session. We both commit and suddenly I've corrupted my database. You simply can't do basic queries to do bad data validation. You're going to end up with a corrupted database. This is one way of working around it. I'll make sure that there are no outstanding transactions when I'm doing my check. This will indeed solve the problem. But of course, now I'm paying an Oracle database license for a single user system. One person at a time gets to insert rows in this table. You may as well use Microsoft Excel for that because you've simply destroyed the amazing power of concurrency in the Oracle database. This is a bad idea. So, how do we do that?

[00:52:36]Well, if we want to try to solve it now, we've got an enormous amount of complexity to build because what am I going to lock on to make sure I have but still allow concurrency? Well, because of doing this check for each individual department, what I can do is use the department table as a locking mechanism.

[00:52:54]So, what'll happen is now is every time with a compound trigger, every time we do an insert, I'll keep a track of what department number I'm playing with here.

[00:53:03]Then when the statement is finished, I'll go lock the department table rows for the department numbers that have came in. Now I know that everyone is competing for that table. So once again, I've serialized at department level. And then I can do my check to see if it's going to work. If it's a insert or update, well, now I've got to capture the new and the old. I've got to add more code and more complexity. And this even doesn't even handle the concept of someone editing the department head.

[00:53:29]None of this is fixed here. At which point developers go, are you kidding me?

[00:53:34]Why did it become this hard? You know, it shouldn't be this hard to do simple data validation. And in particular, if I get rid of that trigger, the thing that really frustrates developers and DBAs for that matter is [snorts] phrasing this requirement in SQL is trivial.

[00:53:53]And this is it. It's a bad thing when for the department head I can find someone who has a salary higher than me.

[00:54:02]So it literally takes me 10 seconds to write the SQL that explains the requirement and yet to convert this into data validation code is this giant amount of complexity and serialization and triggers and mutating and blah blah blah.

[00:54:17]If I could write it with SQL, why can't I just write an SQL that explains that rule? And that's what assertions are.

[00:54:24]That's why this is so mindblowing.

[00:54:28]I just said this is bad. So my assertion is simply the reverse of this. It simply says it must not ever be the case that this happens.

[00:54:38]And there's my assertion. Now it's the database's problem to manage things like concurrency and locking and when this needs to be run and what's the most efficient way to run it and how many rows are impacted, etc. It's all handled inside the database now as opposed to you having to do it in code. And I can give it a test. 1,200 was less than 2850. That was allowed in and 3,000 was too much and it violated the assertion.

[00:55:03]I haven't got the example here, but I can also do this in multiple sessions.

[00:55:06]And it's the database's job to manage all that. So assertions are wild. Very, very cool.

[00:55:15]Let me quickly go back to this interesting thing now which is assertions must be deterministic nondeterministic function calls are not allowed and then we say PL/SQL function not allowed this includes deterministic functions sounds like a contradiction so now that we know what assertions are let's have a look at this interesting contradiction so let's see what assertions I can create yep we'll get this done before 10 here's a real simple assertion there cannot be anyone in the employee table whose job is blah Now if we look at this, there's no functions in there. There's no everything in there is deterministic.

[00:55:49]And so that's fine. How about this one?

[00:55:52]Right? Same thing. Once again, yes, I'm using a function, but this function is deterministic. And therefore, that's fine. The assertion is allowed to be created.

[00:56:03]Let's look at this one. Well, cy state is not deterministic. Every time you call it, you get a different result as long as it's more than 1 second has passed. And this is what the the documentation means when it says you cannot use non-deterministic functions.

[00:56:17]What they're referring to is the delivered kernel functions inside the database. So in this case it says certain SQL functions are not supported and in particular non-deterministic ones cy state cy timestamp uid cis gooid etc. Anything that doesn't give a predictable and one to one, you know, effectively known result for a given input is blocked by assertions in the same way that generally blocked in most other constraints because you can't have a constraint definition that changes over time.

[00:56:50]Here's where things get interesting. It does say a function must be deterministic, but remember we're referring to the kernel functions in the database SQL engine, not PL/SQL. Here's a PL/SQL function that by definition is deterministic because it simply returns a constant. If I try refer to this in my assertion even though it's deterministic, we get this error. You cannot reference any PL/SQL functions at the moment in assertions. So just be aware of it.

[00:57:17]That's why it's not actually a contradiction in the documentation. What we're saying is no PL/SQL functions at all and no non-deterministic delivered Oracle SQL engine functions as opposed to PL/SQL.

[00:57:32]Now I want to come back to this one.

[00:57:33]This is an interesting one because this may have raised some alarm bells as I was going through the demo. Let's say I'm saying this assertion is two char high date M. The month can't be dees.

[00:57:45]Now, there's no month that has dee Z in it. And so, that assertion is fine.

[00:57:51]But, oh, I've got a bug here. What if I say alter language?

[00:57:56]Oh, I'm going to I'm going have to do it manually now.

[00:58:00]What if I say alter session to NLS language equals German? Let me scroll back and do select star from M. Well, now there are months that have de in them. So, what would happen if I grabbed my assertion here? Oops.

[00:58:18]and try to run it. So I can see here right on the screen there's obviously some months that have de and I'm saying it cannot be the case right have I got is that assertion already exists must be I I don't understand German okay let's try this so I'll try that assertion created now that's interesting because I've got data here that says the month is deez I'm guessing that's December in German and I've said yeah you're not allowed to have anything that has deez why does this assertion get created because it doesn't look at your session an endless language it takes that of the database definition and so that's why twochar even though it can vary in terms of things like territory and language etc is deterministic it in assertions because they're taken from the database definition not from your session definition got to fix my demo Now it is 9:59. Look at that. Finished within 30 seconds of the hour. Of course, we skipped several, but that's we actually finished on time. Um, once again, thank you for those that attended. Um, I hope you managed to get in. Um, if you tried to get to the Zoom landing page, or not directly via Zoom, you would have had some problems. Uh hopefully it'll all be sorted out in the coming weeks when we get rid of these nasty people trying to do denial of service attacks on us. Um I think I answered everyone's questions and everyone's chat. Uh so I'll be yeah hopefully I'll get an office hours in next month. I'll have to double check. I might need to bring it forward because toward the end of the month I'll be in Latin America. But uh certainly because I was bad in June um I might try bring the office hours forward so at least I get one done in August and maybe we just finish off these questions. As always thank you for your time. I get paid to do this so I don't really mind.

[01:00:10]So it is my third webinar of the day so apologies for the voice being a bit weird but as I said thank you very much for your time and hopefully we'll see you soon on another Ask Tom live session. Bye for now everybody.