0

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.

Gary G
  • 1
  • 1
  • Have you considered whether you should be using Datapump (either the simple impdp binary from the OS or the more complicated dbms_datapump API from within PL/SQL) rather than dbms_metadata? Datapump actually uses dbms_metadata itself internally, but it's built in all the gotchas and workarounds for the gremlins you're going to find doing it yourself. I know, I've done my own schema replication system using dbms_metadata. It took a huge amount of code and require a lot of complexity to make it comprehensive enough to use as a generic tool. I wouldn't advise others to do the same unless necesry – Paul W Feb 25 '23 at 04:46
  • I am trying to develop a series of steps to move a schema from one database to the another. I want it to be as automated as possible. I could try using the SQLFILE option for datapump. I am not that familiar with anything running from inside PL/SQL. Do you have any further info on that? – Gary G Feb 26 '23 at 17:13
  • It's the year 2023. I find it troubling that I have to hunt and peck all over the internet to figure this out. I'm simply trying to transfer a schema from one DB to another, something I have done countless times before. It's just this time there are several grants to SYS objects that are part of the schema where I guess I have never had that situation prior to now. I mean, why DON'T the SYS object grants come along with the schema? Perhaps there is a reason but it is not obvious to me right now. – Gary G Feb 26 '23 at 17:13
  • Datapump works best outside of the database, run as expdp, and there's no reason why that couldn't be automated (invoked via shell script, which can be invoked via crontab, etc..). However, you can run it from within PL/SQL, it's just a bit more complicated. Look at the docs for dbms_datapump – Paul W Feb 26 '23 at 18:57
  • the Datapump API involves setup procs like dbms_datapump.open, dbms_datapump.metadata_filter, possibly dbms_datapump.metadata_remap and dbms_datapump.set_parameter, then starting it with dbms_datapump.start_job, ending it with dbms_datapump.detach, and possibly handling work-in-progress and/or error messages with dbms_datapump.stop_job, dbms_datapump.get_status, dbms_datapump.ku$_status_job_error, dbms_datapump.ku$_status_wip. I have code but it's far too long to share here. You can look at the docs on dbms_datapump and figure most of it out. Again, simply using the expdp utility is far easier – Paul W Feb 26 '23 at 19:08

0 Answers0