Inside a Shell script One line command: 1# Set the SID 2ORAENV_ASK=NO 3export ORACLE_SID=HANA 4. oraenv 5 6# Trigger oneline command 7echo -e "select inst_id, instance_name, host_name, database_status from gv\$instance;" | sqlplus -S / as sysdba In bash script: 1su - oracle -c ' 2export SQLPLUS="sqlplus -S / as sysdba" 3export ORAENV_ASK=NO; 4export ORACLE_SID='${SID}'; 5. oraenv | grep -v "remains"; 6 7${SQLPLUS} <<EOF2 8set lines 200 pages 2000; 9select inst_id, instance_name, host_name, database_status from gv\$instance; 10exit; 11EOF2 12 13unset ORAENV_ASK; 14' Inside SQL Prompt 1-- with an absolute path 2@C:\Users\Matthieu\test.sql 3 4-- or trigger from director on which sqlplus was launched 5@test.sql 6 7-- START syntax possible as well 8START test.sql Variables usages 1-- User variable (if not define, oracle will prompt) 2SELECT * FROM &my_table; 3 4-- Prompt user to set a variable 5ACCEPT my_table PROMPT "Which table would you like to interrogate ? " 6SELECT * FROM $my_table; Some Examples Example of Shell script to launch sqlplus command: 1export ORACLE_SID=SQM2DWH3 2 3echo "connect ODS/ODS 4BEGIN 5ODS.PURGE_ODS.PURGE_LOG(); 6ODS.PURGE_ODS.PURGE_DATA(); 7END; 8/" | sqlplus /nolog 9 10echo "connect DSA/DSA 11BEGIN 12DSA.PURGE_DSA.PURGE_LOG(); 13DSA.PURGE_DSA.PURGE_DATA(); 14END; 15/" | sqlplus /nolog Example of script to check tablespaces.sh 1#!/bin/ksh 2 3sqlplus -s system/manager <<! 4SET HEADING off; 5SET PAGESIZE 0; 6SET TERMOUT OFF; 7SET FEEDBACK OFF; 8SELECT df.tablespace_name||','|| 9 df.bytes / (1024 * 1024)||','|| 10 SUM(fs.bytes) / (1024 * 1024)||','|| 11 Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1)||','|| 12 Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) 13 FROM dba_free_space fs, 14 (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df 15 WHERE fs.tablespace_name (+) = df.tablespace_name 16 GROUP BY df.tablespace_name,df.bytes 17 ORDER BY 1 ASC; 18quit 19! 20 21exit 0 1#!/bin/ksh 2 3sqlplus -s system/manager <<! 4 5set pagesize 60 linesize 132 verify off 6break on file_id skip 1 7 8column file_id heading "File|Id" 9column tablespace_name for a15 10column object for a15 11column owner for a15 12column MBytes for 999,999 13 14select tablespace_name, 15'free space' owner, /*"owner" of free space */ 16' ' object, /*blank object name */ 17file_id, /*file id for the extent header*/ 18block_id, /*block id for the extent header*/ 19CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 20from dba_free_space 21where tablespace_name like '%TEMP%' 22union 23select tablespace_name, 24substr(owner, 1, 20), /*owner name (first 20 chars)*/ 25substr(segment_name, 1, 32), /*segment name */ 26file_id, /*file id for extent header */ 27block_id, /*block id for extent header */ 28CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 29from dba_extents 30where tablespace_name like '%TEMP%' 31order by 1, 4, 5 32/ 33 34quit 35! 36 37exit 0 SPOOL to write on system from sqlplus: 1SQL> SET TRIMSPOOL on 2SQL> SET LINESIZE 1000 3SQL> SPOOL /root/output.txt 4SQL> select RULEID as RuleID, RULENAME as ruleName,to_char(DBMS_LOB.SUBSTR(EPLRULESTATEMENT,4000,1() as ruleStmt from gep_rules; 5SQL> SPOOL OFF from script.sql: 1SET TRIMSPOOL on 2SET LINESIZE 10000 3SPOOL resultat.txt 4ACCEPT var PROMPT "Which table do you want to get ? " 5SELECT * FROM &var; 6SPOOL OFF Generate DATA Duplicate table to fill up tablespace or generate fake data: 1SQL> Create table emp as select * from employees; 2SQL> UPDATE emp SET LAST_NAME='ABC'; 3SQL> commit;
A list of nice findings for Kubernetes List all images in Helm chart 1images=$(helm template -g $helm |yq -N '..|.image? | select(. == "*" and . != null)'|sort|uniq|grep ":"|egrep -v '*:[[:blank:]]' || echo "") upload images listed in an Helm chart 1load_helm_images(){ 2 # look in helm charts 3 for helm in $(ls ../../roles/*/files/helm/*.tgz); do 4 printf "\e[1;34m[INFO]\e[m Look for images in ${helm}...\n" 5 6 images=$(helm template -g $helm |yq -N '..|.image? | select(. == "*" and . != null)'|sort|uniq|grep ":"|egrep -v '*:[[:blank:]]' || echo "") 7 8 dir=$( dirname $helm | xargs dirname ) 9 10 echo "####" 11 12 if [ "$images" != "" ]; then 13 printf "\e[1;34m[INFO]\e[m Images found in the helm charts: ${images}\n" 14 printf "\e[1;34m[INFO]\e[m Create ${dir}/images images...\n" 15 16 mkdir -p ${dir}/images 17 18 while i= read -r image_name; do 19 archive_name=$(basename -a $(awk -F : '{print $1}'<<<${image_name})); 20 printf "\e[1;34m[INFO]\e[m Pull images...\n" 21 podman pull ${image_name}; 22 printf "\e[1;34m[INFO]\e[m Push ${image_name} in ${dir}/images/${archive_name}\n" 23 podman save ${image_name} --format oci-archive -o ${dir}/images/${archive_name}; 24 done <<< ${images} 25 else 26 printf "\e[1;34m[INFO]\e[m No Images found in the helm charts: $helm\n" 27 fi 28 done 29} Check components version 1function checkComponentsInstall() { 2 componentsArray=("kubectl" "helm") 3 for i in "${componentsArray[@]}"; do 4 command -v "${i}" >/dev/null 2>&1 || 5 { echo "[ERROR] ${i} is required, but it's not installed. Aborting." >&2; exit 1; } 6 done 7} Version comparator 1function checkK8sVersion() { 2 currentK8sVersion=$(kubectl version --short | grep "Server Version" | awk '{gsub(/v/,$5)}1 {print $3}') 3 testVersionComparator 1.20 "$currentK8sVersion" '<' 4 if [[ $k8sVersion == "ok" ]]; then 5 echo "current kubernetes version is ok" 6 else 7 minikube start --kubernetes-version=v1.22.4; 8 fi 9} 10 11 12# the comparator based on https://stackoverflow.com/a/4025065 13versionComparator () { 14 if [[ $1 == $2 ]] 15 then 16 return 0 17 fi 18 local IFS=. 19 local i ver1=($1) ver2=($2) 20 # fill empty fields in ver1 with zeros 21 for ((i=${#ver1[@]}; i<${#ver2[@]}; i++)) 22 do 23 ver1[i]=0 24 done 25 for ((i=0; i<${#ver1[@]}; i++)) 26 do 27 if [[ -z ${ver2[i]} ]] 28 then 29 # fill empty fields in ver2 with zeros 30 ver2[i]=0 31 fi 32 if ((10#${ver1[i]} > 10#${ver2[i]})) 33 then 34 return 1 35 fi 36 if ((10#${ver1[i]} < 10#${ver2[i]})) 37 then 38 return 2 39 fi 40 done 41 return 0 42} 43 44testVersionComparator () { 45 versionComparator $1 $2 46 case $? in 47 0) op='=';; 48 1) op='>';; 49 2) op='<';; 50 esac 51 if [[ $op != "$3" ]] 52 then 53 echo "Kubernetes test fail: Expected '$3', Actual '$op', Arg1 '$1', Arg2 '$2'" 54 k8sVersion="not ok" 55 else 56 echo "Kubernetes test pass: '$1 $op $2'" 57 k8sVersion="ok" 58 fi 59}
The Basics awk is treat each line as a table, by default space are separators of columns.
General syntax is awk 'search {action}' file_to_parse.
1# Give the value higher than 75000 in column $4 2df | awk '$4 > 75000' 3 4# Print the all line when column $4 is higher than 75000 5df | awk '$4 > 75000 {print $0}' But if you look for a string, the search need to be included in /search/ or ;search;. When you print $0 represent the all line, $1 first column, $2 second column etc.
The Basics 1sed -e '…' -e '…' # Several execution 2sed -i # Replace in place 3sed -r # Play with REGEX 4 5# The most usefull 6sed -e '/^[ ]*#/d' -e '/^$/d' <fich.> # openfile without empty or commented lines 7sed 's/ -/\n -/g' # replace all "-" with new lines 8sed 's/my_match.*/ /g' # remove from the match till end of line 9sed -i '4048d;3375d' ~/.ssh/known_hosts # delete lines Number 10 11# Buffer 12s/.*@(.*)/$1/; # keep what is after @ put it in buffer ( ) and reuse it with $1. 13sed -e '/^;/! s/.*-reserv.*/; Reserved: &/' file.txt # resuse search with & 14 15# Search a line 16sed -e '/192.168.130/ s/^/#/g' -i /etc/hosts # Comment a line 17sed -re 's/^;(r|R)eserved:/; Reserved:/g' file.txt # Search several string 18 19# Insert - add two lines below a match pattern 20sed -i '/.*\"description\".*/s/$/ \n \"after\" : \"network.target\"\,\n \"requires\" : \"network.target\"\,/g' my_File 21 22# Append 23sed '/WORD/ a Add this line after every line with WORD' 24 25# if no occurence, then add it after "use_authtok" 26sed -e '/remember=10/!s/use_authtok/& remember=10/' -i /etc/pam.d/system-auth-permanent
