HelpdeskDocsTemplate/logo.png HelpdeskDocsTemplate/UTS.png

When possible, the Data Fix should be setup and tested in TEST first.

Many times, the data prevents this, so single execution in PROD is acceptable.

Create the Worksheet

Create a worksheet in your personal datafix directory (ie. /home/c/cox/datafixes).

This filename should contain the Footprints Ticket Number and possibly a table name if applicable.

  • Ex. ticket_fpnumber_tablename_worksheet.sql ==> ticket_078935_sprhold_worksheet.sql

Several key tasks are required - show data before and after fix and make a backup of the data if needed.

An example worksheet is provided at the end of this page.

  • The SELECTs can be tested for verification.
  • Be sure the WHERE clauses match and are updated.
  • After the exection of the "Data Fix", copy the spooled sysout to the "Data Fix Audit" directory.

"Data Fix Audit" directory is located at \\admnet\shares\University_Technology_Services\UTSDOCS\Database Team\SharedStuff\Data Fix\uts_projects .

First, create a new folder named as the Footprints Ticket number, example 078935.

All sysout from the data fix needs to be placed in this directory in a folder name as the Ticket Number.

Be sure sysout contains record counts of Before and After's, along with "backup" table name and counts if deleting.

Execution of the Data Fix

Open dual or two panels on the appropriate BANJOBS Server.

In one, open or list the worksheet.

In the other, per the worksheet, log into !SQLPlus.

Copy and paste each command from the worksheet to !SQLPlus.

Example of a Data Fix Worksheet

Copy and paste into your worksheet. Change the generic items to fit your specific needs.

-- Log into SQLPLUS as oakdba# or baninst1
sqlplus oakdb#

-- Set output parameters
set time on
set newp 0
set echo on
set verify off
set feedback on
set termout on
set showmode off
set pagesize 66
set linesize 132
set newpage 0

-- Setup Spool - to ticket_#######_tablename.txt (Should match Footprint and Worksheet)
spool ticket_#######_tablename.txt

-- Show the current Date / Time
SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss am') CURR_DATE
  FROM DUAL
;

-- Copy Table --
-- If you only need to copy the data your are removing add the Criteria in a WHERE clause
DROP TABLE OAKDBA#.TABLENAME_DEL_MMDDYYYY;

CREATE TABLE OAKDBA#.TABLENAME_DEL_MMDDYYYY
AS
SELECT  *
  FROM SCHEMA.TABLENAME
-- WHERE COLUMN_KEYS     = 'criteria'
;

SELECT COUNT(*)
  FROM OAKDBA#.TABLENAME_DEL_MMDDYYYY
;

-- Show the Data BEFORE --
SELECT COUNT(*)
  FROM SCHEMA.TABLENAME
;

SELECT COUNT(*)
  FROM SCHEMA.TABLENAME
 WHERE COLUMN_KEYS     = 'criteria'
;


SELECT *
  FROM SCHEM.TABLENAME
-- WHERE COLUMN_KEYS     = 'criteria'
;

-- Execute UPDATEs --
UPDATE SCHEMA.TABLENAME
SET COLUMNAME_FIELD   = 'new value'
WHERE COLUMN_KEYS     = 'criteria'
;

--
-- show the Data AFTER --
SELECT COUNT(*)
  FROM SCHEMA.TABLENAME
;

SELECT COUNT(*)
  FROM SCHEMA.TABLENAME
 WHERE COLUMN_KEYS     = 'criteria'
;


SELECT *
  FROM SCHEM.TABLENAME
-- WHERE COLUMN_KEYS     = 'criteria'
;

-- Once data checks out issue COMMIT
COMMIT;

-- Show the current Date / Time
SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss am') CURR_DATE FROM DUAL;

-- Turn Spool off
spool off
exit

DataAdminHowTo

DB_Administration