sed and awk for DBAs

OS - *ix

Some useful sed, awk and bash commands for dbas.

Some of them needs bit of improvements and might well have other better alternatives (e.g. one liners).

1. Provided with line by line list which needs to be put in to sql "IN" clause.

 [stefan@oc8233082860 ~]$ cat list.txt  
 RMS_USER_ROLE_DEFINITION   
 RMS_USER_ROLE   
 RMS_USER_DETAILS   
 [stefan@oc8233082860 ~]$ cat list.txt | awk -vORS=, '{ print $1 }' | sed 's/,$/\n/' > list2.txt  
 [stefan@oc8233082860 ~]$ sed -r 's/,([^ ),]+)/,'\''\1'\''/g; s/,,/,'\'\'',/g' list2.txt   
 RMS_USER_ROLE_DEFINITION,'RMS_USER_ROLE','RMS_USER_DETAILS'  
 [stefan@oc8233082860 ~]$   

2. Getting datafile name removing the directory path.

 [stefan@pc_stefan ~]$ cat cmd_test.txt   
 /dc2_db2/PACS/data/system.dbf  
 /dc2_db2/PACS/data/undo.dbf  
 /dc2_db2/PACS/data/sysaux.dbf  
 /dc2_db2/PACS/data/pacs_cust_data01.dbf  
 /dc2_db2/PACS/data/pacs_cust_data02.dbf  
 /dc2_db2/PACS/data/pacs_cust_data03.dbf  
 [stefan@pc_stefan ~]$ cat cmd_test.txt | awk -F/ '{print $NF}'  
 system.dbf  
 undo.dbf  
 sysaux.dbf  
 pacs_cust_data01.dbf  
 pacs_cust_data02.dbf  
 pacs_cust_data03.dbf  

Bash Alternative -> basename command

 #!/usr/bin/bash  
 if ! [ -r "$1" ]; then  
     echo "Run with filename"  
     exit 1  
 fi  
 cat "$1" | grep ^/ | while read line; do  
 basename $line  
 done  

3. Appending a newfile path to datafile list, eg changing directory structure for sql "set newname" command

 [stefan@pc_stefan ~]$ cat dataf.txt   
 system.dbf  
 undo.dbf  
 sysaux.dbf  
 [stefan@pc_stefan ~]$ sed -i -e 's#^#/ictpslt/RMS_MAR/#' dataf.txt  
 [stefan@pc_stefan ~]$ cat dataf.txt   
 /ictpslt/RMS_MAR/system.dbf  
 /ictpslt/RMS_MAR/undo.dbf  
 /ictpslt/RMS_MAR/sysaux.dbf  
 [stefan@pc_stefan ~]$   

4. Removing tab from a list.

 [stefan@pc_stefan ~]$ sed 's/\t/,/g' dfile.txt  

5. Concat multiple columns to create a command, eg creating "grant" commands when object name, object_owner, required privilege and grantee is provided.

 [stefan@pc_stefan ~]$ cat priv.txt   
 OWNER     TABLE_NAME     PRIVILEGE USER  
 ANA     ACCOUNT_BILLED_SERVICES     SELECT BETH  
 ANA     CUSTOMER_ID_ACCT_MAP     SELECT BETH  
 ANA     CUSTOMER_ID_EQUIP_MAP     SELECT BETH  
 [stefan@pc_stefan ~]$ cat priv.txt | awk '{print "grant "$3" on "$1"."$2" to "$4";"}'  
 grant PRIVILEGE on OWNER.TABLE_NAME to USER;  
 grant SELECT on ANA.ACCOUNT_BILLED_SERVICES to BETH;  
 grant SELECT on ANA.CUSTOMER_ID_ACCT_MAP to BETH;  
 grant SELECT on ANA.CUSTOMER_ID_EQUIP_MAP to BETH;