Metric Collection Error oracle.sysman.emSDK.emd.comm.CommException: java.io.IOException: Cannot establish proxy connection: 503 Service Unavailable java.net.NoRouteToHostException: No route to host

In Oracle Enterprise Manager Grid Control, the Agents, Hosts, Listeners were all displaying correctly with status of ‘up’. But the databases reported status of “Metric Collection Error”. When I tried to configure a database, the configure database page displayed either one of these errors at top of page: oracle.sysman.emSDK.emd.comm.CommException: java.io.IOException: Cannot establish proxy connection: 503 […]

Read More Metric Collection Error oracle.sysman.emSDK.emd.comm.CommException: java.io.IOException: Cannot establish proxy connection: 503 Service Unavailable java.net.NoRouteToHostException: No route to host
May 21, 2010

5 Comments

Purge and regather all optimizer stats

Purge and regather all optimizer stats with: exec dbms_stats.delete_database_stats exec dbms_stats.delete_system_stats exec dbms_scheduler.run_job(‘GATHER_STATS_JOB’) That works provided statistics level has not been changed to none show parameter statistics_level You can gather more detailed system stats with: exec dbms_stats.gather_system_stats(‘START’) exec dbms_stats.gather_system_stats(‘STOP’) You can see the system stats with: set pages 9999 lines 112 col pval2 form a20 […]

Read More Purge and regather all optimizer stats
May 13, 2010

Comments in spfile alter system

Comments are used much less often in spfile than they were in old init.ora’s. Syntax for them is like: SQL> alter system set pga_aggregate_target=1g comment=’Andrew Fraser 13-May-2010 was 390m’ scope=spfile sid=’*’ ;

Read More Comments in spfile alter system
May 13, 2010

4 Comments

PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters

Result cache functions are a good new feature in Oracle 11g, but they only work with normal functions. A function that returns multiple outputs using OUT parameters cannot be made a result cache function, at least not in 11gR1. Attempting to do so fails with this error: “PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is […]

Read More PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters
May 5, 2010

Move objects including lobs, xml to new tablespace with dynamic SQL

Here objects are moved from tablespace users to tablespace users1: set pages 9999 lines 132 spool m2.sql select ‘alter table ‘||owner||’.’||table_name||’ move lob(‘||column_name||’) store as ( tablespace users1);’ from dba_lobs where tablespace_name = ‘USERS’ order by 1 / select ‘alter table ‘||owner||’.’||segment_name||’ move tablespace users1;’ from dba_segments where tablespace_name = ‘USERS’ and segment_type = ‘TABLE’ […]

Read More Move objects including lobs, xml to new tablespace with dynamic SQL
May 3, 2010