Oracle DBMS_DBVERIFY
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Internal API to the DBV executable for database datafile integrity verification
AUTHID CURRENT_USER
Dependencies Only STANDARD
Documented No
First Available 10.1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmsdbv.sql
 
DBV2
Verify data file integrity

Note: Any information on how to interpret the output will be greatly appreciated.
dbms_dbverify.dbv2(
fname     IN     VARCHAR2,
start_blk IN     BINARY_INTEGER,
end_blk   IN     BINARY_INTEGER,
blocksize IN     BINARY_INTEGER,
output    IN OUT VARCHAR2,
error     IN OUT VARCHAR2,
stats     IN OUT VARCHAR2);
conn sys@pdborcl as sysdba

col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%block%';

NAME                           VALUE
------------------------------ ------------------------------
db_block_buffers                0
db_block_checksum               TYPICAL
db_block_size                   8192
db_file_multiblock_read_count   128
db_block_checking               FALSE

conn sys@pdborcl as sysdba

SELECT file_name, tablespace_name
FROM dba_data_files
ORDER BY 2,1;

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ---------------
C:\APP\ORACLE\ORADATA\ORABASE2\ORCL\EXAMPLE01.DBF            EXAMPLE
C:\APP\ORACLE\ORADATA\ORABASE2\ORCL\SYSAUX01.DBF             SYSAUX
C:\APP\ORACLE\ORADATA\ORABASE2\ORCL\SYSTEM01.DBF             SYSTEM
C:\APP\ORACLE\ORADATA\ORABASE2\ORCL\SAMPLE_SCHEMA_USERS01.DB USERS

SELECT MIN(dbms_rowid.rowid_block_number(rowid)) MINBLOCK
FROM sh.sales;

  MINBLOCK
----------
      6290

SELECT MAX(dbms_rowid.rowid_block_number(rowid)) MAXBLOCK
FROM sh.sales;

  MAXBLOCK
----------
     21761

set serveroutput on
DECLARE
 lOut   VARCHAR2(4000);
 lErr   VARCHAR2(4000);
 lStats VARCHAR2(4000);
BEGIN
  dbms_dbverify.dbv2('c:\oracle\oradata\pdborcl\example01.dbf', 6290, 21761, 8192, lOut, lErr, lStats);

  dbms_output.put_line('Output: ' || lOut);
  dbms_output.put_line('Error:  ' || lErr);
  dbms_output.put_line('Stats:  ' || lStats);
END;
/

Related Topics
DBV
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved