Kde…

Fun times or “geek times” I should say started last weekend. So I finally gave vista a chance. Since I was involved with beta testing of Vista, they sent a licensed Vista Ultimate Edition to me. So now I have Windows Vista Ultimate on my new laptop. The first thing that I do when get a new PC is to clean up all factory installed mess. AOL, Norton Trial, Snapfish login, etc. , for example. The next thing I did was I installed VMWare.

Now I have a Ubuntu running on my machine, under VMWare, with Oracle 10g RAC installed and configured. I am loving this whole new setup. Here is a screenshot.

If you wish to have a similar setup, you can get the required tools from here. I also upgraded my HTMLDB to APEX 3.0. As you can see in the image above, there is an icon on the desktop named “HTMLDB 1.6″. The image below shows the version of the tool after the upgrade. Look at the version name and number on the right-bottom area of the screen in the picture below.

So all I need now is MS Office on my vista and I am all set. No other installation needed. Well, maybe I will install photoshop but that’s about it.

Below are some useful scripts that i found in one of my old hard drives. If you want to gather stat reports for the oracle ser sessions you could use the following scripts. Right before posting this i tried them on 8i, 9i, and 10g.

To find total cpu usage by each/all sessions, you could use the following sql statement.


Select   nvl ( sess.username, 'oracle_process' ) username
,        sess_stat.sid session_id, value cpu_usage
From     v$session sess
,        v$sesstat sess_stat
,        v$statname stat_name
Where    sess_stat.statistic# = stat_name.statistic#
And      name like '%cpu used by this session%'
And      sess_stat.sid = sess.sid
And      value > 0
Order by value desc
/

To find total resource usage by each user, you could use the following sql statement.

Select   sess.sid, nvl ( sess.username, 'oracle_process' ) username
,        sess_stat.sid session_id, stat_name.name statistic, value cpu_usage
From     v$session sess
,        v$statname stat_name
,        v$sesstat sess_stat
Where    sess_stat.sid = sess.sid
And      stat_name.statistic# = sess_stat.statistic#
And      value != 0
Order by value desc
,        sess.username
,        sess.sid
,        stat_name.name
/


To find session i/o activity by each user, you could use the following sql statement.

Select   nvl ( sessions.username, 'oracle_process' ) username, osuser os_user
,        process pid, sessions.sid sid, serial#, physical_reads, block_gets
,        consistent_gets, block_changes, consistent_gets, block_changes
,        consistent_changes
From     v$session sessions
,        v$sess_io session_io
Where    sessions.sid = session_io.sid
Order by physical_reads desc
,        sessions.username
/

hope this helps…

Kirtan

Using Oracle function TRANSLATE and REPLACE to remove special characters from a string.

This is the definition of the function.

TRANSLATE(expr, from_string, to_string)

Descrption: [FROM ORACLE DOCUMENTATION]

TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. If expr is a character string, then you must enclose it in single quotation marks. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in char, then they are removed from the return value.

You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle Database interprets the empty string as null, and if this function has a null argument, then it returns null.

Let’s see…

SQL> create table t6 (a varchar2(20));

A
--------------
ABC!@#$%^&*XYZ
SQL> SELECT TRANSLATE(a, '!@#$%^&*', ' ') FROM t6;
TRANSLATE(A,'!@#$%^&
---------------------
ABC XYZ

#####
Notice that I removed the $ (DOLLAR) sign from the list of junk.
#####

SQL> SELECT TRANSLATE(a, '!@#%^&*', ' ') FROM t6;
TRANSLATE(A,'!@#%^&*
--------------------
ABC $XYZ
#####
I would create a function that accepts a varchar2 and returns a ‘cleaned’ varchar2 for this purpose.
#####

SQL> create or replace function clean_str(in_string in varchar2)
2 return varchar2
3 is
4 out_string varchar2(4000);
5 remove_char varchar2(100);
6 begin
7 remove_char:='!@#$%^&*æ';
8 select TRANSLATE(in_string,remove_char , ' ') into out_string FROM dual;
9 return (out_string);
10 end;
11 /

CLEAN_STR(A)
------------
ABC XYZ

I kept the last argument ‘ ‘ (a space) because of the NULL rule mentioned above in the description. I just asked Oracle to assign a single space instead.

SQL> insert into t6 values ('ABC!@#$XYZ%^&*XML');

CLEAN_STR(A)
------------
ABC XYZ
ABC XYZXML

Now that I have a string like ‘ABC XYZ’ and ‘ABC XYZXML’, I am going to use Oracle function REPLACE to remove the extra space(s). The advantage of REPLACE is that it replaces all the occurances of the string/character with the provided replacement string. So lets change the function a little bit.

SQL> create or replace function clean_str(in_string in varchar2)
2 return varchar2
3 is
4 out_string varchar2(4000);
5 remove_char varchar2(100);
6 begin
7 remove_char:='!@#$%^&*æ';
8 select
9 REPLACE(TRANSLATE(in_string,remove_char , ' '),' ',NULL)
10 into out_string
11 FROM dual;
12 return (out_string);
13 end;
14 /

CLEAN_STR(A)
------------
ABCXYZ
ABCXYZXML
So now all you have to do is keep a list of unwanted or special characters in the remove_char variable of the function and you are golden.

I hope this helps.

-Kirtan

Updated!!!

I just realized that there is no need to use REPLACE function above. I will keep it as it is just in case if someone wants to use it as it is. Below is an updated(and better IMHO) version of clean_str() function. The idea is to pass the character that you want to remove as the THIRD argument and concatenate the variable remove_char with the same character. It is going to be a space for this example.

create or replace function clean_str(in_string in varchar2)
return varchar2
is
out_string varchar2(4000);
remove_char varchar2(100);
begin
remove_char:=' ' || '!@#''''$%^&*æ';
select
--REPLACE(TRANSLATE(in_string,remove_char , ' '),' ',NULL)
TRANSLATE(in_string,remove_char , ' ')
into out_string
FROM dual;
return (out_string);
end;
/


What you can also do is you can allow the character that you want to be removed as  an input to the function.

create or replace function clean_str1(in_string in varchar2,culprit in char)
return varchar2
is
out_string varchar2(4000);
remove_char varchar2(100);
begin
remove_char:=culprit || '!@#''''$%^&*æ';
select
--REPLACE(TRANSLATE(in_string,remove_char , ' '),' ',NULL)
TRANSLATE(in_string,remove_char , culprit)
into out_string
FROM dual;
return (out_string);
end;
/SQL> select clean_str1('KIR$#@%TAN',' ') from dual;

I got an email from someone a couple of days ago. The email said

############## 

hi,
      i am having a problem here pls help me
 
this is my i/p and o/p pls tell me logic to develop this
 
input table or file
101,abc,5000
101,abc,2000
101,abc,1000
101,abc,1500
102,aaa,2000
102,aaa,1500
102,aaa,500
103,asa,1000
103,asa,300
103,asa,600
 
o/p table or file
0,101,abc,5000
1,101,abc,3000(i.e.,5000-2000)
2,101,abc,2000(i.e.,3000-1000)
3,101,abc,500(i.e.,2000-1500)
0,102,aaa,2000
1,102,aaa,500(i.e.,2000-1500)
2,102,aaa,0(i.e.,500-500)
0,103,asa,1000
1,103,asa,700(i.e.,1000-300)
2,103,asa,100(i.e.,700-600)
 
thanks and regards
******

##################

Oracle analytic functions were on my mind just like Ray charles (he had Georgia). Even though I knew how to use analytic functions I had to do some research. But I finally got the answer.

First of all the input data didn’t make sense because there was no spelled out requirements provided. After my research, I took a close look at it and found that by adding a sequence number to the table this can be easily done. So I created a test case and assigned a sequence number to the table along with other columns.

This is what I could come up with.

SQL> select banner from v$version;

10 rows selected.
Don’t write code for something that can be done so easily if you have the right tools.
hope this helps

cheers
Kirtan

This showed up on Google News today. I don’t see a single reason that can convince me to use Windows Vista on a regular basis. Well, atleast not on the basis of these points.

I did install Windows Vista on my machine. I don’t really care for extreme graphics or stuff like that. As long as I can get a sharp 1024×768 screen with small fonts, I am happy. No need for 3D. No need for glossy effects. I changed the way My Windows XP looked to make it look like Windows 2000, i.e. standard stuff.

Unless Vista can run my scripts and programs 5 times faster than Windows XP, I don’t need Vista. I wonder what others think.

In Oracle/PLSQL, the NVL function allows you to substitute a value when a null value is encountered.

The syntax for the NVL function is:

NVL(a_value, replace_it_with )

a_value is the string to test for a null value.
replace_it_with is the value returned if string1 is null.

Examples,

select NVL(product_descr, product_name) from products;

This statement would return product_name everytime it encounters a null value in the product_descr field. If it finds a value (i.e. any value other than null value) in product_descr field than it will return that value.

select NVL(numeric_field,0) from a_table;

This statement would return 0 everytime it finds a null value in the numeric_field.

You can not use NVL like,

Select NVL(distinct(product_name),’not found’),price, category from a_table;

One possible solution for this is to use distinct before NVL in the statement.

Select distinct NVL(product_name,’not found’),price, category  from a_table;

If you wanted to get distinct values before using NVL function, you can do this.

Select

from ( select distinct product_name, price, category from a_table; )

Also not like, (NVL does not function like DECODE.)

NVL(field1, replace_value1,field2, replace_value2,DEFAULT_FOR_ALL)

You will need to make separate NVL function calls for each column.

select NVL(product_name, ‘not found’), NVL(product_descr, ‘not found’) from a_table;

Hope this helps.

Kirtan

This is the basic explanation of what SQL/XML functions provide in Oracle (since 9iR2) Database as per SQL 2003 standards.

• XMLElement takes an element name, an optional collection of attributes for the element, and arguments that make up the element’s content and returns an instance of type XMLType.
• XMLForest converts each of its argument parameters to XML, and then returns an XML fragment that is the concatenation of these converted arguments.
• XMLConcat takes as input a series of XMLType instances, concatenates the series of elements for each row, and returns the concatenated series. XMLConcat is the inverse of XMLSequence.
• XMLAgg takes a collection of XML fragments and returns an aggregated XML document

I am going to write a couple of examples on how to use these functions in next day or two.

info AT kirtandesai DOT com

I had to delete my post on shell scripting that referred to a webpage on oracle.com. I was doing some updates to post formats and such and noticed that the link on my post was not valid (alive) anymore.

Sorry for the inconvinience.

Here is what I had in the post.

Shell Scripting For DBAs
Here is a helpful article on use of shell scripting for DBAs.
A few years ago, Oracle released the first commercial database on Linux. Since then, Oracle, Red Hat, and Novell/SUSE have been steadily collaborating on changes to Linux kernel as they relate to database and application performance. For that reason, Oracle Database 10g for Linux includes enhancements that are closely related to the operating system. Now more than ever, DBAs need to have knowledge of and experience on this platform to best administer the systems under their watch.
There is a traditional division of responsibilities between sysadmins and DBAs. However, in practice, the distinction is not always clear. Many IT shops employ individuals who address concerns at the database as well as the operating system levels. And of course the Oracle Database itself uses operating system resources and is designed to interact closely with its environment.
Furthermore, many sysadmins and DBAs find it necessary or convenient to automate tasks related to their work. The installation of software, monitoring of system resources, and management of systems involve repetitive and error-prone tasks are better addressed through automated processes than manual procedures.
One method that is used to automate such tasks is shell scripting. Shell scripts play a significant role in the Linux System from the time it is installed. Various scripts are called when the system is started up and shut down. Utilities by Oracle and other third-party vendors are invoked through shell scripts. Because they can be developed quickly, they have historically been used for prototyping applications. System Administrators have taken advantage of the functionality available through shell scripting to provide solutions that are tailored for the particular requirements and idiosyncrasies of the systems under their watch.

Top-N queries provide a method for limiting the number of rows returned from sets (mostly ordered) of data.

NOTE: Oracle does not guarantee that it will return the result of an SQL statement in the order the data was inserted into the table(s). Only way of achieving that is to explicitly uses the ORDER BY clause. Let’s see.

Let’s prepare the table first.

Create table test(a number);

INSERT INTO test (a) VALUES (6);
INSERT INTO test (a) VALUES (5);
INSERT INTO test (a) VALUES (4);
INSERT INTO test (a) VALUES (3);
INSERT INTO test (a) VALUES (2);
INSERT INTO test (a) VALUES (1);
COMMIT;

We want the smallest 3 records, so we limit the rows returned using the rownum and order the data by the column (A number).

SQL> select * from test
  2  where rownum<=3
  3  order by a;

NOT what we wanted, right?

What happens is that the rownum <=3 check is performed prior to the order by clause.

So let's use a "Top-N" style query which utilizes an ordered inline view to force the data into the correct order, then performs the rownum<=3 check to limit the data returned.

SQL> select a from (select a from test order by a)
  2  where rownum<=3;

Now if we wanted 5 biggest values out of the table, all we have to do is change the order of data in the inline view.

SQL> select a from (select a from test order by a desc)
  2  where rownum<=3;

Hope this helps.

Kirtan Desai