Home » RDBMS Server » Server Utilities » public synonyms are not getting imported
public synonyms are not getting imported [message #229800] Tue, 10 April 2007 01:41 Go to next message
karismapanda
Messages: 58
Registered: January 2007
Member
Hi,
I have 2 dbs of oracle 10g.user admin is there in both the dbs.
I am doing export as system user of admin schema.Then importing all data to db2 to admin schema as system user,which is a blank schema.Some of the public synonyms are getting imported and some are not.
Any help is appreciated.
Thanks,
Karisma
Re: public synonyms are not getting imported [message #229809 is a reply to message #229800] Tue, 10 April 2007 01:52 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Some of the public synonyms are getting imported and some are not.
Are you sure..
or
which type/mode of your export dump file ?

Regards
Taj
Re: public synonyms are not getting imported [message #229817 is a reply to message #229809] Tue, 10 April 2007 02:07 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
User mode----

This is how i did the export----


C:\Documents and Settings\karisma\Desktop>exp

Export: Release 10.2.0.1.0 - Production on Sun Apr 8 20:57:34 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 >

Export file: EXPDAT.DMP > exp_admin_0408.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
User to be exported: (RETURN to quit) > ww_admin

User to be exported: (RETURN to quit) >

. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user WW_ADMIN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user WW_ADMIN
About to export WW_ADMIN's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export WW_ADMIN's tables via Conventional Path ...
. . exporting table AS_BSNGP_ITM 3859 rows exported
. . exporting table AS_CATEGORY 83 rows exported
. . exporting table AS_ITM 15188 rows exported
. . exporting table AS_ITM_AGGT 0 rows exported



Thanks,
Karisma
Re: public synonyms are not getting imported [message #229845 is a reply to message #229817] Tue, 10 April 2007 02:54 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
In User Mode.
Public Synonym is not exported.

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1004941

Regards
Taj
Re: public synonyms are not getting imported [message #229861 is a reply to message #229800] Tue, 10 April 2007 03:29 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
but why does it says while exporting...........


. exporting PUBLIC type synonyms
. exporting private type synonyms



Thanks,
karisma
Re: public synonyms are not getting imported [message #230056 is a reply to message #229861] Tue, 10 April 2007 14:14 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
It is exporting public synonym created by the USER. Public synonyms created by other users, like SYSTEM (where they should have been created) will not be exported.
Re: public synonyms are not getting imported [message #230081 is a reply to message #230056] Tue, 10 April 2007 16:40 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try something like this
select 'CREATE '|| decode(owner,'PUBLIC','PUBLIC ',null) ||
       'SYNONYM ' || decode(owner,'PUBLIC',null, owner || '.') ||
        lower(synonym_name) || ' FOR ' || lower(table_owner) ||
        '.' || lower(table_name) ||
        decode(db_link,null,null,'@'||db_link) || ';'
  from sys.dba_synonyms
  where table_owner != 'SYS'
  and (  upper(table_owner) in (&1)
      or lower(table_owner) in (&&1) )
  and owner = 'PUBLIC'
  order by table_owner;
Re: public synonyms are not getting imported [message #230901 is a reply to message #229800] Fri, 13 April 2007 04:44 Go to previous message
karismapanda
Messages: 58
Registered: January 2007
Member
thanks for all of ur reply

[Updated on: Fri, 13 April 2007 04:44]

Report message to a moderator

Previous Topic: File name validation
Next Topic: tkprof - not generating output
Goto Forum:
  


Current Time: Fri Dec 09 17:53:18 CST 2016

Total time taken to generate the page: 0.31877 seconds