The stored procedure you are referring to is (proc_DefragmentIndices) and is discussed in this KB article.
The stored procedure works within the limitations of the online index build feature, one of which being that you cannot perform an online index on an index that contains a LOB (large object) column. The bad news? SharePoint stores a lot of BLOB data (images, office docs etc.) in content databases.
Looking at the stored procedure itself, you can see that it attempts to perform an online defragmentation first then switches to offline defragmentation where required - i.e. for image-like columns. From what I can see there is no logic to determine the level of fragmentation - which could be ascertained using avg_fragmentation_in_percent.
My understanding of "online" in this context is an "online index build". This is not the same as the SQL database itself being online - an online index build works by maintaining two copies of the index meaning that data is fully available at all times.
Offline index rebuilds don't take the database itself offline - they place exclusive "locks" on your SQL table indexes during which time queries cannot be run on the table(s) in question. That's very different from the DB itself being in an offline state.
So to answer your question, the timer job added in MOSS 2007 SP2 will almost certainly result in offline index building given that you will have BLOB data in your content DB. The stored procedure will, however perform online index building wherever possible and will not take the database itself offline - only the table(s) in question.