MAX_DUMP_FILE_SIZE

Limit the size of alert log files with this parameter.

Parameter can be changed at system or session level.
i.e. - alter system or alter session

Options are UNLIMITED or a number with size attributes, i.e.: - K, M, G.

To make life easier set this parameter to UNLIMITED.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams129.htm

Defaut Attributes

To change default attributes for partitioned tables and indexes - so tablespace or pctfree is set correctly for new partitions, use the following -

alter table owner . table modify default attributes attribute ...

Subpartitions are slightly different, try -

alter index "owner" . "index" modify default attributes for partition "partition name" ...

where attribute can be -
pctfree, tablespace, pctused etc

scp

Use the following for using scp:

Assumptions - ssh to server has been setup correctly - see note on setting up ssh.

scp -prvqC files user@host target directory

options:
-p --> preserve time stamps
-r --> copy subsdirectories
-v --> verbose mode
-q --> quiet - don't include progress counter.
-C --> compress. Note: When running in compress mode, scp runs at a slower rate.

java thin client

JAVA Thin Client is used by some development / reporting tools to connect to multiple databases.

One such example is dbvisualizer.

The thin driver is useful as it allows users to connect to oracle databases without the requirement of any oracle client software to be installed on desktop / client etc.

Steps:

1. Download the oracle thin driver - http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/winsoft.html

2. Copy into software location or application may load load (and copy to correct location. DBVisualizer does this for you).

3. Create a connection string, something like - jdbc:oracle:thin:@servername:dbport:dbname

Once done, you should be able to connect.

DB Links

Database Links cabn be created as PUBLIC or PRIVATE.

create public database link "dblink name" connect to "target db user" identified by "target db user password" using 'sid';

create database link "dblink name" connect to "target db user" identified by "target db user password" using 'sid';

DB Links use Oracle Networking from the installed oracle home.

Public Database Links will be used before private DB Links.

usage:

select * from dual@dblink

Password File

Create Password File.
orapwd file=password_file_name password=the_secret_password
orapwd file=password_file_name password=the_secret_password entries=n

Add users to password files:
grant sysdba to "user"
grant sysoper to "user"

View users who have SYSDBA or SYSOPER priv:
select * from v$pwfile_users;

Connect:
sqlplus ""user" as sysdba"

note:
in 9.2, connection info shows connection as sys.
in 8.1.x, connection info shows connection as self.

remove db parameter from spfile

Required when obsolete db parameter needs to be removed or reset from the spfile.

alter system reset "parameter name" scope=spfile sid='*'

clustering factor

Clustering Factor shows how synchronized the indexes are. Clustering Factor is updated after running dbms_stats on a particular index.

A good clustering factor is when the clustering factor is near the number of blocks in the index.
A bad clustering factor is when the clustering factor is near the number of distinct rows in the index.

So for each key,
If the child records are found in the same data block then access will be quicker. Good clustering factor.

If the child records are found in may data blocks then access will be slower. bad clustering factor.

tracing

More information on tracing.

Use dbms_system to trace sessions ...

Turn on level 12 tracing
exec sys.dbms_system.set_ev(sid, serial#, 10046, 12,'');

Turn off tracing
exec sys.dbms_system.set_ev(sid, serial#, 10046, 0, null);

To analyze with tkprof
tkprof

sys=yes sort='(prsela, exeela, fchela)'

where sys=yes, means data dictionary queries are included.

External Tables

Some useful information on directories and external tables

External tables rely on database directories, so firstly create a database directory if required.

create or replace directory ... as '...'
note: don't use parameters such as $HOME (as oracle can't work out what these are).

grant privileges to the directory created above:

grant read on directory ... to ...;

Next, create a table (see below for an example) and then start querying the external table.

CREATE TABLE ... ( ... some columns ...) ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER DEFAULT DIRECTORY ...
ACCESS PARAMETERS
(
records delimited by newline
fields REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
'add_partition.cfg'
)
) REJECT LIMIT unlimited

ssh-UNIX

To setup ssh auto connect between servers, run the following:

on from-server run:
cd $HOME/.ssh
ssh-keygen -t dsa

This will generate two files:
- id_dsa
- id_dsa.pub

copy the contents of id_dsa.pub to the target server

append the authorized_keys file on the target server with the contents of the generated id_dsa.pub. i.e.: cat id_dsa.pub >> authorized_keys

example:

ssh user@server ls -l

should run with no passwords being asked for.

user can be ignored if the user you are wanting to ssh to is the same as the user running the ssh command, obviously on different servers.

server should accept DNS entries.

Problems -

If ssh does not noto connect then check the permissions on the $HOME directory.
It should be 750 or drwxr-x---

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.