moving partitions and generating redo

[BeefStu]

I have table with a lot of partitions.

The pctfree is set very high for this table. I can alter the table and
reset pctfree and than move each partition to reclaim my space.

Example of what I am doing:

alter table MTAS.MTAS_RESULT pctfree 0;
alter table MTAS.MTAS_RESULT move partition RESULT_20100831 parallel 8;
....
....
...
500 partitions ...

This generates way too much redo, which I don't want.

My questions:

1) is there anyway I can check a view that will show me which DDL is
generating redo. In my example above I would expect to see this
statement.


2) If I use the nologging option, will this avoid generating ALL redo for
this statement?

alter table MTAS.MTAS_RESULT move partition RESULT_20100831 parallel 8 nologging;

I am trying to verify if this is the case by seeing what is actually written to
the redo logs if that is possible

Thanks to all who answer

[PAVB]

alter table move partition is indeed one of the few statements that will take advantage of "nologging" option.

Only alter table, create index and CTAS statements allow for nologging option.

[BeefStu]

Paul,
When u say take advantage of "nologging" does that mean no writing to
the redo logs at all or writing to the redo logs will be minimized as opposed
to not using the nologging option.

This is why I was looking for some query so I can exactly what is written
to the .arc files. I read some where that log miner allows you to do this.

I have to see if that is included in our licences agreement

Thanks

[vnktummala]

with nologging redo generation will be reduced very much but still oracle has to generate minimal redo(like redo for undo blocks).

Logminer is part of Oracle RDBMS Enterprise Edition, no additional license required.

Thanks,

[BeefStu]

with nologging redo generation will be reduced very much but still oracle has to generate minimal redo(like redo for undo blocks).

Logminer is part of Oracle RDBMS Enterprise Edition, no additional license required.

Thanks,

Thanks for the update. I new nologging did not mean "NO LOGGING" completely. Is there a way to measure how much logging will be generated?
As last time I did this test I filled up my /archive file system. Want to be
proactive and make sure that does not happen again.

[PAVB]

.... I new nologging did not mean "NO LOGGING" completely. Is there a way to measure how much logging will be generated?

Testing is the only alternative - identify a couple of partitions that trully represent your population, run process against them and monitor redo creation then you should be able to infer how much the whole project will generate and at what pace.

When doing partition maintenance I generally set special redo backup jobs running every 15 minutes or so.

[BeefStu]

All:

Thanks for your input.

@Paul I was generating about 80GB of redo with out nologging for ever 5
partitions I was moving. I set the nologging option for each partition and
every 15 minutes I ran rman to remove the .arc files this solved my problem.