explorer hints / tips

Use the following to make better use out of explorer.

start at the downloads folder - /e, u:\downloads

NOT NULL Check Constrailts

Check constraints will stop particular sets of data from been added to a column in a table. One type of check constraints is the NOT NULL keyword when adding a column or creating a table.

note: With the NOT NULL keyword a system generated identifier is used so the name of the check constraint will be different through the SDLC (assuming entire physical database refresh is not completed.)

drop check constraint:
alter table t1 drop constraint c1;

convert scn numbers

The scn number is very important. Whenever a change to the database is made (including the data dictionary) then this scn number is incremented. Oracle uses the scn number for backup / recovery purposes, flashback, recycle bin etc etc etc.

There are a few functions that can be used to convert scn numbers to dates and vice a versa, which may make life easier when working out what date these scn numbers refer to. These functions must look through control file as they will fail if the scn number is too old.

select dbms_flashback.get_system_change_number from dual;
select current_scn from v$database;

select scn_to_timestamp(to_char(timestamp_to_scn(sysdate),'99999999999')) from dual;
select to_char(timestamp_to_scn(sysdate),'99999999999') from dual

note:
These queries will convert the scn number to a timestamp and vice a versa.

recycle bin

The concept of the recycle bin is essentially the same as the recycle bin on the mac or windows.

if you drop a table, the table is not renamed and not dropped. You can query the recyclebin view to look at what objects are currently in the recycle bin. You can actually select from the table that was dropped, although it is quite an ungamely name. i.e.: Something like BIN$HGnc55/7rRPgQPeM/qQoRw==$0

You can restore the table by issuing:
flashback table "bla" to before drop;

scattered read and sequential read

Scattered Read - normally attributable to full table scans. These wait events occur through full table scans (FTS) as FTS normally scan multiple blocks at the same time, re: multi_block_read_count.

Sequential Read - read 1 block at a time are normally attributable to index scans. As index scans read 1 block at a time, multi block read doesn't apply with indexes and performance with sequetial reads could go down.

If any of these events are high then ensure stats are accurate, explain plans look OK and there isn't too much disk contention going on.

note: The amount of blocks that an index has to scan through before completing could have a big impact on performance. This also has implications for the cluster factor.

sed one liners

use the following link as a guide to some usefull one liners:

http://www-h.eng.cam.ac.uk/help/tpl/unix/sed.html

echo "bla bla bla." sed 's/\.//g' # remove the "." anywhere on the line.

pspad

After another search I found, what seems to be, a great little text editing application, called pspad.

http://www.pspad.com/en/

After about ten minutes I have decided that this freeware text editor is fantastic. It has all the programming syntax built right in and is so easy to swap in and out. Beats having to setup specific syntax files in other editors.

If I was really grumpy, the only thing I would suggest (at this stage) is a sftp connection as opposed to a ftp connection.

crimson editor - escape character

The escape character setting in the spec file, just as it is in the actual language is used to treat the next character as a literal, and in the case of crimson, the colour setting is ignored.

for example using the following code:
"She said \" Hello world\".\n"
without a '\' set as an escape character, only upto the 2nd " would have the correct colour. But having the escapecharacter set correctly, the entire line would be coloured correctly.

compile c program

http://users.actcom.co.il/~choo/lupg/tutorials/c-on-unix/c-on-unix.html#single_source_c



to compile a c program in its simplist form:

gcc a.c -o a

where a is the name of the program you want to compile.

crimson editor

notes relating to crimson editor:

to setup ranges with the syntax editing option in crimson editor:

1. update the spc file so that $range1beg and $range1end are included.
2. update the key file so that the keywords for the ranges are updated. You can do all sorts of wonderfull things such as only include keywords for specific ranges.

so keywords found in KEYWORDS3:RANGE1 will only show the colour if the keyword is enclosed by whatever has been defined as RANGE1 in the spc file.

with sql this could work if we had ranges for loop .. end loop, ( .. ) as some examples. But what about having different colours for situations like the set word.

set could be:

set heading on
update table set column=...

some possibilities could be if set follows update, need some more research.

unix finding large files

Use the following commands to work out what directories / files are using most of the disk. Could be usefull to use as aliases.

find . -type d -exec du -sk {} \; sort -n

this command searches, from the current directory, looking at each directory and finding the size of that directory.

it then sorts the first column (size of the directory).

the command will size all directories under the current directory so all the directories at the high level will be last, but it does give a good idea where the large directories can be found.

find . -exec du -sk {} \; sort -n tail

use this command to find the largest 10 files from the current directory.

note: this command may take a long time to complete.

vmstat

vmstat gives average server figures, but gives a good look at disk, memory, swapping, faults, paging and cpu activity. Use other commands to delve deeper into the problem.

The vmstat -S option gives you swapping details

report the number of threads in the following state:

ccpsd:/home/oracle> vmstat
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr m0 m1 m3 m4 in sy cs us sy id
2 0 0 2272968 550664 92 292 1862 22 24 0 12 7 1 3 3 226 418 155 39 13 49

r - in run queue
b - blocked for resources etc
w - swapped

swap - amount of swap space currently available (kbyte)
free - amount of free swap space (kbyte)

info about paging faults / activity (per second)
re - page reclaims
mf - minor faults
pi - kbyte paged in
po - kbyte paged out
fr - kbytes freed
de - anticipated short-term memory shortfall (kbyte)
sr - pages scanned by clock algorithm

disk - number of disk operations / second. refer to other commands for further information.

faults - report the trap/interrupt rates (per second)
in - interrupts
sy - system calls
cs - CPU context switches

cpu - avg of all cpu's
us - user time
sy - system time
id - idle time

4030 oracle error

4030 errors relate to server process errors. That is a user is running a query and the session they are running hits this 4030 error. This error is generated because the operating system is not able to allocate more memory to the process.

Questions to ask:

is there sufficient memory available on the server ?
is there an operating system limit present ?
which process is requesting too much memory ?
how to collect information on what the process is doing ?

Metalink Note of interest: 233869.1

pga sql scripts

-- show sessions that are using more than 100M of PGA
select sid, name, round(value/(1024*1024))
from v$statname n, v$sesstat s
where
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%' and
round(value/(1024*1024)) > 100
order by 3, 1 asc;

-- Script to show total amount of PGA memory that is being used
select sum(value)/1024/1024 Mb
from v$sesstat s, v$statname n
where n.STATISTIC# = s.STATISTIC# and name = 'session pga memory';

tv stuff

some useful comments below:

Contrast ratio is the difference in brightness between blacks and whites. For example, 500:1 is staying that Whites are 500 times brighter than Blacks. What does this means in layman terms?

This means bright whites, darker blacks and everything in-between is more vibrant and accurate.

Higher the contrast ratio, the better the colors you will have on your image. This spec is unrelated to clarity, resolution and refresh rate. It is specifically telling you about color quality.

Picture refresh rate would be response time. You will see the term in ms. 30ms is slow. Good LCD response time is under 10ms. Excellent is under 5ms.

doing more investigation:

looking more at TV's, had a look at the samsung 6 series, ben suggested I do this. To me, comparing the two tv's, panasonic and samsung, the panasonic looks much better. The samsung had just a little bit of edging around the presenters on the tv. The sources were a little bit different.

funny thing: I keep going back to the panasonic. Need to look some more and wait for a good deal.

last night, did some reading on how light may affect plasma tv's. Basically it said, lcd tv's are much better in lighter conditions than plasmas. Need to do some more investigation.

Resolution can be seen in computer or video terms. Computer terms would be two values, such as 1920x1080. Video terms would be 480p, 720p, 1080p. The correlation between the two types of terms is the last value. 1920x1080 is the same as 1080p. 1280x720 is the same as 720p.

encrypting text files in unix

To do this, use the crypt keywork.

to encrypt a file:

crypt "password" < "oldfile" > "newfile"

to access the file:
cat newfile crypt "password" or vi -x newfile

awk commands

http://www.vectorsite.net/tsawk_1.html#m1

Use the following as a guide:

awk '/gold/ {print $5,$6,$7,$8}' coins.txt
print fields 5, 6, 7, 8 from coints.txt that have "gold" somewhere on the line.

awk '/gold/ {print $0}' coints.txt
print all lines from coins.txt that have "gold" somewhere on the line.

awk 'END {print NR,"coins"}' coins.txt
print the number of lines in coins.txt

awk '/gold/ {ounces += $2} END {print "value = $" 425*ounces}' coins.txt
print the sum of the 2nd column * 425 of all rows that have "gold" somewhere in coins.txt

The following is an awk program that is run bu invoking the following:
awk -f script.awk coins.txt

/gold/ { num_gold++; wt_gold += $2 } # Get weight of gold.
/silver/ { num_silver++; wt_silver += $2 } # Get weight of silver.
END { val_gold = 485 * wt_gold; # Compute value of gold.
val_silver = 16 * wt_silver; # Compute value of silver.
total = val_gold + val_silver;
print "Summary data for coin collection:"; # Print results.
printf ("\n");
printf (" Gold pieces: %2d\n", num_gold);
printf (" Weight of gold pieces: %5.2f\n", wt_gold);
printf (" Value of gold pieces: %7.2f\n",val_gold);
printf ("\n");
printf (" Silver pieces: %2d\n", num_silver);
printf (" Weight of silver pieces: %5.2f\n", wt_silver);
printf (" Value of silver pieces: %7.2f\n",val_silver);
printf ("\n");
printf (" Total number of pieces: %2d\n", NR);
printf (" Value of collection: %7.2f\n", total); }

comma delimited fs info in unix

use the following to generate comma delimited output of filesystem sizes in unix.

df -k | sort -n | grep -v Filesystem | awk '{print $6","$2","$3","$4","$5}'

where
sort -n sorts but actually gets rid of double lines
grep -v removes the original heading from the output
awk prints the output in comma format and the information that you want.

sqlplus publishing

Use some of the following techniques when publishing sqlplus reports.

1. TTITLE - gives a title at the top of each page.
2. BTITLE - gives a title at the bottom of each page.

Use TTITLE OFF and BTITLE OFF to turn these headings off so subsequent statements don't have these headings.

interpreting trace files

Use some of the following notes to diagnoise trace files:

Note:39817.1 - Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output

This note will give more information about the different lines that occur through the trace file and what some of the letters with numbers really mean.

Note:224270.1 - Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046

This note will give information on the trcanlzr utility written by oracle. This utility installs scripts into the database and allows you to generate reports on the trace file. The installation documentation includes some useful bits on what the results mean.

Note:62294.1 - The DBMS_SUPPORT Package
This note gives further information about the dbms_support package, including how to install it and how to use it. According to the documentation, it provides more details than the standard scripts.