Perform schema import by changing grant and synonym

Question

Perform schema import by changing grant and synonym

Navigation

#1 by (1 votes)

1

In an environment I have 5 schemas that make grant and synonym between them, of which they are already working.

Now I need to duplicate this default environment for the client environment, and I need to change the name of schemas , grant and synonym .

The schemas part of the IMPDP resolve this:

REMAP_SCHEMA=SCHEMAPADRAO:SCHEMACLIENTE
SCHEMAS=SCHEMAPADRAO

But I always have to redo the grant and synonym because they are incorrect.

I would like to resolve this in the IMPDP itself, but otherwise I would like another solution.

oracle oracle11g

asked by anonymous 26.06.2018 / 13:58

1 answer

Animation with height with relative measures Pass connection string to dataset via code

score 1 · Answer 1

Rotate these selects and execute the result.

 SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',USERNAME) FROM ALL_USERS WHERE (USERNAME IN ('APEX_030200', 'APEX_PUBLIC_USER','ETC')) ORDER BY USER_ID;   

 SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',USERNAME) FROM ALL_USERS WHERE (USERNAME IN ('APEX_030200', 'APEX_PUBLIC_USER','ETC')) ORDER BY USER_ID;

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
  order by owner;