归档方式：ctas不管加不加nologging都会记录redo
非归档方式：ctas不管加不加nologging都不会记录redo，更准确的说是很少量的redo
实验一：归档方式
> @d:\mystat "redo size"
NAME VALUE
-------------------- ----------
redo size 37136
> create table tm tablespace users as select * from dba_objects;
Table created.
> @d:\mystat2
> set echo off
NAME VALUE DIFF
-------------------- ---------- ------------------
redo size 729292 692,156
> @d:\mystat "redo size"
> set echo off
NAME VALUE
-------------------- ----------
redo size 729292
> create table ti tablespace users as select * from dba_objects where 1=0;
Table created.
> insert into ti select * from dba_objects;
6462 rows created.
> commit;
Commit complete.
> @d:\mystat2
> set echo off
NAME VALUE DIFF
-------------------- ---------- ------------------
redo size 1406304 677,012
> create table tn tablespace users as select * from dba_objects nologging;
Table created.
> @d:\mystat2
> set echo off
NAME VALUE DIFF
-------------------- ---------- ------------------
redo size 2095092 688,788
> create table tin tablespace users nologging as select * from dba_objects where 1=0;
Table created.
> insert into tin select * from dba_objects;
6464 rows created.
> commit;
Commit complete.
> @d:\mystat2
> set echo off
NAME VALUE DIFF
-------------------- ---------- ------------------
redo size 3468752 677,300
实验二：非归档方式
> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination D:\ORACLE\oradata\fox\archive
Oldest online log sequence 1
Current log sequence 2
> insert into t select * from t;
6461 rows created.
> /
12922 rows created.
> /
25844 rows created.
> commit;
Commit complete.
> @d:\mystat "redo size"
> set echo off
NAME VALUE
-------------------- ----------
redo size 4627920
> create table t_a tablespace users as select * from t;
Table created.
> @d:\mystat2
> set echo off
NAME VALUE DIFF
-------------------- ---------- ------------------
redo size 4685568 57,648
> @d:\mystat "redo size"
> set echo off
NAME VALUE
-------------------- ----------
redo size 4685568
> create table t_b tablespace users as select * from t where 1=0;
Table created.
> insert into t_b select * from t;
51688 rows created.
> commit;
Commit complete.
> @d:\mystat2
> set echo off
NAME VALUE DIFF
-------------------- ---------- ------------------
redo size 9854064 5,168,496
> @d:\mystat "redo size"
> set echo off
NAME VALUE
-------------------- ----------
redo size 9854064
> create table t_c tablespace users nologging as select * from t;
Table created.
> @d:\mystat2
> set echo off
NAME VALUE DIFF
-------------------- ---------- ------------------
redo size 9909036 54,972
*******************************
附mystat1脚本
******************************
set echo off
set verify off
column value new_val V
define S="&1"
column name format a20
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')||'%'
/
set echo on
********************
mystat2脚本
*********************
set echo off
set verify off
column diff format a18
select a.name, b.value Value, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on
阅读(888) | 评论(0) | 转发(0) |
