1mb del = 1bm archive? -archive generation

[khoshtip_uk]

Lets say we have a table which is 3GB in size and contains 18 millions rows can we say deleting
9millions rows from this table generates 2GB worth of archive logs(assuming every row is equal in size) ?

thanks

[davey23uk]

no, not at all

[khoshtip_uk]

sorry I meant deleting 9million ( half of the records) should generate 1.5GB worth of archive!

hmmm
any possible way of precisely estimating or figuring out how much worth of archive it will generate?

[davey23uk]

again, no

[khoshtip_uk]

any idea on whether its gone be less than 1.5GB or more ? for sure?

what does oracle keeps as part of archive logs when deleting data ?
the actual DML and row id's for the deleted rows?

[jhmartin]

When you delete, the before-image of the block is copied to the undo tablespace and a change is written to the archivelog that the row was deleted. The 'before' image of the block is not written to the archivelog.

[dbaxchangedba]

any possible way of precisely estimating or figuring out how much worth of archive it will generate?

One possibility for you would be to estimate the redo in your test system as follows:

From within your session:

-- Get the starting redo size value
SQL> select value start_value from v$mystat where statistic# = 115;

SQL> Run_your_deletes
-- Get the Ending redo size value

SQL> select value end_value from v$mystat where statistic# = 115;

Your end_value - start_value should give you an idea of how much redo (in bytes) your deletes would generate.

Alternatively, you could do this as well on your test system to get an appoximate idea:

From within your session:

-- Get the starting redo size value
SQL> select value start_value from v$mystat where statistic# = 115;

SQL> Delete_one_row

-- Get the mid redo size value
SQL> select value mid_value from v$mystat where statistic# = 115;

SQL> commit;

-- Get the Ending redo size value
SQL> select value end_value from v$mystat where statistic# = 115;

((mid_value - start_value) * (9 million) ) + ((end_value - mid_value) * no_of_commits_done) should give you an idea on how much redo might be generated for your deletes.

Good luck.....

http://www.dbaxchange.com