DATABASE TIPS: December 2012

ORA-12751: cpu time or run time policy violation

Q:
What is the error "ORA-12751: cpu time or run time policy violation" means?

A:
This is an undocumented error.
Cause: A piece of code ran longer than it is supposed to
Action: If this error persists, contact Oracle Support Services.

This error happens most commonly with MMON process while taking AWR snapshots. If the process holds resources more than 15 minutes, the MMON process will be terminated and the snapshot will not be taken. You will see a snapshot is missing in the repository in this case.

The MOS note 761298.1 regarding this error will be helpful

query to find size of schema in oracle

Execute this query to find the size of each schema in oracle database,

set pages 100
select    a.owner "Owner", cnt_obj "No. of Objects", decode(seg_mb, NULL, 0, seg_mb) "Size in MB"
from
     (select owner, count(*) cnt_obj from dba_objects group by owner) a ,
     (select owner, ceil(sum(bytes)/1024/1024) seg_mb from dba_segments group by owner) b
where     a.owner = b.owner(+)
order    by 3 desc ,2 desc, 1;

Thank you,

Oracle function to round up

SQL function ROUND will round number with respect to the next digit.

Basically ROUND(15.68)=16 and
ROUND(15.43)=15

we have TRUNC to round downwards

TRUNC(15.68)=TRUNC(15.43)=15

Then how to round up wards?
This is very commonly needed function but a many of us are not aware of.. I used to add one and truncate to get the result. I found a function to round up very recently.

Function used is CEIL

Now,
CEIL(15.68)=CEIL(15.43)=16

Thank you..

Script to kill all sessions by a user

Normally to Kill a session connected by a user in oracle, you should collect the sid and serial# from the view V$SESSION and execute the following command with sid and serial# filled in it.

ALTER SYSTEM KILL SESSION 'sid,serial#';

Read more about killing a session Here

We can create a PL/SQL script to kill all the sessions based ton the same, as given below

begin
for x in (select Sid, Serial# from v$session where Username=upper('TypeUserNameHere'))
loop
   execute immediate 'Alter System Kill Session '''|| x.Sid || ',' || x.Serial# || ''' IMMEDIATE';
end loop;
end;
/

If you want to embed it in to a bash (shell) script, it will be like the following script

#!/bin/bash
sqlplus -silent /nolog << EOS
connect / as sysdba
begin
for x in (select Sid, Serial# from v$session where Username=upper('TypeUserNameHere'))
loop
   execute immediate 'Alter System Kill Session '''|| x.Sid || ',' || x.Serial# || ''' IMMEDIATE';
end loop;
end;
/
EOS
exit

How to calculate IOPS in oracle?

IOPS (Input/Output Operations Per Second, pronounced eye-ops) is a common performance measurement used to benchmark computer storage devices like hard disk drives (HDD), solid state drives (SSD), and storage area networks (SAN). (From http://en.wikipedia.org/wiki/IOPS)

But in Oracle database we measure how much of such requests are generated by the database system. We have to check whether our hardware are capable of processing the request generated by the database system in order to maintain the performance.

IOPS is calculated in Oracle database as the sum of "Physical Read Total IO Requests Per Sec" and "Physical Write Total IO Requests Per Sec"

These values will be available in the table, dba_hist_sysmetric_summary

Here is a query to find IOPS in Oracle

break on report
compute sum of Value on report select METRIC_NAME,avg(AVERAGE) as "Value" from dba_hist_sysmetric_summary where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec') group by METRIC_NAME;

Read this PDF for more information:
http://www.oracle.com/technetwork/database/back.pdf

Oracle instance hangs when switching undo tablespace

Oracle instance start up hangs after creating new undo tablespace and switching between old and new tablespaces.

I may be because of this fixed bug: Bug:7313166

ORA-1555 error when query duration less than undo retention

Q:
I am getting the following error in the alert log file

Thu Oct 18 05:27:20 2012

ORA-01555 caused by SQL statement below (SQL ID: fphc75pmdyjpb, Query Duration=4040 sec, SCN: 0x056d.b3393d60):

My undo retention is 6300 seconds and undo management is auto. Undo datafile is auto extensible and has not reached maxbytes.

What may be the reason for ORA-1555 error when query duration less than undo retention?

It is an unsolved bug: Bug 8231583

Creating a new undo tablespace and dropping the old one is the temporary work around.

Read more about this in the MOS document below:

ORA-01555 When Max Query Length Is Less Than Undo Retention

[Article ID 1131474.1]

How to start Oracle database

Q:
How to start Oracle database?

A:
Caution: this note is not for DBAs or database experts.

Starting Oracle database in Windows
Let us assume all required environment variables are set. All these variables will be set at the time of installation itself. Ans also assume that no advanced securities.

1. Type services.msc in run prompt
2. Check for the service OracleService<YourDbName> if it is started.
3. Start the service if it is not started. It will start the db as well.
4. If the service is already started, and db is not working, then
Go to run and type cmd and in the prompt
        \>set ORACLE_SID=yourdbname
        \>sqlplus / as sysdba
now you will get the SQL prompt. In the SQL prompt,
      SQL> startup
it will start the database, you can check if the database is started by executing the command,
     SQL>select status from v$instance;
The output should be OPEN.
STARTED is not enough to work in the database.


Starting Oracle database in Linux
Here also we assume every thing is set to basic, all environment variables are already set.

1. Open the shell
2. Make sure that you are the administrative user of oracle
3. run the command $export ORACLE_SID=urdbname
4. then $sqlplus / as sysdba
now you will get the SQL prompt, now you can startup the database using startup command. and you can check the status by selecting from v$instance also.

If you find any problem in starting your database with this notes contact a DBA :).
Read oracle documentation here:
http://docs.oracle.com/cd/B19306_01/server.102/b14231/start.htm

Why Oracle account getting locked automatically

Q:
Why Oracle account gets locked automatically?

A:
A user profile will be assigned to every Oracle user accounts. The settings in the profile will make the user get locked and unlocked automatically.

In Oracle 11g, the default settings of the profiles are as given below,

1. 10 failed login attempts will lock the account for 0ne day, and it will get unlocked automatically.
2. You have to change the password of the user in every 180 days, and your account will get locked if you after 7 days from the first login after 180 days.

To change password of a user ali to alimajaf and unlock the user you can execute the following command

SQL> alter user ali identified by alimajaf account unlock;

if you want to change the default behavior of password expiration after 180 days you can change the default profile as

SQL>alter profile default limit PASSWORD_LIFE_TIME unlimited;

If the user is having any profile other that default you have to check for the profile of the user from the data dictionary view, dba_users and then you can change the profile.