Bug 445628

Tested on an Oracle 9 database. Oracle logged this as bug 445628 in 1997. Then they closed it saying it was not a bug. Either way, it's interesting so here is a worked example. First create two schemas, one to create a stored procedure and another to own a table:

SQL> conn / as sysdba
Connected.
SQL> create user code_owner identified by code_owner
  2  /

User created.

SQL> grant create session, create procedure to code_owner
  2  /

Grant succeeded.

SQL> create user table_owner identified by table_owner
  2  default tablespace user_data
  3  quota 1m on user_data
  4  /

User created.

SQL> grant create session, create table to table_owner
  2  /

Grant succeeded.

SQL>

Now login as code_owner, create a stored procedure and allow table_owner to execute it:

SQL> conn code_owner/code_owner
Connected.
SQL> create procedure do_nothing as
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.

SQL> grant execute on do_nothing to table_owner
  2  /

Grant succeeded.

SQL>

Now login as table_owner and execute code_owner's stored procedure:

SQL> conn table_owner/table_owner
Connected.
SQL> exec code_owner.do_nothing();

PL/SQL procedure successfully completed.

SQL>

So far so good but if table_owner creates a table with the same name as the schema whose code he wants to execute, that code stops working. I guess this is because Oracle thinks that he is trying to execute his own table:

SQL> create table code_owner (col1 number)
  2  /

Table created.

SQL> exec code_owner.do_nothing();
BEGIN code_owner.do_nothing(); END;

                 *
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00302: component 'DO_NOTHING' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>

The solution is to drop the table:

SQL> drop table code_owner
  2  /

Table dropped.

SQL>

Then the code starts to work again:

SQL> exec code_owner.do_nothing();

PL/SQL procedure successfully completed.

SQL>

Counting NULL Values

You should not use = NULL to check if something is NULL. You should use IS NULL instead. First find a table or view which has a column containing some null values and use the NVL function to count them:

SQL> select count(*) from dba_tab_comments
  2  where nvl(comments,'NULL') = 'NULL'
  3  /

  COUNT(*)
----------
      2354

SQL>

Count them again using IS NULL. The answer will be the same:

SQL> select count(*) from dba_tab_comments
  2  where comments is null
  3  /

  COUNT(*)
----------
      2354

SQL>

Finally, count them using = NULL. This will not find the null values:

SQL> select count(*) from dba_tab_comments
  2  where comments = null
  3  /

  COUNT(*)
----------
         0

SQL>

In Spanish / en español

Division by Zero

Tested on an Oracle 9 database. It is well known that division by zero is not allowed in SQL:

SQL> SELECT 1/0 FROM DUAL
  2  /
SELECT 1/0 FROM DUAL
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL>


It is not allowed in PL/SQL either:

SQL> DECLARE
  2   QUOTIENT NUMBER;
  3  BEGIN
  4   QUOTIENT := 1 / 2;
  5   DBMS_OUTPUT.PUT_LINE('Quotient 1 = '||QUOTIENT);
  6   QUOTIENT := 1 / 0;
  7   DBMS_OUTPUT.PUT_LINE('Quotient 2 = '||QUOTIENT);
  8  END;
  9  /
Quotient 1 = .5
DECLARE
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 6

SQL>

Fortunately you can trap this error tidily as follows:

SQL> DECLARE
  2   QUOTIENT NUMBER;
  3  BEGIN
  4   QUOTIENT := 1 / 2;
  5   DBMS_OUTPUT.PUT_LINE('Quotient 1 = '||QUOTIENT);
  6   QUOTIENT := 1 / 0;
  7   DBMS_OUTPUT.PUT_LINE('Quotient 2 = '||QUOTIENT);
  8  EXCEPTION
  9   WHEN ZERO_DIVIDE THEN
10   DBMS_OUTPUT.PUT_LINE('Division by zero not allowed');
11  END;
12  /
Quotient 1 = .5
Division by zero not allowed

PL/SQL procedure successfully completed.

SQL>

Technorati Claim Token

Q9GD92S6RT44


Some of you might be wondering what this is. Well, I was reading Blogging All-In-One for Dummies by Susan Gunelius and I found out about Technorati, a site which indexes blog posts. I went to their URL, http://www.technorati.com, and registered.

Then I had to claim a blog so I told them I owned this one. To prove it, they sent me the claim token above (Q9GD...) and I had to publish it in a post then tell them when I had done so. Within a very short time, their robot or crawler had found the claim token, thus confirming that I was, indeed, responsible for this blog.

Then they looked through the other posts and assessed my authority. Of course, they don't say exactly how they work it out but I guess it depends on the number and quality of the posts I have written, how often I add new posts, how many followers I have and so on.

At the time of writing, the top blog, The Huffington Post, has an authority of 932 and the other blogs in the top 20 have an authority between 800 and 900. My authority is 96, as you can see below, so I have a bit of work to do (as usual, click once or twice to display the screen print at its original size):

SUBSTR

Tested on an Oracle 9 database.
Oracle provides a substr function, which allows you to extract part of a string of characters. Often, you will use this on a database column:


SQL> select username from dba_users
  2  where substr(username,1,3) = 'SYS'
  3  /

USERNAME
------------------------------
SYSTEM
SYS

SQL> 

This is equivalent to the following:

SQL> select username from dba_users
  2  where username like 'SYS%'
  3  /

USERNAME
------------------------------
SYSTEM
SYS

SQL> 

But for the remaining examples I will use substr on a fixed character string:

SQL> col substring format a9
SQL> select
  2  substr('International DBA',15,3) substring
  3  from dual
  4  /

SUBSTRING
---------
DBA

SQL> 

As you can see above, the column name or string is followed by 2 parameters, start position and length. Start position tells Oracle where to start in the string and length specifies how many characters to return. The first character in the string is counted as position 1. You can see this in the examples at the start of this post and in the SQL below:

SQL> select
  2  substr('International DBA',1,5) substring
  3  from dual
  4  /

SUBSTRING
---------
Inter

SQL> 

But the first character in the string can be numbered 0 instead:

SQL> select
  2  substr('International DBA',0,5) substring
  3  from dual
  4  /

SUBSTRING
---------
Inter

SQL> 

If the start position is negative, Oracle goes to the end of the string and works backwards to determine where to start from:

SQL> select
  2  substr('International DBA',-3,3) substring
  3  from dual
  4  /

SUBSTRING
---------
DBA

SQL>

But specifying a negative length does not cause the characters to be returned in reverse order:

SQL> select
  2  substr('International DBA',-1,-3) substring
  3  from dual
  4  /

SUBSTRING
---------


SQL>

That is because if length is less than 1, Oracle returns a null:

SQL> select
  2  nvl(substr('Andrew',1,0),'NULL') substring
  3  from dual
  4  /

SUBSTRING
---------
NULL

SQL> select
  2  nvl(substr('Andrew',1,-999),'NULL') substring
  3  from dual
  4  /

SUBSTRING
---------
NULL

SQL>

If you miss out the length, you get all the characters from the starting point to the end of the string:

SQL> select substr('Andrew',4) substring from dual
  2  /

SUBSTRING
---------
rew

SQL>

You can even use expressions for start position and length:

SQL> select
  2  substr('Great Britain',2*2,2*3) substring
  3  from dual
  4  /

SUBSTRING
---------
at Bri

SQL>

And floating point numbers are converted to integers first:

SQL> select substr('Andrew',2.5,3.7) from dual
  2  /

SUB
---
ndr

SQL>

Answer to Q3 from Vijay

To find the user who is taking up most CPU time, you need to look in V$SYSSTAT to get the number of the statistic in question e.g.

 

SQL> select statistic# from v$sysstat

  2  where name = 'CPU used by this session';

 

STATISTIC#

----------

        12

 

SQL>

 

The above step is important as the numbers have a habit of changing from one Oracle version to the next. Then you can get the SIDs of the sessions that have used most CPU since they logged in e.g.

 

  1  select * from

  2  (select sid, value/100 from v$sesstat

  3   where statistic# = 12

  4   order by 2 desc)

  5* where rownum <=10

SQL> /

 

       SID  VALUE/100

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

        22     379.49

        15     310.84

        13     301.48

        33     287.93

        53     247.32

        79     176.91

        56     166.92

       153     161.46

        96     141.22

        36     136.64

 

10 rows selected.

 

SQL>

 

The values are in hundredths of a second so dividing them by 100 gives the results in seconds. If you want to get figures for users who are currently using most CPU, you will need to repeat the query after a while and just look at the users whose CPU usage has increased in the meantime. Once you have decided which SID you want to investigate you can find out who it is as follows:

 

  1  select sid, username from v$session

  2* where sid in (13, 15, 22)

SQL> /

 

       SID USERNAME

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

        13 ANDREW

        15 VIJAY

        22 FRED

 

SQL>

Questions from Vijay

I received the 3 questions below by E-mail last night:

Hi,

This is Vijay from India( Hyderabad).

Hope doing well by the grace of God.

Could You Please answer my queries.

Q1) if a user email you that his query taking long time. How will you solved the issue(solve LONG RUNNING QUERY).

Q2) what pre requisite steps will you do when CPU TAKING LONG TIME Before TUNING Database.

Q3) what pre requisite steps will you DO to KNOW which user is using CPU CONSUMPTION

Presently looking job Please help me out with this question.

I would Request you to answer my above Oracle DBA Query

Thanks for your precious time in advance.

take care

With Regards

Vijay

The answer to Q3 follows on from a couple of earlier posts:

http://international-dba.blogspot.com/2011/03/cpu-used-this-session.html

and

http://international-dba.blogspot.com/2011/03/cpu-used-by-this-instance.html

You can see it here:

http://international-dba.blogspot.com/2011/07/answer-to-q3-from-vijay.html