Restore Oracle schema to a known state

snapshot.sql

CREATE OR REPLACE FORCE VIEW V_RESTORABLE_TABLES AS
  SELECT table_name tbl from user_tables WHERE
    table_name not like 'FRESH%'
    AND temporary = 'N'
    AND table_name NOT IN ('SNAPSHOT_DATA','SNAPSHOT_TABLE_GROUPS','TABLES_META_DATA')
  minus
  (select log_table from user_mview_logs)
  minus
  (select object_name from user_objects where object_type='MATERIALIZED VIEW');

CREATE TABLE SNAPSHOT_DATA(
  SNAPSHOT VARCHAR2(100),
  TBL VARCHAR2(30),
  MAX_SCN NUMBER(30),
  NUM_ROWS NUMBER(30),
  CONSTRAINT PK_SNAPSHOT_DATA PRIMARY KEY (SNAPSHOT, TBL)
) ORGANIZATION INDEX;


CREATE GLOBAL TEMPORARY TABLE TEMP_IDS_WITH_INCREASED_SCNS (
  ID NUMBER(30),
  CONSTRAINT TEMP_IDS_WITH_INC_SCNS_PK PRIMARY KEY (ID)
);


CREATE OR REPLACE FORCE VIEW V_TABLES_WITH_SINGLE_COLUMN_PK AS
  SELECT table_name tbl, pk_column FROM
   (
     SELECT cons_cols.table_name, cons_cols.column_name pk_column, tab_cols.DATA_TYPE data_type, COUNT(*) OVER (PARTITION BY cons.table_name) c
     FROM user_constraints cons, user_cons_columns cons_cols, user_tab_columns tab_cols
     WHERE cons.constraint_type = 'P'
           AND cons.table_name = cons_cols.table_name
           AND cons.constraint_name = cons_cols.constraint_name
           AND tab_cols.table_name = cons_cols.table_name
           AND cons_cols.column_name = tab_cols.column_name
   )
  WHERE c = 1 AND data_type = 'NUMBER';

CREATE TABLE TABLES_META_DATA(
  TBL VARCHAR2(30),
  PK_COLUMN VARCHAR2(30),
  SUPPORTS_SET_OPERATIONS NUMBER(1)
);

CREATE UNIQUE INDEX TABLES_META_DATA_UIDX ON TABLES_META_DATA(TBL);

CREATE GLOBAL TEMPORARY TABLE TEMP_V_SNAPSHOT_SCNS(
  tbl VARCHAR2(30),
  max_scn NUMBER(30),
  num_rows number(30),
  CONSTRAINT PK_TEMP_V_SNAPSHOT_SCNS PRIMARY KEY (TBL)
) ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE FORCE VIEW NOT_DEFERRABLE_CONSTRAINTS AS
  select  uc.constraint_name,
    uc.table_name l_tbl,
    ui.table_name r_tbl,
    LISTAGG(ucc.column_name, ',') WITHIN GROUP (ORDER BY ucc.position) l_cols,
    LISTAGG(uic.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY uic.column_position) r_cols
  from user_constraints uc, user_cons_columns ucc, user_indexes ui, user_ind_columns uic
  WHERE uc.DEFERRABLE = 'NOT DEFERRABLE'
        AND uc.constraint_type = 'R'
        AND uc.status = 'ENABLED'
        AND uc.constraint_name = ucc.constraint_name
        AND uc.table_name = ucc.table_name
        AND uc.R_CONSTRAINT_NAME = ui.INDEX_NAME
        AND uic.INDEX_NAME = ui.index_name
        AND uic.column_position = ucc.position
  GROUP BY uc.constraint_name, uc.table_name, ui.table_name;


CREATE TABLE SNAPSHOT_TABLE_GROUPS(
  TABLES_GROUP VARCHAR2(100),
  TBL VARCHAR2(30),
  CONSTRAINT PK_SNAPSHOT_TABLE_GROUPS PRIMARY KEY (TABLES_GROUP, TBL)
) ORGANIZATION INDEX;


CREATE OR REPLACE PACKAGE SNAPSHOT AUTHID CURRENT_USER AS
  PROCEDURE MAKE_CONSTRAINTS_DEFERRABLE;

  FUNCTION  HASH_NAME(name VARCHAR2) RETURN VARCHAR2;

  FUNCTION  FRESH_NAME(tbl VARCHAR2, snapshot VARCHAR2) RETURN VARCHAR2;

  FUNCTION  TABLE_EXISTS(tbl varchar2) RETURN NUMBER;

  PROCEDURE POPULATE_TABLES_META_DATA;

  FUNCTION CREATE_SNAPSHOTS_VIEW(existingSnapshotArg VARCHAR2, tablesGroupArg VARCHAR2) RETURN VARCHAR2;

  PROCEDURE TAKE_SNAPSHOT(snapshotArg VARCHAR2 DEFAULT 'FRESH', tablesGroupArg VARCHAR2 DEFAULT NULL);

  PROCEDURE TAKE_TABLE_SNAPSHOT(business_tbl VARCHAR2,
                                fresh_tbl VARCHAR2,
                                fresh_tbl_num_rows NUMBER,
                                business_tbl_num_rows NUMBER,
                                pk_column VARCHAR2,
                                fresh_tbl_max_scn NUMBER,
                                supports_set_operations NUMBER);

  PROCEDURE RESTORE_TO_FRESH;

  PROCEDURE FILL_TEMP_V_SNAPSHOT_SCNS(snapshotArg VARCHAR2, tablesGroupArg VARCHAR2);

  PROCEDURE CREATE_DELTA_TABLES_GROUP(snapshotArg VARCHAR2, tablesGroupArg VARCHAR2);

  PROCEDURE RESTORE_SCHEMA( snapshotArg VARCHAR2 DEFAULT 'FRESH',
                            tablesGroupArg VARCHAR2 DEFAULT NULL);

  PROCEDURE COPY_TABLE(   source_tbl VARCHAR2,
                          target_tbl VARCHAR2,
                          pk_column  VARCHAR2,
                          fresh_scn NUMBER,
                          source_num_rows NUMBER,
                          target_num_rows NUMBER,
                          supports_set_operations NUMBER,
                          copyFresh2Business NUMBER);
END SNAPSHOT;
/

CREATE OR REPLACE PACKAGE BODY SNAPSHOT AS

  PROCEDURE MAKE_CONSTRAINTS_DEFERRABLE AS
  begin
    for r in (select * from not_deferrable_constraints)
    loop
      execute immediate 'alter table '||r.l_tbl
                        ||' drop constraint '||r.constraint_name;
      execute immediate 'alter table '||r.l_tbl
                        ||' add constraint '||r.constraint_name ||
                        ' FOREIGN KEY (' || r.l_cols || ') REFERENCES ' ||
                        r.r_tbl ||'('||r.r_cols||') DEFERRABLE INITIALLY IMMEDIATE';
    end loop;
  end;

  FUNCTION HASH_NAME(name VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    RETURN substr(dbms_crypto.hash(to_clob(name), 1), 1, 18);
  END;

  FUNCTION FRESH_NAME(tbl VARCHAR2, snapshot VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    RETURN 'FRESH_'||HASH_NAME(tbl||'_'||snapshot);
  END;

  function TABLE_EXISTS(tbl varchar2) RETURN NUMBER as
    table_doesnt_exist EXCEPTION;
    PRAGMA EXCEPTION_INIT(table_doesnt_exist, -942);
  begin
    EXECUTE IMMEDIATE  'SELECT 1 FROM '||tbl||' WHERE ROWNUM < 2';
    RETURN 1;
  EXCEPTION
    WHEN table_doesnt_exist THEN
      RETURN 0;
  end;

  PROCEDURE POPULATE_TABLES_META_DATA AS
  BEGIN
    DELETE TABLES_META_DATA;
    INSERT INTO TABLES_META_DATA SELECT vr.tbl, pk_column, 1 FROM
      V_RESTORABLE_TABLES vr
     LEFT JOIN
      V_TABLES_WITH_SINGLE_COLUMN_PK vs
     ON vr.tbl = vs.tbl;
  END;

  FUNCTION CREATE_SNAPSHOTS_VIEW(existingSnapshotArg VARCHAR2, tablesGroupArg VARCHAR2) RETURN VARCHAR2 AS
    view_sql    CLOB;
    view_name   VARCHAR2(30);
  BEGIN
    select 'V_'||sum(to_number(substr(dbms_crypto.hash(to_clob(TBL), 1), 1, 18), 'xxxxxxxxxxxxxxxxxx')) INTO view_name
    FROM (
      SELECT 'SNAPSHOT_TABLE_GROUPS_'||TBL AS TBL FROM SNAPSHOT_TABLE_GROUPS WHERE TABLES_GROUP = tablesGroupArg
      UNION ALL
      SELECT 'SNAPSHOT_DATA_'||TBL AS TBL FROM SNAPSHOT_DATA WHERE SNAPSHOT = existingSnapshotArg
    );

    IF TABLE_EXISTS(view_name) = 1 THEN
      RETURN view_name;
    END IF;

    for r in (
      SELECT tbl FROM TABLES_META_DATA WHERE
      (existingSnapshotArg IS NULL AND tablesGroupArg IS NULL)

      UNION ALL

      SELECT sng.TBL
      FROM SNAPSHOT_TABLE_GROUPS sng, SNAPSHOT_DATA snp WHERE
      (existingSnapshotArg IS NOT NULL AND tablesGroupArg IS NOT NULL) AND
      sng.TBL = snp.TBL AND sng.TABLES_GROUP = tablesGroupArg AND snp.SNAPSHOT = existingSnapshotArg

      UNION ALL

      SELECT TBL FROM SNAPSHOT_TABLE_GROUPS WHERE
      (existingSnapshotArg IS NULL AND tablesGroupArg IS NOT NULL) AND
      TABLES_GROUP = tablesGroupArg

      UNION ALL

      SELECT TBL FROM SNAPSHOT_DATA WHERE
      (existingSnapshotArg IS NOT NULL AND tablesGroupArg IS NULL) AND
      SNAPSHOT = existingSnapshotArg
    )
    loop
      view_sql := view_sql || '(SELECT '''||r.tbl||''' tbl, NVL(max(ora_rowscn),0) max_scn, COUNT(*) num_rows FROM '||r.tbl||' t)'
                  || ' UNION ALL';
    end loop;

    dbms_lob.trim(view_sql,dbms_lob.getlength(view_sql) - 10);

    EXECUTE IMMEDIATE 'CREATE OR REPLACE FORCE VIEW '||view_name||' AS '||view_sql;

    RETURN view_name;
  END;

  PROCEDURE TAKE_SNAPSHOT(snapshotArg VARCHAR2 DEFAULT 'FRESH', tablesGroupArg VARCHAR2 DEFAULT NULL) AS
  BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_V_SNAPSHOT_SCNS';
    EXECUTE IMMEDIATE 'INSERT INTO TEMP_V_SNAPSHOT_SCNS SELECT * FROM '||CREATE_SNAPSHOTS_VIEW(NULL, tablesGroupArg);

    for r in (
      WITH prev AS (SELECT * FROM SNAPSHOT_DATA WHERE SNAPSHOT_DATA.SNAPSHOT = snapshotArg),
           cur AS (SELECT snp.*, meta.PK_COLUMN pk_column, meta.supports_set_operations
                 FROM TEMP_V_SNAPSHOT_SCNS snp JOIN TABLES_META_DATA meta ON snp.tbl = meta.tbl)
      SELECT  cur.tbl,
              cur.supports_set_operations,
              prev.max_scn prev_scn,
              cur.max_scn cur_scn,
              prev.num_rows prev_num_rows,
              cur.num_rows cur_num_rows,
              FRESH_NAME(cur.tbl, snapshotArg) fresh_name,
              cur.pk_column pk_column
      from cur LEFT JOIN prev ON (cur.tbl = prev.tbl)
      WHERE prev.max_scn IS NULL OR cur.max_scn > prev.max_scn OR cur.num_rows < prev.num_rows
    )
    loop
      IF r.prev_scn IS NULL THEN
        dbms_output.put_line('PK for '||r.tbl||' is '||r.pk_column);
        insert into SNAPSHOT_DATA(snapshot, tbl, max_scn, num_rows) values(snapshotArg, r.tbl, r.cur_scn, r.cur_num_rows);
      ELSE
        update SNAPSHOT_DATA SET max_scn = r.cur_scn, num_rows = r.cur_num_rows WHERE tbl = r.tbl AND snapshot = snapshotArg;
      END IF;

      IF r.cur_num_rows > 0 OR r.prev_num_rows > 0 THEN
        TAKE_TABLE_SNAPSHOT(r.tbl, r.fresh_name, r.prev_num_rows, r.cur_num_rows, r.pk_column, r.prev_scn, r.supports_set_operations);
      END IF;
    end loop;

    COMMIT;
  END;

  PROCEDURE TAKE_TABLE_SNAPSHOT(business_tbl VARCHAR2,
                                fresh_tbl VARCHAR2,
                                fresh_tbl_num_rows NUMBER,
                                business_tbl_num_rows NUMBER,
                                pk_column VARCHAR2,
                                fresh_tbl_max_scn NUMBER,
                                supports_set_operations NUMBER) AS
    illegal_use_of_long_data_type EXCEPTION;
    PRAGMA EXCEPTION_INIT(illegal_use_of_long_data_type, -997);

    name_is_used EXCEPTION;
    PRAGMA EXCEPTION_INIT(name_is_used, -955);

    tableExists NUMBER;
  BEGIN
    tableExists := TABLE_EXISTS(fresh_tbl);
    IF tableExists = 0 THEN
      dbms_output.put_line('Creating fresh table '||fresh_tbl||' for '||business_tbl);
      execute immediate 'CREATE TABLE '||fresh_tbl||' AS SELECT * FROM '||business_tbl;
      IF pk_column is not null THEN
        -- This primary key is used to optimize queries for changed columns from fresh_tbl.
        execute immediate 'ALTER TABLE ' ||fresh_tbl||' ADD CONSTRAINT '||fresh_tbl||'_PK PRIMARY KEY ('||pk_column||')';
      ELSE
        dbms_output.put_line(business_tbl||' does not contain a single column numeric primary key');
      END IF;
    ELSE
      COPY_TABLE(business_tbl, fresh_tbl, pk_column, fresh_tbl_max_scn, business_tbl_num_rows, fresh_tbl_num_rows, supports_set_operations, 0);
    END IF;
  EXCEPTION
    WHEN illegal_use_of_long_data_type THEN
      dbms_output.put_line('Unable to use ''create table as select'' for '||business_tbl||' since it contains a long data type, table will not be restored');

    WHEN name_is_used THEN
      Raise_application_error(-20001,'Failed to use ''create table as select'' for '||business_tbl||', table will not be restored'
                                     ||', CODE - '||SQLCODE||' - ERROR - '||SQLERRM);
  END;

  PROCEDURE RESTORE_TO_FRESH AS
    table_doesnt_exist EXCEPTION;
    PRAGMA EXCEPTION_INIT(table_doesnt_exist, -942);
  BEGIN
    RESTORE_SCHEMA;
    for r in (select tbl, snapshot from snapshot_data where snapshot != 'FRESH')
    loop
      begin
        execute immediate 'drop table '||fresh_name(r.tbl, r.snapshot);
      exception
        WHEN table_doesnt_exist THEN NULL;
      end;
    end loop;
    delete snapshot_data where snapshot != 'FRESH';
    COMMIT;
  END;

  PROCEDURE FILL_TEMP_V_SNAPSHOT_SCNS(snapshotArg VARCHAR2, tablesGroupArg VARCHAR2) AS
  BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_V_SNAPSHOT_SCNS';
    EXECUTE IMMEDIATE 'INSERT INTO TEMP_V_SNAPSHOT_SCNS
      WITH prev AS (SELECT * FROM SNAPSHOT_DATA WHERE snapshot = :1)
      select cur.* from prev JOIN '||CREATE_SNAPSHOTS_VIEW(snapshotArg, tablesGroupArg)||' cur on prev.tbl = cur.tbl
         WHERE
          (cur.max_scn > prev.max_scn OR cur.num_rows < prev.num_rows)' USING snapshotArg;
  END;

  PROCEDURE CREATE_DELTA_TABLES_GROUP(snapshotArg VARCHAR2, tablesGroupArg VARCHAR2) AS
  BEGIN
    FILL_TEMP_V_SNAPSHOT_SCNS(snapshotArg, NULL);
    DELETE SNAPSHOT_TABLE_GROUPS WHERE TABLES_GROUP = tablesGroupArg;
    INSERT INTO SNAPSHOT_TABLE_GROUPS SELECT tablesGroupArg, TBL FROM TEMP_V_SNAPSHOT_SCNS;
  END;

  PROCEDURE RESTORE_SCHEMA( snapshotArg VARCHAR2 DEFAULT 'FRESH',
                            tablesGroupArg VARCHAR2 DEFAULT NULL) AS
    updated_scn           NUMBER;
  BEGIN

    FILL_TEMP_V_SNAPSHOT_SCNS(snapshotArg, tablesGroupArg);

    EXECUTE IMMEDIATE 'SET CONSTRAINTS ALL DEFERRED';

    FOR r IN (
      WITH prev AS (SELECT * FROM SNAPSHOT_DATA WHERE snapshot = snapshotArg)
      select  prev.tbl,
              prev.max_scn prev_scn,
              cur.max_scn cur_scn,
              prev.num_rows prev_num_rows,
              meta.pk_column pk_column,
              cur.num_rows cur_num_rows,
              FRESH_NAME(prev.tbl, snapshotArg) fresh_name,
              meta.supports_set_operations supports_set_operations
      from TEMP_V_SNAPSHOT_SCNS cur, TABLES_META_DATA meta, prev
        WHERE prev.tbl = cur.tbl AND cur.tbl = meta.tbl
    )
    LOOP
      dbms_output.put_line('table: '||r.tbl||' prev scn: '||r.prev_scn||' cur scn: '||
                           r.cur_scn||' prev num rows: '||r.prev_num_rows||' cur num rows: '||r.cur_num_rows);
      COPY_TABLE(r.fresh_name, r.tbl, r.pk_column, r.prev_scn, r.prev_num_rows, r.cur_num_rows, r.supports_set_operations, 1);
    END LOOP;

    COMMIT;

    SELECT current_scn INTO updated_scn FROM V$DATABASE;

    update SNAPSHOT_DATA SET max_scn = updated_scn WHERE tbl in (SELECT tbl FROM TEMP_V_SNAPSHOT_SCNS) AND snapshot = snapshotArg;

    COMMIT;
  end;

  PROCEDURE COPY_TABLE(   source_tbl VARCHAR2,
                          target_tbl VARCHAR2,
                          pk_column  VARCHAR2,
                          fresh_scn NUMBER,
                          source_num_rows NUMBER,
                          target_num_rows NUMBER,
                          supports_set_operations NUMBER,
                          copyFresh2Business NUMBER) AS

    inconsistent_datatypes EXCEPTION;
    PRAGMA EXCEPTION_INIT(inconsistent_datatypes, -932);

    updated_target_num_rows NUMBER;
    business_tbl    VARCHAR2(30);
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('Copying table '||source_tbl||' to '||target_tbl);

    IF source_num_rows = 0 THEN
      -- Cannot truncate (which implicitly commits), as we must deffer integrity constraints
      -- check until changes are made to all tables.
      EXECUTE IMMEDIATE 'DELETE '|| target_tbl;
    ELSIF pk_column is null OR supports_set_operations = 0 THEN
      execute immediate 'DELETE '||target_tbl;
      execute immediate 'INSERT INTO ' ||target_tbl||' SELECT * FROM '||source_tbl;
    ELSE
      delete temp_ids_with_increased_scns;

      IF copyFresh2Business = 1 THEN
        business_tbl := target_tbl;
      ELSE
        business_tbl := source_tbl;
      END IF;

      EXECUTE IMMEDIATE 'INSERT INTO temp_ids_with_increased_scns SELECT '||pk_column||' FROM '|| business_tbl || ' WHERE ora_rowscn > :1' USING fresh_scn;

      EXECUTE IMMEDIATE 'DELETE '||target_tbl||' WHERE '||pk_column||' IN (select '||pk_column||' from
      (SELECT * FROM '|| target_tbl || ' WHERE '||pk_column||' IN (SELECT ID FROM temp_ids_with_increased_scns)
        minus
      SELECT * FROM '||source_tbl||' WHERE '||pk_column||' IN (SELECT ID FROM temp_ids_with_increased_scns)))';

      updated_target_num_rows := target_num_rows - SQL%ROWCOUNT;

      EXECUTE IMMEDIATE 'INSERT INTO '||target_tbl||'
      (SELECT * FROM '|| source_tbl || ' WHERE '||pk_column||' IN (SELECT ID FROM temp_ids_with_increased_scns)
        minus
      SELECT * FROM '||target_tbl||' WHERE '||pk_column||' IN (SELECT ID FROM temp_ids_with_increased_scns))';

      updated_target_num_rows := updated_target_num_rows + SQL%ROWCOUNT;

      IF updated_target_num_rows < source_num_rows THEN
        EXECUTE IMMEDIATE 'INSERT INTO '||target_tbl||' (SELECT * FROM '|| source_tbl || ' minus SELECT * FROM '||target_tbl||')';
      ELSIF updated_target_num_rows > source_num_rows THEN
        EXECUTE IMMEDIATE 'DELETE '||target_tbl||' WHERE '||pk_column||' IN
        (SELECT '||pk_column||' FROM (SELECT * FROM '|| target_tbl || ' minus SELECT * FROM '||source_tbl||'))';
      END IF;
    END IF;

  EXCEPTION
    -- will be thrown when trying to use minus operator on tables that contain LOB columns.
    WHEN inconsistent_datatypes THEN
      execute immediate 'DELETE '||target_tbl;
      IF source_num_rows > 0 THEN
        execute immediate 'INSERT INTO ' ||target_tbl||' SELECT * FROM '||source_tbl;
      END IF;
      UPDATE TABLES_META_DATA SET SUPPORTS_SET_OPERATIONS = 0 WHERE tbl = target_tbl;

    WHEN OTHERS THEN
      Raise_application_error(-20001,'Error while restoring '||target_tbl||', CODE - '||SQLCODE||' - ERROR - '||SQLERRM);
  END;

END SNAPSHOT;
/

EXECUTE SNAPSHOT.POPULATE_TABLES_META_DATA;
EXECUTE SNAPSHOT.MAKE_CONSTRAINTS_DEFERRABLE;

The snapshot package supports restoring a schema to a known state.
It was developed as part of Traiana's automation project.

Usage:

EXECUTE SNAPSHOT.TAKE_SNAPSHOT('snapshot name');
EXECUTE SNAPSHOT.RESTORE_SCHEMA('snapshot name');

The following privileges should be granted:

GRANT EXECUTE ON dbms_crypto TO <USER>;
GRANT SELECT ON V$DATABASE TO <USER>;

Caveats:

1. The above script makes all non deferrable constraints deferrable.
2. Snapshots are implemented by cloning tables, so they use at least the amount of space your schema uses.
3. Virtual columns are not supported.

Like

Anyone used this with 12c?

I had to change the V$DATABASE grant to: grant all on V_$DATABASE to <USER>;.

But I'm not sure why this is failing:

SQL> EXECUTE SNAPSHOT.TAKE_SNAPSHOT('foo');
BEGIN SNAPSHOT.TAKE_SNAPSHOT('foo'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 1116
ORA-06512: at "FOO.SNAPSHOT", line 90
ORA-06512: at "FOO.SNAPSHOT", line 100
ORA-06512: at line 1

Ok, I had another pass with the correct grants and everything is working perfectly, so I don't know what I did wrong the first time, but it does work ok with 12c.