André Spadini | MultiCloud | DBA

André Spadini | MultiCloud | DBA

Oracle: Generate user DDL / Grants

Oracle: Generate user DDL / Grants

Subscribe to my newsletter and never miss my upcoming articles

Gerando script de DDL com DBMS_METADATA.GET_DDL

Oracle Database 19c / 18c / 12c / 11g / 10g

spool grants.sql

SELECT dbms_metadata.get_ddl('USER','&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off

Gerando para os profiles

-- In my case, the result was a total of three profiles. Use the resulting rows in the script below:
SET HEADING OFF SET TRIMSPOOL ON SET FEEDBACK OFF EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE); 

-- your calls to dbms_metadata here SELECT 
DBMS_METADATA.GET_DDL('PROFILE','PROF') from dual; SELECT 
DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') from dual; SELECT 
DBMS_METADATA.GET_DDL('PROFILE','ONLINEUSR') from dual;
 
Share this