Monthly Archives: May 2010

Oracle Forms Dropdown List Item values populated from database table

Posted on 27 May 2010 by Andrew Fraser

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 seeddata.regress.rdbms.dev.us.oracle.com is Down

Posted on 21 May 2010 by Andrew Fraser

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

Posted in Grid control | 1 Comment

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

Posted on 21 May 2010 by Andrew Fraser

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

Posted on 13 May 2010 by Andrew Fraser

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

Posted on 13 May 2010 by Andrew Fraser

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

Posted on 5 May 2010 by Andrew Fraser

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

Posted on 3 May 2010 by Andrew Fraser

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