Exclude Tables from Archive log
effl...@my-deja.com
is there a way to exclude tables from ARCHIVELOG.
Of course this Tables have no triggers or constraits to other tables .
Background is: I load nightly data from a ASCII-file via SQL*Loader,
which are a datapool for the "real" Application. (I did SQL Command
"Alter table xxx nologging")
But unfortunately I have after the sqlload a script running which is
doing lots of formating stuff.
Today I have approx 80% Archiv-Log files due to of UPDATE commands on
the datapool Tables executed by formating scripts.
I'm using Oracle 8.1.5 on WinNT
Sent via Deja.com http://www.deja.com/
Before you buy.
Connor McDonald
Check out the NOLOGGING setting for a table, combined with direct load
and unrecoverable settings for SQL Loader.
HTH
--
===========================================
Connor McDonald
http://www.oracledba.co.uk
We are born naked, wet and hungry...then things get worse
Thomas J. Kyte
No, not when doing normal INSERTS/UPDATES and DELETES.
If you direct path load, insert /*+ APPEND */, and truncate -- yes, you
can avoid redo -- otherwise no.
> Of course this Tables have no triggers or constraits to other tables .
>
> Background is: I load nightly data from a ASCII-file via SQL*Loader,
> which are a datapool for the "real" Application. (I did SQL Command
> "Alter table xxx nologging")
> But unfortunately I have after the sqlload a script running which is
> doing lots of formating stuff.
>
> Today I have approx 80% Archiv-Log files due to of UPDATE commands on
> the datapool Tables executed by formating scripts.
>
> I'm using Oracle 8.1.5 on WinNT
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Since 8.1.5 has temporary tables and:
...
DML statements on temporary tables do not generate redo logs for the
data
changes. However, undo logs for the data and redo logs for the undo
logs are
generated. Data from the temporary table is automatically dropped in
the case of
session termination, either when the user logs off or when the session
terminates
abnormally such as during a session or instance crash.
....
That means that redo should effectively be cut in half (see test
below). So, what you could do is:
o direct path load into your table (no logging)
o insert /*+ append */ into a session based global temporary table (no
logging)
o mod this table instead (~1/2 the redo)
o truncate the table you loaded (no logging)
o insert /*+ append */ into that table from the temp table (no logging)
To test this approach, you could
o sqlldr as normal
o make the first line in your update script be "rename T to T_HOLD"
o create global temporary table T on commit preserve rows as select *
from T_HOLD where 1=0;
o insert /*+ append */ into t select * from t_hold
o -- run the existing script ---
o truncate t_hold
o insert /*+ append */ into t_hold select * from t;
o drop table t
o rename T_HOLD to T
to see how it goes (only need to modify the script a little to test)
Here is the test of redo generated on a temp table:
scott@dev8i> create global temporary table tmp_tab on commit preserve
rows as select * from all_objects where 1=0;
Table created.
scott@dev8i> create table t nologging as select * from all_objects
where 1=0;
Table created.
scott@dev8i> insert /*+ append */ into tmp_tab select * from
all_objects;
21785 rows created.
Statistics
----------------------------------------------------------
839 recursive calls
133 db block gets
186380 consistent gets
0 physical reads
15828 redo size
996 bytes sent via SQL*Net to client
832 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
21785 rows processed
scott@dev8i> commit;
Commit complete.
REM -- 15k of redo to log the data dictionary changes made only.
append is like a direct path insert.
scott@dev8i> update tmp_tab set object_name = initcap(object_name);
21785 rows updated.
Statistics
----------------------------------------------------------
0 recursive calls
22397 db block gets
286 consistent gets
0 physical reads
3693968 redo size
1011 bytes sent via SQL*Net to client
826 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
21785 rows processed
3.6 meg of redo more or less....
scott@dev8i> insert /*+ append */ into t select * from all_objects;
21785 rows created.
Statistics
----------------------------------------------------------
97 recursive calls
71 db block gets
186187 consistent gets
0 physical reads
7192 redo size
998 bytes sent via SQL*Net to client
826 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
21785 rows processed
about 7k of redo generated for this insert...
scott@dev8i> commit;
Commit complete.
scott@dev8i> update t set object_name = initcap(object_name);
21785 rows updated.
Statistics
----------------------------------------------------------
0 recursive calls
22399 db block gets
286 consistent gets
286 physical reads
6067764 redo size
1014 bytes sent via SQL*Net to client
820 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
21785 rows processed
6meg of redo generated for the same update.
--
Thomas Kyte tk...@us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp