Backup Tables in a Sandbox

This script is to backup a set of common tables. The owner and table_name are converted to a shorter version so there is room to add a date on the end of the name.

These are all written to a sandbox area, but are gathered from 5 different schemas.

If this is written in Golden, the SETs have to be on separate lines.

A report is produced at the end to show the names of the new tables.

/* SQL+ Commands to leave only SQL statements in the spool file */
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TIMING OFF

/* write SQL to this file from the following query */
SPOOL h:\Bkup_Parm.sql

-- Generate Create statements for all support_parameter tables
SELECT 'CREATE TABLE sbx_str_uat.'
    || CASE owner
          WHEN 'ODS_UAT'     THEN 'ODSUAT__'
          WHEN 'SBX_STR_UAT' THEN 'SBXSTRU_'
          WHEN 'SBX_BRD_UAT' THEN 'SBXBRDU_'
          WHEN 'ODS_UAT1'    THEN 'ODSUAT1_'
          WHEN 'FSS_UAT'     THEN 'FSSUAT__'
         END
    || CASE table_name
          WHEN 'SUPPORT_PARAMETER_VALUES'  THEN 'SPV_'
          WHEN 'SUPPORT_PARAMETER_GROUP'   THEN 'SPG_'
          WHEN 'SUPPORT_PARAMETER_CHOICES' THEN 'SPC_'
          WHEN 'SUPPORT_PARAMETER'         THEN 'SP__'
         END

    || TO_CHAR(SYSDATE,'MMDDYYYY')
    || ' AS SELECT * FROM '
    || owner
    || '.'
    || table_name
    ||';'
  FROM all_tables
 WHERE REGEXP_LIKE(table_name , '^PARAMETER') -- starts with
   AND NOT REGEXP_LIKE(table_name,'[[:digit:]]') -- don't get other backups
 ;

-- close this sql file
SPOOL OFF

-- run the code generated above
@h:\Bkup_Parm

-- list tables backed-up
SELECT owner      AS "Owner"
     , table_name AS "UAT Parameter Backups"
  FROM all_tables
 WHERE table_name like '%_SP%'||TO_CHAR(SYSDATE,'MMDDYYYY')
 ORDER BY table_name;