Transaction Isolation Level

Good link for getting a refresh on the isolation level: http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read
READ UNCOMMITTED	Permitted	Permitted	Permitted
READ COMMITTED	–	Permitted	Permitted
REPEATABLE READ	–	–	Permitted
SERIALIZABLE	–	–	–

Oracle supports READ COMMITTED and SERIALIZABLE. Oracle doesn’t allow dirty reads(READ UNCOMMITTED). It allows a third isolation level which is READ ONLY. No insert, update, delete can be done in this mode.

Oracle Collections

Good Link for on Oracle collection : http://sheikyerbouti.developpez.com/collections/collections.htm

Oracle Collections

Good Link for on Oracle collection : http://sheikyerbouti.developpez.com/collections/collections.htm

KeepResident plugin for Eclipse/Jdeveloper

Jdeveloper being slow when shifting the application in windows. Reason being the windows swapping the jdeveloper memory even when large amount of physical memory is available.Application has a working set size. The default value is much too small for big Java applications like Eclipse. Hence, windows does a swapping. To prevent this, one thing that can be done is to increase the minimum water mark of this working set size. Then, there is another problem that when the user memory is less than the minimum working set size, windows will think that the application is not used and will do swapping again. Using VirtualLock() will prevent this and force Windows to keep Eclipse in memory.

Got this information from the blog : http://www.orablogs.com/gdavison/archives/001659.html

FAQ on this plugin also provides a clear information : http://suif.stanford.edu/pub/keepresident/faq.html

Uninstalling Oracle 10g Manually from Windows XP

List of additional steps to do for cleaning the system completely after uninstall using universal installer.
# Stop any Oracle services that have been left running.
Start->Settings->Control Panel->Services
Look for any services with names starting with ‘Oracle’ and stop them.
# Run regedit and delete the following keys (some may have slightly different names in your registry):
HKEY_CURRENT_USER\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\EventLog\Application\Oracle.oracle
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\OracleDBConsole
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\Oracle10g_home
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet Services\OraclService
Note that the services control panel will still show the old services until you reboot.
# Delete the Oracle home directory
C:\Oracle
# Delete the Oracle Program Files directory:
C:\Program Files\Oracle
# Delete the Oracle Start Menu shortcuts directory:
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
Where * indicates the name of your install. Look for and remove all Oracle directories from that location.
# Remove Oracle refereces from the path. To edit your path go to:
Start->Settings->Control Panel->System->Advanced->Environment Variables
Edit both of the environment variables user PATH and system PATH. Remove any Oracle references in them.
# Remove Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\

Difference between Stored Procedure and Function

Stored Procedure :supports deffered name resoultion Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error
Function wont support deffered name resolution. Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values(SQL Server).Stored Procedure is pre compiled exuction plan where as functions are not.

Making trace files available

There is an undocumented parameter _trace_files_public that if set to true changes the file permissions in the user_dump_dest directory when trace files are created to allow everyone to read them. This parameter can be checked with the following SQL. You can set this parameter by adding the following line to the init.ora file:
# allow trace files to be created with public permissions
_trace_files_public=true
SQL to check the value of this parameter:
SQL> select x.ksppinm name,y.ksppstvl value
from sys.x$ksppi x,sys.x$ksppcv y
where x.inst_id=userenv(‘Instance’)
and y.inst_id=userenv(‘Instance’)
and x.indx=y.indx
and x.ksppinm=’_trace_files_public’;

Who eats my Temp Space

Many a times we get the error : “TEMP SEGMENT MAXIMUM EXTENT EXCEEDED”. The following script will provide a list of users and which processes occupy space in the TEMP tablespace.

SET pagesize 10000;
    SET linesize 133;
    column tablespace format a15 heading 'Tablespace Name';
    column segfile# format 9,999 heading 'File|ID';
    column spid format 9,999 heading 'Unix|ID';
    column segblk# format 999,999,999 heading 'Block|ID';
    column size_mb format 999,999,990.00 heading "Mbytes|Used";
    column username format a15;
    column program format a15;
SELECT
    b.tablespace,
    b.segfile#,
    b.segblk#,
    round(((b.blocks*p.value)/1024/1024),2 ) size_mb ,
    a.sid,
    a.serial#,
    a.username,
    a.osuser,
    a.program,
    a.status
FROM v$session a ,
    v$sort_usage b ,
    v$process c ,
    v$parameter p
WHERE p.name='db_block_size'
    AND a.saddr = b.session_addr
    AND a.paddr=c.addr
ORDER BY b.tablespace,
    b.segfile#,
    b.segblk#,
    b.blocks
/