General
cancel
Showing results for 
Search instead for 
Did you mean: 

Logging, turning off for select temp tables.

Paula Gregorich
Occasional Visitor

Logging, turning off for select temp tables.

We are running a logged database.
I create temp tables from that database
using dbaccess. I want to turn off logging
for my temp tables in my sql statement that
I am using to create the temp tables with.
Is this even possible?
3 REPLIES
harry d brown jr
Honored Contributor

Re: Logging, turning off for select temp tables.


is there a problem with turning off logging first, and then creating the tables?


live free or die
harry
Live Free or Die
Brian Crabtree
Honored Contributor

Re: Logging, turning off for select temp tables.

You can do this one of two ways.

1. CREATE TABLE ... NOLOGGING;

2. CREATE TEMPORARY TABLE ...;

The first one creates a normal permanant tablespace with NOLOGGING enabled. The second one creates a true "temporary" table. The temporary table will not keep any data until either a commit is performed, or you exit your session. This is an option when creating the table (one or the other). If you are loading your data with sqlldr, you will want to do the first one. If you are processing data in your session, and want to use a table to hold processed data for a short period of time, use the second.

Hope this helps,

Brian
JJ_4
Frequent Advisor

Re: Logging, turning off for select temp tables.

create temp table tab1 (col1 int with no log;
insert into tab1
select tabid from systables;

or

select tabid from systables insert into temp tab1 with no log;
Not enough Zappa makes you sad.