How to use dbms_metadata to generate DDL for profiles

An easy way to migrate your profiles from a source database to a target database during migration is to use the dbms_metadata package.

To generate one call for each profile:

SELECT UNIQUE 'SELECT DBMS_METADATA.GET_DDL(''PROFILE'',' || ''''|| PROFILE || ''') FROM DUAL;'
FROM DBA_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;