ALTER TABLE MODIFY ... Redo-Logs, Will this statemtent generate Redo Logs or not? Options

[Jens]

Hello,

i've got a Table with 13 million datasets. The table contains a column
YWERT NUMBER(6,3) NOT NULL. I need to change the Column to Number(10,5),
so my question is, will the statement
ALTER TABLE TBLSSERESAVBEINZEL MODIFY(YWERT NUMBER(10,5));
generate redo logs or will it not, since it is DDL?

Thanx!

[SteveC]

What are datasets?

Are any of your "datasets" being updated?

[burleson]

Hi Jens,

>> i've got a Table with 13 million datasets.

No you don't . . .

A dataset is a name for an OS file!

For example, the files referenced in dba_data_files are dataset . . .

***********************************************
>> will the statement ALTER TABLE TBLSSERESAVBEINZEL MODIFY(YWERT NUMBER(10,5)); generate redo logs or will it not, since it is DDL?

Is this a homework question?

Why not just try it and see for yourself?

Trust me, you will know right away!

**********************************************
>> ALTER TABLE TBLSSERESAVBEINZEL MODIFY(YWERT NUMBER(10,5));

It may be faster to make a new table and copy-in the values with parallel CTAS . . .

[Steve]

The DDL statement itself will go to redo for the purpose of recovery (otherwise a Dataguard or Streams target could have the wrong column size), but the resize operation should not log extra redo. The NUMBER datatype is not fixed width and so it should not generate extra redo for each row.

Notice the use of the word "should". I have not personally tested this, and would advise you to make a small copy of your table and test it. You can turn on autotrace with "set autotrace traceonly" and run your alter command. The statistics after the run will show the amount of redo generated.

[Jens]

Hi Burleson, Hi Steve,

"datasets" was probably a problem in my translation. I guess rows is the better word. In german these two words have the same meaning.
And no, it's not a homework, if the database will get in trouble the whole company i am working for will have a problem.

So i will probably test it with a small copy first and see what happens.

Does the oracle documentation contain an overview of the amount of redo logs generated by an operation? I couldn't find something like that.

[burleson]

Hi,

>> So i will probably test it with a small copy first and see what happens.

That's smart!

FYI, it's a Oracle best practice to have a full-sized test environment, and I would carefully plan this first.

Note:

Size your redo properly: http://www.dba-oracle.com/concepts/redo_log_files.htm

You can dedicate a large undo segment to the job: http://www.remote-dba.net/t_oracle_9i_admi...ck_segments.htm

****************************************************
>> Does the oracle documentation contain an overview of the amount of redo logs generated by an operation?

Yes! See these scripts for moinitoring redo log history:

http://www.dba-oracle.com/tips_oracle_v&am...log_history.htm

When testing for redo log usage, start the job by performing a redo log switch (alter system switch logfile), so you start with a fresh redo log. Then you simply measure the number of archived redo logs generated, plus the remianing space in the current redo log.

Also, remember that the salient measure is the number of redo "blocks" generated, and this depends on your blocksize.

You also need to ensure that your archived redo log doirectory will have enough space . . .

Obviously, the amount of redo is the accumulated "before image" of the number columns, plus the ROWID.

I would SWAG this at 20 bytes per row, and estimate the number of redo blocks.

Karl Reitschuster offers this script for minitoring redo usage:

http://www.orcasoracle.org/

CODE

SELECT Start_Date,
       Start_Time,
       Num_Logs,
       Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),
             2) AS Mbytes,
       Vdb.NAME AS Dbname
  FROM (SELECT To_Char(Vlh.First_Time,
                       'YYYY-MM-DD') AS Start_Date,
               To_Char(Vlh.First_Time,
                       'HH24') || ':00' AS Start_Time,
               COUNT(Vlh.Thread#) Num_Logs
          FROM V$log_History Vlh
         GROUP BY To_Char(Vlh.First_Time,
                          'YYYY-MM-DD'),
                  To_Char(Vlh.First_Time,
                          'HH24') || ':00') Log_Hist,
       V$log Vl,
       V$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date,
          Log_Hist.Start_Time;