Author: Dean Capps

Database consultant at Amazon Web Services.

Processing a text file with spaces in the data and a file separator

I needed to code a script that would read through a text file and process some data. The format of the file was as follows:

Item Id|Item Name
01|Car
02|Bus
03|Plane

The code was:

#!/usr/bin/ksh
for parm_line in `cat dean_test.txt`
do
    export item_id=$(echo $parm_line | cut -f1 -d\|)
    export item_name=$(echo $parm_line | cut -f2 -d\|)
 
    if  [ $item_id != "Item Id" ]; then
        echo $parm_line
    fi
done

My expectation was that it would skip the header line and process the three data lines. The output looked like:

Item
Id|Item
Name
01|Car
02|Bus
03|Plane

This was being caused by the fact that the header row had spaces in the names of the fields. I had to change the code as follows and add the IFS:

#!/usr/bin/ksh
 
IFS=$'\n' 
 
for parm_line in `cat dean_test.txt`
do
    export item_id=$(echo $parm_line | cut -f1 -d\|)
    export item_name=$(echo $parm_line | cut -f2 -d\|)
 
    if  [ $item_id != "Item Id" ]; then
        echo $parm_line
    fi
done

This change fixed the issues and I got the correct output:

01|Car
02|Bus
03|Plane

The short snippet of code was part of a much larger script. I could not use a WHILE loop as I was going to be issuing ssh commands.

Read tar.gz files without extracting

I had a need to look for certain commands in a list of Oracle audit logs what had been tarred and zipped (*.tar.gz format). I created a list with an ls command:

ls *.gz > dean.txt

I then used the below python code to read through the zipped tar balls looking for certain strings:

#!/usr/bin/python
import tarfile,os
import sys
 
list_of_tar = open('dean.txt', 'r')
for tar_name in list_of_tar:
    tar_name = tar_name.rstrip()
    print tar_name
    tar = tarfile.open(tar_name,'r:gz')
    for member in tar.getnames():
        file_name=tar.extractfile(member)
        for line in file_name:
            line = line.lower()
            if 'alter ' in line:
                if ' system ' in line:
                    if 'kill' in line:
                        print tar_name, member , line
tar.close()

This was a quick and dirty python to fulfill an immediate need. With more time the search condition could be improved.

“No space left on device” despite being less than 100% used

We were encountering issues writing to a mount point on a server: 

[test-server] ls > dean
-bash: dean: No space left on device

The file system was 18% free:

[test-server] df -h
Filesystem            Size  Used Avail Use% Mounted on
/file-system           99G   77G   18G  82% /mount-point

The issue was caused by the inodes being used up:

[test-server] df -i /mount-point
Filesystem            Inodes   IUsed IFree IUse% Mounted on
/file-system         6553600 6553600     0  100% /mount-point

Working on the assumption that the largest directory would have the largest number of files

du -h /mount=point | grep '[0-9]G' | sort -nr -k1

I identified the mount point and cleaned up the files.

Hail mary on db links

We had a situation wherein a colleague was attempting to get DB Links working. This was on a 11.2.0.4 database where password obfuscation is not allowed and the users were unsure of the passwords. After multiple changes to the passwords on the target system and drops and recreates of the links, the users continued to encounter errors while using the db link. We looked up a number of posts by different people on the web and then tried

alter system flush shared pool;

This resolved the issue. This was a non production environment. Please consider all the implications before you issue a flush in a production environment.

ORA-20005: object statistics are locked in AWS/RDS environment

While testing the AWS RDS environment, I ran across the issue that the statistics were locked:

begin 
     DBMS_STATS.GATHER_TABLE_STATS 
       (
          ownname => '"DEAN"',
          tabname => '"table name"',
          estimate_percent => 5
       );
end;

The error was:

Error starting at line : 6 in command -
begin 
     DBMS_STATS.GATHER_TABLE_STATS 
       (
          ownname => '"DEAN"',
          tabname => '"table name"',
          estimate_percent => 5
       );
end;
Error report -
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 34707
ORA-06512: at line 2

I checked with

select table_name, stattype_locked from dba_tab_statistics where table_name in ( select table_name from dba_tables where owner = 'DEAN');

The STATTYPE_LOCKED column contained the value of “ALL”. I was able to unlock this with

exec dbms_stats.unlock_table_stats('DEAN', 'table name');

This changed the value in the STATTYPE_LOCKED to “null” after which I was able to gather statistics.

The Fred Factor by Mark Sanborn

The Fred Factor by Mark Sanborn is based on the author’s observations of the work habits of his very memorable postal carrier, Fred Shea. The author was intrigued by the quality of customer service that Fred extended to all the customers on his route despite the constraints of his role as a member of the US Postal Service. Mr. Sanborn’s observations led him to write this book as a guide for both employees and organizations to improve their level of involvement and accomplishment in every aspect of their work.
The Fred Factor

An overview of DevOps from The Phoenix Project

The below Power Point is my review of the “The Phoenix Project: A Novel About IT, DevOps, and Helping Your Business Win” by Gene Kim, Kevi Behr and George Spafford. The book is a fictional account as experienced by Bill Palmer. The Company, Parts Unlimited, is facing challenges, both external and internal. On a Tuesday morning, Bill is called into the CEOs office and informed that the executive hierarchy of the IT department have decided to “pursue other opportunities” and that he is the new VP of IT.
Phoenix Project DevOps

Cleaning up the sys.aud$ table

I inherited a non-production database that had been auditing to the DB (audit_trail=DB) without any cleanup process in place. The sys.aud$ table had over 8 million rows of data. Before I set up the cleanup jobs, I decided to remove all the data in the table. I issued a truncate:

Error starting at line : 16 in command -
truncate table sys.aud$
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

A select on dba_objects indicated that sys.aud$ was a synonym

select owner, object_name, object_type from dba_objects where object_name = 'AUD$';
 
OWNER    OBJECT_NAME   OBJECT_TYPE
-------- -----------   -----------
SYS      AUD$          SYNONYM
SYSTEM   AUD$          TABLE

The table is SYSTEM.AUD$, but I thought I would check anyway:

select table_owner, table_name from dba_synonyms where owner = 'SYS' and synonym_name = 'AUD$';
 
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYSTEM                         AUD$

Truncated the base table:

truncate table system.aud$;
142
51
73
77