Monthly Archives: May 2010

Oracle Forms Dropdown List Item values populated from database table

Popup LOV boxes that retrieve their list from a database table are relatively easy to set up in oracle forms, the equivalent for a dropdown list item takes a bit more work. 1) First, create a new item in the … Continue reading

Posted in Oracle forms | 6 Comments

Service is Down

This alert in Oracle Enterprise Manager Grid Control: Service is Down Was brute-force fixed for me by running this on the target database: SQL> exec dbms_service.delete_service(‘’)

Posted in Grid control | 1 Comment

Metric Collection Error oracle.sysman.emSDK.emd.comm.CommException: Cannot establish proxy connection: 503 Service Unavailable 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 … Continue reading

Posted in Grid control | 3 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 … Continue reading

Posted in Stats | Leave a comment

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=’*’ ;

Posted in Uncategorized | 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 … Continue reading

Posted in Performance tuning, PL/SQL | Leave a comment

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 … Continue reading

Posted in Scripts, Space | Leave a comment