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);