instr, substr

A couple of usefull oracle functions for manipulating strings.

instr(string1, string2, start position, nth appearance) returns the position where the string exists.

where
string1 is the original string to search from
string2 is the string to search in string 1
start posititon is optional and is the start position to start searching from
nth appearance is optional and is the number of occurances of string2 in string1.

instr('Tech on the net', 'e', 1, 3) would return 14.

substr(string1, start position, length) returns a string.

where
string1 is the string to check.
start position is the position in the string to start the substring from
length is optional and the length of the position.

substr('Tech on the net',1,5) would return 'Tech '

Often using a combination of the above functions is useful.

substr('Tech on the net',1, instr('Tech on the net', 'e', 1, 3)) would return ''Tech on the ne'

bug - 2942857 - OERI:12327 from complex view merging

Bug 2942857 - OERI:12327 from complex view merging
DOC ID 2942857.8

Fixed: 9.2.05, 10.1.0.2

Complex view merging may fail with ORA-600[12327] if an COUNT(*) is used and the query has a subquery predicate against the COUNT(*) .

Workaround: Set "_complex_view_merging"=false

Affects EDR, EDMT, DWH.

sed & awk script - find num SID entries in oratab

sed and awk command to
- remove blank lines from the start of each line
- Find entries based on input parameter - SID.
- find the number of entries.

cat oratab sed -e 's/^[ \t]*//' awk -F: '/^'"$SID"'/ { print $1 }' wc -l

truss

Truss is a command that enables you to trace a unix program. The truss output will show the libraries, executables, log files etc the command is touching including the return code.

Use the following commands -
truss -ae -o truss.txt -p 123456
truss -f -o truss.txt abc

where

truss.txt is the output file.
123456 is the process id that the truss will run on.
abc is the program that truss will run on.

SGA

SGA is made up of the following -

Fixed - Small and always constant depending on platform, os and database version
Variable (large pool, shared pool, java pool)
Database Buffer
Redo Log Buffer

10G
Oracle introduced memory management. Basically you set the following parameters and oracle manages the memory for you. The theory is the database will adjust the memory components as on a as required basis.

sga_taget <> 0
statistics_level = TYPICAL