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:

select force_logging from v$database

NO

--so we have no force logging


select name, unrecoverable_time from v$datafile order by
unrecoverable_time desc nulls last NAME UNRECOVERABLE_TIME
/oracle/oradata/PMSTEST/appclob01.dbf 05/10/2010 10:34:56
/oracle/oradata/PMSTEST/appdata01.dbf 10/05/2009 19:45:49
/oracle/oradata/PMSTEST/system01.dbf
/oracle/oradata/PMSTEST/tools01.dbf
/oracle/oradata/PMSTEST/qualitycentre.dbf
/oracle/oradata/PMSTEST/perfstat01.dbf
/oracle/oradata/PMSTEST/SYSAUDIT_01.DBF
/oracle/oradata/PMSTEST/users01.dbf
/oracle/oradata/PMSTEST/appindex01.dbf
/oracle/oradata/PMSTEST/undotbs01.dbf



so I had an uncrecoverable operation at 10:34:56 woohoo

ok


the time is now:

select sysdate from dual

05/10/2010 13:08:05

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:

select name, unrecoverable_time from v$datafile order by
unrecoverable_time desc nulls last
NAME
UNRECOVERABLE_TIME
/oracle/oradata/PMSTEST/appclob01.dbf 05/10/2010 10:34:56
/oracle/oradata/PMSTEST/appdata01.dbf 10/05/2009 19:45:49
/oracle/oradata/PMSTEST/system01.dbf
/oracle/oradata/PMSTEST/tools01.dbf
/oracle/oradata/PMSTEST/qualitycentre.dbf
/oracle/oradata/PMSTEST/perfstat01.dbf
/oracle/oradata/PMSTEST/SYSAUDIT_01.DBF
/oracle/oradata/PMSTEST/users01.dbf
/oracle/oradata/PMSTEST/appindex01.dbf
/oracle/oradata/PMSTEST/undotbs01.dbf


still nothing yet!


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;

select * from vintest
ID NAME
1 test
1 test
1 test
1 test
1 test
1 test



truncate table vintest;


select name, unrecoverable_time from v$datafile order by
unrecoverable_time desc nulls last
NAME
UNRECOVERABLE_TIME
/oracle/oradata/PMSTEST/appclob01.dbf 05/10/2010 10:34:56
/oracle/oradata/PMSTEST/appdata01.dbf 10/05/2009 19:45:49
/oracle/oradata/PMSTEST/system01.dbf
/oracle/oradata/PMSTEST/tools01.dbf
/oracle/oradata/PMSTEST/qualitycentre.dbf
/oracle/oradata/PMSTEST/perfstat01.dbf
/oracle/oradata/PMSTEST/SYSAUDIT_01.DBF
/oracle/oradata/PMSTEST/users01.dbf
/oracle/oradata/PMSTEST/appindex01.dbf
/oracle/oradata/PMSTEST/undotbs01.dbf




select sysdate from dual
05/10/2010 13:15:15

Why am I not managing to create unrecoverable operations to experiment
with ? I have also checked that I am not forcelogging at the
tablespace level and i've also tried alter database no forcelogging
just to be doubly sure and i'm still not managing to make any!

If someone could tell me what I am not doing that would be great.
I've tried creating indexes with nologging, and CTAS nologging still
no luck.

On Oct 5, 3:50*pm, Vinni <hea... (AT) gmail (DOT) com> wrote:
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.

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-.../dp/1430226684