 Import - views/synonyms [message #329273] |
Tue, 24 June 2008 21:45  |
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 #329284 is a reply to message #329273] |
Tue, 24 June 2008 22:48  |
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 #329290 is a reply to message #329286] |
Tue, 24 June 2008 23:15 |
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 |
 |
BlackSwan
Messages: 26766
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)!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
