Re: Nologging/Unrecoverable operations

From: Randolf Geist <mahrah_at_web.de>
Date: Wed, 6 Oct 2010 02:28:00 -0700 (PDT)
Message-ID: <c2cebc86-ffd2-4d76-a62e-3ae7e4a362fb_at_i5g2000yqe.googlegroups.com>

On Oct 5, 3:50 pm, Vinni <hea..._at_gmail.com> wrote:
> I folks, I decided to experiment creating an unrecoverable operation
> however I seem to have failed somehow and cannot work out why yet!
>
> The database is Oracle 9.2.0.8 in a Solaris environment.
>
> Ok here is my experiment:
>
> Ok so its time to go make some uncrecoverable operations!
>
> create table vintest (id number, name varchar2(14)) tablespace users
> nologging  --create table
> select * from dba_tables where table_name='VINTEST'  --check it exists
> and double check it is in the correct tablespace
>
> OK the create table was ddl and I said NOLOGGING so I should have an
> unrecoverable operation:

A plain CREATE TABLE itself is not a NOLOGGING operation. There are only a limited set of operations that can be performed as NOLOGGING, and among these are:

CTAS (Create Table As Select)
CREATE INDEX
Direct-Path INSERTs (INSERT /*+ APPEND */) Some Parallel DML (Parallel Direct-Path INSERTs) Reorganization tasks like ALTER TABLE MOVE, ALTER INDEX REBUILD

So your CREATE TABLE ... NOLOGGING only told Oracle that if you performed an operation on your table that is capable of performing NOLOGGING mode it should do so.

> ok so im going to insert some values into the new table, commit and
> then truncate and see if ive managed to do an unrecoverable operation
> yet!
>
> insert into vintest values (1,'test')
> insert into vintest values (1,'test')
> insert into vintest values (1,'test')
> insert into vintest values (1,'test')
> insert into vintest values (1,'test')
> insert into vintest values (1,'test')
> commit;

A conventional (non-direct path) INSERT is not capable of performing a NOLOGGING operation, it will always generate redo (and undo). If you for example tried an INSERT /*+ APPEND */ this should turn out as NOLOGGING operation.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Wed Oct 06 2010 - 04:28:00 CDT