.Check For Logging / Nologging On DB Object(s) [ID 269274.1]

Two example methods of querying the database for this information:

select tablespace_name, logging
from dba_tablespaces

[TABLE]
select distinct owner,object_name,object_type from dba_objects
where object_type='TABLE' and
owner not in
('SYS','SYSTEM','SYSMAN','XDB','DBSNMP','EXFSYS','OLAPSYS','MDSYS','WMSYS',
'WKSYS','DMSYS','ODM','EXFSYS','CTXSYS','LBACSYS','ORDPLUGINS','SQLTXPLAIN',
'OUTLN','TSMSYS')
and
object_name in
(select table_name
from dba_tables
where logging='NO');

[INDEX]
select distinct owner,object_name,object_type from dba_objects
where object_type='INDEX' and
owner not in
('SYS','SYSTEM','SYSMAN','XDB','DBSNMP','EXFSYS','OLAPSYS','MDSYS','WMSYS',
'WKSYS','DMSYS','ODM','EXFSYS','CTXSYS','LBACSYS','ORDPLUGINS','SQLTXPLAIN',
'OUTLN','TSMSYS')
and
object_name in
(select index_name
from dba_indexes
where logging='NO'); 

[LOB]

select owner,table_name,column_name,tablespace_name from dba_lobs where logging='NO' and owner not in ('SYS','SYSTEM','SYSMAN','XDB','DBSNMP','EXFSYS','OLAPSYS','MDSYS','WMSYS',
'WKSYS','DMSYS','ODM','EXFSYS','CTXSYS','LBACSYS','ORDPLUGINS','SQLTXPLAIN',
'OUTLN','TSMSYS') order by owner,table_name,column_name;

NOLOGGING means whatever operations are performed on the object with the NOLOGGING option set, will NOT be recorded in archive logs. You will have to get a full backup of the database AFTER the NOLOGGING is turned off. The current archive logs will be useless for recovery as there have been changes made to the database that the archives do not know about.

Even though you can set the NOLOGGING attribute for a table, partition, index, or tablespace, this mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute.

There is no interaction between discrete transactions, which always generate redo, and the NOLOGGING mode, which applies only to direct path operations. Discrete transactions can therefore be issued against tables that have the NOLOGGING attribute set.

The following operations can make use of NOLOGGING mode:

direct load (SQL*Loader)
direct-load INSERT
CREATE TABLE ... AS SELECT
CREATE INDEX
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... SPLIT PARTITION
ALTER INDEX ... REBUILD
ALTER INDEX ... REBUILD PARTITION
INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line


The following operations are a few that cannot make use of NOLOGGING mode:

Table redefinition cannot be done NOLOGGING
Tempfiles are always set to NOLOGGING mode.