Re: "Nologging"-option tablespace and tables

Hello!

Note the warning at the end of this message.

Nologging on the tablespace sets the default for the create table statement.

The create table action itself is logged (change in the data dictionary) but any data in the same statement is not logged.

example
tablespace is nologging
create table test as select * from all_objects;

The create table is logged.
The data inserted will not be logged.

If you insert new data in the table the inserts will be logged.

insert into test select * from all_objects;

If you are not intrested in logging inserts: insert /* +append */ into test select * from all_objects;

If you have indexes on the table you will have to set them unusable and alter session set skip_unusable_indexes=true; (otherwinse the index changes will be logged)

After the insert is done -> rebuild index nologging.

+append only works for insert into ... as select (updates, deletes or insert into .... values () are logged)

Using nologging and +append is only intresting when you are in archivelog mode.

Warning!
Be _very_ carful when using nologging and +append in a production database.

It is useful sometimes (big batch inserts) but you have to backup the database afterwards in order to have a consistent backup. Dont play around with it without coordinating the person responsible for the backups.

You should research more about it in the documentation, books etc before you start usning it.

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.

Jens Mueller wrote:
> Hello,
>
> tables and tablespaces have the option "nologging". If the intstance
> is runing in archivlog-modus, wich one counts and is "stronger".
> If I have a tablespace with nologging but the tables inside the
> tablespace with logging --> what is written into the redo-files??
>
> Thanks Jens
>
Received on Wed Nov 19 2003 - 08:16:13 CST