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

grep - the coolest Unix command ever :)

This is a cool Unix command.

The grep command searches the given file for lines containing a match to the given strings or words. By default, grep displays the matching lines. Use grep to search for lines of text that match one or many regular expressions, and outputs only the matching lines.

Usage Examples:

$ grep ath /etc/passwd

> simple search for a word in a single file


$ grep -r "192.168.1.1" /etc/

> search through the files within a folder


$ egrep -w -i 'thomas|david' filename

> search for 2 words in a single line