Operate Oracle SQL developer – Adding table on Shadow System – All

  1. Click on the New Table to create a blank table.
    Adding table to Shadow 1
  2. Fill in the table name and click the Advanced box. Use green cross to add a new column and fill the column properties. Use red cross to delete column you do not want.
    Adding table to Shadow 2
  3. When finish step 2, you will get this.
    Adding table to Shadow 3
  4. Create a new procedure.
    Adding table to Shadow 4
  5. Program a script in the procedure, the script should following the template below.
    • create or replace
      PROCEDURE here replace by percedure name AS
      ecode    NUMBER;
      emesg    VARCHAR2(200);
      BEGIN
      —
      —
      INSERT
      INTO shadow_copy_log VALUES
      (
      shadow_copy_log_seq.nextval,
      ‘SHADOWCOPY’,
      ‘COPYSTART’,
      ”,
      ‘SHARTSHADOWCOPY’,
      ‘noerror’,
      sysdate,
      TO_CHAR(sysdate,’YYYYMMDD:HH24:MI:SS’)
      );
      COMMIT;
      ——
      INSERT
      INTO shadow_copy_log VALUES
      (
      shadow_copy_log_seq.nextval,
      ‘SHADOWCOPY’,
      ‘here replace by table name‘,
      ”,
      ‘COPY START’,
      ‘noerror’,
      sysdate,
      TO_CHAR(sysdate, ‘YYYYMMDD:HH24:MI:SS’)
      ) ;
      EXECUTE IMMEDIATE ‘TRUNCATE TABLE here replace by table name REUSE STORAGE’;
      INSERT /*+ APPEND */
      INTO here replace by table name
      SELECT  here replace by name of column 1,
          here replace by name of column 2,
          here replace by name of column 3,
          … … …
          here replace by name of column n
      FROM here replace by table name @FODHOSTING ;
      INSERT
      INTO shadow_copy_log VALUES
      (
      shadow_copy_log_seq.nextval,
      ‘SHADOWCOPY’,
      ‘here replace by table name‘,
      ”,
      ‘COPY ENDED’,
      ‘noerror’,
      sysdate,
      TO_CHAR(sysdate, ‘YYYYMMDD:HH24:MI:SS’)
      ) ;
      COMMIT;
      —
      INSERT
      INTO shadow_copy_log VALUES
      (
      shadow_copy_log_seq.nextval,
      ‘SHADOWCOPY’,
      ‘COPYEND’,
      ”,
      ‘ENDSHADOWCOPY’,
      ‘noerror’,
      sysdate,
      TO_CHAR(sysdate, ‘YYYYMMDD:HH24:MI:SS’)
      );
      COMMIT;
      —
      EXCEPTION
      WHEN OTHERS THEN
      BEGIN
      ecode := SQLCODE;
      emesg := SQLERRM;
      INSERT
      INTO shadow_copy_log VALUES
      (
      shadow_copy_log_seq.nextval,
      ‘SHADOWCOPY’,
      ‘COPYABORT’,
      ”,
      ‘ERROR’,
      TO_CHAR(NVL(ecode, 0)) || ‘-‘ || emesg,
      sysdate ,
      TO_CHAR(sysdate, ‘YYYYMMDD:HH24:MI:SS’)
      );
      COMMIT;
      END;
      END;
      –select * from shadow_copy_log order by timestamp desc
      –truncate table shadow_copy_log
      –EXEC famis_dashboard.shadow_copy;
  6. Compile, debug and run the procedure.
  7. In your local computer, find the check out folder of commit software, such as Versions or SVN. Open the SHADOW_COPY.SQL file in the scripts/dev/main folder of the check out folder.
  8. Only add the following part in the appropriate location of SHADOW_COPY.SQL
    —
    INSERT
    INTO shadow_copy_log VALUES
    (
    shadow_copy_log_seq.nextval,
    ‘SHADOWCOPY’,
    ‘here replace by table name‘,
    ”,
    ‘COPY START’,
    ‘noerror’,
    sysdate,
    TO_CHAR(sysdate, ‘YYYYMMDD:HH24:MI:SS’)
    ) ;
    EXECUTE IMMEDIATE ‘TRUNCATE TABLE here replace by table name REUSE STORAGE’;
    INSERT /*+ APPEND */
    INTO here replace by table name
    SELECT  here replace by name of column 1,
    here replace by name of column 2,
        here replace by name of column 3,
        … … …
        here replace by name of column n
    FROM here replace by table name @FODHOSTING ;
    INSERT
    INTO shadow_copy_log VALUES
    (
    shadow_copy_log_seq.nextval,
    ‘SHADOWCOPY’,
    ‘here replace by table name‘,
    ”,
    ‘COPY ENDED’,
    ‘noerror’,
    sysdate,
    TO_CHAR(sysdate, ‘YYYYMMDD:HH24:MI:SS’)
    ) ;
    COMMIT;
  9. Commit the SHADOW_COPY.SQL by your commit software such as Versions or SVN.