Database Reference
In-Depth Information
•
After performing
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!
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.
■
again, this example was performed in an
ARCHIVELOG
-mode database. You would not see the differences in
redo size in a
NOARCHIVELOG
mode database as the index
CREATE
and
REBUILD
operations are not logged in
NOARCHIVELOG
mode.
Note
Search WWH ::
Custom Search