Join Bob Bryla for an in-depth discussion in this video Manage the UNDO tablespace, part of Learning Oracle Database 12c.
- [Narrator] Let's find out more about your undo tablespace. The undo tablespace is very critical, as you know, because for long-running DML statements, let's say you're updating 100,000 rows, until you hit commit, you have to be able to put those rows back the way they were, and that's basically what the undo tablespace does for you. So, let's go ahead and take a look at your undo tablespace using Enterprise Manager Database Express. You can also do something similar with Enterprise Manager Cloud Control, but Enterprise Manager Database Express is very handy, it's set up every time you install a database on a particular server.
And it's just for that server. We'll go ahead and log in to Database Express. I'll log in as sysdba, otherwise I won't see all the components of the database. First thing you see when you log in to Database Express are the SQL monitor on the bottom to see what kind of queries are running right now, how much host CPU is used, how many active sessions and so forth. The reason we're here is for the undo tablespace. Let's go under the storage drop-down. Undo management.
Alright, so, most of what I'm already looking for is right there. The status of the undo tablespace, the summary, the undo retention threshold, the name of the undo tablespace, how big it is, if there's any warnings or errors. In the bottom middle part of the screen you see how the undo tablespace has been used over the last particular time period. Most of it's expired, not needed for recovery of a query that was rolled back. So, we already know what the current state is.
What's really great is that we can see what it was in the past. Right down at the bottom, here, where you see the undo space usage for February 8th and over the last day. We can change analysis parameters to, say, let's go for the last week. Clicking on the analysis parameters, at the bottom middle of the page, you see what the undo space usage was over time over the last three days, in this particular case. What you can also do, is do a what-if analysis. For example, if you wanted to change the retention threshold to 9,000 seconds instead of 900 seconds, click on change analysis parameters, specify 9,000 as your undo retention, and click ok.
In this particular case, the undo advisor says you don't need 9,000 seconds for your retention threshold given the activity in this database. Enterprise Manager Database Express has a wealth of information. Go there first when you need some information. And as far as the undo tablespace, you can see what it's doing now, you can see what it was doing in the past, and you might even be able to predict what it's going to do in the future.
- Design methodology and tools
- SQL query basics
- Advanced data manipulation
- Modifying and creating a database object
- Database architecture
- DBA configuration tools
- Managing tablespaces and disk space
- Table compression
- Manage system-related tablespaces
- Database monitoring