++++++++++++++++++++ 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…
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…
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…
mailx -s “test message” pmehta@1800flowers.com
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…
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 .
change the file /etc/sysctl.conf for the kernel parameters. DBA generally do this for changing semaphore and shared memory parameters. Run the following command to change the current kernel parameters. $ /sbin/sysctl -p
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…
