Saturday, January 13, 2024

Oracle sys grants missing after Schema level export import


During  schema level export/import    there are high  chances of   grants to go missing .    This is  one of case where  sys grants  are found missing  . It  has been  nicely explained  in Oracle Doc 1911151.1 .

Workaround is  to  manually  get DDL  for grants from source database and apply in target  using below   script . 



spool grants_tc.out

col GRANTS for a80
-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-- Add below the users and/or roles as appropriate for GRANTEE
-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
select 'grant ' || privilege || ' on ' ||'"'||table_name ||'"'||
      ' to ' || grantee || ';' "GRANTS"
 from dba_tab_privs
where owner = 'SYS' and privilege not in ('READ', 'WRITE')
  and grantee in ('TC')
order by 1;

spool off



References : 

Data Pump: GRANTs On SYS Owned Objects Are Not Transferred With Data Pump And Are Missing In The Target Database (Doc ID 1911151.1)



No comments:

Post a Comment