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 canvas with item type as ‘List Item’ – or, in my case I changed the […]
Read More Oracle Forms Dropdown List Item values populated from database tableThis 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’)
Read More Service seeddata.regress.rdbms.dev.us.oracle.com is DownIn 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 hostPurge 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 statsComments 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 systemResult 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 parametersHere 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 SQLPlain text email: cat << EOF | /usr/sbin/sendmail -t to: myemail@mysite.com from: “no reply” (Automated Processor) subject: My subject My email body. EOF Or HTML formatted email: cat
Read More Script to send email unix with sendmailFind out what directories are using up disk space in unix with this command: du -sk * | sort -n I often put it into a function so I can call it quickly: g () { du -sk * | sort -n } df -h (df -k on old machines) shows the filesystem disk utilisation.
Read More du -sk to identify space usage unixSee the SQL create definition of a materialized view by running this SQL: set long 200000 pages 0 lines 131 doc off column txt format a121 word_wrapped spool recreate_sql select dbms_metadata.get_ddl(‘MATERIALIZED_VIEW’,’my_mv_name’,’my_owner’) txt from dual; spool off set pages 9999 Look especially for the refresh option, e.g.: REFRESH FORCE ON DEMAND NEXT null There are three […]
Read More Materialized Views with dbms_metadata.get_ddl