| public synonyms are not getting imported [message #229800] |
Tue, 10 April 2007 01:41  |
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 #229817 is a reply to message #229809] |
Tue, 10 April 2007 02:07  |
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 #230081 is a reply to message #230056] |
Tue, 10 April 2007 16:40 |
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;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
