First find the block size:
select value "BLOCK_SIZE"
from v$parameter
where name='db_block_size' ;
Now check the usage of the tablespace:
select
s.username "USER",s.sid,s.osuser,
u.tablespace "TS" ,
sum(u.blocks) * &BLOCK_SIZE./1024/1024 MB,
x.sql_text
from v$session s,v$sort_usage u,v$sqltext x
where s.saddr=u.session_addr
and s.sql_address=x.address
group by
s.sid, s.username, osuser,
tablespace, sql_text, address, piece
order by sid, piece asc;
Here is very simple trick to force the grep command to display file name, when it’s used together with find operation.
Just write /dev/null as the “second file”
find . -type f -exec grep somestring {} /dev/null \;
Here is the small reminder about the syntax of the “case” command and the usage of the pattern lists.
#!/bin/ksh
print -n "Please enter the line: "
read line
case "$line" in
?(dog|cat) ) print "zero or one occurrence of any pattern" ;;
*(low|high) ) print "zero or more occurrences of any pattern" ;;
@(duncan|methos) ) print "exactly one occurrence of any pattern" ;;
+(rudolph|blitzen) ) print "one or more occurrence of any pattern" ;;
!(grinch|babay) ) print "everything except patterns" ;;
-@([hH?]) ) print "Some help..." ;;
-v*(erbose) ) print "Some more words..." ;;
*) print "Something else..." ;;
esac
echo $PATH| awk -v RS=":" '{ print $0 }'
echo $LD_LIBRARY_PATH |awk -v RS=":" '{ system ( "ls -rltd " $0 ) }'
Warning!
As far as the option ‘-v’ is used, the new awk(nawk in some systems) should be used.
To check if the new version of awk is installed:
awk 1 /dev/null
The output will be empty for new awk.
You have the old awk, if the output is something like
awk: syntax error near line 1
In this case nawk should be used instead awk
MS-DOS modification: echo %PATH% | awk -v RS=”;” “{ print $0 }”
Some standard queries to check the state of the Oracle DB
select 'alter '
|| decode(lower(object_type), 'package body','package',lower(object_type))
|| ' ' || owner || '.'
|| object_name
|| decode(object_type,'PACKAGE BODY', ' compile body;',' compile;') INVALID_OBJECTS
from DBA_OBJECTS
where STATUS = 'INVALID';
select 'ALTER INDEX '|| owner || '.' || index_name || ' rebuild online ;'
from dba_indexes where status = 'UNUSABLE'
union
select 'ALTER INDEX ' || index_owner || '.' || index_name
|| ' rebuild partition ' || partition_name || ' online ;'
from dba_ind_partitions
where status = 'UNUSABLE'
union
select 'ALTER INDEX ' || index_owner || '.' || index_name
|| ' rebuild subpartition ' || subpartition_name || ' online ;'
from dba_ind_subpartitions
where status = 'UNUSABLE';
SELECT name, unrecoverable_change# , unrecoverable_time
FROM v$datafile
order by 2;
Search for Oracle errors in the last $NUM lines.
NUM=1000
SIGNAL_LIST='^(...-|Error|Starting.*instance)|terminating instance'
tail -$NUM alert_${ORACLE_SID}.log | awk '
BEGIN {prev="" ; ret=1 }
/'"$SIGNAL_LIST"'/ { if ( prev !~ /'"$SIGNAL_LIST"'/ ) { print "" ; print prev;} print $0;ret=0}
{prev=$0}
END { exit ret } '
On Unix Oracle alert log is normally located in bdump/alert_{ORACLE_SID}.log
Sometimes it’s necessary to convert Sybase timestamp to something more readable
Warning! Timestamp is not date type!
declare @p timestamp
declare @s varchar(16)
declare @t1, @t2 varbinary(4)
//--- from char to timestamp
@t1 = hextoint(substring(lower(@s),1,8))
@t2 = hextoint(substring(lower(@s),9,8))
@p = @t1 + @t2
//--- from timestamp to char
@s=(lower(convert(varchar(8),intohex(substring(p,1,4))))+ lower(convert(varchar(8),intohex(substring(p,5,4))))
Examples for changing the Oracle LDAP entries.
Warning!
Put the space after every ldif command!
Check the version of ldapmodify (it should be Oracle binary, not the OS standard one)
#### modify.sh ####
ldapmodify -D 'cn=orcladmin' -w $LDAP_PASS -h $LDAP_HOST -p $LDAP_PORT -v -f modify.ldif
#### modify.ldif ####
dn: cn=SOME_ORACLE_SID,cn=OracleContext,dc=ny,dc=company,dc=com
changetype: modify
replace:orclnetdescstring
orclnetdescstring: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=456.456.456.456)
(PORT=1521))(CONNECT_DATA=(SID=SOME_ORACLE_SID) (GLOBAL_DBNAME=SOME_ORACLE_SID.ny.company.com)))
#### add.sh ####
ldapmodify -D 'cn=orcladmin' -w $LDAP_PASS -h $LDAP_HOST -p $LDAP_PORT -v -f add.ldif
### add.ldif ###
dn: cn=SOME_ORACLE_SID,cn=OracleContext,dc=ny,dc=company,dc=com
orclnetdescstring: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=456.456.456.456)
(PORT=1521))(CONNECT_DATA=(SID=SOME_ORACLE_SID) (GLOBAL_DBNAME=SOME_ORACLE_SID.ny.company.com)))
objectclass: top
objectclass: orclNetService
cn: SOME_ORACLE_SID
orclnetdescname: 000:cn=DESCRIPTION_0
### unload.sh ###
#!/bin/ksh
if [ -z "$1" ] ; then
if [ -z "$CONTEXT" ] ; then
print "Examples of context: ny.company.com or dc=ny,dc=company,dc=com" >&2
printf "Enter context (Enter for default): " >&2
read CONTEXT
fi
CONTEXT=${CONTEXT:-"dc=world"}
else
CONTEXT=${1:-"dc=world"}
fi
case "$CONTEXT" in
"dc="* ) ;;
* ) CONTEXT=$( echo "${CONTEXT}" | sed 's#\([^.]*\)$#dc=\1#;s#\([^.]*\)\.#dc=\1,#g') ;;
esac
echo "# CONTEXT: $CONTEXT" >&2
ldapsearch -h $LDAP_HOST -p $LDAP_PORT -D "cn=orcladmin" -w $LDAP_PASS -L -s sub -b "cn=OracleContext,${CONTEXT}" "(objectclass=orclnetService)" "*"
Quick and dirty parsing procedure for unix shell scripts
parse_command_line ()
{
typeset -i user_opt help_opt password_opt verbose_opt
typeset errmsg
arg_cou=$#
while [ "$#" -gt 0 ]
do
case "$1" in
-@([U]) ) let user_opt=user_opt+1 ;;
-P ) let password_opt=password_opt+1 ; ask_pass=0 ;;
-option1 ) ;;
-option2 ) ;;
-option3 ) ;;
-@([hH?])) help_opt=1;;
-v*(erbose)) let verbose_opt=verbose_opt+1 ;;
*)
if [ user_opt -eq 1 ];then
USER="$1"; user_opt=0
elif [ password_opt -eq 1 ];then
PASS="$1"; password_opt=0
elif [ verbose_opt -eq 1 ];then
verbose_level="$1"; verbose_opt=0
else
### help_opt=1
CMD=$* ; break
fi
;;
esac
shift
done
if [ arg_cou -eq 0 ] ; then
help_opt=1 ; errmsg="Nothing to do"
fi
for opt in user password ; do
if [ ${opt}_opt -ne 0 ] ; then
help_opt=1 ; errmsg="${errmsg}\n${opt} is absent"
fi
done
if [ verbose_opt -ne 0 ] ; then
verbose_level=$verbose_opt
fi
if [ help_opt -eq 1 ]; then
help "$errmsg"
fi
}
print -n "Enter Your password:"
stty_orig=`stty -g`
trap "stty ${stty_orig}; exit" 1 2 3 15
stty -echo >&- 2>&-
read PASS stty ${stty_orig} >&- 2>&-
trap 1 2 3 15 print
trap :catches interruptions. I.e. if the user presses Ctrl+C, the normal stty mode is set before stopping the program
stty -echo :switches off the display echo
>&- 2>&- :helps to avoid “stty: Not a typewriter” message for non-interactive scripts.