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.

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.

trace query

use the following to trace another session:

dbms_system.set_bool_param_in_session(sid, serial#, 'timed_statistics',true);
dbms_system.set_sql_trace_in_session(sid,serial#,truefalse);

note: There are plenty of other options, and the above are just defaults.

changing tab stops in unix editors

ensure the following is in the $HOME/.exrc file

set tabstop=4
set shiftwidth=4

log in again (the .exrc must be run as part of the shell when logging in)
be carefull when setting this up as it could change the formatting of all the sripts .

counting cpu's

On SUN Servers, use the following to work out the number of cpus:

expr `mpstat wc -l` - 1

where:

mpstat gives information about cpu's and heading info,
wc-l gives the number of lines in the output,
expr lets you use some maths to remove the heading in the count of number of cpu's.

add / create redo logs

use the following to view current redo log file information
select * from v$log ;

use the following to drop a redo log (if required)
alter database drop logfile group ...

use the following to add a new redo log
alter database add logfile group ... '...' size ...m reuse;

when trying to drop a logfile the following error occurs

ERROR at line 1:ORA-01624: log 2 needed for crash recovery of instance CSHMNT
ORA-00312: online log 2 thread 1: '/db/cshmnt/redolog/001/redo02.log'

Try : alter system checkpoint
This should checkpoint the database and make all redolog files INACTIVE, allowing to drop redo log files.

putting standby database into readonly

1. cancel standby recover mode.
SQL> alter database recover managed standby database cancel;

2. put db into readonly mode
SQL> recover standby database until cancel;

NOTE:
It is possible that the standby database was configured to shutdown the standby database instance when managed recovery mode is cancelled. If this is the case, the command above will shutdown the standby instance and you will need to mount the standby database in standby mode before attempting to put the standby database in READ ONLY mode:

SQL> startup nomount
SQL> alter database mount standby database;
SQL> recover standby database until cancel;

pl/sql and roles

permissions granted through roles will fail when accessed through packages. See the note and link below, but these permissions fail because roles are turned off during compilation due to performance and security reasons.

Oracle requires that permissions to non-owned objects in a stored procedure be granted directly to the user. Roles are temporarily turned off during compilation, and the user has no access to anything granted through them. This is done for performance and security reasons. Roles can be dynamically activated and deactivated via the SET ROLE command, and it would be a large overhead for Oracle to constantly check which roles and permissions are currently active.

http://articles.techrepublic.com.com/5100-10878_11-6183799.html

set roles

Some applications use the set role function within the application so the tables for a specific query can only be granted via the application.

However the set role function is all encommpassing, meaning that if an active role is not included in the set role function, then that current role will become non-current.

To test how a database session would work without roles, run : set role none;
this is very useful when testing how commands will work within procedures etc.

current database privileges

To find out what privileges a user currently uses:

login as the user

select * from session_roles -- gives the current roles the user can use.
select * from session_privs -- gives the current privs the user can use.

at

use the unix at command to run a unix script at a given time.

To schedule a script via at:

echo "some command"at
where time could be hh:mm[ampm]

at -l shows the id that are currently scheduled
at -r [jobid] will remove a particulat job from the schedule.

note: The -r option will only work upto the point where the job has been scheduled.

locked statistics

you can lock statistics. I'm guessing this means that the stats for this table will never change (until at least you lock them).

to lock statistics try:

dbms_stats.lock_table_stats(owner, tablename);

there should be additional parameters to lock column and index statistics as well.

Use the dba_tab_statistics view to verify what tables have been locked.

slow query

use the following when testing something that requires a slow query.

http://www.jlcomp.demon.co.uk/deterministic.html

create or replace function wait_row(
i_v in varchar2,
i_sec in number default 5 ) return varchar2
deterministic
parallel_enable as
begin
sys.dbms_lock.sleep(i_sec);
return i_v;
end;
/

buffer busy waits

http://www.jlcomp.demon.co.uk/bbw.html

ORA-27300: OS system dependent operation:semget failed with status: 28

Found this error when creating a 10.2.0.3 database on solaris 9.

This error can occur on any unix host when starting an oracle database. The error is saying that the semiphore parameters are not set correctly.

In cases like this, the first parameter to look for is SEMMNS

Essentially this parameter tells how many os processes shared memory components can use on the server. So the important oracle database parameter to look at is process.

Use the following calulation to work out what SEMMNS should be set to:

SEMMNS = sum(oracle processes) + num oracle databases + system requirements

This all suggests when setting up a new server you should have an idea of the number of databases running on the server or set the SEMMNS parameter to a fairly high number.

vnc setup

Use the following to set up xwindows using vnc, putty

1. Make sure vnc server has been started on unix host.
If it hasn't then run:
- vncserver :serverid

2. Ensure the putty client has been setup with tunnels option completed.
Create / Update a profile, goto ConnectSSHTunnels
port should be 590serverid and host should be localhost.

note: This is only required if protocol that is being used is ssh.

Start putty client.

3. run xhost +

The xwindows window should now start. Run whatever oracle xwindows program you want.

If the window fails to start then run:
- vncserver -kill :serverid

PUSH_SUBQ hint

Found the following in a 10G database.

insert into ... select /*+ PUSH_SUBQ */ * from ... type query, only a portion of the rows would be returned.

removing the hint, all expected rows would be inserted into the table.

A definite trick for young players, as the insert statement never returned any errors.

group by and having options

The having command with the group by command is very useful when wanting specific rows, based on the group by construct to be removed.

For example, to work what values of a unique key is forcing the create unique index type command to fail, use something like:

select column, count(*) from table
group by column
having count(*) > 1;

will cause only occurances to display that have more than two rows, i.e.: the key values that is causing the create unique index to fail.

v$parameter2

This view is slightly different to v$parameter in that it seperates out the values for specific parameters into seperate rows.

Some examples of parameters where this may occur are :
- control_files
- utl_file_dir

Useful when wanting to query specific values, say a particular utl_file_dir or control file.
For this to work, the seperate values for these parameters must be seperated by commas.

i.e.:
alter system set utl_file_dir=
'/u01/ora_utl_file/hpac/',
'/home/sequel/cms/test/socrates/output/',
'/u01/ora_utl_file/contt/' scope=spfile ;