IN ORACLE MILIEU …

Beyond Knowledge

Inside Index block – Oracle Database 11g

Leave a Comment Posted by on December 12, 2011

I ran into a scenario where I really wanted to check whats inside the leaf block of an index. This post is to dig inside the index leaf block and find out various facts stored inside a block. We will start with taking a dump of a block SYS.ORCL>alter session set tracefile_identifier=’INDEX_BLOCK_DUMP’; Session altered. SYS.ORCL>alter [...]

Oracle Database 11g , , , ,

Oracle SQL Plan Management – Part 3

1 Comment Posted by on August 7, 2011

SQL Plan Management – Oracle Database 11g You have seen the first 2 parts of SQL plan baselines Part 1 – http://avdeo.com/2011/06/02/oracle-sql-plan-management-part-1/ Part 2 – http://avdeo.com/2011/06/07/oracle-sql-plan-management-%e2%80%93-part-2/ In part 1 we have seen information about SPM and how to automatically capture the baseline In part 2 we have seen how to manually capture the baseline and [...]

, , , ,

Oracle SQL Plan Management – Part 2

3 Comments Posted by on June 7, 2011

SQL Plan Management – Oracle Database 11g In SPM part 1 we saw information about SPM and how to automatically capture the baseline. In this part we will see how to manually capture the baseline and the affect of using FIXED variable. Capturing Baseline Manually Occasionally we have to to capture the baseline manually. This [...]

, , ,

Oracle SQL Plan Management – Part 1

3 Comments Posted by on June 2, 2011

SQL Plan Management – Oracle Database 11g SQL Plan Management or SPM is a new feature introduced in 11g. We will take a detailed look at SPM in the following sessions. Before starting with practical example lets see some theory. What is SQL Plan Management? SPM is a new feature introduced in Oracle database 11g [...]

Database 10g Performance Tuning, Oracle Database 10g, Oracle Database 11g , , , ,

Creating Standby database using Active Duplication – Oracle Database 11g

1 Comment Posted by on May 9, 2011

Introduction: Oracle database 11g introduced a new feature called Active database duplication. Using this feature you can create a new database (primary/standby) from your current running database. This feature does not needs any backup to be taken, nor we have to do any restore. Creating active duplication is a RMAN feature and command for creating [...]

Oracle Database 11g , , , , ,

Avoiding “no data found” : Tips

2 Comments Posted by on April 29, 2011

Some time we face issue about no data found depending on selection criteria. And when this happens in PLSQL procedure we get annoying error ORA-01403: No data found and execution stops. One way to get around this error is to add exception block in our PLSQL procedure as shown below EXCEPTION WHEN NO_DATA_FOUND THEN <Take Action> [...]

ORA Errors, Oracle Database 11g , , ,

An insight into Oracle Index rebuild

2 Comments Posted by on April 12, 2011

You must have gone through my previous article on Oracle Index rebuild – 10g Vs 11g – http://avdeo.com/2011/03/17/oracle-index-rebuild-online-10g-vs-11g/ Well, there is another nice article written by a friend of mine Sumit Bhatia. He has talked about the actions and events happening while rebuilding an index online in oracle database 11g. Different wait events that we [...]

Oracle Database 11g , , , ,

Exchange Partition in Oracle

5 Comments Posted by on March 30, 2011

Some times we faced a need to convert our existing table from non-partition table to a partition table or vice-versa. Or moving a partition of one partition table to another partition. Or make one of the table as a partition of some partition table. Or make a partition of one of the partition table as [...]

Oracle Database 10g, Oracle Database 9i , ,

Virual Index and Invisible Index

Leave a Comment Posted by on March 23, 2011

Oracle has come up with a feature called virtual index in oracle 9i. This feature allow us to test an index on a table without actually adding an index on the table. The table will be visible only in our session and will be used by our queries only (if optimizer decide it to use). [...]

Oracle Database 10g, Oracle Database 11g, Oracle Database 9i ,

Oracle Index rebuild online – 10g Vs 11g

5 Comments Posted by on March 17, 2011

An index is basically used for faster access to tables. Over a period of time the index gets fragmented because of several DMLs running on table. When the index gets fragmented, data inside the index is scattered, rows / block reduces, index consumes more space and scanning of index takes more time and more blocks [...]

Oracle Database 10g, Oracle Database 11g , ,

← Older posts

Follow

Get every new post delivered to your Inbox.

Join 136 other followers