Oracle DB SQLs – Table Data Backup Steps

 

Business Need:

Before changing a record, previous data needs to be backed up in a table for analysis.

Helpful to analyze change done by custom program or user actions on a table data.

 

Queries:

CREATE TABLE OE_OLINES_NOV23A
AS
SELECT * FROM OE_ORDER_LINES_ALL OL WHERE HEADER_ID = 5801517
/
           
ALTER TABLE OE_OLINES_NOV23A ADD (OL_INSERT_DATE DATE DEFAULT SYSDATE)
/
 
INSERT INTO OE_OLINES_NOV23A
SELECT OL.*, SYSDATE FROM OE_ORDER_LINES_ALL OL WHERE HEADER_ID = 5801517;
 
SELECT * FROM OE_OLINES_NOV23A;
 

 

Notes:

1.    These steps are backing up a single order’s lines. The WHERE clause can be substituted for a time window ( CREATION_DATE > SYSDATE-1 ) or Order TYPE ( LINE_TYPE_ID = 2529 ) or any other parameters that needs to be monitored.

 It may be a good idea to have all temp table have a standard format extensions (Eg: %_DATE or  %_BACK) and delete them periodically (After 1 month after creation)

  

Keywords:

Oracle DB, EBS, OM, Order Lines, OE_ORDER_LINES_ALL, Query, SQL, SQLPLUS