63 lines
1.5 KiB
SQL
63 lines
1.5 KiB
SQL
/*
|
|
*
|
|
* 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;
|
|
/
|