Skip to end of metadata
Go to start of metadata

Overview

Overview about Oracle ANALYZE command, purpose and usage.

General Information

  • Use this only with combinations of DBV and/or RMAN
  • No logfile is written if executed directly from SQLPLUS. Called from within BRCONNECT, a logfile gets created.
  • Can run extremely long (days)
  • Terminates with an error message as soon the first error found! Further errors won't be not recognized! 

What IS checked by 'ANALYZE'

  • tables
  • dependent indexes of table
  • the cross-reference between indexes and tables

What IS NOT checked by 'ANALYZE'

  • LOB segments
  • UNDO segments
  • Oracle Datafiles
  • Archived Redo Logs
  • Redo Logs
  • Control File
  • Parameter File (SPFILE)
  • Oracle Wallet File 

Checking a single object

  1. SQL> ANALYZE TABLE "<owner>"."<table name>"  VALIDATE STRUCTURE CASCADE;
     
    or
     
    SQL> ANALYZE TABLE "<owner>"."<table name>"  VALIDATE STRUCTURE CASCADE ONLINE; 
     
  2. OS> brconnect  -u / -c -f stats -t <table_name> -v cascade
     
       or in case BR*Tools SSFS is set
     
    OS> brconnect  -u // -c -f stats -t <table_name> -v cascade
      

Checking the Oracle Dictionary

  1. OS>  brconnect -u / -c -f stats -t oradict_tab -v
    or
    OS>  brconnect -u // -c -f stats -t oradict_tab -v
     
  2. OS> sqlplus "/ as sysdba" @analyze_oracle_dictionary.sql

    Download 'analyze_oracle_dictionary.sql' from SAP note 23345. You must then check the spool output analyze_cascade.log for errors.

Checking the entire database

  1. OS> brconnect  -u / -c -f stats -v cascade -t all -e null -p <quantity>
     
      
    or in case BR*Tools SSFS is set

    OS> brconnect  -u // -c -f stats -v cascade -t all -e null -p <quantity>
     
    where:
      '-p <quantity>' is the number of Oracle tables checked in parallel.
     
  2. Use below script from note 23345
    SQL> sqlplus "/ as sysdba" @analyze_complete_db_9.sql

Navigating to Other Chapters

 

General Information

Detecting Corrupted Blocks

Consistency Check with DBV

Consistency Check with RMAN

Consistency Check with Data Pump

Which object is stored in the corrupted block(s)

Checks found corrupted blocks. What to do now