Non-transactional tablespaces

We are currently storing TIFF images as BLOBs in an 8.0.5 database. The problem we have is, because the TIFFs are so large, the transactions are also large so a lot of archive logs are being generated. On the average we are cutting a 500M archive log every 45 minutes or so. At this rate we have to back up the logs to tape and delete them every hour or the hard drive fills and Oracle stops. This became a major issue this morning when the tape drive failed and we had no where to put the archive logs.

I have read a little on non-transactional tablespaces in 8i but am still a little confused. It appears non-transactional only makes a difference when you perform an INSERT INTO ... SELECT FROM type of update. If so, I don't see a whole lot of use for non-transactional tablespaces.

Basically the question I am trying to find an answer for is, can we mark the tablespace containing the BLOBs as non-transactional in order to cut down on the amount of redo logs generated, even if the inserts/updates are done through the DBMS_LOB package (as opposed to selecting from another table)? In our case we don't need the insert of the TIFF to be a transaction because we also have the image on optical disk and we can retrieve it again if necessary.

Any help would be appreciated.

--
Mark Wagoner
To reply, remove no.spam from my e-mail address Received on Fri Aug 20 1999 - 07:47:06 CDT