Truncates ARE Logged

Someone asked me yesterday where do I get my blog topics and ideas from. Usually it’s from watching too much TV, but often it’s an actual question or topic that is raised by a co-worker or customer. Yesterday I was asked by my boss about how to find out when TRUNCATEs have been issued because

I know that truncates cannot be rollbacked from so they wouldn’t be in the redo logs

Now, in my boss’ defense, this is mostly true.

• You cannot ROLLBACK a TRUNCATE.
• TRUNCATEs will be in the Redo Log, but there is no ‘SQL Undo’ entry for that record.

So, just because you cannot ROLLLBACK something, doesn’t mean it does not get logged.

Let’s say that your database crashes, and you need to recover it from backup. So, you take your last known ‘good’ backup and restore it. Then you ask RMAN to plow through the logs to apply all of the transactions that occurred from the backup to the point right before the crash.

Do you think it would be important to be able to ‘replay’ any TRUNCATEs that occurred in that timeframe?

So, just because Oracle doesn’t let you ROLLBACK a TRUNCATE doesn’t mean that Oracle doesn’t LOG the TRUNCATEs.

Now, also in my boss’ defense (just in case he stumbles across this blog) – I have heard this claim uttered many, many times. So, there are lot of people out there who have heard this before and have probably passed it on.

This happens in the SQL Server world too – although you can ROLLBACK TRUNCATEs over there – so it’s not just us Oracle folks who suffer from this. Go read myth #19 from the geniuses over there at SQLSkills,
‘Myth #19: A TRUNCATE TABLE operation is non-logged.’

Ok, so now that we know all database user transactions are logged, how do we go find them?

Introducing LogMiner

Oracle gives us an interface to ‘mine’ the online and archived transaction logs. You can read ALL about it here (Oracle Docs). I will say this – please be very, very careful with this utility. I’ve had a co-worker play with it and she corrupted her test database. So, once you have the basics down, now we can go digging for our TRUNCATEs.

First, let’s do a TRUNCATE

[sql]
–create table
create table scott_emp_trunc as select * from scott.EMP;
–truncate table
truncate TABLE SCOTT.EMP_TEST;
[/sql]

Now I am going to dive into my ONLINE redo logs using LogMiner and query V$LOGMNR_CONTENTS. This view gets populated once you’ve mined your log(s).

For example:
[sql]
Select
SCN,
timestamp "Time Stamp",
session# "Session #",
sql_redo "SQL Redo"
From V$LOGMNR_CONTENTS
Order by 1
[/sql]

Instead of querying it ‘manually’, I’m using my favorite IDE which has interface to LogMiner. I just give it a start and stop time or SCN and then I can start running my queries. I like it because the data grids allow me to do grouping around things like ‘Segment Owner.’

I have 1 record logged for ‘SCOTT’, and it is indeed our TRUNCATE!

You will notice that our UNDO SQL entry is a very boring and useless ‘NULL’

You want to know why TRUNCATE is super-duper fast? It’s partially because Oracle isn’t logging all the records that are dropped. Logging is expensive. That’s why Oracle allows us to turn logging off at the object and tablespace level (LOGGING clause – don’t do it!) You can also turn off logging for your IMPORT sessions.

The Real Question Was About Auditing

The question originated from a customer who was really wanting a method for auditing their Oracle tables. Yes, you can find TRUNCATEs that have been issued. But, it assumes you have the logs handy, and that you know how to use LogMiner. Instead, I recommend you just use Oracle’s built-in Auditing feature. It will capture everything you want it to, including TRUNCATEs. Then it’s a simple matter of querying the audit trail tables.

At least, I THINK Oracle tracks this via auditing. So I promise to blog soon proving that.

Nobody’s Going to Help You

I read an excellent blog this morning from The Coding Horror blog. The general gist is, you can’t rely on a self-help book to do your work for you. You need to work and do things for yourself. Excellent, excellent read! Anyways, I wanted to get some help yesterday and I Googled and Googled and even tweeted a few times, but I came up dry.

In less than 15 minutes this morning, I was able to answer my own question by just ‘DOING IT’ for myself. Thanks for the great read Jeff (Atwood, not me). Go follow codinghorror now.