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.
Search WWH ::




Custom Search