How to exclude public synonyms from an Oracle DB Import

[Decivox]

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:

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.

[stecal]

Why not just exclude synonyms instead of using a select that won't select anything to begin with?

[Decivox]

Why not just exclude synonyms instead of using a select that won't select anything to begin with?

How can I do that?

[stecal]

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)

[Decivox]

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.

[stecal]

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.

  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.

[Decivox]

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

[LKBrwn_DBA]

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.

[Decivox]

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