Oracle RDBMS 12.1.0.2
I'd like to generate a script that holds all grants issued by a user, using DBMS_METADATA.GET_GRANTED_DDL.
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from user_tab_privs tp
where tp.grantor= 'DATAOWNER'
and tp.table_name not like 'BIN$%'
and tp.grantee not in ('PUBLIC','METAOWNER')
and rownum=1
order by tp.table_name, tp.privilege;
I expect 2267 rows and for it to be ordered by table_name and privilege.
I can see this from user_tab_privs.
However when I add in the dbms_metadata call above, I only get 737 rows and it's not ordered.
I'm definitely missing something, perhaps I should go back to creating the SQL from user_tab_privs instead, but I was trying to use something new to me.
Sherrie