Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: PUBLIC schema is visible?

    I have access to OEM and TOAD, but PUBLIC doesn't show up as a schema, despite the following message from OEM:

    21 object(s) are invalid in the PUBLIC schema.

    If I had to guess, I'd say the object(s) are PUBLIC SYNONYMS. How can I find out what they are?

    -Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    OK, sorry for the easy question. I am curious about the answers I found, though. How do I research why these synonyms and the underlying SYS objects are INVALID? Do I just try to recompile them, and then keep looking if they fail to recompile? Just nervous since they're not objects that we created.

    SQL> select object_name
    2 from dba_objects
    3 where status = 'INVALID' and owner = 'PUBLIC';

    OBJECT_NAME
    ---------------------------------------------------
    DBA_HIST_FILESTATXS
    DBA_HIST_SQLSTAT
    DBA_HIST_SQLBIND
    DBA_HIST_SYSTEM_EVENT
    DBA_HIST_WAITSTAT
    DBA_HIST_LATCH
    DBA_HIST_LATCH_CHILDREN
    DBA_HIST_LATCH_PARENT
    DBA_HIST_LATCH_MISSES_SUMMARY
    DBA_HIST_DB_CACHE_ADVICE
    DBA_HIST_ROWCACHE_SUMMARY
    DBA_HIST_SGASTAT
    DBA_HIST_SYSSTAT
    DBA_HIST_SYS_TIME_MODEL
    DBA_HIST_OSSTAT
    DBA_HIST_PARAMETER
    DBA_HIST_SEG_STAT
    DBA_HIST_ACTIVE_SESS_HISTORY
    DBA_HIST_TABLESPACE_STAT
    DBA_HIST_SERVICE_STAT
    DBA_HIST_SERVICE_WAIT_CLASS

    21 rows selected.

  3. #3
    Join Date
    Oct 2004
    Posts
    145
    More than likely these object reference objects that were removed and public synonyms not removed.

    select object_type as well from the dba_objects table.

    One simple method of verification is recompiling or deleting and attempting to recreating the object based on existing definition. This will give you error that will make more sense to you.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    The underlying objects are owned by SYS though. In this case, most of them deal with the Automatic Workload Repository, so recreating them is undoubtedly more involved.

    -cf

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Just for grins ... here's a script that will find synonyms that no longer
    are valid ....


    select 'drop ' || decode(owner,'PUBLIC','PUBLIC SYNONYM ',
    'SYNONYM '||owner||'.')||
    '"' || synonym_name|| '"' || ' -- refers to '
    || table_owner
    || '.' || table_name ||
    decode (db_link,null,null,
    '@'||db_link) || '-- ;'
    from dba_synonyms A
    where TABLE_OWNER not in ('SYS','SYSTEM','DBSNMP')
    and owner not in ('SYS', 'SYSTEM', 'DBSNMP')
    and table_name not in (select object_name
    from dba_objects B
    where object_type in
    ('TABLE','VIEW','PROCEDURE',
    'PACKAGE','FUNCTION',
    'SEQUENCE')
    and A.table_owner = B.owner)
    and db_link is null
    order by table_owner, table_name
    ;


    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •