DEV Community

mrcaption49
mrcaption49

Posted on

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;
Enter fullscreen mode Exit fullscreen mode

🧹 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)"
Enter fullscreen mode Exit fullscreen mode

✅ Make the script executable 

chmod +x /path/to/cleanup_external_logs.sh
Enter fullscreen mode Exit fullscreen mode
  • 🕒 Cron Job Setup
  • To schedule weekly cleanup at 2:00 AM every Saturday, edit your crontab: 
crontab -e
Enter fullscreen mode Exit fullscreen mode

Add this line:

0 2 * * 6 /path/to/cleanup_external_logs.sh >> /path/to/cleanup_log.txt 2>&1
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • 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"
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
  • 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")
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
  • 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 {} \;
Enter fullscreen mode Exit fullscreen mode

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."
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)