RE: Import Public Grants (9.2)

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 14 Jun 2005 09:21:55 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF41050395B92B@usahm236.amer.corp.eds.com>

 Public grants below to PUBLIC and not to the base object owner. You cannot import user PUBLIC so the only way to import public synonyms is as part of a full imp, full=3Dy. We usually run a imp with full=3Dy and rows=3Dn to get public objects after rebuilding a database, but you = cannot
do this if you only want portions of the source db.

So the solution is just to generate the create synonyms by selecting from dba_synonyms. Just add the necessary where clause to the following and a few SQLPlus set commands for feedback, linesize, etc....

select
  'create '||decode(owner,'PUBLIC','PUBLIC',NULL)||' synonym '||

             decode(owner,'PUBLIC',NULL,table_owner||'.')||
           synonym_name||' for '||
           table_owner||'.'||table_name||
           decode(db_link,NULL,NULL,'@'||db_link)||';'

from
  sys.dba_synonyms
Where ....

HTH -- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Smith, Ron L. Sent: Monday, June 13, 2005 4:38 PM
To: oracle-l_at_freelists.org
Subject: Import Public Grants (9.2)

I am trying to import grants made to Public for all the objects in a schema. I have tried running a full import as SYSTEM and as the Schema owner. Neither imports the Public grants. What am I doing wrong?

Thanks!
Ron=3D20
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 14 2005 - 09:27:14 CDT