I was asked if it was possible to generate all ddl since last restore point. I came up with following script. It might help others looking to do something similar
set pages 0 long 200000 feedback off lines 2000 trims on newpage 0 space 0 echo off verify off heading off termout off col USER_DDL format a500; spool meta_data.sql SET SERVEROUTPUT ON DECLARE MD_DDL CLOB; BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); --Generate all database objects using dba_objects FOR I IN (SELECT OWNER, OBJECT_NAME, CASE WHEN OBJECT_TYPE = 'DATABASE LINK' THEN 'DB_LINK' WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN 'PACKAGE_BODY' ELSE OBJECT_TYPE END OBJECT_TYPE FROM DBA_OBJECTS WHERE ( CREATED > (SELECT TIME FROM V$RESTORE_POINT) OR LAST_DDL_TIME > (SELECT TIME FROM V$RESTORE_POINT)) AND OWNER NOT LIKE '%SYS%' AND OWNER NOT LIKE '%ORACLE%') LOOP MD_DDL := DBMS_METADATA.GET_DDL (I.OBJECT_TYPE, I.OBJECT_NAME, I.OWNER); DBMS_OUTPUT.PUT_LINE (MD_DDL || chr(10)); END LOOP; END; / --Generate Roles SELECT DBMS_METADATA.GET_DDL ('ROLE', U.NAME) ROLE_DDL FROM SYS.USER$ U, DBA_ROLES R WHERE U.CTIME > (SELECT TIME FROM V$RESTORE_POINT) AND U.NAME = R.ROLE; --Generate "CREATE USER" statements SELECT DBMS_METADATA.GET_DDL ('USER', U.USERNAME) USER_DDL FROM DBA_USERS U WHERE CREATED > (SELECT TIME FROM V$RESTORE_POINT); --Generate GRANT ROLE statements SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || ';' ROLE_GRANT FROM DBA_ROLE_PRIVS WHERE GRANTEE IN (SELECT NAME FROM SYS.USER$ U WHERE CTIME > (SELECT TIME FROM V$RESTORE_POINT)); --Generate grants on database objects SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE || ';' TAB_GRANT FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SELECT NAME FROM SYS.USER$ U WHERE CTIME > (SELECT TIME FROM V$RESTORE_POINT)); --This is step is optional. Depending on your environment, you might not want to run the following statement SELECT 'ALTER USER ' || USERNAME || ' DEFAULT ROLE ALL;' FROM DBA_USERS WHERE CREATED > (SELECT TIME FROM V$RESTORE_POINT);