Useful dictionary objects / queries

  • V$ACCESS – Displays objects in the database that are currently locked and the sessions that are accessing them.
  • V$FILESTAT – Contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time. This is useful in isolating where the I/O activity is happening if the bottleneck is I/O related.
  • show invalid objects:
    select * from all_objects o where o.status <> 'VALID
    
  • show dependencies for some object:
    select DISTINCT D.OWNER, D.name, D.REFERENCED_NAME
    from aLL_dependencies d where d.name = '...your_object...'
    ORDER BY D.REFERENCED_NAME
    
  • show lately changed objects:
    select * from all_objects o
    where o.owner = '...your_schema...'
    -- AND O.OBJECT_NAME NOT LIKE '...some_mask...%' 
    -- if you want to exclude something
    order BY O.TIMESTAMP DESC
    
  • check database for waits:
    select * from v$system_event where event like '%wait%';
    select * from v$waitstat;
    

Leave a Reply

Your email address will not be published. Required fields are marked *