Re: Strange - missing synonyms after export/import

From: BD <bobby_dread_at_hotmail.com>
Date: 18 Nov 2005 12:08:12 -0800
Message-ID: <1132344492.641700.232170@g14g2000cwa.googlegroups.com>

>1. did you import with full=y or did you import a subset of the
>schemas?
>2. please differentiate between private and public synonyms. the
>private synonyms imported just fine I would assume, but the public syns
>would not be created if the import was less than full

I'm referring to public synonyms.
it was not a full export - here is the export script:

exp USERID=/            \
   BUFFER=200000       \
   FULL=N              \
   OWNER=SCHEMA1,SCHEMA2,SCHEMA3,SCHEMA4,SCHEMA5,SCHEMA6 \
   GRANTS=Y            \
   ROWS=Y              \
   INDEXES=Y           \
   CONSISTENT=Y        \
   FILE=$PIPEFILE      \

   LOG=${FNAME}_DB.log

What I did to check for missing synonyms was:

select owner, table_name from dba_tables where owner like 'SCHEMA%' and table_name not in (select table_name from dba_synonyms)

select owner, view_name from dba_views where owner like 'SCHEMA%' and view_name not in (select table_name from dba_synonyms)

****I assume that since there is no 'view_name' in dba_synonyms, that a view is treated the same as a table in that system view.

Regardless - I ran the same exact query in the original db and on the new one - on the original, it returned 7 records - I was able to cull all of these as being 'correctly absent' - but on the new db, it returned over 50 records.

I've re-created them all with a gen script, but the question still stands - how could 10% of the public synonyms be missing, when they were all owned by a schema owner and not a dba/sys schema? I'd think that they'd either all be present or all be absent.

Frankly, I expect I'll have to try some more iterations of the exp/imp process to confirm these findings. Received on Fri Nov 18 2005 - 14:08:12 CST