This discussion is archived
12 Replies Latest reply: Apr 17, 2013 8:35 AM by KR10822864 RSS

truncate corrupted partition

user361671 Newbie
Currently Being Moderated
hi,

We have a partition table ,partition is on date wise , one of a partition DAY_21MAR2013 has been corrupted , partition cant be truncated.

how we can truncate the partition to insert the fresh data.

Thanks
  • 1. Re: truncate corrupted partition
    TSharma-Oracle Guru
    Currently Being Moderated
    Oracle Version? OS? Why do you think the partition is corrupted? What is the error you are getting when you truncate the partition?
  • 2. Re: truncate corrupted partition
    KR10822864 Pro
    Currently Being Moderated
    user11062519 wrote:
    hi,

    We have a partition table ,partition is on date wise , one of a partition DAY_21MAR2013 has been corrupted , partition cant be truncated.

    how we can truncate the partition to insert the fresh data.

    Thanks
    post error details how it got corrupted ?.

    check below thread.

    Truncating A Partitioned Table

    Edited by: KR10822864 on Apr 17, 2013 7:24 AM
  • 3. Re: truncate corrupted partition
    user361671 Newbie
    Currently Being Moderated
    we are using oracle 11.2.0.3.0 on solaris 10 sparc 64 bit. Actually one of our asm disk in which this particular partition resides has been formatted.
    so when we query on this partition it shows ORA-01578: ORACLE data block corrupted (file # 10, block # 26345557).

    How can we truncate this partition so that we insert new data.

    Thanks.
  • 4. Re: truncate corrupted partition
    KR10822864 Pro
    Currently Being Moderated
    user11062519 wrote:
    we are using oracle 11.2.0.3.0 on solaris 10 sparc 64 bit. Actually one of our asm disk in which this particular partition resides has been formatted.
    so when we query on this partition it shows ORA-01578: ORACLE data block corrupted (file # 10, block # 26345557).

    How can we truncate this partition so that we insert new data.

    Thanks.
    why your going to truncate instead of BMR?
    is it your database is in archive log mode or not?backup strategies not defined for your db?
  • 5. Re: truncate corrupted partition
    TSharma-Oracle Guru
    Currently Being Moderated
    I do not have a test database to play with this right now but you have couple of options. First you can try using DBMS_REPAIR.CHECK_OBJECT and DBMS_REPAIR.FIX_CORRUPT_BLOCKS which would help you fix or skip corrupt blocks.
    Second, If you are pretty sure you do not need any data, you can try DROPPING that partition and recreate it again.
    Alter table <table_name> drop partition <partition_name>;
    Thirdly, Youc an do the block level media recovery if you have RMAN backpups available and you are on Enterprise Edition.
  • 6. Re: truncate corrupted partition
    user361671 Newbie
    Currently Being Moderated
    unfortunately database is in no archive log mode and we don't have backup of such table.

    Thanks
  • 7. Re: truncate corrupted partition
    TSharma-Oracle Guru
    Currently Being Moderated
    Did you try dropping the partition?
  • 8. Re: truncate corrupted partition
    DK2010 Guru
    Currently Being Moderated
    Hi,

    Have you tried the DROP STORAGE Caluse.. like

    ALTER TABLE <TAB_AME> TRUNCATE PARTITION <PART_NAME> DROP STORAGE;
  • 9. Re: truncate corrupted partition
    KR10822864 Pro
    Currently Being Moderated
    user11062519 wrote:
    unfortunately database is in no archive log mode and we don't have backup of such table.

    Thanks
    as Sharma suggested try those options.(if you have test db..)

    if you have any important data in corrupted partition...take export bkp of corrupted table... recreate it and then import.
  • 10. Re: truncate corrupted partition
    TSharma-Oracle Guru
    Currently Being Moderated
    KR10822864 wrote:
    user11062519 wrote:
    unfortunately database is in no archive log mode and we don't have backup of such table.

    Thanks
    as Sharma suggested try those options.(if you have test db..)

    if you have any important data in corrupted partition...take export bkp of corrupted table... recreate it and then import.
    Anyhow the OP was trying to truncate the table which is permanent loss of data anyhow and I did ask OP to make sure if he does not need data at all, then only he can try dropping the partition. You are just repeating the words.
  • 11. Re: truncate corrupted partition
    KR10822864 Pro
    Currently Being Moderated
    TSharma wrote:
    KR10822864 wrote:
    user11062519 wrote:
    unfortunately database is in no archive log mode and we don't have backup of such table.

    Thanks
    as Sharma suggested try those options.(if you have test db..)

    if you have any important data in corrupted partition...take export bkp of corrupted table... recreate it and then import.
    Anyhow the OP was trying to truncate the table which is permanent loss of data anyhow and I did ask OP to make sure if he does not need data at all, then only he can try dropping the partition. You are just repeating the words.
    can please tell me where i have repeated words....?if you think i repeated words my apologise for that.
  • 12. Re: truncate corrupted partition
    KR10822864 Pro
    Currently Being Moderated
    i have suggested one forum link mine very first post which is having same info like how to truncate partition and what do after truncate. plz check it.
    note: truncate a portioned table invalidate indexes.rebuild those

    Edited by: KR10822864 on Apr 17, 2013 8:34 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points