Database Reference
In-Depth Information
EODA@ORA12CR1> exec dbms_output.put_line( (get_stat_val('redo size')-:redo)
➥
|| ' bytes of redo generated...' );
4487796 bytes of redo generated...
PL/SQL procedure successfully completed.
That
CREATE TABLE
generated about 4MB of redo information (your results will vary depending on how many
rows are inserted into table
T
). We'll drop and re-create the table, in
NOLOGGING
mode this time:
EODA@ORA12CR1> drop table t;
Table dropped.
EODA@ORA12CR1> variable redo number
EODA@ORA12CR1> exec :redo := get_stat_val( 'redo size' );
PL/SQL procedure successfully completed.
EODA@ORA12CR1> create table t
2 NOLOGGING
3 as
4 select * from all_objects;
Table created.
EODA@ORA12CR1> exec dbms_output.put_line( (get_stat_val('redo size')-:redo)
➥
|| ' bytes of redo generated...' );
90108 bytes of redo generated...
PL/SQL procedure successfully completed.
This time, we generated only 90KB of redo. As you can see, this makes a tremendous difference—4MB of redo vs.
90KB. The 4MB written in the first example is a copy of the actual table data itself; it was written to the redo log when
the table was created without the
NOLOGGING
clause.
If you test this on a
NOARCHIVELOG
-mode database, you will not see any differences between the two. The
CREATE
TABLE
will not be logged, with the exception of the data dictionary modifications, in a
NOARCHIVELOG
-mode database.
That fact also points out a valuable tip: test your system in the mode it will be run in production,
as the behavior
may be different
. Your production system will be running in
ARCHIVELOG
mode; if you perform lots of operations that
generate redo in this mode, but not in
NOARCHIVELOG
mode, you'll want to discover this during testing, not during
rollout to the users!
Of course, it is now obvious that you will do everything you can with
NOLOGGING
, right? In fact, the answer is a
resounding
no
. You must use this mode very carefully, and only after discussing the issues with the person in charge
of backup and recovery. Let's say you create this table and it is now part of your application (e.g., you used a
CREATE
TABLE AS SELECT NOLOGGING
as part of an upgrade script). Your users modify this table over the course of the day.
That night, the disk that the table is on fails. “No problem,” the DBA says. “We are running in
ARCHIVELOG
mode and
we can perform media recovery.” The problem is, however, that the initially created table, since it was not logged, is
not recoverable from the archived redo log. This table is unrecoverable and this brings out the most important point
about
NOLOGGING
operations: they must be coordinated with your DBA and the system as a whole. If you use them and
others are not aware of that fact, you may compromise the ability of your DBA to recover your database fully after a
media failure.
NOLOGGING
operations must be used judiciously and carefully.
