pvmehta.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…

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” »