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
 
 Read more here:
[ ID 1076092.1]

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?

A:
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:



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.  

Simple Script to check alert.log file for errors in oracle

We have to check alert log files for errors, but its a time consuming process to read the file using less and check for errors. If you do a grep it will take the whole file and if the log rotation is done by month, you will get undesired output. Here is a script to check alert.log file in oracle for errors for today. We just have to copy this to a file and name it as alertcheck.sh and then from the OS prompt run
 "sh alertcheck.sh <alert log file with complete path>" without quotes.

######################################################################################################
#
#This script can be used to check any ORA- errors are there in the alertlog file for the present day #
#
######################################################################################################
#!/sbin/bash
if ! [ $1 ] ; then
echo Usage:
echo "sh alertcheck.sh 'alertlogFilename'"
else
 alrt=$1
 frm=$(grep -m 1 -n  "`date '+%a %b %d'`" $alrt |awk -F[:] '{print $1}')
 if ! [ $frm ]; then
  echo -e "\033[33mWe cannot find any entry for today."
  echo -e "Please check the file you have given is correct OR check with tail command manually\033[0m"
 else
  lst=$(wc -l $alrt | awk '{print $1}')
  lns=$(awk -v a="$lst" -v b="$frm" 'BEGIN{print a-b+1}')
  dt=$(date '+%a %b %d')
  echo -e "\033[34m..........Checking $lns lines writen today -$dt- out of $lst lines in the alert log.........\033[0m"
  err=$(tail -$lns $alrt | grep ORA-|tail -1|awk '{print $1}')
  if [ $err ]; then
    echo -e "\033[31mErrors found:\033[0m"
    echo ------------
    tail -$lns $alrt |  grep  ORA- |grep -n ORA-
    echo ""
    echo ""
    echo -e "\033[31mCheck the details of the errors below. \033[0m (Details means the surroundig lines of the error message only)"
    echo  "-------------------------------------"
    tail -$lns $alrt |  grep -A 5 -B 2 --color ORA-
  else
    echo -e "\033[32mNo Errors Found. Enjoy \033[33m:)\033[0m"
  fi
 fi
fi
#############################End of the script ##################################

If there are errors it will list you all errors first and then a few lines around the error message with a heading error details.

If no errors it will give you corresponding message

If the script can't find any entry for the date, it will give a message for it and will remind you to check the file.

Enjoy

Thank you,
ALI

DBMS_STATS consuming resource

Q: DBMS_STATS.GATHER_SCHEMA_STATS consuming much resources. How to optimize statistics collection using DBMS_STATS?

A: We DBAs used to collect object statistics to  improve performance of the database. But actually statistics collection will use much resources. You may see dbms_stats in top sql statements.

Collect statistics only in recovery window.

We do not need to collect statistics every day for all tables except those with a lot of changes. There is a way to collect statistics of only tables having outdated statistics. The method is to collect schema statistics only for stale statistics tables.

How does it works??

Our db will record all changes in a table in user_tab_modifications, When the modification reaches a threshold, the statistics will be marked as stale. And when you collect the statistics with stale option, It will collect statistics for only those tables with stale statistics.

How to do it?

Here is an example

SQL> BEGIN
2 DBMS_STATS.GATHER_SCHEMA_STATS (
3 ownname => 'SCOTT',
4 estimate_percent => 20,
5 block_sample => TRUE,
6 method_opt => 'FOR COLUMNS SIZE 10',
7 options => 'GATHER STALE',
8 cascade => TRUE);
9 END;
10 /

You can also give GATHER AUTO instead of GATHER STALE.
visit oracle documentation for more information

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461

dbms_space.object_growth_trend

Q. What does the following query do? It is one of the most expensive Queries.


insert into wri$_adv_objspace_trend_data select timepoint,
 space_usage, space_alloc, quality from
      table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
         NULL, 'FALSE', :5, 'FALSE'))

A:
It is used to collect data for space adviser for enterprise manager. You can disable the job if you don't use space adviser.
Execute dbms_scheduler.disable('sys.auto_space_advisor_job');

The real problem is solved if we go to 10.2.0.4.
And yes it has to do with bug 5376783.
All users on 10.2.0.2 have this performance problem !!!
















https://forums.oracle.com/forums/thread.jspa?threadID=1111378

Import and Export (links)



Thinking to do  a research on expdp and impdp, I have collected some links. I am sharing these with you hoping it would be helpful for you as well.

You will find billions of sites about the same, please share if you find some of them worth reading.

 I have just did a cross read, felt its worth reading and marked for further thorough read. 




This link may be much useful for us. But I felt it is a little bit difficult to understand.

Full oracle documentation:




see these issues, The same happened even for me.


A lot of  bugs related to this  topic are listed here, 

SQL*Net break/reset to client

SQL*net break/Reset client wait occurs when exceptions are left unhandled, I have prepared a demo of this.

1.       Created a table “test”  in scott schema with one column and made it primary key. Inserted one row value “duplicate” in it.

2.       There is no waits of this kind in scott session:

3.       Inserted a duplicate row with exception handled

4.       Still no waits

5.       Inserted a row without handling the exception

6.       Now wait comes (I have no idea why two)

7.       Let’s drop a table which is not exist and no exception handling

8.       Waits increased by two

9.       What about an handled exception once more?

10.   No problems, waits are still 4

So, These exceptions are to be handled properly in the database itself (using pl/sql) to avoid this wait events. Handling inside programs(java) will not solve this events. If insets or updates make problems, one suggestion we can give the application developers is to use merge statement

beautifying output in sqlplus

Q:
Why SQLPLUS output is not coming properly?
How to get proper output in SQLPLUS?

A:

Here are some techniques to enhance the readability of sqlplus output.

Check this snap. The out put is really difficult to understand.


What can we do to make it readable?
First set the page size to a high values (it will be 6 by default). I used to set it to 100 so that i can see the headings after each 100 lines(including headings lines footers etc).
SQL>set pagesize 100
OR
SQL>set pages 100

If you don't want to see the heading at all. You can set
SQL>set pages 0
lets see the output after setting the page size.

Even now it is not much clear. Now lets set the line size also.I will set the line size to 150 because my screen can accommodate 150 letters in a single line
You can set it as
SQL>set linesize 150
OR
SQL>set lines 150



 Here we cant see much differences in the output apart from the line under FILE_NAME has became longer till the end of the screen. But still you have to do this because this will be the length of the line in your output after formatting the rest as well.

Here the output is coming like this because the column FILE_NAME is 513 bytes long.
Now we can format the column array size.
Now I am setting the column length of FILE_NAME to array of 30 characters. (you can do it depending up on the length of the names of the files in your output).
You can do it as
SQL> column file_name format a30
OR
SQL>col file_name for a30

Now things became under control :P

I am making the output a little more packed so that you don't have to scroll to see the entire outout. I am changing the lengths of the columns TABLESPACE_NAME & FILE_NAME



 OKEY?

Now we can go a little more additional. Here I have 11 rows selected, some of the tablespaces have more than one data file.  I want each tablespace names to be printed only once, it will be easy for me to identify the tablespaces. I will do like this.



Now I feel like calculating the sum of the sizes of each tablespaces here it self. You don't have to re write the query.!!!



 The out put will be a little confusing at the beginning but will be very useful for daily dba activities.
If you don't want to give your own label, you can simply type like
SQL>compute sum of max_gb on tablespace_name
here the label will be 'sum'

If you want the total sum, You can do like this.



 like sum, you can calculate average, max, min etc using this function.
for more information go to oracle documentation
(if you are getting page not found message, click at the address and press enter)

Hope it is helpful,

Thanks
alimajaf