Want to limit results from dbms_metadata.get_granted_ddl for object_grant in Oracle

111 Views Asked by At

I am trying to do an Oracle schema level transfer from one database to another. I have discovered that grants to SYS objects are NOT transferred with a schema. This is documented here: Doc ID 1911151.1 but the proposed solution script does not always work.

I am trying to use get_granted_ddl to extract ONLY the sys object grants using the statement below, and I am constantly given ALL the objects grants instead of just the SYS grants.

set long 1120000 longchunksize 1120000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
    dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
    dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'USERNAME') AS ddl
from   dba_tab_privs tp
where  tp.grantee = 'USERNAME' and tp.grantor = 'SYS'
and rownum = 1
/

I cannot limit my results using this format. It's as if the tp.grantor = 'SYS' is not there. The results are the same with or without it.

I ran this statement and was expecting fewer rows in the result.

0

There are 0 best solutions below