Return-Path: <root@fatcity.com>
Received: from ensim.rackshack.net (root@localhost)
 by orafaq.net (8.11.6/8.11.6) with ESMTP id h459Qij15282
 for <oracle-l@orafaq.net>; Mon, 5 May 2003 04:26:44 -0500
X-ClientAddr: 209.68.248.164
Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164])
 by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h459QhA15277
 for <oracle-l@orafaq.net>; Mon, 5 May 2003 04:26:43 -0500
Received: from fatcity.com (uucp@localhost)
 by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id AAA38608;
 Mon, 5 May 2003 00:02:09 -0700 (PDT)
Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0058F278; Sun, 04 May 2003 23:06:45 -0800
Message-ID: <F001.0058F278.20030504230645@fatcity.com>
Date: Sun, 04 May 2003 23:06:45 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
X-Comment: Oracle RDBMS Community Forum
X-Sender: Saminathan_Seerangan@i2.com
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Saminathan_Seerangan@i2.com
Subject: Redo Logfile  content- (A)SQL Statements OR (B)Modified Data Block OR (C)BOTH
Organization: Fat City Network Services, San Diego, California
X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
Precedence: bulk
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=_alternative 0021C55D65256D1D_="
--=_alternative 0021C55D65256D1D_=
Content-Type: text/plain; charset="us-ascii"

Dear All DBAs,

I've a very basic doubt. I got size of 300MB archive
redo logfile  for "one create table" statement.

What do we have in Redo Log File? 
(A)SQL Statements OR (B)Modified Data Block OR (C)
BOTH.

Kindly look at case study below.

Oracle 9iR2 Windows NT ARCHIVE LOG Mode
---------------------------------------

Tablespace info:-
Name                                             : EXAMPLE
Datafile Name                            : example01.dbf
Extent_management                : LOCAL
Allocation_type                          : SYSTEM
Segment_Space_Management: AUTO
Default Tablespace of            : HR

1)Original example01.dbf datafile size 500MB;

2)create table HR.T2 pctfree 99 pctused 1 as select *
from dba_objects;
                 (used PCTFREE 99 to increase the table size by
absorbing more number of blocks)

3)

SQL> select count(*) from t2;

  COUNT(*)
----------
     38085

SQL> select bytes,blocks,extents from dba_segments
where segment_name='T2' and  owner='HR';

     BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
 318767104      38912        109 


4)After table creation, example01.dbf datafile size
becomes 800MB;
                 (datafile size got increased because of Table T2
creation)


Q1) How much archive will be generated between step(1)
and step(4). 
(Assume that this is the only activity happened in the
DB)

Here 60 number of 5MB archive files got
generated(close to 300 MB). 

My question about Redo log file-->
(A) Does it have actual modified blocks? OR 

OR

(B) Does it have just the stament which has been used
to modify the datablocks?
                 (Using LogMiner I see only statements)
OR

(C) BOTH





--=_alternative 0021C55D65256D1D_=
Content-Type: text/html; charset="us-ascii"


<table width=100%>
<tr valign=top>
<td width=100% bgcolor=white>
<table width=100%>
<tr>
<td width=100%><font size=2 face="Times New Roman"><br>
</font>
<table width=100%>
<tr>
<td width=100%><font size=1 face="Courier New">Dear All DBAs,<br>
<br>
I've a very basic doubt. I got size of 300MB archive<br>
redo logfile &nbsp;for &quot;one create table&quot; statement.<br>
<br>
What do we have in Redo Log File? <br>
(A)SQL Statements OR (B)Modified Data Block OR (C)<br>
BOTH.<br>
<br>
Kindly look at case study below.<br>
<br>
Oracle 9iR2 Windows NT ARCHIVE LOG Mode<br>
---------------------------------------<br>
<br>
Tablespace info:-<br>
Name &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; : EXAMPLE<br>
Datafile Name &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;: example01.dbf<br>
Extent_management &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; : LOCAL<br>
Allocation_type &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;: SYSTEM<br>
Segment_Space_Management: AUTO<br>
Default Tablespace of &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; : HR<br>
<br>
1)Original example01.dbf datafile size 500MB;<br>
<br>
2)create table HR.T2 pctfree 99 pctused 1 as select *<br>
from dba_objects;<br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (used PCTFREE 99 to increase the table size by<br>
absorbing more number of blocks)<br>
<br>
3)<br>
<br>
SQL&gt; select count(*) from t2;<br>
<br>
 &nbsp;COUNT(*)<br>
----------<br>
 &nbsp; &nbsp; 38085<br>
<br>
SQL&gt; select bytes,blocks,extents from dba_segments<br>
where segment_name='T2' and &nbsp;owner='HR';<br>
<br>
 &nbsp; &nbsp; BYTES &nbsp; &nbsp; BLOCKS &nbsp; &nbsp;EXTENTS<br>
---------- ---------- ----------<br>
 318767104 &nbsp; &nbsp; &nbsp;38912 &nbsp; &nbsp; &nbsp; &nbsp;109 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>
<br>
<br>
4)After table creation, example01.dbf datafile size<br>
becomes 800MB;<br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (datafile size got increased because of Table T2<br>
creation)<br>
<br>
<br>
Q1) How much archive will be generated between step(1)<br>
and step(4). <br>
(Assume that this is the only activity happened in the<br>
DB)<br>
<br>
Here 60 number of 5MB archive files got<br>
generated(close to 300 MB). <br>
<br>
My question about Redo log file--&gt;<br>
(A) Does it have actual modified blocks? OR <br>
<br>
OR<br>
<br>
(B) Does it have just the stament which has been used<br>
to modify the datablocks?<br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (Using LogMiner I see only statements)<br>
OR<br>
<br>
(C) BOTH<br>
<br>
</font></table>
<br></table>
<br></table>
<br>
--=_alternative 0021C55D65256D1D_=--
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Saminathan_Seerangan@i2.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).