Non-standard block size tablespace

Non-standard block size tablespace

1.Assume my undo tablespace has maximum size to 32GB with undo_retention=900 and currently it's used 10GB.

While I querying a table more than 15 min and others users perform DML statements to the database that request undo.

Does it use expired undo extents or allocate new extents first?

----- If your undo retention is 15mins, then once information in the undo area is more than 15 mins old, then it is a candidate for reuse. If you have queries that run for more then 15mins, then you'd be at risk.

2.If my database has a non-standard block size tablespace, when a user perform DML to a segment in the tablespace, Does it use which tablespace to keep before images for that segment?

----- Block size doesn't matter, because we dont record before images of *blocks*. Undo is more about "instructions".

So if you do an update which is say:

update EMP

set sal = 500

where empno = 10;

then the undo would be something conceptually like:

"change SAL in row 17, in block 1235, back to 100"

So you can see the blocksize of the source data is not really an issue.

3.) Undo tablespace keeps growing

OK, let's say at 9am, the power (and hence your server) goes out.

Leading up to that point in time, your database probably had

- plenty of committed changes from completed transactions

- a batch of un-committed changes from active transactions

There are two things to keep in mind here:

1) Some of those *committed* changes, may NOT be reflected in the datafiles

2) Some of those *uncommitted* changes may BE reflected in the datafiles.

Because the "datafiles" are not actually a reflection of the exact state of your database, its the redo logs (or the sum total of every redo log entry every made). It's almost like datafiles are a "performance enhancement" designed to save you from reading every redo log in the history of the database in order to see the current state.

So when your server powers back up again, we need use the redo logs to get our datafiles to a consistent state.

So we roll the redo changes forward to bring our datafiles to a point where all the committed and potentially *uncommitted* changes are applied. In doing do, we've also brought forward the files we use for our UNDO tablespace (which stores info on how to undo a transaction).

Now we need to back out (rollback) those uncommitted changes. So we can use the freshly recovered undo information, to now reverse out those uncommitted changes.

Thus at the end of the recovery, we have our desired consistent state - just those changes that committed successfully before the power was lost.