The LIKE-clause can ignore indexes, causing queries to run forever while doing full table scans. This document describes how to tune such SQL statements by making use of Oracle Text or reverse key indexes.
A materialized view that is verified to be fast refresh should update relatively fast. But, what happens when there are few changes to the master table, no network issues, no aggregation in the snapshot query and the refresh still runs slow?
It is always a puzzle for a DBA to look into the user's complaint of getting "ORA-01555 Snapshot too old : rollback segment number x with name "_SYSSMUx$" too small " error. You have looked into the database. If your UNDO_MANAGEMENT is set to AUTO, you can not do anything to size the rollback segments manually since it is being managed by oracle. All the associated tables and indexes have been analyzed and statistics are up to date. The undo tabelspace is almost full. You may advise the user that there should be frequent commits (if it is a data loading process) or if there is a long running query and other users change the data that is being selected by the query, this can happen and in that case, if possible, advise the user not to run these two at the same time.
This article describes the procedure to install the April 2007 CPU patch on Oracle Database Release 10.1.0.5. The Patch Number is p5907304_10105_WINNT.zip
This article describes the process of upgrading the Oracle Database Server 10g Release 10.1.0.2 to Oracle Database 10g Release 10.1.0.5 on Windows XP SP2.
Caution: This method is for Testing & Learning purposes only.
Most of the Oracle 10g databases are using ASM for storage as it's very simple to maintain the storage w.r.t. to Disks, Datafiles etc.
Oracle 10g’s brilliant alternative to database point in time recovery is the the Flashback Database feature. With this feature in place you can do almost everything that you can with point in time recovery, without actually having to go through all the disruptions and hassle that a PITR necessarily entails.I recently had a first hand opportunity to see the power of this feature, when I ran a scriptfile to drop tables and unwittingly dropped one of the tables containing sensitive information belonging to my employer Creative Infotech. I later recovered the table and was amazed at seeing how easy it had become to get back dropped objects in Oracle 10g, especially Release 2. Below is a simplified version of what I did
Advanced features of Oracle Net include failover and load balancing. These features are actually interrelated in as much as you usually don’t have one without the other. While they are mostly used in a RAC environment, they can be set up in a single instance environment as well.
In the context of Oracle Net, failover refers to the mechanism of switching over to an alternate resource when connection to the primary resource gets terminated due to any reason. Connection failure may be broadly categorized as:
Once upon a time Le Petit Chaperon Rouge (LPCR) decided to change db_files from 200 to 300 on a busy cluster of four nodes.
Soon after the change applications started to block each other, timeout and crash (enq: TX - contention, cursor: pin S wait on X, enq: HW - contention). Those applications which were actively inserting into LOB columns - suffered the most.
Many lock holders were waiting for "DFS lock handle". Thus LPCR learned that DFS stands for "Distributed File System".
As soon as she reversed the changes - everything went back to normal.
Recently my friends at work saw me doing things with indexes they didn't understand. They saw what looked like unneeded columns in some indexes, and funny expressions they didn't know could be done in an index. So I explained to them the benefits of INDEX ONLY ACCESS, and FUNCTION BASED INDEXES. They had not seen these two techniques before so I figured maybe other people would be interested too. Here is it, a short discussion on what are fast becoming my two favorite tuning techniques.