/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display controlfile record sections
*  Parameters : 
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  22-Jul-16  Vishal Gupta  Created
*
*/



set serveroutput on
set lines 1000
set trimspool on

SELECT type
    , (record_size * records_total) TotalSize
    , (record_size * records_used) UsedSize 
    , a.record_size, a.records_total, a.records_used
FROM V$CONTROLFILE_RECORD_SECTION a 
where type in ('DATABASE BLOCK CORRUPTION'
              ,'COPY CORRUPTION'
	      ,'DELETED OBJECT'
              ,'BACKUP CORRUPTION'
		)
order by type ;


BEGIN
for i in (SELECT type
        , (record_size * records_total) TotalSize
        , (record_size * records_used) UsedSize
        , a.record_size, a.records_total, a.records_used
    FROM V$CONTROLFILE_RECORD_SECTION a
    where type in ('DATABASE BLOCK CORRUPTION'
                  ,'COPY CORRUPTION'
                  ,'DELETED OBJECT'
                  ,'BACKUP CORRUPTION'
                    )
      and  records_used > 0
     ) 
LOOP
dbms_output.put_line('Reseting Controlfile section ...');
/* Reset BACKUP CORRUPTION  section */
dbms_backup_restore.ResetCfileSection(17);

/* Reset COPY CORRUPTION  section */
dbms_backup_restore.ResetCfileSection(18);

/* Reset DELETED OBJECTS  section */
dbms_backup_restore.ResetCfileSection(19);

/* Reset DATABASE BLOCK CORRUPTION  section */
dbms_backup_restore.ResetCfileSection(35);
END LOOP;
END;
/