Database Reference
In-Depth Information
The important things to note about
NOLOGGING
operations are as follows:
•
Some amount of redo will be generated
, as a matter of fact. This redo is to protect the data
dictionary. There is no avoiding this at all. It could be of a significantly lesser amount than
before, but there will be some.
•
NOLOGGING does not prevent redo from being generated by all subsequent operations
. In the
preceding example, I did not create a table that is never logged. Only the single, individual
operation of creating the table was not logged. All subsequent “normal” operations such as
INSERT
s,
UPDATE
s,
DELETE
s, and
MERGE
s will be logged. Other special operations, such as a
direct-path load using SQL*Loader, or a direct-path
INSERT
using the
INSERT /*+ APPEND */
syntax, will not be logged (unless and until you
ALTER
the table and enable full logging again).
In general, however, the operations your application performs against this table
will be logged
.
NOLOGGING
operations in an
ARCHIVELOG
-mode database,
you must take a
new baseline backup of the affected data files as soon as possible
, in order to avoid losing the
data created by the
NOLOGGING
operation due to media failure. Since the data created by the
NOLOGGING
operation is not in the redo log files, and is not yet in the backups, you have no way
of recovering it!
•
After performing
Setting NOLOGGING on an Index
There are two ways to use the
NOLOGGING
option. You have already seen one method—embedding the
NOLOGGING
keyword in the SQL command. The other method, which involves setting the
NOLOGGING
attribute on the segment
(index or table), allows
certain
operations to be performed implicitly in a
NOLOGGING
mode. For example, I can alter
an index or table to be
NOLOGGING
by default. This means for the index that subsequent rebuilds of this index will not
be logged (the index will not generate redo; other indexes and the table itself might, but this index will not). Using the
table
T
we just created, we can observe:
EODA@ORA12CR1> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
EODA@ORA12CR1> create index t_idx on t(object_name);
Index created.
EODA@ORA12CR1> variable redo number
EODA@ORA12CR1> exec :redo := get_stat_val( 'redo size' );
PL/SQL procedure successfully completed.
EODA@ORA12CR1> alter index t_idx rebuild;
Index altered.
EODA@ORA12CR1> exec dbms_output.put_line( (get_stat_val('redo size')-:redo)
|| ' bytes of redo generated...');
672264 bytes of redo generated...
PL/SQL procedure successfully completed.