Category: Oracle

set time on set timing on set verify off select to_char(sysdate, ‘DD-MON-RRRR:HH24:MI’) from dual; prompt ‘Total distinct users and total user sessions’ select count(distinct username), count(1), round(count(1)/count(distinct username),1 ) from v$session where username is not null; prompt ‘Total terminal server distinct users and total user sessions’ select count(distinct username), count(1), round(count(1)/count(distinct username),1 ) from v$session…

Read More “get_ratio.sql get the ratio of users from v$session and this uses CASE-WHEN-THEN clause” »

col AVG_ACTIVE_USR format 999999.99 col AVG_TOT_USR format 9999.99 col AVG_USR_CPU format 9999.99 col AVG_SYS_CPU format 99.99 col AVG_IDLE_CPU format 99.99 col AVG_WAIT_CPU format 99.99 col TOT_ODR format 99999999 col AVG_ACT_USR format 999999.99 col RUNQ format 999.99 col CSW format 99999.99 set lines 120 pages 2000 accept start1 prompt ‘Enter Start Dare in DD-MON-RRRR:HH24:MI format :…

Read More “pvm_metric.sql for gathering report from vmstat tables” »

++++++++++++++++++++ Problem description ++++++++++++++++++++ Some users started getting ORA-4031. This is the eror that show following: 4031, 00000, “unable to allocate %s bytes of shared memory (“%s”,”%s”,”%s”,”%s”)” ++++++++++++++++++++++ Problem solution ++++++++++++++++++++++ We ran following query and found failues that may result into 4031. set echo off verify off set lines 120 pages 555 /* V$SHARED_POOL_RESERVED_SIZE…

Read More “ORA-4031 issue and solution on 09-MAY-2008” »

SEMMSL Set to 10 plus the largest initsid.ora PROCESSES parameter of an Oracle database on the system. The PROCESSES parameter can be found in each initsid.ora file, loc ORACLE_HOME/dbs directory. The default value of PROCESSES for the 8.1.5 prebuilt database is 50. SEMMNS Set to the sum of the PROCESSES parameter for each Oracle database…

Read More “How to calculate PROCESSES parameter” »

The only drawback in setting the processes value high is that the corresponding kernel settings, SEMMNS and SEMMSL need to be set higher based on the following formulas: SEMMSL – Set to 10 plus the largest PROCESSES parameter of any Oracle database on the system. SEMMNS – Set to the sum of the PROCESSES parameter…

Read More “How to set Processes Parameter” »

If you use dbms_stats in 10g it will not automatically invalidate dependant cursors. This is the default behaviour as of 10. To revert to older previous behaviour you will have to set the parameter no_invalidate to false. . no_invalidate Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors…

Read More “Explain Plan Doesn’T Change For Sql After New Statistics Generated” »

Backing it up in a human readable format: alter database backup controlfile to trace; alter database backup controlfile to trace as ‘/some/arbitrary/path’; alter database backup controlfile to trace as ‘/some/arbitrary/path’ reuse; If you are using “alter database backup controlfile to trace as ‘test1.sql’; then test1.sql will be created in $ORACLE_HOME/dbs folder.

Here is a much simpler version: ——————————- Create table t1 (c1 number primary); session1: insert into t1 values (1); session2: insert into t1 values (1); — would hang v$lock would reveal session 1 blocking session2 on TX lock why? because you have a constraint where you are asking Oracle to check uniqueness of data (unique…

Read More “Insert cause enqueue locks” »

Case 1:- Allocate More space to temp tablespace before starting the analyze. U can reclaim space by doing. Alter tablespace temp offline; Alter tablespace temp online; Alter tablespace temp coaelsce; Case 2:- If there is some spcace then allocate around 10 GB of space to TEMP .

A new mechanism to control the allocation of temporary storage space was introduced in 7.3, initially as support for OPS, but it is equally applicable to non-OPS environments. This requires the creation of a designated temporary tablespace, that is one created with the designation TEMPORARY. The effect of this is twofold: 1. No permanent objects…

Read More “Temporary tablespace explaination” »