After a migration of database to major performance problem(slowness) was observed.When the database was on 9i, stats were gathered weekly using dbms_stats.gather_schema_stats package for the application specific schemas. exec dbms_stats.gather_schema_stats(ownname =>'ABC',cascade => true,degree =>3); The above was scheduled in the crontab to be run every saturday.After the database was migrated successfully, the crontab … Continue reading DBMS_STATS.GATHER_SCHEMA_STATS Failing in


Silly and Interesting V – “/ as sysdba” insufficient privileges

During a server migration activity(higher version of Sun Solaris Box), Oracle 9i was installed on new server,patched to and the cold backup of database was restored.Oracle 9i installation was done with user "oracle" and group "dba".For Oracle 10g installation user "ora10g" and group "dba10" was created.During the installation "dba10" was provided as the OS … Continue reading Silly and Interesting V – “/ as sysdba” insufficient privileges

Mview Log – Size Increasing !!!!!

Recently while checking the segment's size for a particular schema, i noticed a materialized view log (MLOG$_TABLE_NAME) occupying almost 10Gb of space. What is materialized view log? From the Oracle document :- When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then … Continue reading Mview Log – Size Increasing !!!!!