Quick search

Quick search only dictionary tables
    
select * from dictionary

Quick search everything on db

SELECT TYPE, owner, NAME, obj_owner, obj_name, syn_obj_type, db_link, 
  decode(db_link_private, 1, '/* run with user '|| owner || '*/', '') ||
  'select * from ' || obj_owner || '.' || obj_name || '@' || db_link query,
  query_for_linked_db, db_link_private
FROM (
  select owner, object_name||'.'||procedure_name NAME, 'pckg_procedure' as type, '' obj_owner, '' obj_name, '' AS syn_obj_type, '' AS db_link, 0 as db_link_private,
    '' AS query_for_linked_db 
  from dba_procedures
  UNION ALL
  SELECT owner, table_name NAME, 'TABLE' AS TYPE, '' obj_owner, '' obj_name, '' AS syn_obj_type, '' AS db_link, 0 as db_link_private,
    '' AS query_for_linked_db
  FROM dba_tables
  UNION ALL
  SELECT owner, view_name NAME, 'VIEW' AS TYPE, '' table_owner, '' table_name, '' AS syn_obj_type, '' AS db_link, 0 as db_link_private, 
    '' AS query_for_linked_db
  FROM dba_views
  UNION ALL
  SELECT owner, synonym_name, 'SYNONYM' AS TYPE, table_owner, table_name
                           --remember synonyms may refer to objects having sub_objects, so returning more than one row
    , (SELECT DECODE (object_type, 'TABLE PARTITION', 'TABLE', object_type)
       FROM dba_objects o
       WHERE o.owner = s.table_owner AND o.object_name = s.table_name AND ROWNUM = 1) syn_obj_type
  , db_link
  , (select count(*) from dba_db_links where owner = s.owner and db_link = s.db_link) db_link_type
  , DECODE(db_link, NULL, NULL, 'select * from dba_synonyms where synonym_name = ''' || table_name
        || ''' and owner = ''' || table_owner || '''') query_for_linked_db
   FROM dba_synonyms s
)
WHERE NAME LIKE UPPER ('%dbws%')
ORDER BY TYPE DESC, owner, NAME;