How to exclude public synonyms from an Oracle DB Import
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to exclude public synonyms from an Oracle DB Import

Hybrid View

  1. #1
    Join Date
    Dec 2010
    Posts
    7

    Question How to exclude public synonyms from an Oracle DB Import

    Hello,

    I am trying to import a database excluding public synonyms using impdp. A colleague gave me the following line to add to my impdp command, but it results in a syntax error:
    Code:
    INCLUDE=SYNONYM:"IN(SELECT synonym_name FROM dba_synonyms WHERE 1=0)"
    
    LRM-00116: syntax error at ')' following '0'
    Any help would be appreciated.

    Thank you.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Why not just exclude synonyms instead of using a select that won't select anything to begin with?

  3. #3
    Join Date
    Dec 2010
    Posts
    7
    Quote Originally Posted by stecal View Post
    Why not just exclude synonyms instead of using a select that won't select anything to begin with?
    How can I do that?

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    EXCLUDE

    Default: There is no default

    Purpose

    Enables you to filter the metadata that is exported by specifying objects and object types to be excluded from the export operation.

    Syntax and Description

    EXCLUDE=object_type[:name_clause] [, ...]

    http://download.oracle.com/docs/cd/E...t.htm#i1009903 (scroll down to EXCLUDE)

  5. #5
    Join Date
    Dec 2010
    Posts
    7
    So EXCLUDE=dba_synonyms is what I am looking to add?

    Sorry for my lack of knowledge, the DBA is gone for 3 weeks on vacation (will be back in early Jan.). He taught me the basics on how to import data with impdp, so Ive been fine untill now. But now this user is asking for something specific and I cannot deliver, and it seems it will be easy enough to add and not make them wait.

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,470

    Cool

    Are you doing FULL import or only schema import?

    If you are doing a full database import you should NOT exclude public synonyms, otherwise if you are just importing one or more schema(s), then no public synonyms should be created.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  7. #7
    Join Date
    Dec 2010
    Posts
    7
    Quote Originally Posted by LKBrwn_DBA View Post
    Are you doing FULL import or only schema import?

    If you are doing a full database import you should NOT exclude public synonyms, otherwise if you are just importing one or more schema(s), then no public synonyms should be created.
    Generally I just import one schema, and one tablespace, and remap them. So I guess I should be good.

    Thanks

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    No, it should just be "synonym" (metadata, not data). You're not excluding the dba_synonyms data dictionary view, but (private) synonyms as a whole. Public synonyms should still be accessible.

    Code:
      1  SELECT OBJECT_PATH, COMMENTS FROM SCHEMA_EXPORT_OBJECTS
      2* WHERE OBJECT_PATH LIKE '%SYN%' AND OBJECT_PATH NOT LIKE '%/%'
    SQL> /
    
    OBJECT_PATH
    ------------------------------------------------------------------
    COMMENTS
    ------------------------------------------------------------------
    SYNONYM
    Private synonyms in the selected schemas
    Private synonyms only.
    Last edited by stecal; 12-15-2010 at 01:24 PM.

  9. #9
    Join Date
    Dec 2010
    Posts
    7
    Hey,

    First just want to say I really appreciate the help... Your probably face palming at my questions lol.

    But you said that the public synonyms will be intact? The user specifically asked that the public synonyms not be included in the restore.

    Thanks

Posting Permissions

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



Click Here to Expand Forum to Full Width