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;

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.