Skip to content

Generate Meta Data Since Last Restore Point

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);
Published inOracle Database 11gR2

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *