How to keep table activity out of REDO logs

[rtefft]

While running a production DB in ARCHIVELOG mode, does anyone know if there is a way to have 1 table that is NOT logged? We use a temp table for transferring files to ColdFusion (we load them in as strings or CLOBs and it queries them out). We end up with excessive redo log usage and fill up disks with the archives. The web server and DB server can talk only via the JDBC driver, so FTP etc. is out.

We can load the file into the table, read it out, and delete it (or even rollback) in a single transaction. Can a transaction be marked as "no logging" similar to the way SQL*Loader can?

Thanks in advance,
Rich

____________________________
Rich Tefft
PL/SQL Programmer

 

[LKBrwnDBA]

Try:

ALTER TABLE TMP_CF NOLOGGING;
ALTER INDEX TMP_CF_PK NOLOGGING;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: Actually, for the NOLOGGING to be more effective, use with a CREATE TABLE or SQL*Loader load.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sem]

NOLOGGING affects only DIRECT-path loads

Regards, Dima

http://seminihin.narod.ru

 

[rtefft]

I could use direct-path inserts, but would still pay the REDO price when deleting them after use. We tried CREATE GLOBASL TEMPORARY TABLE but it still hit the redo logs.


____________________________
Rich Tefft
PL/SQL Programmer

 

[LKBrwnDBA]

@sem
From Oracle manual:

LOGGING | NOLOGGING
Specify whether subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against a nonpartitioned table, table partition, all partitions of a partitioned table, or all subpartitions of a partition will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file.

Rich: your problem with using NOLOGGING are the CLOB's:

From Oracle manual:

NOLOGGING does not apply to LOBs that are stored inline with row data. That is, if you specify NOLOGGING for LOBs with values less than 4000 bytes and you have not disabled STORAGE IN ROW, Oracle ignores the NOLOGGING specification and treats the LOB data the same as other table data.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sem]

@LKBrwnDBA

How does it differ from my words? When you don't specify explicitly that direct-path is to be used, it doesn't matter it's not used, I mean especially <b>create table as select</b>. In any case NOLOGGING doesn't affect regular inserts/updates and affects only direct-path operations. Or are you confused with the loads term? OK, it may be replaced by inserts, if you find it better

Regards, Dima

http://seminihin.narod.ru