Re: "Nologging"-option tablespace and tables

From: Jan Korecki <Jan.Korecki_at_contactor.se>
Date: Wed, 19 Nov 2003 19:48:29 +0100
Message-ID: <3fbbbb2f$0$97837$57c3e1d3@news3.bahnhof.se>

> It overrides nologging. I was under the impression that +append has to
> do with extend management, not with logging. But I could be wrong here.
>
> So, during those inserts, ANY operations with nologging in it will not be
> logged. Those may be a lot more operations than the inserts you want to
> do.
> As for trust, do you trust *anybody* not to make mistakes?
>
>

>>But as i said i did not know about the "force logging" so i have to
>>check it up first if it has any drawbacks.

>
> http://otn.oracle.com/pls/db92/db92.drilldown?levelnum=1&preference=&expand_all=&book=Admin&method=FULL&chapters=0&verb=&word=force+logging#a96540
>
>
> Lots of Greetings!
> Volker
>

+append writes over the high water mark (HWM)

You have to specifically type +append or nologging in every sqlstatement to avoid logging.

You cannot use +append on a normal insert: insert /* +append*/ into x values(.....)

The hint is ignored in the above case. You cannot +append updates or deletes either.

I have a few corrections to make. It was a while ago i used +append. If you are in archivelog mode you have to set the table to nologging when you use +append.
If you are in noarchivelog mode you dont have to set the table to nologging.

Se my examples below:

---

create table test3 as select * from all_objects where 1=2;

• Archivelog off

SQL> set autotrace STATISTICS

SQL> alter table test3 logging;

Table altered.

SQL> insert into test3 select * from all_objects;

35576 rows created.

Statistics

---

     4222652 redo size

SQL> insert /*+append*/ into test3 select * from all_objects;

35576 rows created.

Statistics

---

         864 redo size

SQL> rollback;

Rollback complete.

SQL> alter table test3 nologging

   2 /

Table altered.

SQL> insert /*+append*/ into test3 select * from all_objects;

35576 rows created.

Statistics

---

         864 redo size

• archivelog on

SQL> alter table test3 logging;

Table altered.

SQL> set autotrace on STATISTICS
SQL> insert into test3 select * from all_objects;

35576 rows created.

Statistics

---

     4148776 redo size

SQL> rollback;

Rollback complete.

SQL> insert /*+append*/ into test3 select * from all_objects;

35576 rows created.

Statistics

---

     4164056 redo size

SQL> rollback;

Rollback complete.

SQL> alter table test3 nologging;

Table altered.

SQL> insert /*+append*/ into test3 select * from all_objects;

35576 rows created.

Statistics

---

         864 redo size

---

Sorry if i confused anyone.

As for trust... i dont even trust myself most of the times :-)

Regards,
Janne!

Disclaimer:
The above are the opinions of God as recited by my telepathic goldfish. Those who oppose them with be struck by lightning. Such an event will be reflected on their electrical bill. Received on Wed Nov 19 2003 - 12:48:29 CST