Wednesday, 14 December 2005

Update a table from another table

How to update a table from another table via a 'join update' using column aliases

update
(select a.f falias1, b.f falias2 from a, b where a.id=b.id and a.falias1!=b.falias2 )
set falias1=falias2;

Wednesday, 16 November 2005

See number of CPUs in a SUN Solaris box

In a sun solaris box to see the number of CPUS in the box type the following at the prompt.

$/usr/platform/sun4u/sbin/prtdiag -v | more

Thursday, 10 November 2005

Remove ^M from end of each line in Vi

Sometimes you might want to remove a character like '^M' from the end of each line in a vi file. This '^M' character is usually intrduced in the file if you transfer the txt or csv file from a windows environment to unix. It is the extra carriage return introduce in the end of line.


The command to do that is like this in Vi:

:%s/^M//g


but to get the ^M you must press ctrl+v, then ctrl+m on your keyboard when at the vi prompt... it is one of those things

Delete any blank lines in VI

The substitution command in vi to delete any blank lines in Vi editor open the file and type the command below at the VI command prompt.

$v/./d

Convert the entire file's case in VI

Using Vi editor you can convert the entire file to (U) uppercase or
(L) lowercase with the following command.

open the file in Vi and at Vi's command propmt type:

%s/.*/\L&/

Use unix 'sed' to re-format csv files

Suppose you have a csv file which contains text like this

...
aaaa@bCC.com
cccc@dddddd.com.tr
lllll@kkkk.de
...

and you want to convert it to a CSV file so that you can use it in SQL,
something like this:

...
'aaaa@bCC.com',
'cccc@dddddd.com.tr',
'lllll@kkkk.de',
...


You can use a sed on unix to do it like this.

$ sed -e "/^$/d" -e "s/^/\'/" -e "s/$/\',/" textfile.txt > newfile.txt

Friday, 22 July 2005

How to create / read unix tar files

To create a unix tar file supply the path to the directory which you want to tar in the 'directoryname' below:

$ tar cvf filename.tar directoryname


To extract a tar file into a directory supply the path to the 'directoryname' as below.

$ tar xvf filename.tar directoryname

Thursday, 5 May 2005

Import time monitoring

Use this script to monitor how long the import into a table takes and how many rows have already been imported. Log in as sysdba to do that.

It shows how many rows are already imoprted dymamically. If you already know how many rows you have in the table it is very handy and you can see the progress of your import.

select substr(sql_text,instr(sql_text,'INTO "'),30)
table_name, rows_processed, round((sysdate- to_date(first_load_time,'yyyy-
mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/ ((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)
) rows_per_minute from sys.v_$sqlarea where sql_text like 'INSERT %INTO "%'
and command_type = 2 and open_versions > 0
/

Tuesday, 12 April 2005

Delete files older than X certain days

To delete files older than certain days you use -mtime argument of the find command like this. The following example deletes files which start with the word 'control.' and older than 3 days. That is keeps only the files of the last 3 days and removes the rest.

$ find control.* -mtime +3 -print -exec rm -f {} \;

Friday, 25 February 2005

See what's in the DB_CACHE_BUFFER

See what's in you db_cache buffer at that time, log on as SYSDBA and run the following query.
You will see what occupies the db_cache buffer at that time.


OLUMN object_name FORMAT a40
COLUMN number_of_blocks FORMAT 999,999,999,999

SELECT o.object_name, COUNT(1) number_of_blocks
FROM DBA_OBJECTS o, V$BH bh
WHERE o.object_id = bh.objd
AND o.owner != 'SYS'
GROUP BY o.object_name
ORDER BY count(1);

Wednesday, 16 February 2005

Solaris ls command to list large files

Use this command on Solaris with bash, to get your files sorted from the smallest to the largest and quickly list large files.

$ ls -lhs | sort -nSk5

Note: k5 is the column number from left to right (in this example column 5). if your filesizes are displayed on the 3rd column you put ..k3. All depends on which column the command ls -l displays the filesizes.

Tuesday, 1 February 2005

Unix/Linux kernel parameters for Oracle installation

This is Unix/Linux Kernel parameters configuration based on how many oracle
instances with how many processes you wish to run on the Unix box.

SHMMAX = The maximum size(in bytes) of a single shared memory
segment.
SHMMIN = The minimum size(in bytes) of a single shared memory
segment.

The above settings let the max be bigger then we wil ever use and the min much
smaller. They are sufficient for pretty much all systems.

SHMMNI = The number of shared memory identifiers.
SHMSEG = The maximum number of shared memory segments that can
be attached by a process.

The above settings should also be sufficient.

SEMMNS = The number of semaphores in the system.
SEMMNI = The number of semaphore set identifiers in the system;
determines the number of semaphore sets that can be
created at any one time.
SEMMSL = The maximum number of sempahores that can be in one
semaphore set. It should be same size as maximum number
of Oracle processes
(The PROCESSES parameter in the init.ora file).

SEMMSL
Set to 10 plus the largest initsid.ora PROCESSES parameter of an Oracle
database on the system. The PROCESSES parameter can be found in each
initsid.ora file, loc ORACLE_HOME/dbs directory. The default value of PROCESSES
for the 8.1.5 prebuilt database is 50.

SEMMNS
Set to the sum of the PROCESSES parameter for each Oracle database the
largest one, plus 2 times the largest PROCESSES value, plus 10 number of Oracle
databases. For example, consider a system that has three Oracle instances with
the PROCESSES parameter in their initsid.ora files set to the following values:

ORACLE_SID=A, PROCESSES=100
ORACLE_SID=B, PROCESSES=100
ORACLE_SID=C, PROCESSES=200


The value of SEMMNS is calculated as follows:

SEMMNS = ((A=100) + (B=100)) + ((C=200) * 2) +
((# of instances=3) * 10) = 630


Definition: Semaphore

Semaphores are serialization devices (only one person at a time will get a
semaphore). In general, they are "heavy weight" (we have light weight latches
as well) and implemented by the OS itself. We use them to protect some shared
data structures from being updated by more then one process at a time.


From : http://asktom.oracle.com , Tom Kyte

Tuesday, 18 January 2005

Unix prompt with ORACLE_SID

Modify your command prompt on unix box to include the oracle SID in your prompt. Type this the .profile file of your user login.

PS1="
\${ORACLE_SID} on \h \${PWD}
$ "


#