grants after schema import

A few ways you can do this:
Run a full import to a sqlfile to get the missing grants, you could then run them manually against the schema (Looks like you may have already tried this):
impdp full=y dumpfile=schema1.dmp sqlfile=schema1.sql INCLUDE=GRANT

Or run a full import of all grants:

impdp full=y dumpfile=schema1.dmp  INCLUDE=GRANT
Also verify that any Public Synonyms that your application might depend on that were created in your PRODUCTION instance are also created in your TEST instance.

Thanks.. If I run the grant file (from include=grant) that should cover all grants/privileges include system grants right. Also I dont need to edit the grant file right? I ran the grant file as sys.

Correct, the full import to a sqlfile will give you DDL for all object level grants from granter and grantee objects including system privileges but you will lose grants on 'sys' objects. You would need to use a sql script to extract object level grants on sys objects and recreate them in the target schemas if necessary.  You can use the sql above from 'franckpachot' to extract the DDL statements for 'sys' objects.

Hello,

Thanks for your clarifications I appreciate it. That means after I run the sql file, the below statement that retrieves
Object grants from sys should suffice right?

dbms_metadata.get_granted_ddl ('OBJECT_GRANT','BHJ') FROM DUAL;


For this schema 'BHJ' it results in two grants. Whereas the second query shown below that queries the dba_tab_privs is resulting in six grants...



SQL> SELECT dbms_metadata.get_granted_ddl ('OBJECT_GRANT','BHJ') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','BHJ')
--------------------------------------------------------------------------------


  GRANT EXECUTE ON "SYS"."UTL_FILE" TO "BHJ"

  GRANT EXECUTE ON "SYS"."UTL_SM


SQL> Select 'grant '||privilege||' on '||table_name||' to '||grantee||';'
  2  from dba_tab_privs
  3  where
  4  owner='SYS'
  5  AND grantee='BHJ';

'GRANT'||PRIVILEGE||'ON'||TABLE_NAME||'TO'||GRANTEE||';'
--------------------------------------------------------------------------------

grant WRITE on BASE to BHJ;
grant READ on BASE to BHJ;
grant EXECUTE on UTL_FILE to BHJ;
grant EXECUTE on UTL_SMTP to BHJ;
grant EXECUTE on DBMS_RLS to BHJ;
grant EXECUTE on DBMS_RANDOM to BHJ;

6 rows selected.

The result is a long and you see only part of it.
in sqlplus, do the following before:
set long 1000000
Regards,
Franck.

Hi, before i close the question, the script ran well however, there are no semicolons or / after each grant statement. They are only appearing after a block of grant statements. I dont think all statements will run. I tried adding a pipe and a semicolon or / after each line but it did not work. The script doesn't output them....

dbms_metadata.get_granted_ddl ('SYSTEM_GRANT',username)

Any ideas? Thanks..

Gentlemen, Thank you so much for your answers and help.