Results 1 to 3 of 3

Thread: Orphan Synonyms

  1. #1
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41

    Unanswered: Orphan Synonyms

    One quick question, why synonyms ain't dropped when you dropped the owner?

    I did a 'drop user owner cascade'; but all the synonyms are still there. And the owner of the synonyms is the *dead* user.

    Can anyone explain why? Thanks!

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Since you are making a PUBLIC synonym then they belong to public and not the table or object owner.

    I agree it is a pain and probably should be looked into.
    On the good side, I don't have to re-create my synonyms when I re-create all my objects ...

    Check out dba_synonyms which will show the owner.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by The_Duck
    Since you are making a PUBLIC synonym then they belong to public and not the table or object owner.

    I agree it is a pain and probably should be looked into.
    On the good side, I don't have to re-create my synonyms when I re-create all my objects ...

    Check out dba_synonyms which will show the owner.
    This is again one where you need to think toward the egde of the cube. Get your database to write your script.

    SELECT 'DROP PUBLIC SYNONYM '||SYNONYM_NAME||';'
    FROM ALL_SYNONYMS
    WHERE TABLE_OWNER ='<OWNER>'

    SPOOL "FILENAME".SQL
    /
    SPOOL OFF
    @"FILENAME".SQL


    You could even use a similar script off of All_Tables not in all_synonyms to create them after an import/build.

    Just throw a penny at this...I can't afford to use up both cents right now.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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