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

[Srinivas_Sharma]

cheers guys for the info.

Srini

[DaPi]

This is v.timely - I've been hacking round the doc trying to bring all the implications of NOLOGGING together. Perhaps someone can help me with this:

NOLOGGING will only avoid writing log entries for "bulk" operations: Direct Load to Table (SQL or SQL*Loader), CTAS, Move Table, Create Index, Rebuild Index, (plus some similar operations for partitions).

So if an object has the NOLOGGING attribute, but only "normal" DML has executed against it (i.e. none of the above), all the entries exist in the log files to permit recovery.

Can such an object be restored & recovered?
Can Oracle know if there are changes for which the log entries are missing (none in this case)?
What is the status of the object if restored & recovered? (e.g. is an index USABLE?)

[DaPi]

Originally posted by tamilselvan
In NOLOGGING the redo size is reduced to (8341560 - 8300836 ) = 40,724 bytes.
As Slimdave said, even in NOLOGGING mode, oracle generates minimum redo.

Is that due to logging on the Data Dictionary?

[tamilselvan]

Yes, Dapi.
Do you live in US or Europe?

Tamil

[DaPi]

Originally posted by tamilselvan
Yes, Dapi.

Got it right!
* warm glow *

Do you live in US or Europe?

That's Geneva, Switzerland (CH). I did once visit Geneva, Illinois (IL) so I though I ought to be specific!

. . . . ahhh ummmmmm is NOLOGGING different in Europe ? . ? . ? . . . help mummmmmyyyyyyyy ! ! ! ! !

[slimdave]

... is NOLOGGING different in Europe ...

Only in the environmental, forestry management sense.

All index operations are logged, except for a create index in nologging mode (minimal logging there, blah blah).

For bulk insert operations I believe that the minimal logging is associated mostly with extent management in the data dictionary. So the fact that additional extents were created, or the HWM was moved, gets logged, but the new contents do not.

[DaPi]

Thanks guys.

Any comment on my questions about restore & recovery?
PING -

[slimdave]

Originally posted by DaPi
[B]So if an object has the NOLOGGING attribute, but only "normal" DML has executed against it (i.e. none of the above), all the entries exist in the log files to permit recovery.

Can such an object be restored & recovered?

yes, i don't think that there's a problem with that unless the object was actually created with the nologging option. Not sure what happens then ... I would imagine that the creation in nologging mode of a table would make it non-recoverable because even if no rows were initially inserted in it Oracle has no way of telling that.

http://download-west.oracle.com/docs...gy.htm#1004784

Can Oracle know if there are changes for which the log entries are missing (none in this case)?

Except where the object is created as nologging, i believe it knows all. If a bulk insert occured in nologging mode then I believe that the redo log marks the affected blocks with a code to indicate that their contents are indeterminate.

What is the status of the object if restored & recovered? (e.g. is an index USABLE?)

I think it's marked as corrupt in some way. The recommendation is to take an incremental or full backup after nologging operations. I have a feeling that RMAN is preferred for this because it can backup just the affected blocks -- I'm no RMAN expert though.

[DaPi]

Originally posted by slimdave
The recommendation is to take an incremental or full backup after nologging operations. I have a feeling that RMAN is preferred for this because it can backup just the affected blocks . . .

In fact this is the only reference to NOLOGGING I found in the 8i RMAN manual - which left me stuggling to see the big picture.

Slim, Tamal, thanks v.much for your help.