Pranav Bakare

Posted on Aug 2

External Tables | File Management

✅ External Table Log Management – Overview & Cleanup Strategy

📌 Objective
We are creating Oracle External Tables (like EXT_STN_OPN_CLS_HRS) which access CSV data files stored in directories (e.g., NGCSUTL). During query execution or loading, log files (.log) and bad files (.bad) may be generated automatically by Oracle’s external table mechanism (if not suppressed in ACCESS PARAMETERS).

To ensure:

Observability (error tracking from .bad/.log),
Storage hygiene (no clutter over time),

We’re enabling log file generation even in production and setting up a weekly cleanup script via cron job to retain only the last 7 days of .log and .bad files.

📁 External Table Definition

CREATE TABLE CGO_OWNR.EXT_STN_OPN_CLS_HRS
(
    STN_CODE        VARCHAR2(3),
    STN_ENTITY      VARCHAR2(25),
    DAY_1           VARCHAR2(1),
    DAY_2           VARCHAR2(1),
    DAY_3           VARCHAR2(1),
    DAY_4           VARCHAR2(1),
    DAY_5           VARCHAR2(1),
    DAY_6           VARCHAR2(1),
    DAY_7           VARCHAR2(1),
    OPEN_HOURS_1    VARCHAR2(10),
    CLOSE_HOURS_1   VARCHAR2(10),
    OPEN_HOURS_2    VARCHAR2(10),
    CLOSE_HOURS_2   VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY NGCSUTL
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        LOGFILE 'stn_opn_cls_hrs.log'
        BADFILE 'stn_opn_cls_hrs.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' RTRIM
        MISSING FIELD VALUES ARE NULL
    )
    LOCATION ('stn_opn_cls_hrs.csv')
)
REJECT LIMIT UNLIMITED;

🧹 Cleanup Strategy

We’ve created a scheduled script (cleanup_external_logs.sh) to:

Retain .bad and .log files for only the last 7 days
Run the script every Saturday at 2:00 AM
Prevent storage overload

🔁 Shell Script (cleanup_external_logs.sh)

#!/bin/bash

# Directory where log/bad files reside
LOG_DIR="/full/path/to/NGCSUTL"

# Days to keep (files older than this will be deleted)
DAYS_TO_KEEP=7

# File patterns to clean
FILE_PATTERNS=("*.bad" "*.log")

# Loop over file types and delete old files
for pattern in "${FILE_PATTERNS[@]}"; do
    find "$LOG_DIR" -type f -name "$pattern" -mtime +$DAYS_TO_KEEP -exec rm -f {} \;

done

echo "External table log cleanup completed on $(date)"

✅ Make the script executable

chmod +x /path/to/cleanup_external_logs.sh

🕒 Cron Job Setup
To schedule weekly cleanup at 2:00 AM every Saturday, edit your crontab:

crontab -e

Add this line:

0 2 * * 6 /path/to/cleanup_external_logs.sh >> /path/to/cleanup_log.txt 2>&1

This does:

0 2 * * 6: At 2:00 AM on Saturday

Let’s go line by line:

🧠 2. What Each Line Does — Step-by-Step

➤ #!/bin/bash

Shebang line.
Tells the system: "Run this script using the bash shell."
It ensures the commands are interpreted correctly (even if user shell is different).

➤ LOG_DIR="/your/full/path/to/NGCSUTL"

This sets a variable called LOG_DIR that stores the path to your log directory.
You will use this variable in the script instead of typing the path every time.

➤ DAYS_TO_KEEP=7

Tells the script: “Keep only files modified in the last 7 days”.
Older files (8 days or more) will be deleted.

➤ FILE_PATTERNS=("*.bad" "*.log")

This is a bash array.
We're telling the script: “Target files that match .bad or .log extensions.”

➤ The for Loop:

for pattern in "${FILE_PATTERNS[@]}"; do

This starts a loop.
It will run once for *.bad, and again for *.log.

➤ Inside the loop:


find "$LOG_DIR" -type f -name "$pattern" -mtime +$DAYS_TO_KEEP -exec rm -f {} \;

This is the core logic, let’s break it down:

Part Meaning
find Unix command to search for files
"$LOG_DIR" The directory where the search starts
-type f Only look for files (not folders)
-name "$pattern" Match the file pattern (*.bad or *.log)
-mtime +7 Match files modified more than 7 days ago
-exec rm -f {} \; For each match: run rm -f (force delete the file)
{} Placeholder for the found file
\; End of -exec command
So this line finds old .bad or .log files and deletes them one by one.

➤ echo "Cleanup completed."

Prints a message so that when you run the script, you know it finished.

▶️ 3. What Happens When You Run the Script
✅ Manually:

./cleanup_external_logs.sh

Here’s what happens:

Bash reads the script line-by-line.
It defines variables.
It loops through patterns (*.bad, *.log).
It finds and deletes old files.
Prints "Cleanup completed.

DEV Community