nologging 模式
     当要批量加载数据时,数据库还记录redo,很影响加载速度.这是没必要的,但默认情况下是记录redo的.
     数据库为保证批量加载数据时避免产生redo,以提高加载速度,提供了强制和不强制记录日志功能
    
创建实验表
SQL> drop table t1 purge;

Table dropped.

SQL> drop table t2 purge;

Table dropped.

SQL> create table t1 as select * from scott.emp;

Table created.

SQL> create table t2 as select * from scott.emp where 0=9;

Table created.

SQL> insert into t1 select * from t1;

14 rows created.

SQL>
......
SQL> /

1792 rows created.

SQL> commit;

Commit complete.

SQL>

SQL> select  FORCE_LOGGING from v$database;

FOR
---
NO

SQL>
计算当前会话redo的大小脚本
SQL> ! cat mystat.sql
set echo off
set verify off
column value new_val V
define S='redo size'

set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/

SQL>
SQL> ! cat mystat2.sql
set echo off
set verify off
column value new_val V
define S='redo size'

select a.name, b.value V, to_char(b.value-&V,'999999999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

SQL>
SQL> @mystat
SQL> set echo off

NAME                                   VALUE
-------------------------------------------------- ----------
redo size                               1054292

SQL> insert into t2 select * from t1 nologging;

3584 rows created.

SQL> @mystat2

NAME                                       V DIFF
-------------------------------------------------- ---------- ----------
redo size                               1242168       187876

SQL>


SQL> @mystat
SQL> set echo off

NAME                                   VALUE
-------------------------------------------------- ----------
redo size                               1242168

SQL> insert /*+ append */ into t2 select * from t1 nologging;

3584 rows created.

SQL> commit;

Commit complete.

SQL> @mystat2

NAME                                       V DIFF
-------------------------------------------------- ---------- ----------
redo size                               1247896         5728

SQL>

SQL> alter table t2 nologging;

Table altered.

SQL> @mystat
SQL> set echo off

NAME                                   VALUE
-------------------------------------------------- ----------
redo size                               1249948

SQL> insert /*+ append */ into t2 select * from t1;

3584 rows created.

SQL> @mystat2

NAME                                       V DIFF
-------------------------------------------------- ---------- ----------
redo size                               1255464         5516

SQL> commit;

Commit complete.

SQL> @mystat
SQL> set echo off

NAME                                   VALUE
-------------------------------------------------- ----------
redo size                               1256004

SQL> insert /*+ append */ into t2 select * from t1 nologging;

3584 rows created.

SQL> @mystat2

NAME                                       V DIFF
-------------------------------------------------- ---------- ----------
redo size                               1256488          484

SQL>


虽然此时加载数据速度提高了 但却对闪库有了负面影响

SQL> insert /*+ append */ into t2 select * from t1 nologging;

3584 rows created.

SQL> @mystat2

NAME                                       V DIFF
-------------------------------------------------- ---------- ----------
redo size                               1256488          484

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     927990

SQL>
SQL> commit;

Commit complete.

SQL> insert /*+ append */ into t2 select * from t1 nologging;

3584 rows created.

SQL>

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size              1218992 bytes
Variable Size             79693392 bytes
Database Buffers       201326592 bytes
Redo Buffers              2973696 bytes

SQL> alter database mount;

Database altered.

SQL> flashback database to scn 927990;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from t2;
select count(*) from t2
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 60721)
ORA-01110: data file 1: '/db254/system01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


SQL>


将数据库改为强制日志模式
alter database force logging;
alter database no force nologging;

强制模式
     忽略表nologging参数
     忽略语句nologging参数
     忽略hint /*+ append */