Home » RDBMS Server » Server Utilities » Import - views/synonyms (Oracle 10)
icon5.gif  Import - views/synonyms [message #329273] Tue, 24 June 2008 21:45 Go to next message
paul_s
Messages: 3
Registered: June 2008
Junior Member
Hi,
I'm doing an export/import from one schema to another. As well as the tables and row data I want to copy synonyms, views, etc. When I do this using exp/imp the target schema, understandably, has its views and synonyms pointing back to the source schema tables.
Is there a way of importing a schema and having the imported views, synonyms, grants, etc., altered so that they are associated with the target schema tables rather than the source?
I can alter synonyms and grants post-import by analysing user_synonyms, user_tabs_privs, but can't alter the views (as they are stored as longs not varchars) - am thinking there has to be an easier way!
Any ideas?
Thanks!
Paul.
Re: Import - views/synonyms [message #329276 is a reply to message #329273] Tue, 24 June 2008 22:03 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

>but can't alter the views
Please clarify.
Re: Import - views/synonyms [message #329284 is a reply to message #329273] Tue, 24 June 2008 22:48 Go to previous messageGo to next message
paul_s
Messages: 3
Registered: June 2008
Junior Member
I'm importing the schema then, as part of a batch script so it
can be re-run, creating a temp script which drops all current
synonyms and grants then recreates them for the new schema. I
can't do this for a view since views' text are stored as a
long, and I just want to perform a replace on the source schema
name to become the target schema name. (There may be a simpler
way of doing this but I'm not a db expert).

e.g. to change grants I run the following script which creates
another script for me to run...(where &1&2 is source schema and &1&3 is target schema)

spool &4.\alterGrants-&1.&3..sql;
select 'spool &5.\alterGrants-&1.&3..log' from dual;
select distinct 'REVOKE '||privilege||' ON '||table_name||'
FROM '||grantee||';' FROM user_tab_privs WHERE grantee LIKE
UPPER('%'||&2.);
select 'GRANT '||privilege||' ON '||table_name||' TO '||replace
(grantee,'&2.','&3.')||decode(grantable,'YES',' WITH GRANT
OPTION',NULL)||';' FROM user_tab_privs WHERE grantee LIKE UPPER
('%'||&2.);
select 'spool off;' from dual;
select 'exit' from dual;
spool off;

Hope that makes some sense. Seems a long-winded way for doing
this though.
Re: Import - views/synonyms [message #329286 is a reply to message #329273] Tue, 24 June 2008 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
What we have here is a failure to communicate.
Read & FOLLOW the Posting Guidelines!

I asked about VIEW & you expound on GRANT & ROLES.

I support 1 application which is completely contained single schema.

I "move" Production schema into any database simply by:

imp newuser/pwd1 file=prod.dmp fromuser=prod touser=newuser ....

where schema "newuser" is new & empty schema which has been GRANTed appropriate privs/roles.

you also might want to research:
dbms_metadata.get_ddl
Re: Import - views/synonyms [message #329290 is a reply to message #329286] Tue, 24 June 2008 23:15 Go to previous messageGo to next message
paul_s
Messages: 3
Registered: June 2008
Junior Member
Thank you for your prompt response. What we have here is a lack
of social skills often found with IT professionals. One of the
items in the guidelines is 'Be polite'.

You asked about VIEWS (caps, ok?). I stated that with my
limited database knowledge I could not replace views by using
the replace function as views are stored in a long, not a
varchar, but that I could do it with, for example, grants (as
illustrated). My method for doing it may be totally wrong, but
forums tend to exist to help out those who are NOT experts.

I am dealing with exporting/importing several schemas which
have views and synonyms between them. I should have mentioned
that first, but even so I don't think it quite warrants 'What
we have here...' etc.

Will work it out somehow, Cheers.

[Updated on: Tue, 24 June 2008 23:16]

Report message to a moderator

Re: Import - views/synonyms [message #329292 is a reply to message #329273] Tue, 24 June 2008 23:26 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You continue to not follow Posting Guidelines.

VIEW, GRANT, SYNONYM are all separate, distinct & different database objects.

You're On Your Own (YOYO)!
Previous Topic: schema export/import
Next Topic: SQL LOADER... URGENT HELP !!!
Goto Forum:
  


Current Time: Thu Dec 08 02:25:51 CST 2016

Total time taken to generate the page: 0.26438 seconds