Added OCP to my credentials today

Finally, cleared my OCP 9i Database on 5th April. I had kept it pending for quite sometime.
I should be getting my Oracle 9i database administration OCP certificate and the Managing Oracle 9i Database on Linux certificate in a month or so.

[Re-post from Jaffar's blog] Converting your single-instance database to RAC with RCONFIG

Amazing post on how to convert a Single-Instance Database to a Real Application Cluster using RCONFIG.

Please do have a look at this post. It includes a comprehensive presentation on the subject.

Converting your single-instance database to RAC with RCONFIG

I am CISA (Qualified) now

I had appeared for CISA (Certified Information Systems Auditor) on 11th December, 2010 in Kuwait.

Because of the amount of efforts put in for this examination, I have been anxiously waiting for the results. When I saw the email from ISACA in my inbox.... all I can say is my heartbeats were quite higher than normal... Adrenaline was doing its job. When I saw "PASSED" in the message body... there was a quick change of emotions.... Really felt the switch happening...
Going from Fear, through a mix of emotions to a big smile on my face.... Alhamdulillah (All Praises are for Allah [Glorified be He]).

The exam was difficult and it tested my CISA skills to the extreme. Never prepared so hard for any exam till date... Really felt that Oracle Certifications are piece of cake in front CISA examination. Was tough.... You really need to think like an auditor. It was difficult for me to think from an "Asessing" point-of-view as I have been studying, thinking and doing things from "Doing" point-of-view. Even when I worked on IT Governance and Balance Scorecard in my present company ()using COBIT 4.0), it was from a "Doing" perspective. Only when I was with the IS Auditors, I would try to understand how they do their work.

From a DBA's standpoint, I can see a lot of my work that needs improvement using the knowledge acquired by CISA contents. Personally, I need to audit my own work now, so as to suggest myself in areas of improvement (as observations) or to raise an NCR (Non-Compliance Report) for myself. This should help me self-assess my work and be proactive with preventive actions.

For more information about ISACA and CISA (and also a variety of certifications) please hop into ISACA's website: https://www.isaca.org/Pages/default.aspx

For now, I look forward to certify my CISA soon.

Next in the queue should be CISSP, I think?

"Knowledge is Power"

tnsManager v1.8.3

tnsManager is an interesting tool by Andrew Barry to centralize your TNSNames management.

Version 1.8.3 is the latest stable build. Simple steps and easy to deploy and manage. It is available for both Windows and Linux Environments. You can also configure tnsManager for High Availability.Test it out, it should be fun.

You can download the installation and check out the Installation Guide and Administration Guide.
http://www.shutdownabort.com/tnsmanager/index.php

RMAN Clone erroring out with ORA-19804

ORA-19804: cannot reclaim string bytes disk space from string limit

I received ORA-19804 on one of my RMAN clone jobs this morning.

RMAN-00571: ===========================================
RMAN-00569: ========= ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571: ===========================================
RMAN-03002: failure of Duplicate Db command at 11/30/2010 17:59:32
RMAN-03015: error occurred in stored script Memory Script
ORA-19870: error reading backup piece \\BLADE5\MIS\BACKUP\RMAN\ORCL\ORCL_ESLU9PLK_1_1_20101130
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 103388672 bytes disk space from 1073741824 limit

The problem is that Oracle cannot reclaim disk space from DB_RECOVERY_FILE_DEST_SIZE set limit.

There are five possible solutions:
1) Take frequent backup of recovery area using RMAN.
2) Consider changing RMAN retention policy.
3) Consider changing RMAN archivelog deletion policy.
4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
5) Delete files from recovery area using RMAN.

One can choose all or any that corresponds to his situation to fix the problem. Although, it is easy to figure out and apply the fix, thought of sharing it here with an OTN Thread link, as Aman, Anand and Robert have explained it very well.

Thread: RMAN BACKUP

[AGIS STAMATOPOULOS] Installation of Oracle 10gR2 database on RedHat Enterprise Linux/Oracle Enterprise Linux 5 (x86_64)

Installation of Oracle 10gR2 database on RedHat Enterprise Linux/Oracle Enterprise Linux 5 (x86_64)

WEDNESDAY, NOVEMBER 17, 2010 | AGIS STAMATOPOULOS


As root

1) Check Hardware Requirements

# grep MemTotal /proc/meminfo >= 1Gb
# grep SwapTotal /proc/meminfo 
Between 1024 MB and 2048 MB 1.5 times the size of RAM 
Between 2049 MB and 8192 MB Equal to the size of RAM 
More than 8192 MB 0.75 times the size of RAM 

# df -h 
400 MB of disk space in the /tmp directory 
Between 1.5 GB and 3.5 GB of disk space for the Oracle software, depending on the installation type 
1.2 GB of disk space for a preconfigured database that uses file system storage (optional) 
To determine the amount of free disk space on the system, enter the following command

2) Kernel Requirements 

# uname -a >= 2.6.18-8 

3) Disable SELinux or switch it to "Permissive" mode
#/usr/sbin/sestatus 
SELinux status: disabled
or 
#/usr/sbin/getenforce
Disabled

If you see the previous output SELinux is disabled.

If SELInux is enabled then run :

Commands, as root: 
====================== 
getenforce (returns "Enforcing") 
setenforce 0 
getenforce (returns "Permissive") 

End to restore it to enforcing 
setenforce 1 
getenforce (returns "Enforcing") 

For more info check Oracle Metalink Note: 454196.1

4) Installing Required packages

Use rpm -qa | grep package_name for the following packages

binutils-2.17.50.0.6-2 (x86_64)
compat-db-4.2.52-5.1 (x86_64)
compat-libstdc++-296-2.96-138 (i386)
compat-libstdc++-33-3.2.3-61(x86_64)
compat-libstdc++-33-3.2.3-61 (i386)
control-center-2.16.0-14 (x86_64)
gcc-4.1.1-52 (x86_64)
gcc-c++-4.1.1-52 (x86_64)
glibc-2.5-12 (x86_64)
glibc-2.5-12 (i686)
glibc-common-2.5-12 (x86_64)
glibc-devel-2.5-12 (x86_64)
glibc-devel-2.5-12 (i386)
glibc-headers-2.5-12 (x86_64)
ksh-20060214-1.4 (x86_64)
libaio-0.3.106-3.2 (x86_64)
libgcc-4.1.1-52 (i386)
libgcc-4.1.1-52 (x86_64)
libgnome-2.16.0-6 (x86_64)
libgnomeui-2.16.0-5 (x86_64)
libgomp-4.1.1-52 (x86_64)
libstdc++-4.1.1-52 (x86_64)
libstdc++-devel-4.1.1-52 (x86_64)
libXp-1.0.0-8 (i386)
make-3.81-1.1 (x86_64)
sysstat-7.0.0-3 (x86_64)

The version of the above packages is the lower needed. So if you are in higher you are OK

e.g
# rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep binutils
binutils-2.17.50.0.6-14.el5 (x86_64)

5) Set the kernel parameters

kernel.shmall = physical RAM size / pagesize (getconf PAGESIZE) -- If the defaults are greater then leave it.
kernel.shmmax = 1/2 of physical RAM, but not greater than 4GB -- If the defaults are greater then leave it.
kernel.shmmni = 4096 
kernel.sem = 250 32000 100 128 
fs.file-max = 512 x processes (for example 65536 for 128 processes) 
net.ipv4.ip_local_port_range =9000 65500
a.) The runInstaller (OUI) checks may expect this to be the old guidance of “1024 65000”. The new guidance from Oracle development is “9000 65500”. Please allow the runInstaller (OUI) to proceed with the new guidance from Oracle development.
net.core.rmem_default = 262144 
net.core.rmem_max = 262144 
net.core.wmem_default = 262144 
net.core.wmem_max = 1048576 

# vi /etc/sysctl.conf 

Set the values and then run :

# sysctl -p 

6) Add Oracle user and groups and create the ORACLE_BASE directory for Oracle Software and database.

# groupadd oinstall 
# groupadd dba 
# useradd -g oinstall -G dba oracle 
# passwd oracle 

e.g
# mkdir -p /u01/app/oracle 
# chown -R oracle:oinstall /u01/app/oracle

7) Set the session limits for Oracle User

Set the following to the /etc/security/limits.conf 
oracle soft nproc 2047 
oracle hard nproc 16384 
oracle soft nofile 1024 
oracle hard nofile 65536

Set the following to the /etc/pam.d/login 
session required pam_limits.so 

Add the following to /etc/profile if Oracle user will use the bash shell.

if [ $USER = "oracle" ]; then 
ulimit -u 16384 
ulimit -n 65536 
fi 


As oracle user

8) Login as Oracle and change the .bash_profile

Add the following in order to begin the installation

export ORACLE_BASE=/u01/app/oracle

export ORACLE_SID=orcl # You can not set the ORACLE_SID if you do not want write now

export DISPLAY=YOUR_XTERM_IP:0 in order to you an X session for the installation

unset ORACLE_HOME

$ . ./.bash_profile

to apply the new settings

9) Put the software for 10gR2 to a desired server directory.

10201_database_linux_x86_64.cpio.gz

10201_companion_linux_x86_64.cpio.gz

p6810189_10204_Linux-x86-64.zip

Unzip the database and run 

$ gunzip 10201_database_linux_x86_64.cpio.gz

$ cpio -idmv < 10201_database_linux_x86_64.cpio

$ cd database

$./runInstaller –ignoreSysPrereqs

Then unzip the companion and run

$ gunzip 10201_companion_linux_x86_64.cpio.gz

$ cpio -idmv < 10201_companion_linux_x86_64.cpio

$ cd companion

$./runInstaller –ignoreSysPrereqs

Finally unzip the 10.2.0.4 Patch Set and run (At the time this post was written only the 10.2.0.4 patch was available): 

$ unzip p6810189_10204_Linux-x86-64.zip

$ cd Disk1

$ ./runInstaller –ignoreSysPrereqs

10) Create the database

Add the following to the .bash_profile

export ORACLE_HOME=[YOUR_ORACLE_HOME]

export PATH=$ORACLE_HOME/bin:.:$PATH

Run 

$ . ./.bash_profile

$ dbca

Create your desired database and you finished

After that run 

$ netca 

To create a listener

ATTENTION if your host name is more than 32 characters then you are about to have the following situation:

If you have choose to install EM and during its creation you get from dbca

ORA-06502: PL/SQL: numeric or value error: character string buffer too small 

then apply the following workaround:

1) Ignore error and continue with database creation

2) After successful finish of dbca do :

Verify the case using sqlplus

SQL> select length(host_name) from v$instance where rownum=1;

LENGTH(HOST_NAME)

-----------------
36

Here the length is 36 (>32) and for 10.2 this is a bug and em repository creation fails.

So

a) Make a backup copy of the file

$ cp $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql 
$ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql_bak

b) Change the l_host_name variable in self_monitor_post_creation.sql to have 128 characters

The l_host_name variable needs to be changed twice in the script.
$ vi $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql

c) Drop and recreate the DB Control configuration files and repository

$ $ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop
$ $ORACLE_HOME/bin/emca -config dbcontrol db -repos create

Check your em by using the URL mentioned.

REFERENCES
Metalink Note ID 421308.1, 335118.1

Source: http://agstamy.blogspot.com/2010/11/installation-of-oracle-10gr2-database.html

The Seven Deadly Habits of a DBA . . and How to Cure Them

The Seven Deadly Habits of a DBA . . and How to Cure Them
by Paul Vallée

Calling widespread bad habits in database administration "deadly" may seem extreme. However, when you consider the critical nature of most data, and just how damaging data loss or corruption can be to a corporation, "deadly" seems pretty dead-on.

Although these habits are distressingly common among DBAs, they are curable with some shrewd management intervention. What follows is a list of the seven habits we consider the deadliest, along with some ideas on how to eliminate them.

Habit #1. THE LEAP OF FAITH: "We have faith in our backup."
Blind faith can be endearing, but not when it comes backing up a database. Backups should be trusted only as far as they have been tested and verified.
Cures:
§ Have your DBAs verify that the backup is succeeding regularly, preferably using a script that notifies them if there's an issue.
§ Maintain a backup to your backup. DBAs should always use at least two backup methods. A common technique is to use those old-fashioned exports as a backup to the online backups.
§ Resource test recoveries as often as is practical. An early sign that your DBA team is either overworked or not prioritizing correctly is having a quarter go by without a test recovery. Test recoveries confirm that your backup strategy is on track, while allowing your team to practice recovery activities so they can handle them effectively when the time comes.

Habit #2. GREAT EXPECTATIONS: "It will work the way we expect it to. Let's go ahead."
Although not user friendly in the traditional sense, Oracle is very power-user friendly— once you've been working with it for a while, you develop an instinct for the way things "should" work. Although that instinct is often right, one of the most dangerous habits any DBA can possess is an assumption that Oracle will "just work" the way it should.
Cures:
§ Inculcate a "practice, practice, practice" mentality throughout the organization. DBAs need to rehearse activities in the safe sandbox of a test environment that's designed to closely mimic the behaviour of the production system. The organization needs to allow the time and money for them to do so.
§ Pair inexperienced DBAs with senior ones whenever possible—or take them under your own wing. New DBAs tend to be fearless, but learning from someone else's experience can help instill some much needed paranoia.
§ Review the plans for everything. It's amazing how often DBAs say, "I've done that a hundred times, I don't need a plan." If they're heading into execution mode, they absolutely need a plan.

Habit #3. LAISSEZ-FAIRE ADMINISTRATION: "We don't need to monitor the system. The users always let us know when something's wrong."
If you depend on the users to inform the DBA team that there's a problem, it may already be too late.
Cures:
§ Install availability and performance monitoring systems so that issues are identified and resolved before they cause service-affecting failures.
§ Avoid post-release software issues by working with developers and testers to ensure that all production-ready software is stable and high-performance.

Habit #4. THE MEMORY TEST: "We'll remember how this happened, and what we did to get things going again."
It may seem impossible that a DBA team would forget a massive procedure that took them weeks to get right, and yet it happens all the time. In order to prevent recurring mistakes and take advantage of gained experience, documentation is essential.
Cures:
§ Require that your DBAs maintain a comprehensive documentation library and activity diary, including a significant level of rationale, syntax, and workflow detail.
§ Provide your team with groupware on your intranet so that these documents become searchable in an emergency.
§ Enforce the discipline of documentation and check it periodically. Ask your DBAs: When was this tablespace created, by whom, and with what SQL? What tasks were performed on a particular day? If they can't answer quickly, you'll know they've gone back to relying on memory.

Habit #5. THE BLAME GAME: "Don't look at me, it's the developer's fault that SQL is in production"
Some DBAs have a real "us versus them" mentality when it comes to developers in their organization. They see themselves not as facilitators helping the developers develop quality code from a database standpoint, but rather as guardians who prevent poor-quality code from making it into production. This might seem like semantics, but a confrontational relationship between developers and DBAs results in a lack of developer initiative and significant slowdowns in release cycles.
Cures:
§ Select DBAs who understand it's their responsibility to work as an integrated team with the developers they support.
§ Cultivate a team attitude by structuring continuous DBA involvement in every project rather than at review milestones.
§ Consider assigning an individual DBA in a developer support role. If it's clearly in the job description, there's more motivation to do it well.

Habit #6. THE SOLO ACT: "I know what I'm doing and don't need any help."
Database administration is increasingly complex and even the most senior DBAs can't possibly know every last detail. DBAs have different specialties, which need to be culled and utilized. When DBAs feel like they know, or should know, everything, they don't ask questions and miss out on valuable knowledge they could be gaining from others.
Cures:
§ Foster a teamwork culture where it's acceptable for DBAs to admit they don't know the answer and to ask for help.
§ Encourage your DBAs to seek out an outside peer group as a forum for brainstorming and testing their assumptions. No single person can match the expertise and experience of even a relatively small group.
§ Provide a safety net of tech resources such as reference materials, courses, and outside experts or consultants on call.

Habit #7. TECHNO-LUST: "Things would work so much better if only we had..."
DBAs are often on top of the latest technology, which can help them do a superlative job. But when the desire for new technology causes DBAs to recommend unnecessary hardware purchases or software add-ons, costs tend to skyrocket quickly—as do problems.
Cures:
§ Never upgrade your hardware infrastructure without first exhausting all tuning opportunities. Remember, ten years ago enormous enterprises were run on servers one-tenth the capacity—all thanks to necessity and skill.
§ Never consent to using advanced or new features until you're well aware of the ongoing maintenance commitment and resulting costs.
§ Watch out for DBA support software that presents friendly GUI interfaces for difficult tasks. This type of interface allows a beginner DBA to act as an intermediate DBA under certain circumstances, but simultaneously prevents that beginner from learning the actual skills behind the tasks. Moreover, these tools tend to hide real risks from the DBA, making potentially damaging activities as easy as point-and-click.

Whether it takes a twelve-step program or one tiny adjustment, all of these deadly DBA habits can be kicked. Of course, the first step is recognizing the problem. By starting with this list and doing a careful inventory of the successes and failures in your team's database administration, you'll be well on your way to finding a cure.

-------------------------------------------------------------------------------------------------
Paul Vallée is the Executive Chairman and Founder of Pythian, a leading global database application and infrastructure services company for Oracle, MySQL and SQL Server.

Toad World PIPELINE Newsletter (May '10 Issue) has my Second Technical Paper

The May 2010 Edition of Toad World PIPELINE Newsletter has published my second Oracle Technical Paper "Database Disaster Recovery using only RMAN Backups".

Infact, they asked me to re-write the paper using Oracle 10g or 11g so that we could relate it with the supported versions. I have chosen Oracle 10g R2 (10.2.0.4) Standard Edition on Windows O/S to demonstrate the same.

Here is the link to Toad World PIPELINE Newsletter (May '10 Issue):
http://www.quest.com/ecard/28941/default.htm

Hope it is a nice read.

Published my Second Oracle Technical Paper

Today, I have successfully published my Oracle Technical Paper titled "Database Disaster Recovery using only RMAN Backups" under My Oracle Support Community (formerly known as Oracle Customer Knowledge Exchange)
Link: https://communities.oracle.com/

The technical paper demonstrates how an Oracle Database can be recovered or reconstructed by using only the RMAN Backup files in case of a complete server crash. On this blog, you know it as "Disaster Recovery using RMAN Backups".

The access to the published document in My Oracle Support Community would require customer logon credentials. So if you are interested to know more about my technical paper, feel free to email me at zk.oracle@gmail.com for the PDF file.

Default Sort Behavorial Change for GROUP BY queries without ORDER BY clause in Oracle 10g Database

After our successful production database migration to 10gR2 (10.2.0.4), we came across a few user complaining about sorting issue in specific screens. On further investigation, we found out that some screens having fields with list of data values were not ordered, as they were in 9iR2.

Upon a little googling we found an interesting case at Jaffar's blog "Change behavior of GROUP BY clause in Oracle 10g.". It seems he had faced the same issues when he migrated from 9i to 10g, and has well documented the case with resolution in his post.

Basically, in 9i, SQL Queries having the GROUP BY clause only had a default behavior of being ordered. These SQLs in 9i appeared sorted (despite any ORDER BY clause) because of the default sorting behavior seen in the execution plan as "SORT GROUP BY".

On the other hand, 10g now has a sorting behavorial change, in which the SQL Queries having the GROUP BY clause only with no ORDER BY clause, there will be no default sorting done, which is seen in the execution plan as "HASH GROUP BY".

The 3 possible resolutions are as follows:

1. Change the affected SQL Queries to embedd ORDER BY Clause, or
2. Set "_gby_hash_aggregation_enabled" = FALSE at session level or system level, or
3. Set optimizer_features_enabled=9.2.0

In our case, as we have 3rd party ERP software, we don't have the source code of most of the affected screens. So, we could not choose the 1st option entirely (except for reports, and backend queries). Choosing the 3rd option defeats to purpose of migrating to 10g as most of the 10g optimizatin features will not be available, but this option was the last resort. We tested out the 2nd option and it worked perfectly fine.

Let me demonstrate how I implemented the 2nd option on my production 10gR2 database.

Before changing "_gby_hash_aggregation_enabled" parameter:

SQL> set autotrace on
SQL> select owner,count(1) from dba_objects where object_type='VIEW' group by owner;

OWNER COUNT(1)
------------------------------ ----------
WKSYS 79
MDSYS 59
DMSYS 1
WK_TEST 6
CTXSYS 54
SYSTEM 12
EXFSYS 56
DBSNMP 1
ORDSYS 5
SYSMAN 136
XDB 2
SYS 2885
WMSYS 112
..... (some element removed)
LBACSYS 57
25 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 651175835

------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------
0 SELECT STATEMENT 69320 1895K 195 (6) 00:00:03
1 HASH GROUP BY 69320 1895K 195 (6) 00:00:03
2 VIEW DBA_OBJECTS 69320 1895K 192 (4) 00:00:03
3 UNION-ALL
* 4 FILTER
* 5 HASH JOIN 85274 7744K 191 (4) 00:00:03
6 TABLE ACCESS FULL USER$ 86 1290 3 (0) 00:00:01
* 7 TABLE ACCESS FULL OBJ$ 85274 6495K 187 (3) 00:00:03
* 8 TABLE ACCESS BY INDEX ROWID IND$ 1 8 2 (0) 00:00:01
* 9 INDEX UNIQUE SCAN I_IND1 1 1 (0) 00:00:01
* 10 TABLE ACCESS BY INDEX ROWID SUM$ 1 26 0 (0) 00:00:01
* 11 INDEX UNIQUE SCAN I_SUM$_1 1 0 (0) 00:00:01
* 12 FILTER
13 NESTED LOOPS 1 28 1 (0) 00:00:01
14 INDEX FULL SCAN I_LINK1 1 13 0 (0) 00:00:01
15 TABLE ACCESS CLUSTER USER$ 1 15 1 (0) 00:00:01
* 16 INDEX UNIQUE SCAN I_USER# 1 0 (0) 00:00:01
------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------
4 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
AND DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNON YM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
(SELECT DISTINCT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B2 AND
BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'SECURITY
PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'WINDOW
GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='VIEW')
5 - access("O"."OWNER#"="U"."USER#")
7 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL)
8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
9 - access("I"."OBJ#"=:B1)
10 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
11 - access("S"."OBJ#"=:B1)
12 - filter(NULL IS NOT NULL)
16 - access("L"."OWNER#"="U"."USER#")

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
31053 consistent gets
2039 physical reads
0 redo size
955 bytes sent via SQL*Net to client
406 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25 rows processed

After changing "_gby_hash_aggregation_enabled" parameter :

SQL> alter system set "_gby_hash_aggregation_enabled" = FALSE scope=both;
System altered.

SQL> select owner,count(1) from dba_objects where object_type='VIEW' group by owner;
OWNER COUNT(1)
------------------------------ ----------
CTXSYS 54
DBSNMP 1
DMSYS 1
EXFSYS 56
LBACSYS 57
MDSYS 59
ORDSYS 5
SYS 2885
SYSMAN 136
SYSTEM 12
WKSYS 79
WK_TEST 6
WMSYS 112
..... (some element removed)
XDB 2
25 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3063327986

------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------
0 SELECT STATEMENT 69320 1895K 195 (6) 00:00:03
1 SORT GROUP BY 69320 1895K 195 (6) 00:00:03
2 VIEW DBA_OBJECTS 69320 1895K 192 (4) 00:00:03
3 UNION-ALL
* 4 FILTER
* 5 HASH JOIN 85274 7744K 191 (4) 00:00:03
6 TABLE ACCESS FULL USER$ 86 1290 3 (0) 00:00:01
* 7 TABLE ACCESS FULL OBJ$ 85274 6495K 187 (3) 00:00:03
* 8 TABLE ACCESS BY INDEX ROWID IND$ 1 8 2 (0) 00:00:01
* 9 INDEX UNIQUE SCAN I_IND1 1 1 (0) 00:00:01
* 10 TABLE ACCESS BY INDEX ROWID SUM$ 1 26 0 (0) 00:00:01
* 11 INDEX UNIQUE SCAN I_SUM$_1 1 0 (0) 00:00:01
* 12 FILTER
13 NESTED LOOPS 1 28 1 (0) 00:00:01
14 INDEX FULL SCAN I_LINK1 1 13 0 (0) 00:00:01
15 TABLE ACCESS CLUSTER USER$ 1 15 1 (0) 00:00:01
* 16 INDEX UNIQUE SCAN I_USER# 1 0 (0) 00:00:01
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
AND DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNON
YM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA
CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
(SELECT DISTINCT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B2 AND
BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'SECURITY
PROFILE',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'WINDOW
GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP','UNDEFINED')='VIEW'
5 - access("O"."OWNER#"="U"."USER#")
7 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL)
8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
9 - access("I"."OBJ#"=:B1)
10 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
11 - access("S"."OBJ#"=:B1)
12 - filter(NULL IS NOT NULL)
16 - access("L"."OWNER#"="U"."USER#")

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
31051 consistent gets
0 physical reads
0 redo size
949 bytes sent via SQL*Net to client
406 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
25 rows processed

We checked our affected screens in the system, and all of them are doing fine!!!!