How to Reduce redo size in Oracle

Lab instructions

1. In non-archive mode:

SQL> archive log list
Database Log mode non-archive Mode
Disable automatic archiving
Archiving end point USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2491
Current Log sequence 2493

Use the sys user to create a view for querying the redo size (convenient query)

SQL> create or replace view redo_size
2
3 select value
4 from v $ mystat, v $ statname
5 where v $ mystat. statistic # = v $ statname. statistic #
6 and v $ statname. name = 'redo size ';

The view has been created.

Create synonyms with sys users

SQL> create public synonym redo_size for redo_size;

You have created a synonym.

Perform the following operations with scott:

Create test table

SQL> create table test_redos as select * from dba_objects where 1 = 2;

The table has been created.

View Current redo volume

SQL> select * from redo_size;

VALUE
----------
736

Insert data and view results

SQL> insert into test_redos select * from dba_objects;

You have created 73104 rows.

SQL> select * from redo_size;

VALUE
----------
8473536

SQL> insert/* + append */into test_redos select * from dba_objects;

You have created 73100 rows.

SQL> select * from redo_size;

VALUE
----------
8504856

SQL> select (8473536-736) normal insert, (8504856-8473536) append insert from dual;

Normal insert APPEND insert
--------------------
8472800 31320

The above results indicate that in non-archive mode, append inserts data to produce much less redo.

1 2 3 Next Page