remove duplicates data from a table

[tamilselvan]

10:23:13 SQL> get stat_info
  1  select a.name, b.value from v$statname a, v$mystat b
  2  where a.statistic# = b.statistic# and
  3*       lower(a.name) like '%redo size%' 
10:23:28 SQL> /

NAME                VALUE     
------------------------------     
redo size           5541584     

1 row selected.

10:23:53 SQL> drop table t1 ;

Table dropped.

Now I create a table in LOGGING MODE.

10:23:59 SQL> create table t1 as select * from dba_objects ;

Table created.

10:24:04 SQL> get stat_info
  1  select a.name, b.value from v$statname a, v$mystat b
  2  where a.statistic# = b.statistic# and
  3*       lower(a.name) like '%redo size%' 
10:24:08 SQL> /

NAME                VALUE     
------------------------------     
redo size           8280104     

1 row selected.

Oracle generated (8280104 - 5541584)= 2,738,520 bytes of redo. 

10:24:09 SQL> drop table t1 ;

Table dropped.

10:24:15 SQL> get stat_info
  1  select a.name, b.value from v$statname a, v$mystat b
  2  where a.statistic# = b.statistic# and
  3*       lower(a.name) like '%redo size%' 
10:24:23 SQL> /

NAME                VALUE     
------------------------------     
redo size           8300836     

1 row selected.

10:24:24 SQL> create table t1 nologging as select * from dba_objects ;

Table created.

10:24:43 SQL> get stat_info
  1  select a.name, b.value from v$statname a, v$mystat b
  2  where a.statistic# = b.statistic# and
  3*       lower(a.name) like '%redo size%' 
10:24:47 SQL> /

NAME                VALUE     
------------------------------     
redo size           8341560     

1 row selected.

In NOLOGGING the redo size is reduced to (8341560 - 8300836 ) = 40,724 bytes.

As Slimdave said, even in NOLOGGING mode, oracle generates minimum redo. 

Tamil