Oracle Update - March 2008

Oracle Update :-

This Months Highlights:

1) Certification Becomes Cheaper – Certify Yourself
2) Different Oracle Tools
3) Why Only Oracle ?
4) Severity Vs Priority
5) Oracle 11g Pl/Sql new Features.
6) Interesting Queries & Topics

Certification Becomes Cheaper – Certify Yourself :

Certification prices are going down as the dollar price has become lower. Oracle University gives discounts on some certifications on certain dates, Where candidate has to take up the exam in the given location. Now it is very simple to register for exam.

Check the link below for registering the exam :
http://www.oracle.com/global/in/education/exam_request_2008.html

List of exams available :
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=42

Different Oracle Tools :

There are different oracle tools available in market. Check the advantages and disadvantages of different tools.

Oracle Tools ::
http://www.orafaq.com/tools/
Comparison table for Oracle tools ::
http://www.orafaq.com/tools/comparison

TOAD (From QUEST) is the number one in the market.
About Toad :
http://www.quest.com/toad-for-oracle/

Toad Freeware Download Link::
http://www.toadworld.com/Downloads/tabid/60/Default.aspx
http://www.toadsoft.com/

Why Oracle :

Read the explanation given by Tom Kyte by comparing the other databases :
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1886476148373


Seviority Vs Priority :

Seviority : Seriousness of the defect.
Priority : How Quickly the bug should be fixed.

Priority is Business and Severity is Technical.Many people think, High seviority bugs should be taken on High priority. But in real scenario, High seviority bugs may or may not be taken on High priority.
Below are few examples where we can have ‘High priority & Low Seviority’ and ‘High Seviority and Low Priority’ bugs.

1] High Severity And Low Priority----If there is an application , if that application crashes after multiple use of any functionality
Means High Severity because application crashed, Low Priority because no need to debug right now you can debug it after some days.

2] High Priority And Low Severity :
If the logo of any site is wrongly spelled, Priority is high but severity is low.
Because it effect the name of the site, it is important to do quick.
It is not going to crash because of spell change, so severity low.

Oracle 11g Pl/Sql new Features:

Nice Explanation with examples :
http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php

Interesting Queries & Topics:
==>>>
In Oracle 11g , No need to use the dual table for getting the next value or current value of a sequence.
Eg:
‘Select Sequence_Name.NextVal into ln_seq from dual’
can be replaced by
ln_seq:= Sequence_Name.NextVal;

==>>>
The following query gives you the Schema name , Connection Status , OS User , Terminal and the number of sessions :

*************************************************************************************

SELECT
DBA_USERS.USERNAME "Schema_name",
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED') "Connection_Status",
NVL(OSUSER, '-') "OS_User",
NVL(TERMINAL,'-') "Terminal",
SUM(DECODE(V$SESSION.USERNAME, NULL, 0,1)) "No_Of_Sessions"
FROM
DBA_USERS, V$SESSION
WHERE
DBA_USERS.USERNAME = V$SESSION.USERNAME (+)
GROUP BY
DBA_USERS.USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED'),
OSUSER,
TERMINAL
ORDER BY 1 ;

*************************************************************************************
==>>>

What is the difference between ? and HELP?
There is no difference. Both "?" and HELP will read the SYSTEM.HELP table (if available) and shows help text on the screen.
To use the help facility, type HELP followed by the command you need to learn more about. For example, to get help on the SELECT statement, type:

Help Select
------------------------------------------------------------------------------------

What is the difference between @ and @@?
The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts.
A single @ symbol runs a script in the current directory (or one specified with a full or relative path, or one that is found in you SQLPATH or ORACLE_PATH).

@@ will start a sqlplus script that is in the same directory as the script that called it (relative to the directory of the current script). This is normally used for nested command files.

------------------------------------------------------------------------------------

What is the difference between & and &&?
"&" is used to create a temporary substitution variable and will prompt you for a value every time it is referenced.
"&&" is used to create a permanent substitution variable as with the DEFINE command and the OLD_VALUE or NEW_VALUE clauses of a COLUMN statement. Once you have entered a value it will use that value every time the variable is referenced.

Eg: SQL> SELECT * FROM TAB WHERE TNAME LIKE '%&TABLE_NAME.%';
------------------------------------------------------------------------------------
What is the difference between ! and HOST?
Both "!" and "HOST" will execute operating system commands as child processes of SQL*Plus. The difference is that "HOST" will perform variable substitution (& and && symbols), whereas "!" will not. (Note: use "$" under MVS, VMS, and Windows environments, not "!")

------------------------------------------------------------------------------------
-->>
Sreekanth Babu Vakiti